Adding additional columns or rows to a dimension

View previous topic View next topic Go down

Adding additional columns or rows to a dimension

Post  mr_neal on Tue Jan 31, 2012 6:06 pm

I tried to find a related post but was not able to, so feel free to reference a similar posting.

Anyway, I have a Survey dimension where I have Survey_name and related attributes. Now I want to add each day that the specific survey is conducted. Days vary and some Surveys are taken over 7 days, some 3 months. I want to record each day that the Survey is taken so that the reporting team does not have to calculate each day between survey_start_date and survey_end_date. I can add a row to the dim for each day, but then how will I know which PK to grab for the related fact? Do I have to add an attribute to the Survey dim and call it day_number, and then grab the PK where day number = 1? If I add columns, then, well that is just ugly and my dim gets mucho wide! I'm guessing this is an issue when the DIM has a 1 to M in it? Anyway, any help, advice would be appreciated!

mr_neal

Posts : 17
Join date : 2012-01-26

View user profile

Back to top Go down

Re: Adding additional columns or rows to a dimension

Post  ngalemmo on Tue Jan 31, 2012 6:22 pm

What you are describing is tracking the occurrences when the survey is conducted. As such it is a business event and is best represented as a fact table. A simple faceless fact table with two FKs, one to date and the other to survey dimensions should be all you need. I most definitely would not create daily dimension rows... it would not help you accomplish what you are trying to do since a fact can only reference on dimension row.

To handle the count without the BI folks actually doing it would be to add it as an attribute to the dimension row and update it (total days used) when surveys are performed or based on some predetermined plan.

avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

"tracking the occurrences when the survey is conducted"

Post  mr_neal on Mon Feb 06, 2012 2:41 pm

Thanks for your reply and my realization for further clarification!

During our survey period we try to make contact attempts (fact_contacts) in order to complete the Survey. However, some days there may not be any attempts made and therefore no records in the fact_contacts table, yet I still want to report a zero for that day. (Almost like I'm trying to track non-occurrences as well) So my task is to have the data model store all the days, not the count, between survey start date and survey end date regardless of any occurrence happening. If I create the faceless fact table as you suggest than the reporting team would have to join the fact_contact table with the faceless fact table. Is that what you recommend?

mr_neal

Posts : 17
Join date : 2012-01-26

View user profile

Back to top Go down

Re: Adding additional columns or rows to a dimension

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