Business Intelligence Utopia – Dream to Reality: Key Enablers

In the last post, I discussed my view of BI Utopia in which information is available to all stakeholders at the right time, in the right format enabling them to make actionable decisions at both strategic & operational levels. Having said that, the BI street is not paved with gold.

I consider the following key enablers as pre-requisites to achieve true ‘Information Democracy’ in an enterprise. The “Power of Ten” includes:

1. Proliferation of agile, modular & robust transaction processing systems.
2. 
   
3. Real Time Data Integration Components
4. 
   
5. Strong Data Governance structure
6. 
   
7. Service Oriented Architecture
8. 
   
9. Extensible Business centric Data Models
10. 
    
11. Flexible business rules repositories surrounded by clean metadata/reference data environments
12. 
    
13. Ability to integrate unstructured information into the BI architectural landscape
14. 
    
15. Guided context-sensitive, user-oriented analytics
16. 
    
17. Analytical models powered by Simulations
18. 
    
19. Closed loop Business Intelligence Utopia
20. 
    

Each of these units comprising the “Power of Ten” is a fascinating topic on its own. We will drill-down and focus on some of the salient features of each of these areas in the coming weeks.

LifeSaver of the Week -2

CLIENT_INFO

Pinpointing PeopleSoft user sessions to Oracle session is vital for troubleshooting performance related issues. Previously, I used some crude techniques (using the V$SESSION.STATUS =
‘ACTIVE’ and V$SESSION.PROGRAM) to map the PeopleSoft session to the Oracle session. This was not always accurate in a production system where multiple user sessions are active at the same time and running the same program. I came across V$SESSION.CLIENT_INFO and it made my life so much easier…

PeopleSoft populates CLIENT_INFO by default for all 2-tier sessions, COBOL, SQR and Process Scheduler. However, for 3-tier connections we need to enable ‘EnableDBMonitoring = 1’ in the psappsrv.cfg configuration file.

Now, if I get a call from a user having performance problem running the custom report AABC123.sqr then I can log on to SQLPLUS and run the following SQL to determine what the session for OPRID NPAI is currently doing.

SELECT SQL_TEXT FROM V$SQLTEXT

WHERE ADDRESS = (SELECT SQL_ADDRESS FROM V$SESSION

WHERE STATUS = ‘ACTIVE’ AND UPPER(PROGRAM) LIKE ‘SQR%’

AND CLIENT_INFO LIKE ‘NPAI%’)

ORDER BY PIECE

/

Note:

PeopleSoft also delivers a function GET_PS_OPRID which can be used to determine the OPRID. So if you have that function created in your database then you can use the below SQL to determine the OPRID.

SELECT GET_PS_OPRID(client_info) FROM V$SESSION WHERE …

Alternatively you can use -

