HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Fact table design - Level or contribution measures

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
contributionfactleveldesignmeasurestable

Problem

I am trying to come up with a dimensional model for the following:

The source data comes from game machines as meter messages. Basically, the game machine accepts payments and sends a snapshot of the current values of several measures. The message contains over 15 meter types such as the coin-in level (in cents), games played level, games won, games lost and so on. This message is captured on a daily basis. Because of the fact that the snapshots of numeric levels are taken on a daily basis, I thought that we should use a periodic snapshot fact table.

Would it be better to store the levels of the numeric values as measures (ex. the total coin-in value and total games played of the machine as of the snapshot) or should we store the contribution amount for the snapshot period (ex $100 was deposited and 10 games were played in the one day ). Or maybe both levels and contributions (levels would not be additive across the date dimension but contributions would be)??
What is the difference in terms of analysis.

We have not started the requirements gathering yet but it seems that there are a lot requests on trending the coin-in data for example by month per machine.

Thanks,

Solution

What is the difference in terms of analysis.

Storing the values that apply to the snapshot period would be the natural fit for most kinds of reporting, including "requests on trending the coin-in data for example by month per machine."

The risk is that if a snapshot goes missing the total on the machine may not match the totals in the cube. Storing both would give you the means to cross-check.

Context

StackExchange Database Administrators Q#6971, answer score: 3

Revisions (0)

No revisions yet.