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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Star Schema - Conformed Dimensions

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 etc

The 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 etc

So 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 F
join DimDate D on D.DateKey=F.DateKey
jin DimProduct P on P.Productkey=F.ProductKey
group by D.Smonth, P.Model

would produce a different result than:


select D.SDate, P.Model, SUM(F.NumOrders)
from FactOrders F
join DimDate D on D.DateKey=F.DateKey
jin DimProduct P on P.Productkey=F.ProductKey
group by D.SDate, P.Model

I 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!
Go to Top of Page

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 F
join DimDate D on D.DateKey=F.DateKey
jin DimProduct P on P.Productkey=F.ProductKey
group by D.Smonth, P.Model

would produce a different result than:


select D.SDate, P.Model, SUM(F.NumOrders)
from FactOrders F
join DimDate D on D.DateKey=F.DateKey
jin DimProduct P on P.Productkey=F.ProductKey
group by D.SDate, P.Model

I 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-11-04 : 11:45:20
Thanks Vis...this makes sense!
Go to Top of Page
   

- Advertisement -