I am finally getting serious about trying to understand BI, Analysis services, etc so I've tried to transform our existing data into something that can be easily consumed by SSAS. In general, reading a technical book before I have a vague foundation for the material is a strategy for failure so I've been trying to cobble together a enough of a star schema with "real" data for my books to make sense
Problem
Our sales areas are defined by Regions. Regions have Divisions and Divisions may have Districts. If they don't have districts, in the data at least, they will still have a fake district, 0. Analysis of the data is typically done by areas which says Dimension. Some sample data is below
region division district rdd 02 03 00 0203-00 02 05 00 0205-00 02 05 01 0205-01 02 05 11 0205-11
While I'm sure it's a "it depends" question, would I want to model this as multiple tables or can I make it work with a single dimension? If I make it multiple tables, does this look like I'm going down the right path?
 |
1 comment:
Although people say "never snowflake" or "always snowflake" I don't think it makes much difference. I tend to model my relational schemas to make ETL easiest, then merge to a star schema via SQL views or the DSV for SSAS consumption. As long as you can have an attribute value at every level in this hierarchy, the way you've modeled it here will work.
If you haven't already, check out Kimball's Data Warehouse Toolit 2nd edition. It's the magna carta of dimensional design.
HTH, @tomhug
Post a Comment