1 Reply Latest reply: Jan 21, 2013 8:35 AM by Robert Angel RSS

    Multi key (or composite key) dimension for operational reporting

    User761901-Oracle
      Hi, I am creating operational reports in OBIEE 11g with PeopleSoft FSCM as source. As they are straight forward reports, I am planning to use OLTP tables directly for my OBIEE reporting.

      Here is the simplified scenario - ACCOUNT_TBL_VW is dimension table which has BUSINESS_UNIT & ACCOUNT_ID as keys.
      I have LEDGER Fact that has BUSINESS_UNIT, ACCOUNT_ID & other dimension keys with amount measures.

      How do I link my LEDGER Fact with ACCOUNT Dimension?

      Can I join multiple keys of Dimension with multiple keys of Fact? like this

      ACCDIM.BUSINESS_UNIT ---> LEDGFACT.BUSINESS_UNIT
      ACCDIM.ACCOUNT_ID ---> LEDGFACT.ACCOUNT_ID

      As it is operational reporting, I don't have any ETL type process to prepare single column primary key(surrogate key) in Dimension Table & modifying Fact Table with dimension surrogate key value.
        • 1. Re: Multi key (or composite key) dimension for operational reporting
          Robert Angel
          Hi,


          on the principal of how (in the absence of ETL); -


          1. Physical layer should reflect the actual physical joins, in some cases you may need the same table more than once to reflect self joins

          2. The business model layer is where you transform the physical into your desired star schema, hence here you will frequently (when using RDBMS tables) have numerous physical tables forming the source for a single logical table - this is also where you flatten the snowflake (or worse) into a star


          Make sense?


          regards,

          Robert.