Business Intelligence Utopia – Enabler 2: Real Time Data Integration

Business Intelligence practitioners tend to have lot of respect and reverence for transaction processing systems (OLTP), for without them the world of analytical apps simply does not exist. That explains my previous blog in introducing the first enabler for BI Utopia – The Evolution of OLTP systems to support Operational BI.

In this post, I introduce the second enabler in the “Power of Ten” – Real Time Data Integration

Data Integration in the BI sense, is all about, extracting data from multiple source systems, transforming them using business rules and loading it back into data repositories built to facilitate analysis, reporting, etc.

Given that the raw data has to be converted to a different form more amenable for analysis & decision-making, there are 2 basic questions to be answered:

1. From a business standpoint, how fast should the ‘data-information’ conversion happen?
2. From a technology standpoint, how fast can the ‘data-information’ conversion happen?
3. 
   

Traditionally, BI being used more for strategic decision-making,  batch mode of data integration with periodicity of a day or later, was acceptable. But increasingly, businesses demand that the conversion has to happen much faster and technology has to support it. This leads to the concept of “Real Time BI” or more correctly "RightTime Data Integration"

Since the answer to the first question “How Fast” is fast becoming “as fast as possible”, the focus has shifted to the technology side. One area where I foresee a lot of activity, from a Data Warehouse architectural standpoint, is in the close interaction of messaging tools like IBM Websphere MQ etc. with data integration tools. At this point in time, though the technology is available, there aren’t too many places where messaging is embedded into the BI architectural landscape.

Bottom-line is that there is significant value gained by ensuring that raw business data is transformed to information by the BI infrastructure, as fast as possible – the limits being prescribed by business imperatives. The best explanation I have come across to explain the value of information latency is the article by Richard Hackathorn .

Active Data Warehousing is another topic closely related to Real Time Data Integration and you can get some perspective on it thro’ the blog on Decision management by James Taylor:

Demystifying PSPROJECTITEM -Part 2

Continuing from my previous post, here are the steps to load the upg.sqc data relevant for our manipulation into the Oracle database.

1. Create record HX_COMP_TBL in App Designer as shown below.

2. Build/create the table
3. Add it to Query Security if you need to access it using PeopleSoft Query Manager.
4. Create Download upg_sqc.txt file
5. Create SQL*Loader control file as shown below.
load data
infile ‘/export/home/npai/psprojectitem/upg_sqc.txt’
into table PS_HX_CMP_TBL
fields terminated by ” ” optionally enclosed by ‘”‘
(OBJ_DESCR2, OBJNAME, VAL1)
6. Execute sqlldr command to load the file
sqlldr sysadm/sysadm@ftest control=sqlld.ctl
7. Use it in query through Query Manager or SQLPLUS

Read More about  Peoplesoft

Race to Victory

In the world of Formula One Racing, ‘Pit Stop’ is a common term. During the race day and pit stops, One could see almost all the components of the car being maintained/changed by the crew . Ranging from the nuts and bolts to the engine, everything is under quick and fast maintenance. Similarily, in the corporate world – each and every company acts as a race car and to improve their performance and to grow quicker: they are in need of such Pit Stop Crew. In the real-time, it would contain a variety of software products that supports their Company Resources which consists their Human Resources, Material Resources, Financial Resources, etc.. To be specific products like Peoplesoft, SAP, Kronos, OrgPlus, SumTotal, Docent, Optim, and IDE’s like Application Designer, Docent Outliner, Business Intelligence Development Studio, JBuilder, NetBeans are widely used. Lets see what makes the companies to choose a product and how it plays a unique role in enhancing the overall companies performance. We will also come across the differences and unique features of the products that urges the companies to use it. Pit Stop will cover the product specific comparisons, Technology specific advantages, and finally the service oriented architecture. 
Read More about  Race to Victory

Peoplesoft Time and Labor Vs Kronos Workforce TimeKeeper

Couple of weeks back, the google news portal was crawling in almost many a news source pages about Kronos and its upperhand than that of the renowned Peoplesoft’s Time and Labour Module. I was surprised to see the once market leader in Human Resources is being overthrown by the Kronos Workforce Central Suite. Yes, the reason being ,.. Kronos had worked hardly in identifying the end user’s concerns and various other problems that they were facing with Peoplesoft. For example : The Time and Labour module contains a feature in which the employee work hours are being tracked and fed back into the Payroll module for the Pay Calculations and Pay Cheque creations. Its found that ” after the fact / historical changes “ was not possible to follow with Peoplesoft whereas Kronos Workforce Time Keeper module supports the same. Many a peoplesoft clients are retaining their payroll in peoplesoft and are moving towards the product kronos for one of this reason.
Read More about  Peoplesoft And kronos

