Property and Property List

In my source system I have 2 tables, one for properties and another for property lists (a property list has multiple properties associated with it but not every property is associated with a list). I was wondering how best to handle this type of relationship in a data warehouse. I believe the fact table should reference the individual property so I can see results by property but I would also like the ability to see results by property list.

Thanks for your help.

Sounds like a hierarchy. Add property list attributes to the property dimension (assuming the relationship is one to many).

My bad, a property can be part of multiple lists.

I would then create a new dimension for property list. If you also want to know which properties are associated with which lists, I would look at creating a factless fact table.

How would the factless table relate to the fact table?

Sorry I am still learning about all this.

Thanks.

A bridge table is needed to handle the many to many relationship between the property and lists. A single property can be on multiple lists and a list can have multiple properties. The List Dimension has a row, typically a -1, which would assigned to properties that are not on any list.

Or

The grain of the Fact table is property/list and a property can be in the fact table multiple times.

I am trying this again but still don't get how I can filter on either property or list.  I have tried to document how the data is represented.  I am assuming that based on the previous replies that I need to create a bridge table but I am not sure how that would work and how it would relate to a fact table.  Any help would be appreciated.  Thanks.

" />

It may just be me but the picture in your last post was too small to read - but hopefully this helps:

The basic idea is that you have
2 Dimension tables: Dim_Property and Dim_PropertyList
1 Fact table: Fact_Property
1 Bridge table: Bridge_Property_List

Your bridge table has two columns: 1 for the Dim_Property key and one for the Dim_PropertyList key. You create one record in the bridge table for every property/property list combination.

The logical joins when querying this structure are then:

Fact > Property Dim > Bridge ? PropertyList Dim

Does this help?

It does help. I tried creating it in power pivot to test it out but when I try to filter by the list it tells me I need to create a relationship between the lists and the fact. Do you know if this will work in Power Pivot?

Thanks

I'm afraid I don't know PowerPivot so can't really comment - however a quick Google of PowerPivot and many-to-many relationships throws up a number of articles, most of which seem to be saying that even having created the relationships PowerPivot doesn't handle these very well and you need to use DAX expressions to get measures to aggregate properly

Thanks. I will give it a shot.