DW Design


  • Hi,

    I am designing a Data Warehouse for gathering financial data. I will get the data from a denormalized OLTP table containing the below columns.

    I am not sure how many dimensions to create. There are multiple assettypes, multiple investmenttypes, multiple currencies, and multiple security ids. A security can contain only a single assettype, investmenttype, currency, etc. 

    Do I create a Dimension for each of these - so dimAssetType, dimCurrency, etc?
    But that is normalizing the tables right? Instead in DW, should I have a single 'Security' dimension containing all of those columns?


    Thursday, February 20, 2014 10:17 AM


  • I will say it will be good to use separate dimensions for each of these and every dimension will be referenced in Fact. It will make your life easy to provide different kind of reporting in future. I agree in this way we are going towards normalized tables. 

    On the other hand, if you are planning to go with one security dimension, it will make your ETL easy but you will face couple of issues during designing SSAS or adhoc reports.

    Friday, February 21, 2014 3:53 AM