Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
 Posted - 2012-11-01 : 01:47:10
|
| I'm building a star schema while reading a book from Ralph Kimball on datawarehousing. One concept that I'm struggling with is conformed dimensions. Specifically, when we're talking about the atomic grain.I get the basics, but I'm perplexed by a particular situation. If you want to ensure that the Fact tables "mean" the same thing in each Dimension table, then wouldn't you want the dimension tables to only "mean" one thing?So for example, I very often see Dimension Tables on "Date" or "Time". The dimDate table winds up looking something like this:DateKey DateTime DayOfWeek DayOfMonth Month Year etcThe problem with that is this...if you group by the date, you're not at the same grain as if you group by the Month or by the Year. Consequently, the dimension table doesn't mean the same thing as other dimensions in terms of level of granularity.If this is "ok" to do during development, then it would stand to reason you could take the similar tact with another dimension. Take for example a Product dimension as such:ProductKey ProductName Make Model etcSo in this example, a productname is to have more detail than the Model. The Model will have more detail than the Make. So again, productname doesn't mean the same things as make.If you were to group by Make, you'd get an entirely different result than if you grouped by productname.Hopefully I'm just overreading into the "atomic grain" concept.Thanks in advance! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-01 : 10:13:07
|
| Sorry I dont get your exact problem here. But Date,Month,Year is a natural hierarchy so obviously you maintain them at atomic grain level in dimension and while using in cube you will include the hierarchy. Based on what level you want analysis to happen (daily,monthly,quarterly,yearly etc) you can do aggregation at required level attribute and results will get rolled up to that level.For other dimensions it depends on your business scenario whether they form a hierarchy (ie in this case Name,Make,Model) you still be maintaining dimension in flattedned structure and in cube you'll define them as a hierarchy so that you can roll them up to level you want------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-01 : 16:48:37
|
| Hi Vis,I think that gets to my problem. I think my confusion had to do with the fact that in other dimension tables that do NOT have a heirarchy of any sort,would it cause a problem if you're bringing multiple dimensions into a query for analysis if one dimension is being queried at a higher hierarchy than another. From strictly a SQL standpoint, I don't have an issue..it's more about the design itself. I'm trying to make sure I fully understand the atomic grain concept.Give the tables I mentioned, I fully get that the following query:select D.SMonth, P.Model, SUM(F.NumOrders)from FactOrders Fjoin DimDate D on D.DateKey=F.DateKeyjin DimProduct P on P.Productkey=F.ProductKeygroup by D.Smonth, P.Modelwould produce a different result than:select D.SDate, P.Model, SUM(F.NumOrders)from FactOrders Fjoin DimDate D on D.DateKey=F.DateKeyjin DimProduct P on P.Productkey=F.ProductKeygroup by D.SDate, P.ModelI think I was looking at the dimension tables as needing to contain the lowest common denominator at all times (ie only the date or only the month etc for date, or only the make or only the model for products etc).Thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-03 : 22:13:00
|
quote:
Originally posted by flamblaster
Hi Vis,I think that gets to my problem. I think my confusion had to do with the fact that in other dimension tables that do NOT have a heirarchy of any sort,would it cause a problem if you're bringing multiple dimensions into a query for analysis if one dimension is being queried at a higher hierarchy than another. From strictly a SQL standpoint, I don't have an issue..it's more about the design itself. I'm trying to make sure I fully understand the atomic grain concept.Give the tables I mentioned, I fully get that the following query:select D.SMonth, P.Model, SUM(F.NumOrders)from FactOrders Fjoin DimDate D on D.DateKey=F.DateKeyjin DimProduct P on P.Productkey=F.ProductKeygroup by D.Smonth, P.Modelwould produce a different result than:select D.SDate, P.Model, SUM(F.NumOrders)from FactOrders Fjoin DimDate D on D.DateKey=F.DateKeyjin DimProduct P on P.Productkey=F.ProductKeygroup by D.SDate, P.ModelI think I was looking at the dimension tables as needing to contain the lowest common denominator at all times (ie only the date or only the month etc for date, or only the make or only the model for products etc).Thanks!
Ok...In typical star schema you usually bing dimension flattened out including all levels in a denormalised format.In snowflake schema, there can cases where you prefer to keep higher level attribute in its own dimension so you dont have to go through grain level always in case of summarised analysis------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-03 : 23:39:54
|
| Vis,Thanks...I don't have a problem fully denormalizing the dimensions and staying with a star schema over snowflake. I just want to make sure I avoid as many pitfalls as possible so that we can build reports without having to do a thousand workarounds!Thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-04 : 11:38:27
|
quote:
Originally posted by flamblaster
Vis,Thanks...I don't have a problem fully denormalizing the dimensions and staying with a star schema over snowflake. I just want to make sure I avoid as many pitfalls as possible so that we can build reports without having to do a thousand workarounds!Thanks again
ok.. in that case you can go with flattened approach so that it enables you to report on different levels from grain level to highest level.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
flamblaster
Constraint Violating Yak Guru
384 Posts |
Posted - 2012-11-04 : 11:45:20
|
| Thanks Vis...this makes sense! |
|
|
|
|
|
|
|
|
Star Schema - Conformed Dimensions