Home Forums Data Warehousing Strategies and Ideas Dimension key in a fact table should be repetitive? RE: Dimension key in a fact table should be repetitive?

  • October 8, 2013 at 3:48 am

    #1656651

    We do have a reqirement to design a OLAP model for consolidating the issues identified on ASSETS from various sources\system.

    Let us take one sample record;

    Issue | Asset | Status |Priority |St_DT |End_Dt | Brief Description | Owner | location | Source

    ISS1 | ASS1,ASS2 |Open |High |1-Jan-13 |NULL | issue description | scott | CA | SRC1

    We have designed the below model for this scenario:

    Dimension_Asset: Conformed dimesion for assets.

    Dimension_Asset_Group: Since each issue can have multiple assets we have designed a group table.

    Dimension_Date: Conformed and role playing dimension for start and end date

    Dimension_Issue: Issue_ID,Owner,Location,Brief Description,Source

    Fact_Issue: Issue_ID,Asset_Group_ID,Status,Priority,St_DT,End_DT

    Actually issues will be associated with many deatiled text and attributes. Do we need to capture this information in the FACT_ISSUE table or dimension_issue table. Dimension_Issue (though we cannot call it as dimension since its not repitative data in the fact table), is more like details of the fact table.