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

    Multi key (or composite key) dimension for operational reporting

      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


      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

          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?