CODEWITHSUNDEEP
data-warehousestar-schema
thijsvdanker
thijsvdanker

Reputation: 13

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

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:

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

Upvotes: 1

Views: 1116

Answers (1)

Ron Dunn
Ron Dunn

Reputation: 3078

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.

Upvotes: 1

Related Questions