Let me give the understanding on working of WID (warehouse_id) in dimension, fact relationship as this concept is very important to understand and clarity is required as it is often asked in OBIEE interviews.
The unique numeric key (surrogate key ROW_WID) for each dimension table is generated during the load process. This key is used to join each dimension table with its corresponding fact table. It is also used to join the dimension with associated hierarchy or extension table, the row_wid columns in the Oracle Business Analytics warehouse table are numeric.
In every dimension table, the row_wid value of zero is reserved for unspecified. If one or more dimensions for a given record in fact table is unspecified, the corresponding key fields in that record is set to zero.
_FS (fact staging) -> org_id (source system id)
_DS (dimension staging) -> org_id (source system id – primary key)
_D (dimension table) -> integration_id (source system id; not primary key in D/W) org_id in _DS table
-> Row_wid (new primary key as surrogate key used in D/W to create relationship)
_F (fact table) -> org_wid (foreign key - primary of dimension table)
NOTE: not to migrate org_id from _FS
The ROW_WID in the dimension tables maps to the WIDs in the fact table.
An example of this is -- The ROW_WID in W_INT_ORG_D table maps to the ORG_WID in the fact table W_PAYROLL_F.
Now, the question is where do we do this linking. Let's understand with an example.
Consider the W_PAYROLL_FS fact staging table. This fact staging table should contain the ORG_ID from the source system.
Similarly, the ORG_ID from the source system becomes INTEGRATION_ID in W_INT_ORG_DS and W_INT_ORG_D table.
So, in the SIL Fact mapping, we include the W_INT_ORG_D tables as a source along with the W_PAYROLL_FS table. Now, the aim is to pass the ROW_WID (primary key) from W_INT_ORG_D table to the W_PAYROLL_F table's column ORG_WID to create the foreign key relationship with the W_INT_ORG_D dimension table.
This join is mentioned in the SIL Fact Source Qualifier query. The join condition in the Source Qualifier should be:
W_INT_ORG_D.INTEGRATION_ID = W_PAYROLL_FS.ORG_ID
AND
W_INT_ORG_D.DATASOURCE_NUM_ID = W_PAYROLL_FS.DATASOURCE_NUM_ID.
Important to NOTE -> Also first load ur dimension table then fact table (use informatica Target Load Order Setting to achieve this)
Remember, any key OLTP source ID that comes from the source system cannot go into the fact table, it is always the surrogate key ROW_WID from the dimension tables that should reside in the fact table.
The rules are simple. Let's understand with an example:
1. Assume the source is S_ORG_EXT and the OLTP source ID for this table is ORG_NUM and this table contains organization related data. The S_ORG_EXT table is the source for the dimension staging table W_INT_ORG_DS. The mapping between the S_ORG_EXT fields to W_INT_ORG_DS will be in SDE_OrganizationDimension mapping. In this mapping, the ORG_NUM OLTP source ID is mapped to INTEGRATION_ID field of staging table.
2. In the SIL_OrganizationDimension mapping, against each INTEGRATION_ID from the dimension staging table, a unique surrogate key ROW_WID is generated. eg:
ORG_ID = 765 in S_ORG_EXT table becomes INTEGRATION_ID = 765 in W_INT_ORG_DS table, again becomes INTEGRATION_ID = 765 in W_INT_ORG_D table and also a ROW_WID (read ROW WarehouseID) = 20 (unique sequence generated) in W_INT_ORG_D table.
3. So, now you need to load the ROW_WID = 20 from W_INT_ORG_D table to the ORG_WID in the Fact table W_PAYROLL_F. You, cannot map the INTEGRATION_ID = 765 to the ORG_WID in fact table because the value '765' is a source system key, which should never be used as a primary key in the datawarehouse dimension table.
The unique numeric key (surrogate key ROW_WID) for each dimension table is generated during the load process. This key is used to join each dimension table with its corresponding fact table. It is also used to join the dimension with associated hierarchy or extension table, the row_wid columns in the Oracle Business Analytics warehouse table are numeric.
In every dimension table, the row_wid value of zero is reserved for unspecified. If one or more dimensions for a given record in fact table is unspecified, the corresponding key fields in that record is set to zero.
_FS (fact staging) -> org_id (source system id)
_DS (dimension staging) -> org_id (source system id – primary key)
_D (dimension table) -> integration_id (source system id; not primary key in D/W) org_id in _DS table
-> Row_wid (new primary key as surrogate key used in D/W to create relationship)
_F (fact table) -> org_wid (foreign key - primary of dimension table)
NOTE: not to migrate org_id from _FS
The ROW_WID in the dimension tables maps to the WIDs in the fact table.
An example of this is -- The ROW_WID in W_INT_ORG_D table maps to the ORG_WID in the fact table W_PAYROLL_F.
Now, the question is where do we do this linking. Let's understand with an example.
Consider the W_PAYROLL_FS fact staging table. This fact staging table should contain the ORG_ID from the source system.
Similarly, the ORG_ID from the source system becomes INTEGRATION_ID in W_INT_ORG_DS and W_INT_ORG_D table.
So, in the SIL Fact mapping, we include the W_INT_ORG_D tables as a source along with the W_PAYROLL_FS table. Now, the aim is to pass the ROW_WID (primary key) from W_INT_ORG_D table to the W_PAYROLL_F table's column ORG_WID to create the foreign key relationship with the W_INT_ORG_D dimension table.
This join is mentioned in the SIL Fact Source Qualifier query. The join condition in the Source Qualifier should be:
W_INT_ORG_D.INTEGRATION_ID = W_PAYROLL_FS.ORG_ID
AND
W_INT_ORG_D.DATASOURCE_NUM_ID = W_PAYROLL_FS.DATASOURCE_NUM_ID.
Important to NOTE -> Also first load ur dimension table then fact table (use informatica Target Load Order Setting to achieve this)
Remember, any key OLTP source ID that comes from the source system cannot go into the fact table, it is always the surrogate key ROW_WID from the dimension tables that should reside in the fact table.
The rules are simple. Let's understand with an example:
1. Assume the source is S_ORG_EXT and the OLTP source ID for this table is ORG_NUM and this table contains organization related data. The S_ORG_EXT table is the source for the dimension staging table W_INT_ORG_DS. The mapping between the S_ORG_EXT fields to W_INT_ORG_DS will be in SDE_OrganizationDimension mapping. In this mapping, the ORG_NUM OLTP source ID is mapped to INTEGRATION_ID field of staging table.
2. In the SIL_OrganizationDimension mapping, against each INTEGRATION_ID from the dimension staging table, a unique surrogate key ROW_WID is generated. eg:
ORG_ID = 765 in S_ORG_EXT table becomes INTEGRATION_ID = 765 in W_INT_ORG_DS table, again becomes INTEGRATION_ID = 765 in W_INT_ORG_D table and also a ROW_WID (read ROW WarehouseID) = 20 (unique sequence generated) in W_INT_ORG_D table.
3. So, now you need to load the ROW_WID = 20 from W_INT_ORG_D table to the ORG_WID in the Fact table W_PAYROLL_F. You, cannot map the INTEGRATION_ID = 765 to the ORG_WID in fact table because the value '765' is a source system key, which should never be used as a primary key in the datawarehouse dimension table.
Comments
Post a Comment