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