The most important question to answer when designing a fact table is to determine what the grain of the transaction you wish to record is. Quite literally, it’s all about the base grain. If you select too high of a grain, the you lose your ability to drill down into more detailed analysis. If you select too low of a grain, your performance may suffer – both in your queries and in your ability to analyze the data.

Personally, I’m a fan of getting the absolute lowest level of detail for a transaction into the fact table. The reasons for this are pretty straightforward. Just as every house needs a solid foundation, every warehouse needs the most basic transactional data incorporated into it. By starting at the lowest level, you can always roll up the data into a higher level grouping. If you start at the higher level, there is no way to know the proportions each lower level would represent. I discussed this in part in an earlier post but it bears repeating here. Data will shift over time, seasons, and geography – just to name a few potential reasons. As such, you cannot assume equal allocation of transactions as you drill down in the data. Since it’s called a “FACT” table, it needs to give your end users the ability to analyze FACTS – not assumptions. A perfect example of this is your monthly bank statement. If you only look at the starting and ending balances, it will be tougher to change your spending habits (even more so if you are married and share the account). By taking the data at the lowest transactional grain, you can see exactly how money flowed into the account (credits) and how money flowed out (debits).

The difference between being able to slice, dice, and rollup to any aggregation level is the difference between “Reporting” and “Business Intelligence”. Don’t settle for writing reports for the rest of your career. It’s a disservice to you, your company, and your customers. Do it right and you only have to do it once. Implement a BI Analytics capable solution and move on to the next solutioning opportunity.