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?
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.
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.