Maintain integrity in DIM/FACT WIDs

Brian666H

Summary

Looking for the best way to maintain links/joins from DIM & FACT tables

Content

All DIM tables have a Warehouse ID number (WID).  Joins are added to the Target FACT tables to link said DIM table in Data-Sync.  When/If changes (Add/Drop) columns or refreshing data back to initial load status are done.  This resets these WIDs.  This in-turn breaks the links within the report designer and can cause the lose of the visualizer report.

How can we keep this from happening?

Version

Oracle Analytics Cloud Data Sync (2.6) / OAAC

[Deleted User]

That's a principle of DWHing that you need to address at the ETL layer, it's not related to OAC.

It should never be the case, that keys get wiped out and referential integrity impaired by re-loads of the any kind of storage. That is generally a result of poor or straight forward wrong design.

Are you using somehting pre-configured liek OBIA to load your warehouse?

Brian666H

We are using Source: PSOFT // ETL Data-Sync // Target: ADW // Design DVD.  We are having issue in our FACT table design that pull from the PSOFT transaction tables.    I am sure it was self-inflicted.   We are in a high level frustration at the moment.  Thanks

[Deleted User]

I can understand your frustration. Unfortunately this isn't something I can alleviate via a post in here, sorry This would have to be investigated in detail in your ETL Data Sync layer.

RichardChan

Are you truncating and reloading. If this is the case you will be resetting the Dimension ROW_WIDS and these will be out of sync with your fact.

You should be using an update/insert strategy for your dimensions

 

Brian666H

Correct