Foundation: Transaction Table Granularity

Most business processes occur in batches. Take for example a trip to the grocery store, you purchase 10 items, but you buy them all at once. Transactions occur this way in many other areas of business as well like Sales Orders and Shipments etc. You'll have a single transaction, containing many "micro" transactions within it. In databases this is handled by a set of related tables, each with their own Granularity. The Sales Order Header table, with the granularity of a single Sales Order. And the Sales Order Detail table, with a finer granularity of a single Item sold.

Traditionally, due to the requirements of many analytical solutions at the time, this was resolved by denormalizing the header attributes and keys, down to the detail level. So all dimension tables related to the same transaction table in a Star Schema.

While the star schema is still a great approach, times have changed, and a strict dimensional model IS NOT the only option. The analytical tools of today use highly advanced in-memory technology able to aggregate data across many tables with ease. Today's business users driving these tools want options. They want to make decisions about what data to use, not have these decisions made for them. So in the Modern Data Warehouse, we still denormalize some of the keys to support various approaches, but we include the header tables in the final presentation layer to support Self-Service users.

In this section, we will cover denormalization of the transaction tables, adding calculated fields, and presenting the data in the Modern Data Warehouse.