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