Modelling multi attribute dimensions

View previous topic View next topic Go down

Modelling multi attribute dimensions

Post  JingsCrivvens on Tue Mar 08, 2016 11:00 am

Hi

I have a individual demographics dimension which holds all possible values of the following attributes:
age-group(5), religion(15),ethnicity(20),Gender(3), sexualOrientation(5) which gives 22500 rows.

I also have a 'Spend' fact table which allows me to track Spend against individual demographics eg how much has been spent by Males this month.
There obviously may be some demographic attributes for which there are no Fact rows.
eg there are no Spend rows for those whose religion is 'Pagan'. My question is: does this model impact on my ability to

i. build reports which will include a results row for Pagans ie 0 spend amount
ii. build a cube given that the demographics dimension has mixed attributes

Many thanks in advance

Philip

JingsCrivvens

Posts : 7
Join date : 2016-02-13

View user profile

Back to top Go down

Re:Modelling multi attribute dimensions

Post  zoom on Tue Mar 08, 2016 1:10 pm

religion ,ethnicity ,Gender, and sexual Orientation are the attribute of a customer and they should exist in your customer dim. The only reason you cannot add then to a customer dim, If your reporting tool cannot pivot data if those attributes exist in a customer dim. If that is the case, then you can create dims for each data attribute you mentioned and create their relationship for a customer in the fact table. For example, you have customer dim and gender dim. If customer A is male then your Fact table has a customer A dim foreign key (FK) and gender dim male FK in the fact table.

Your age-group defiantly a stand alone dim. It would have different ranges of age group. Above approach allows you to maintain this data easily and let you slice and dice data as you described.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re:Modelling multi attribute dimensions

Post  JingsCrivvens on Tue Mar 08, 2016 1:42 pm

Thanks for that - are you saying then that if I have 40/50 such customer attributes which I need to model in the way which you describe for the Gender dimension, I would need to create 40/50 foreign keys in the fact table joining to each of the individual dimensions?

JingsCrivvens

Posts : 7
Join date : 2016-02-13

View user profile

Back to top Go down

Re:Modelling multi attribute dimensions

Post  zoom on Tue Mar 08, 2016 2:30 pm

The ideal design approach is to have 1 customer dim with those attribute ( except age_group), but some reporting tools cannot pivot data if all the data elements exist in a dim. To over come that reporting tool limitations, people create dims for those attribute/fields which they need to pivot. You can ask your reporting software company that tool's limitation around pivoting data. Now your question is more towards your reporting tool limitation and functionality.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re:Modelling multi attribute dimensions

Post  JingsCrivvens on Tue Mar 08, 2016 3:40 pm

Thanks again for that - can I ask why you single out age-group as having its own dimension. Are the other attributes not in essence the same i.e. single value /customer with multiple possible values - which is the same as what we have for age-group?

JingsCrivvens

Posts : 7
Join date : 2016-02-13

View user profile

Back to top Go down

Re: Modelling multi attribute dimensions

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