Demystifying PSPROJECTITEM – Part 1

As a DBA, I do not always trust Application Designer when it comes to following tasks.

• Merging projects – typical example is migration of 2 or more bundles using a merged project.
• Quickly changing the update flags based on KEEP/DROP decisions on a huge project
• Any other manipulations

Interpreting Data in PSPROJECTITEM can help you get one step closer to sailing through some of the above mentioned problems.
The following columns provided me with significant information to understand the data in PSPROJECTITEM table.

Column Name

PROJECTNAME

OBJECTTYPE

OBJECTVALUE1

OBJECTVALUE2

OBJECTVALUE3

OBJECTVALUE4

SOURCESTATUS

TARGETSTATUS

UPGRADEACTION

TAKEACTION

COPYDONE

The translation for OBJECTTYPE, SOURCESTATUS, TARGETSTATUS, and UPGRADEACTION are not present in the PeopleSoft database (unlike application translates which are available in PSXLATITEM). The translation for these columns exists in delivered upg.sqc.

In my next post, I will share with you a technique I use to quickly decode the data instead of having to refer back to upg.sqc.

How to monitor PeopleSoft process scheduler for availability?

There are many approaches to monitor the PeopleSoft process scheduler. One of the options that come to mind is to have a script running on the OS hosting the process scheduler to monitor the OS process. But having process schedulers running on multiple OS (PSUNX and PSNT) will need development of scripts running on both servers.

My preferred method is to use the below SQL to monitor all the process schedulers running on the database.

SELECT A.SERVERNAME, B.XLATSHORTNAME,B.FIELDNAME,B.FIELDVALUE,TO_CHAR(B.EFFDT,’YYYY-MM-DD’)

FROM PSSERVERSTAT A, PSXLATITEM B

WHERE B.EFFDT =

(SELECT MAX(B_ED.EFFDT) FROM PSXLATITEM B_ED

WHERE B.FIELDNAME = B_ED.FIELDNAME

AND B.FIELDVALUE = B_ED.FIELDVALUE

AND B_ED.EFFDT <= SYSDATE)

AND B.FIELDNAME = ‘SERVERSTATUS’

AND B.FIELDVALUE = A.SERVERSTATUS

Depending on the results of the above SQL you can trigger an email notification or any other form of action.

Read More about  Peoplesoft

Data Integration Challenge – Identifying changes from a table by a Scratch

In scenarios when a table in the staging area or in the data warehouse needs to be queried to find the changed records (inserted or updated), we can use the Scratch table design. Scratch table is a temporary table that can be designed to hold the changes happening against a table, once the changes are noted by the required application or process then the Scratch table can be cleaned-off.

The process to capture the changes and the clean up would be designed as part of ETL process. The scenarios where to use this concept and the steps to use the Scratch table is discussed below:

Steps to use Scratch table

1. Create a Scratch table ‘S’ of structure to hold the Primary Key column value from the table ‘T’ that needs to the monitored for changes
2. 
   
3. In the ETL process that loads the table ‘T’ add the logic in such way that while inserting or updating a record into table ‘T’ we insert the Primary Key column values of the record into the Scratch table ‘S’
4. 
   
5. If required while inserting the record into the Scratch table ‘S’ have a flag column that says ‘Insert’ or ‘Update’
6. 
   
7. Any process that needs to find the changes would join the Scratch table ‘S’ and the table ‘T’ to pull the changed records, if it just needs the key directly access ‘S’
8. Once the changes have been pulled and processed, have a process that would clean up the Scratch table
9. 
   
10. We can also bind the Scratch table ‘S’ to be always loaded to the memory for higher performance
11. 
    

When to use Scratch table

1. When we have a persistent staging area, using Scratch table would be ideal choice to move the changes to the data warehouse
2. 
   
3. When the base table ‘T’ is really huge and only few changes happen
4. 
   
5. When the changes (or the Primary Key values) in table ‘T’ are required by multiple processes
6. 
   
7. When the changes in table ‘T’ is to be joined with other tables i.e., now the Scratch Table ‘S’ can be used as the driving table in joins with other tables which would give better performance since the Scratch table would be thinner with few records
8. 
   

Alternate Option: Having a flag or a timestamp column in the table ‘T’ and having an index on it. Having an index on Timestamp is costly and a bit map index on the flag column may be seen as an option, but the aspect of updating the column during updates, huge volume and in scenarios of joining with other tables this would be a disadvantage, have seen Scratch table to be a best option. Let me know the other options you have used to handle such situations…

To add more variety to your thoughts, you can read it More Data Integration Challenge