Business Intelligence

Get Involved. Join the Conversation.


    Brian Hall
    Maintain integrity in DIM/FACT WIDs
    Topic posted May 21, 2019 by Brian HallSilver Medal: 2,000+ Points, tagged BI Publisher, Business Intelligence, Data Sync 
    56 Views, 5 Comments
    Maintain integrity in DIM/FACT WIDs
    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



    • Christian Berg

      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?

      • Brian Hall

        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

        • Christian Berg

          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.

    • Richard Chan (Neodata Australia)

      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


    • Brian Hall