Dimension design question

View previous topic View next topic Go down

Dimension design question

Post  impdocs on Wed Jul 18, 2012 4:27 pm

Hi all,

We have a relatively small datawarehouse DB with 2 Facts tables and around 10 Dimension tables. We now need to accommodate and seggregate the data from Multiple Propeties in to single DWH database, our next release. Each Property can have multiple Sites.

What would be the best design approach for the Dimensions, among the below?
- 1 new Denormalized Dimension added with "Property", "Site" and a Surrogate Key? SK added to the Facts tables as FK.
- 2 new separate Dimensions added for "Property" and "Site" with "PropertyKey" and "SiteKey" respectively, as the Surrogate Keys? SKs added to the Facts tables as FKs.
- 2 new separate Dimensions added for "Property" and "Site" and a Bridge table to link them. The SK from Bridge table added to the Fact tables.

Thanks in advance!

impdocs

Posts : 3
Join date : 2012-07-18

View user profile

Back to top Go down

Re: Dimension design question

Post  ngalemmo on Wed Jul 18, 2012 7:10 pm

What level of detail are the facts? Are they related to site or property or both?

If they are by site only you could consider a single dimension which contains property information as well.

If you go with two dimensions and need to deal with facts at the property level, you are usually dealing with two fact tables.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Dimension table design question

Post  impdocs on Thu Jul 19, 2012 1:05 am

I believe the Fact tables are at the Site level detail.

Just to clarify a little more and to make sure I understand this correctly; there could be 1 Property with 5 Sites and another Property with 3 Sites and so on. The measures in the Facts will be from each Site, which belong to one Property. So, there could be queries aggregating the measures at the Property level too. Do you think the below single dimension physical structure makes sense in this case?

DimPropertySiteRef table
------------------------------
PropertySiteKey (PK/SK)
PropertyName
SiteId
SiteName

Thanks!

impdocs

Posts : 3
Join date : 2012-07-18

View user profile

Back to top Go down

Re: Dimension design question

Post  ngalemmo on Thu Jul 19, 2012 12:05 pm

Yep. I would consider putting property in its own dimension if you expect to receive measures at that level or if property has a large number of attributes.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Dimension design question

Post  sgudavalli on Fri Jul 20, 2012 6:18 am


The Below Dimension perfectly makes sense to me..
as the association b/w property and site is one to many & the granularity of the facts is at site level we are good to go..




sgudavalli

Posts : 29
Join date : 2010-06-10
Age : 32
Location : Pune, India

View user profile

Back to top Go down

Re: Dimension design question

Post  impdocs on Fri Jul 20, 2012 2:08 pm

Thanks all! Just let me know if anybody has a different opinion. Thanks again!

impdocs

Posts : 3
Join date : 2012-07-18

View user profile

Back to top Go down

Re: Dimension design question

Post  hang on Fri Jul 20, 2012 6:45 pm

When you have a dimension for site with property in it, whether as an outrigger or denormalised attributes, the grain of the dimension is said to be at site level. I would simply call it DimSite instead of anything that indicates the relationship between site and other attributes/outriggers.

It might be trivial as it's just naming. However calling a dimension that suggests it is a bridge or some kind of association table is misleading. What would you call it if you end up having many other repeating groups in the same dimension.

Whether you should denormalise the property attributes in site dimension depends on the size of the site dimension, and how many times the property attributes are used in other dimensions. Having a fact at property level requires a separate property dimension, but it does not stop you from denormaling property attributes in site dimension.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Dimension design question

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