Property and Property List

View previous topic View next topic Go down

Property and Property List

Post  stout27 on Mon Oct 07, 2013 12:25 pm

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.

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

Post  BoxesAndLines on Mon Oct 07, 2013 2:09 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Property and Property List

Post  stout27 on Mon Oct 07, 2013 3:36 pm

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

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

Post  BoxesAndLines on Mon Oct 07, 2013 5:15 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Property and Property List

Post  stout27 on Thu Oct 24, 2013 4:37 pm

How would the factless table relate to the fact table?

Sorry I am still learning about all this.

Thanks.

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

Post  Jeff Smith on Thu Oct 24, 2013 4:50 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Bridge Property and List (ctd)

Post  stout27 on Thu Sep 25, 2014 1:43 pm

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.

" />

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

Post  nick_white on Fri Sep 26, 2014 7:15 am

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?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Property and Property List

Post  stout27 on Fri Sep 26, 2014 8:49 am

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

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

Post  nick_white on Fri Sep 26, 2014 10:28 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Property and Property List

Post  stout27 on Fri Sep 26, 2014 2:22 pm

Thanks. I will give it a shot.

stout27

Posts : 9
Join date : 2013-05-14

View user profile

Back to top Go down

Re: Property and Property List

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