Handling change of grain for a snapshot fact table in a star-schema

Question

The question

How do you handle a change in grain (from weekly measurement to daily measurement) for a snapshot fact table.

Background info

For a star-schema design I want to incorporate the results of a survey as a fact (e.g. in week 2 of 2015 80% of the respondents have responded 'yes', in week 3 76% etc.) This survey is conducted each week, and I only have access to the result of the survey (% of people saying yes this week) and not to the individual responses.

Based on (my interpretation of) Christopher Adamson's "Star Schema: The complete reference" I believe I should use a snapshot fact table for these kind of measurements.

The date dimension for this fact should be on the week-level, and be a conformed rollup of a more fine-grained date dimension for other facts in other stars that take place on a daily basis.

Here comes trouble

Now someone decides they want to conduct these surveys daily instead of weekly. What is the best way to handle this? Some of the options I'm currently considering:

• change the week dimension to a daily one, and fake the old facts as if they happened on the last day of the week.
• change the week dimension to a daily one, and add 7 facts for each weekly one.
• create a new star, with the daily fact and dimension and treat the old one as an aggregate.

I'd appreciate any input. Please tell me if my logic is off, or my question is not clear :)

Answer 1

I'm not convinced that this is a snapshot. Each survey response represents a "transaction".

With an appropriate date dimension you can calculate the Yes/No percentages, rolled up by week.

Further, this would enable you to show results like "Surveys issued on a Sunday night get more responses", or "People who respond on Friday are more likely to answer 'Yes'". (contrived examples)

---

Following clarification, this does look like a periodic snapshot. The example of a bank account balance is often used to describe a similar scenario.

A key feature of a periodic snapshot is that every combination of every dimension should be present. If your grain is monthly, then every month you record the fact, even if it has not changed from the previous month.

I think that is the key to your problem. Knowing that your grain may change from weekly to daily, make your grain daily. It does mean you'll be repeating the weekly value on every day of the week, but that is a true representation of your knowledge of the fact; on Wednesday you only knew that its value was the same as Monday.

If you design your ETL right, you won't need to make any changes when the daily updates begin.

Your second option is the one I'd choose in your place.