Dimension Hierarchy Tables and Slowly Changing Dimensions in the Oracle Business Analytics Warehouse
The blog posts I wrote earlier in the week about ragged hierarchies and bridge tables got me thinking about how these things are represented in the Oracle Business Analytics Warehouse (OBAW), the pre-defined data warehouse that comes with the Oracle BI Applications. The OBAW comes with support for ragged hierarchies in the form of dimension hierarchy tables, I also thought I'd spotted some bridge tables in there, and I've been meaning to look at how it supports slowly changing dimensions, and so I fired up my virtual machine and took a look.
The OBAW comes with a number of tables with the _DH suffix, which complement the usual set of tables with _D (dimension), _F (fact), _A (aggregate), _DS (dimension staging) and so on. Listing out the table names in SQL*Plus, the full set in BI Apps 7.9.5 is:
SQL> select table_name 2 from user_tables 3 where table_name like '%_DH' 4 / TABLE_NAME ------------------------------ W_ALIGNVER_DH W_AGREEMENT_DH W_ALIGNMT_DH W_INDUSTRY_DH W_INT_ORG_DH W_PRODUCT_DH W_POSITION_DH W_MED_PLAN_DH W_OPTY_DH W_FUND_DH W_GEO_REGION_DH W_PRODCAT_DH W_PERIOD_DH W_ORG_DH W_REGN_DHSo what are these hierarchy tables for, given that there's already a bunch of dimensions defined in the business model and mapping layer of the repository, with their own hierarchies, levels and so on?
The clue is in the level-based bit. What these _DH dimension hierarchy tables are for is to flatten-out the ragged hierarchies that might be in your source system, in this case Peoplesoft, so that your internal parent-child organizational hierarchy relationships can be represented in the level-based way that OBIEE requires them. These are then used for reports that drill-down through the hierarchy (making use of regular dimension and hierarchy objects in the repository), and other dimension history tables such as W_POSITION_DH are used within the repository to secure fact table information to just staff members and their superiors.
Let's take a look at the structure of the W_INT_ORG_DH table, and the W_INT_ORG_D table that's the primary source for the other logical table source that this logical dimension table uses, to see how this works.
Looking at the W_INT_ORG_D table first, you can see that it contains the list of organizational units within the business
SQL> SELECT row_wid
2 , org_num
3 , org_name
4 , effective_from_dt
5 , effective_to_dt
6 , current_flg
7 from w_int_org_d
8 where rownum < 10
9 /
ROW_WID ORG_NUM ORG_NAME EFFECTIVE EFFECTIVE C
---------- ------- ------------------------------ --------- --------- -
0 01-JAN-01 Y
6001 0 Setup Business Group 01-JAN-99 01-JAN-14 Y
6002 202 Vision Corporation 01-JAN-99 01-JAN-14 Y
6003 204 Vision Operations 01-JAN-99 01-JAN-14 Y
6004 205 Manufacturing 01-JAN-99 01-JAN-14 Y
6005 206 Widgets Product Line 01-JAN-99 01-JAN-14 Y
6006 207 Seattle Manufacturing 01-JAN-99 01-JAN-14 Y
6007 208 Chicago Subassembly Plant 01-JAN-99 01-JAN-14 Y
6008 209 Boston Manufacturing 01-JAN-99 01-JAN-14 Y
9 rows selected.
Looking at the names of the organization units, you can probably guess that some are higher in the organizational hierarchy than the others. For many organizations their organization hierarchy is stored in their source systems in a parent-child fashion and can often be unbalanced, and the ETL routine that prepares the internal organization hierarchy in the BI Apps anticipates this, initially storing this parent-child hierarchy in the W_INT_ORG_DH_TMP temporary table, with one row per organization unit per source system hierarchy, like this:
SQL> SELECT org_id 2 , org_num 3 , org_name 4 , par_org_id 5 , par_org_num 6 , par_org_name 7 , hierarchy_name 8 FROM w_int_org_dh_tmp 9 WHERE org_num between 0 and 209 10 ORDER BY org_id 11 / ORG_ID ORG_NUM ORG_NAME PAR_ORG PAR_ORG PAR_ORG_NAME HIERARCHY_NAME ------- ------- ------------------------------ ------- ------- ---------------------- ------------------------------ 0 0 Setup Business Group 202 202 Vision Corporation 204 204 Vision Operations 202 202 Vision Corporation Vision Global Reporting 204 204 Vision Operations 202 202 Vision Corporation US Commercial Sales (DBI) 204 204 Vision Operations 202 202 Vision Corporation Global 204 204 Vision Operations 202 202 Vision Corporation Primary Reporting Hierarchy 204 204 Vision Operations 205 205 Manufacturing Vision Corp EBI Hierarchy 204 204 Vision Operations 202 202 Vision Corporation Assets Hierarchy 204 204 Vision Operations 202 202 Vision Corporation Single Establishment Reporting 204 204 Vision Operations 260 260 Vision Administration PJI 204 204 Vision Operations 202 202 Vision Corporation Vision Corp - Global 204 204 Vision Operations 458 458 Vision Services Burden Schedule Hierarchy 204 204 Vision Operations 202 202 Vision Corporation US DBI Hierarchy 205 205 Manufacturing 202 202 Vision Corporation Vision Global Reporting 205 205 Manufacturing 204 204 Vision Operations Single Establishment Reporting 205 205 Manufacturing 204 204 Vision Operations Primary Reporting Hierarchy 205 205 Manufacturing 202 202 Vision Corporation Vision Corp EBI Hierarchy 205 205 Manufacturing 204 204 Vision Operations US Commercial Sales (DBI) 205 205 Manufacturing 204 204 Vision Operations US DBI Hierarchy 205 205 Manufacturing 202 202 Vision Corporation Vision Corp - Global 206 206 Widgets Product Line 1615 1615 Widget Companies Vision Global Reporting 206 206 Widgets Product Line 1615 1615 Widget Companies Vision Corp - Global 206 206 Widgets Product Line 205 205 Manufacturing Primary Reporting Hierarchy 206 206 Widgets Product Line 205 205 Manufacturing Single Establishment Reporting 207 207 Seattle Manufacturing 204 204 Vision Operations Global 207 207 Seattle Manufacturing 205 205 Manufacturing Vision Corp - Global 207 207 Seattle Manufacturing 205 205 Manufacturing Vision Global Reporting 207 207 Seattle Manufacturing 206 206 Widgets Product Line Primary Reporting Hierarchy 207 207 Seattle Manufacturing 206 206 Widgets Product Line Single Establishment Reporting 207 207 Seattle Manufacturing 204 204 Vision Operations Vision Corp EBI Hierarchy 207 207 Seattle Manufacturing 204 204 Vision Operations US DBI Hierarchy 207 207 Seattle Manufacturing 204 204 Vision Operations US Commercial Sales (DBI) 207 207 Seattle Manufacturing 204 204 Vision Operations DBI - Vision Operations 208 208 Chicago Subassembly Plant 205 205 Manufacturing Vision Corp - Global 208 208 Chicago Subassembly Plant 206 206 Widgets Product Line Single Establishment Reporting 208 208 Chicago Subassembly Plant 206 206 Widgets Product Line Primary Reporting Hierarchy 208 208 Chicago Subassembly Plant 205 205 Manufacturing Vision Global Reporting 209 209 Boston Manufacturing 205 205 Manufacturing Vision Global Reporting 209 209 Boston Manufacturing 205 205 Manufacturing Vision Corp - Global 209 209 Boston Manufacturing 207 207 Seattle Manufacturing SEATTLE ECO HIERARCHY 209 209 Boston Manufacturing 206 206 Widgets Product Line Single Establishment Reporting 209 209 Boston Manufacturing 204 204 Vision Operations DBI - Vision Operations 209 209 Boston Manufacturing 206 206 Widgets Product Line Primary Reporting Hierarchy 43 rows selected.Now if you read my posting the other day about ragged hierarchies and OBIEE, you'll know that the semantic model used by OBIEE doesn't (currently, as of the 10gR3 release) support ragged hierarchies, requiring them instead to be "flattened out" into level-based hierarchies. This is exactly the route that the OBAW load routine takes when processing ragged hierarchies, flattening them out into tables such as W_INT_ORG_DH table, so that you now have a row per organization unit, per hierarchy, that shows their path up the organization chart. There's a number of SDE (Source-Dependent Mappings) that carry out this task, flattening the hierarchy through joining the temporary table about several times to itself using a Source Qualifier transformation, with the rest of the mapping now working with a set of flattened out level columns.
SQL> SELECT row_wid 2 , org_wid 3 , org_hier1_num 4 , org_hier2_num 5 , org_hier3_num 6 , org_hier8_num 7 , org_top_num 8 , effective_from_dt 9 , effective_to_dt 10 , current_flg 11 FROM w_int_org_dh 12 WHERE org_hier1_num between 0 and 209 13 /This table is then made available in the OBAW, and added to the Internal Organization logical table as an additional logical table source (not sure why its separate logical table source, I would have thought it'd be more efficient to just add it to the existing logical table source that gets the main dimension member information), so that reports can drill up and down the dimension hierarchy. One slight drawback to this approach that's common to all hierarchy flattening techniques is that you'll need to amend the _DH warehouse table, plus all the dependent staging and temporary tables if your source parent-child hierarchy has more than the set number (nine I think) of flattened levels, this will be particularly interesting as you'll need to amend the tables themselves plus the SDE and SIL (source-independent load) Informatica mapping that load them, quite a big task when you think about it especially as there are SDE mappings for each source and for each hierarchy category.ROW_WID ORG_WID ORG_HIER1_NUM ORG_HIER2_NUM ORG_HIER3_NUM ORG_HIER8_NUM ORG_TOP_NUM EFFECTIVE EFFECTIVE C
54 6004 205 205 205 202 01-JAN-99 01-JAN-14 Y 183 6002 202 202 202 202 202 01-JAN-99 01-JAN-14 Y 193 6001 0 0 0 0 0 01-JAN-99 01-JAN-14 Y 91 6008 209 209 209 204 202 01-JAN-99 01-JAN-14 Y 92 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 93 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 102 6007 208 208 208 204 202 01-JAN-99 01-JAN-14 Y 1230 6001 0 0 0 0 0 01-JAN-99 01-JAN-14 Y 1411 6008 209 209 209 204 202 01-JAN-99 01-JAN-14 Y 1412 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 1413 6005 206 206 206 204 202 01-JAN-99 01-JAN-14 Y 1414 6004 205 205 205 204 202 01-JAN-99 01-JAN-14 Y 1415 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 1416 6002 202 202 202 202 202 01-JAN-99 01-JAN-14 Y 1527 6007 208 208 208 204 202 01-JAN-99 01-JAN-14 Y 3045 6004 205 205 205 205 202 01-JAN-99 01-JAN-14 Y 3047 6008 209 209 209 205 202 01-JAN-99 01-JAN-14 Y 2090 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 2618 6007 208 208 208 205 202 01-JAN-99 01-JAN-14 Y 2093 6004 205 205 205 204 202 01-JAN-99 01-JAN-14 Y 2095 6005 206 206 206 204 202 01-JAN-99 01-JAN-14 Y 2096 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 2097 6007 208 208 208 204 202 01-JAN-99 01-JAN-14 Y 2098 6008 209 209 209 204 202 01-JAN-99 01-JAN-14 Y 2902 6003 204 204 204 458 202 01-JAN-99 01-JAN-14 Y 2428 6004 205 205 205 205 202 01-JAN-99 01-JAN-14 Y 2679 6005 206 206 206 1615 202 01-JAN-99 01-JAN-14 Y 2430 6008 209 209 209 205 202 01-JAN-99 01-JAN-14 Y 2436 6006 207 207 207 205 202 01-JAN-99 01-JAN-14 Y 2441 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 2696 6004 205 205 205 205 202 01-JAN-99 01-JAN-14 Y 2701 6003 204 204 204 205 202 01-JAN-99 01-JAN-14 Y 2703 6006 207 207 207 205 202 01-JAN-99 01-JAN-14 Y 2208 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 3490 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 4068 6008 209 209 209 207 202 01-JAN-99 01-JAN-14 Y 3053 6006 207 207 207 205 202 01-JAN-99 01-JAN-14 Y 3058 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 3880 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 3882 6008 209 209 209 204 202 01-JAN-99 01-JAN-14 Y 3651 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 3666 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 3183 6007 208 208 208 205 202 01-JAN-99 01-JAN-14 Y 3979 6003 204 204 204 260 202 01-JAN-99 01-JAN-14 Y 3465 6003 204 204 204 204 202 01-JAN-99 01-JAN-14 Y 3236 6005 206 206 206 1615 202 01-JAN-99 01-JAN-14 Y 3478 6004 205 205 205 204 202 01-JAN-99 01-JAN-14 Y 3479 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y 3480 6004 205 205 205 204 202 01-JAN-99 01-JAN-14 Y 3481 6006 207 207 207 204 202 01-JAN-99 01-JAN-14 Y50 rows selected.
The eagle-eyed amongst you would also have spotted effective from, and effective to, dates in the above tables, together with a current flag column that you usually see in dimensions configured to store history (known in Kimball terms as a slowly changing dimension). That's precisely what they are, organization dimension entries having start and end dates and a flag to show whether the row you're looking at is the current (i.e. active) row, with the same columns also being present in the dimension hierarchy tables and in fact every seeded dimension table within the OBAW.
The fact that every dimension table, and in fact every dimension SIL mapping, is set up in order to support slowly changing dimensions means that you can turn on this feature just by setting the $$TYPE2_FLG mapping parameter to "Y", though you'll need to start customizing the mapping if you want to go with anything other than the default set of Type 2 trigger columns. If you compare this to SCD handling in OWB it approaches things from a slightly different way, with OWB setting SCD types at the dimension object level and the BI Apps setting it at the mapping level, for the standard set of BI Apps seeded mappings their arrangement works well but it gets a bit messy when you want to alter the set of trigger columns and of course you have to reproduce all of this mapping logic in your own mappings if you want your own custom dimensions to be handled in the same way.
Anyway, that's it for dimension history tables and slowly changing dimensions. In terms of what I want to cover next in this series, there's the integration of the BI Apps with E-Business Suite security (probably the number 1 thing I'm asked) and a low-down on the universal adapter and how it can be used to bring in your own, unsupported data sources. But for now though that's it, let me know if you've got any feedback on the above.