This discussion is archived
1 Reply Latest reply: Jan 21, 2013 6:35 AM by Robert Angel RSS

Multi key (or composite key) dimension for operational reporting

user761901 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points