This discussion is archived
1 Reply Latest reply: Jan 8, 2013 3:46 AM by Maqsood Hussain RSS

OBIEE Data Modelling queries

ChanM76 Newbie
Currently Being Moderated
Hi,

I am trying to create a data model for the OBIEE reporting from a source database.

I have a fact table that looks like the below

Fact-Key Customer_id Facility_id market_value_amt Coll_key Guar_key Derivative_key
========================================================
1 10 100 10000 30 NULL NULL
2 20 100 10000 NULL 100 NULL
3 30 100 10000 NULL NULL 200


In the above fact table for a given fact key i can find the correspondign customer details by using the customer_id by joining it with the customer dimesnion table
I can get the facility details by using the facility_id and joinign it with the facility dimension table.
But the coll_key,guar_key derivative_key are all from different tables.
Any one row in teh fact table will have only oen of the keys coll key or guar_key or derivative key populated.
When a row has a coll key value populated i will use the key to join with the coll_attr tabl to get collateral related values.
If the guar_key table is populated i will use that key and joion with the guar_attr table to get guaranter related details.

The problem with this model is tommorrow if there is another type that is added say equity, then to get th attribute details i will need another column added to the
fact table called equity_key and i will use this key and join with say the equity_attr table to get the values.

Is there any other way to design the data model for these kind of data.
  • 1. Re: OBIEE Data Modelling queries
    Maqsood Hussain Journeyer
    Currently Being Moderated
    By looking at the data and fact table i can say that you need to have one key in the fact instead of different keys for different types.

    Fact
    Fact-Key Customer_id Facility_id market_value_amt Type_key(example)
    1 10 100 10000 300
    2 20 100 10000 100
    3 30 100 10000 200

    All the tables can join using the same key and you wont need to add another column for equity.

    Hope this helps

Legend

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