SELECT SUBSTR(client_info,1,INSTR(client_info,’,') – 1) FROM V$SESSION WHERE …

Read more about LifeSaver.

LifeSaver Series: LifeSaver of the Week–1

Every seasoned DBA has his bag of tips and tricks. I have such a bag which I will open and share with you all one code/script/tip/trick at a time. I am calling this new series‘LifeSaver of the Week’ because these scripts actually save life (most of the time) during crunch time. There have been occasions when I had a high priority ticket breathing down my neck and pulling the right trick helped identify the problem instantly.

The LifeSaver series will include tips relevant to Oracle or PeopleSoft or UNIX.

Here is the first one to kick-off the series.

”LifeSaver of the Week – 1”

LAST_CALL_ET:

A few years back, I was trying to build a script to identify long running SQL’s. The script was supposed to check for any SQL running for more than 5 mins and notify me if it found any such SQL. I was trying different methods to determine the optimal approach to build this script. I came across the column LAST_CALL_ET present in V$SESSION. The data in this column shows in seconds the time since last call. Below is the SQL I used in the script.

SELECT A.SQL_TEXT, B.PROGRAM, B.CLIENT_INFO

FROM V$SQLTEXT A, V$SESSION B

WHERE A.ADDRESS = B.SQL_ADDRESS

AND B.STATUS = ‘ACTIVE’

AND B.LAST_CALL_ET > 300

AND B.USERNAME IS NOT NULL

ORDER BY A.PIECE

Read more about LifeSaver.

Data Integration Challenge – Facts Arrive Earlier than Dimension

The fact transactions that come in earlier than the dimension (master) records are not bad data, such fact records needs to be handled in our ETL process as a special case. Such situations of facts coming in before dimensions can occur quite commonly like in case of a customer opening a bank account and his transactions starting to flow into the data warehouse immediately.

But the customer id creation process from the Customer Reconciliation System can get delayed and hence the customer data would reach the data warehouse after few days.

How do we handle this scenario differs based on the business process being addressed, there could be two different requirements

• Make the fact available and report under “In Process” category; commonly followed in financial reporting systems to enable reconciliation
• Make the fact available only when the dimension is present,; commonly followed in status reporting systems

Requirement 1: Make the fact available and report under “In Process” category

For this requirement follow the below steps

1. Insert into the dimension table a record that represents a default or ‘In Process’ status like in case of the banking example the Customer Dimension would have a ‘default record’ inserted that represents the information that the customer detail has not yet arrived
2. 
   
3. In the ETL process while populating the Fact table, for the transactions that do not have a corresponding entry in the Dimension table, assign a default Dimension key and insert into the Fact. In the same process insert the Dimensions Lookup values into a ‘temporary’ or ‘error’ table
4. 
   
5. Build an ETL process that checks the new records inserted into the Dimension table, queries the temporary table and identifies the records in facts for which the dimension key has to be updated and updates the respective fact’s dimension key
6. 
   

Requirement 2: Make the fact available only when the dimension is present

For this requirement follow the below steps

1. Build an ETL process that populates the fact into a staging table
2. 
   
3. Build an ETL process that pushes only the records that have a dimension value to the data warehouse tables
4. 
   
5. At the end of ETL process delete all the processed records from the staging table making the other unprocessed records available to be pulled next time

You can read more about  Data Integration Challenge

Business Intelligence: Gazing at the Crystal Ball

Circa 2015 – 8 years from now

CEO of a multinational organization enters the corner office overlooking the busy city down below. On flicking a switch near the seat, the wall in front is illuminated with a colorful dashboard, what is known in CEO circles then, as the Rainbow Chart.

The Rainbow Chart is the CEO’s lifeline as it gives a snapshot of the current business position (the left portion) and also figures/colors that serves as a premonition of the company’s future (the right portion).

The current state/left portion of the dashboard, on closer examination, reveals 4 sub-parts. On the extreme left is the Balance Sheet of the business and next to it is the Income statement. The Income statement has more colors that are changing dynamically as compared to the Balance sheet. Each line item has links to it, using which the CEO can drill down further to specific geographies, business units and even further to individual operating units. The third part has the cash flow details (the colors are changing far more rapidly here) and the fourth one gives the details on inventory, raw materials position and other operational details.

The business future state/right portion of the dashboard has a lot of numbers that can be categorized into two. The first category is specific to the business – Sales in pipeline, Revenue & Cost projections, Top 5 initiatives, Strategy Maps etc. and the second category are the macroeconomic indicators across the world. At the bottom of the dashboard is a stock ticker (what else?) with the company’s stock prices shown in bold.

All these numbers & colors change in real-time and the CEO can drill up/down/across/through all the line items. Similar such dashboards are present across the organization and each one covers details that are relevant for the person’s level and position in the company.

This in essence is the real promise of BI.

Whether it happens in 2015 or earlier (hopefully not later!) can be speculated but the focus of the next few blogs from my side will zero-in on some of the pre-requisites for such a scenario – The  Business Intelligence Utopia!

Business Intelligence @ Crossroads

Business Intelligence (BI) is well & truly at the crossroads and so are BI practitioners like me. On one hand there is tremendous improvement in BI tools & techniques almost on a daily basis but on the other hand there is still a big expectation gap among business users on Business Intelligence’s usage/value to drive core business decisions.

This ensures that every Business Intelligence (BI) practitioner develops a ’split’ personality – a la Jekyll and Hyde, getting fascinated by the awesome power of databases, smart techniques in data integration tools etc. and the very next moment getting into trouble with a business user on why ‘that’ particular metric cannot be captured in an analytical report.

For the BI technologists, there is never going to be a dull moment in the near future. With all the big product vendors like Microsoft, Oracle, SAP etc. throwing their might behind BI and with all the specialty BI product vendors showing no signs of slowing down, just get ready to join the big swinging party.

For the business users, there is still the promise of BI that is very enticing – ‘Data to Information to Knowledge to Actions that drive business decisions’. But they are not giving the verdict as of now. Operational folks are really not getting anything out of BI right now (wait for BI 2.0) and the strategic thinkers are not completely satisfied with what they get to see.

The techno-functional managers, the split personality types are the ones in the middle trying to grapple with increasing complexity on the technology side and the ever increasing clamor for insights from the business side.

Take sides right away – there is more coming from this space on the fascinating world of Business Intelligence.

Perils of DataMover Access – 3

The story is a bit different in a non-production environment. Depending on your organization change control policies, developers might have Data Mover Access in non-production. In this case, we might want to prevent the OPRID from exploiting Data Mover Access to perform undesired DDL on the database.

To tackle this problem, you can create a DDL trigger as shown below. This will ensure that no DDL operations are performed from Data Mover.

CREATE OR REPLACE TRIGGER DATAMOVER_PREVENT_DDL

BEFORE CREATE OR ALTER OR DROP OR GRANT OR RENAME OR REVOKE ON SCHEMA

DECLARE

VAR_DDLEVENT VARCHAR2(25);

VAR_OBJ_NAME VARCHAR2(128);

V_AUDIT_OPRID VARCHAR2(32);

BEGIN

DBMS_APPLICATION_INFO.READ_CLIENT_INFO(V_AUDIT_OPRID);

SELECT ORA_SYSEVENT, ORA_DICT_OBJ_NAME, GET_PS_OPRID(V_AUDIT_OPRID)

INTO VAR_DDLEVENT, VAR_OBJ_NAME, V_AUDIT_OPRID FROM DUAL;

IF ( VAR_DDLEVENT IN (‘CREATE’,'ALTER’,'DROP’, ‘GRANT’, ‘RENAME’, ‘REVOKE’) AND V_AUDIT_OPRID != ‘!NoOPRID’)

THEN

RAISE_APPLICATION_ERROR(-20001,’**** THIS OPERATION IS NOT ALLOWED ****’);

END IF;

END;

Now, if the developer with Data Mover Access tries to grant his Oracle id DBA access then he will get the below message.

Conclusion
We have seen how dangerous Data Mover Access can be if controls are not in place. This access is often overlooked and can have serious implications. The best approach will need to start with cleaning the privileges assigned to the access id. The PeopleSoft access id should have only the required access. Do not go overboard and assign DBA role to the access id.