Fact table design

[arowshan]

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.

If that is the right fact table type, 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)??

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,
Ali

[ngalemmo]

I would maintain it as transactional (net change) data, like it was received. The notion of trying to maintain running totals doesn't appear to make sense. Part of that issue is running totals based on what? All time? Year-to-date? Month-to-date? Usually these are calculated on the fly based on the context of the query, which is easy enough to do if the data is stored as transactions. Transactional data also makes trend analysis much simpler since the data already reflects the magnitude of change for a period (just add up whatever period you need).
The other issue you may encounter is the business changes the frequency of pulling the data. It complicates things if you are trying to maintain a snapshot. I would have no effect (other than more rows) if you store transactions.

[arowshan]

So you are saying if the decision is to do a daily snapshot, treat that as a transaction and store the net change which makes sense. However, if the frequency of the snapshot changes later, wouldn't that mean a different grain for the fact table?

[ngalemmo]

No, the grain does not need to change. As long as each load contains the difference from the previous reading, grain does not matter. Where the grain would change is if they increase the load frequence and wish to do analysis in finer time intervals than day. In which case you would need to add a time of day dimension to the table.

[arowshan]

So if we are going from daily to monthly and doing the snapshot on the 1st of every month, then we would keep using the date dimension with the day grain and just use the 1st day of the month key and not the rest of the records in the date dimension.

[ngalemmo]

That would work. For the monthly aggregate, I would use a rolename for the date FK to make it clear it represents a month. For example, if you normally use "date_key", I would name month references "month_date_key".

[Sponsored content]