In the star-schema based data modeling, it is not very often to turn on slowly change dimension as type 2, while it will bring some advantage to feed some requirements such as ‘show me the history change for one business group folks’, ‘show me the revenue and cost incurred by the employees at point of time’, etc. All of these require tracking records for the persons. While in this short post I would like to share another type of requirement which is built upon SCD type 2 and my approach of how to resolve the issues – it may not be perfect but will give the OBIEE developers a quick way to GO.
SCD Type 2 in Employee Dim
Just to bring some basic concepts of slowly change dimension, this is common model that exists in any BI products whatever SAP, IBM or MS platform. In my example we implement it in the Oracle BI suite.
Each employee has at least one record in EDW. If the project decides to turn on SCD type 2, the simple way is go to DAC and change the flag to ‘Y’ as the ETL logic supports both SCD type 1 and type 2. If you happen to look into such piece ETL mapping, it will be pretty complex as Oracle consolidate the implementation of all use cases together. When the candidate joins in the company, a new Employee dim record will be created with Active Flag ‘Y’, while his/her major information are changed like job title, there probably be a new row created with ‘Y’ and the previous one active flag changed to ‘N’; When he/she terminates employment, a new active record in type of Ex-employee created and all of previous ones changed to ‘N’. In addition, there are always date fields Effective Start/End date populated to reflect the history change and date range. Example as below.
As-is reporting Behavior and new Requirement
Note that there are different IDs in the DW. When the employee produce the facts in different time periods, it is actually linked to separate IDs – the cost happens during 2000 and 2002 will link to 1 and after that it will link to 2 or ever 3. If we export the employee and his incurred facts on report with a prompt called ‘Resource Active Flag’, it becomes very strange. When user selects ‘Y’ in this prompt, the report will be listed out all facts linked to ID 3. This partial data visualization is not the user’s expectation because it includes the person who has already terminated. The user expects to see all transactions for the person who is Currently Active when he selects ‘Y’, on the other hand, if the user chooses ‘N’, he wants to see all transaction to Ex-employees.
Implementation in OBIEE RPD and Analysis
To resolve above mentioned issues, we should not join the Employee dim with fact directly, instead we can build a new extension flag to produce value for each ID in DW.
1.Create View ‘Dim_W_EMPLOYEE_D_Current_Resource_Active_Flag’ in Physical Layer to utilize current active status by determining from Employee type.
The query is following placed in cacheable Default In tialization String:
SELECT ROW_WID, INTEGRATION_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, FULL_NAME,
(CASE WHEN NVL(UPPER(X_PERSON_TYPE),’NA’) IN (‘EX-SUB CONTRACTOR’,’EX-EMPLOYEE’) THEN ‘N’ ELSE ‘Y’ END) X_CURRENT_ACTIVE_FLAG
FROM OBIDW.W_EMPLOYEE_D WHERE CURRENT_FLG = ‘Y’
With this query we won’t change data grain or skip any record.
2.Since our purpose is to retrieve flag value, we don’t need to create new employee related Dim. Besides the field ‘Active Flag’, we also need to add ‘Employee Current Active Flag’ under ‘Dim – Employee’ and map to Dim_W_EMPLOYEE_D_Current_Resource_Active_Flag.X_CURRENT_ACTIVE_FLAG.
3.In the presentation and analysis layer, it’s quite simple. Just bring the field ‘Employee Current Active Flag’ to those places.
Now when the user selects the ‘Y’/’N’ in the drop-down prompt, he will pick all unlimited transaction facts data associated with the person. I ever thought about the possibility to implement it within existing Employee physical table without an extra view but it is pretty hard to achieve. If you have other ideas on the technical implementation, feel free to share.
hi,
can’t we create current_active_flg when we create Dim_W_EMPLOYEE_D from Dim_W_EMPLOYEE_DS (we should not use Dim as _D tell that it is a dimension table) and create one _D table only in BMM layer, awaiting ur response on this.
I think instead of this we can use look up table functionality.