Oracle Analytics Cloud and Server

Products Banner

Maintain integrity in DIM/FACT WIDs

Received Response


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


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?


Oracle Analytics Cloud Data Sync (2.6) / OAAC


  • 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
    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

  • 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
    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