Dimension design question

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!

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.

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!

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.

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..

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

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.