Snowflake a dimension if facts are at different granularities?

View previous topic View next topic Go down

Snowflake a dimension if facts are at different granularities?

Post  min.emerg on Mon Oct 01, 2012 12:14 pm

Hi

I'm currently modelling a data warehouse that will contain property management data. Properties consist of one or more suites, and suites can only belong to one property.

My initial thought was to normalize properties and suites into a single dimension. However, I have a fact table with a measure that will be at the property level, and another fact table with a measure that will be at the suite level. This is because the measure in the former is not stored at the suite level in the source system, and is only meaningful at the property level (for instance, the price that a property was sold for - suites aren't sold off individually).

My question is whether it would be considered bad practice to snowflake the design so that suites and properties are stored in two separate dimensions that are linked to one another (i.e.: a suite will have a foreign key to a property). This could cause problems as both are SCD dimensions of type 2, however.

Are there any other approaches that may be better than this?

Thanks.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  ngalemmo on Mon Oct 01, 2012 5:46 pm

Yes. Just carry both dimensions on the suite level facts.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  min.emerg on Tue Oct 02, 2012 4:26 am

Hi Nick

Thanks for the response. By both dimensions, do you mean to suggest that it it's okay for me to snowflake the one denormalized dimension into two dimensions (one for property and one for suite)? Apologies for being slow - I'm still a bit confused as to how I deal with the property-level facts.

I was thinking of perhaps doing something like this:

PropertySuiteKeyPropertyNameSuiteName
1Property OneSuite One
2Property OneSuite Two
3Property OneN/A

For the fact tables that do not go down to the suite level, I can link to properties in the PropertyKey dimension that have a SuiteName of 'N/A' . So each building will have a record in the dimension with a SuiteName of 'N/A'.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  ngalemmo on Tue Oct 02, 2012 4:48 am

No. For suite level facts, carry both the properly and suite dimension keys on the fact.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  min.emerg on Tue Oct 02, 2012 5:31 am

Hi Nick

Thanks again for the response. Do you mean that I should create two dimensions (one for property, one for suite), and associate properties to suites using the fact table?

DimProperty
PropertyKeyPropertyName
1Property One

DimSuite
SuiteKeySuitName
1Suite One

Fact
FactKeyPropertyKeySuiteKey
111

This should show that Suite One belongs to Property One.

Thanks again.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  min.emerg on Tue Oct 02, 2012 11:39 am

Something that Kimball suggested in his DW Toolkit book is to use roll-up dimensions, which seem to involve creating a new dimension that is a subset of a more complex dimension.

In my situation, I'd keep the denormalized dimension with both property and suite data:

DimPropertySuite
PropertySuiteKeyPropertyNameSuiteName
1Property OneSuite One
2Property OneSuite Two

But will create a new dimension that contains an exact subset of the attributes in the above table:

DimProperty
PropertyKeyPropertyName
1Property One

In the fact tables that go down to the suite level, I'll use the DimPropertySuite dimension, and I'll use the DimProperty dimension for facts that only go to the property level.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  VHF on Mon Oct 29, 2012 5:41 pm

I attempted to use roll-up dimensions (aka mini-dimensions) at one point to handle facts at different granularities, but ran into difficulty reporting across fact tables with BI tools that didn't understand that an attribute of the mini-dimension represented the same thing as the attribute in the original dimension. In your example, this would be that the tool didn't understand that DimPropertySuite.PropertyName represented the same thing as DimProperty.PropertyName, and therefore the users couldn't do easy ad hoc drill-across fact tables.

I ended up snowflaking, which has worked fine in my modest (11GB) DW on SQL Server 2008.

I did some performance testing and found there was a performance hit of about 30% overall, but most queries still finish in a second or two (10 seconds for the slow ones) so this isn't an issue for me. I would not recommend snowflaking for a very large DW, and I would only snowflake to the extent needed to support various fact table granularies (i.e. don't try to 3NF your dimension tables!)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  min.emerg on Wed Oct 31, 2012 7:33 am

VHF

Thanks for the response. What you say makes sense to me. The warehouse I'll be building will be relatively small (50GB), so I don't think that snowflaking where needed will kill it.

Thanks again.

min.emerg

Posts : 39
Join date : 2011-02-25

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  hayrabedian on Sat Nov 17, 2012 8:05 am

I think we should question some common believes more often.
There is no guarantee that the snowflaking leads to a poor query performance, i.e. to a query which won't be acceptable by the end user.
It could delay a query, but the real question is how slower it will become.
A good understanding of the RDBMS engine and how a join will be actually implemented, i.e. the ability to tune the SQL statement, can make the delay acceptable or even not noticable..
So, be sure to have an experienced team member, who can test possible design models (by quick prototyping of the riskier queries).

hayrabedian

Posts : 7
Join date : 2011-04-01

View user profile

Back to top Go down

Re: Snowflake a dimension if facts are at different granularities?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum