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.



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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.