1 Reply Latest reply on Jan 8, 2013 11:46 AM by Maqs-Oracle

    OBIEE Data Modelling queries


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