There are three fundamental types of fact tables in the data warehouse presentation area: transaction fact tables, periodic snapshot fact tables, and accumulating snapshot fact tables. Most DW/BI design teams are very familiar with transaction fact tables. They are the most common fact table type and are often the primary workhorse schema for many organizations. Many teams have also incorporated periodic snapshot fact tables in their presentation areas. Fewer organizations leverage the accumulating snapshot fact table. Design teams often don’t appreciate how an accumulating snapshot fact table can complement transaction and/or periodic snapshot fact tables.
Each of the fact table types is a design response to the wide variety of requirements posed by the business community. Often the best design response is a combination of two, or even all three, fact table types. The multiple fact tables complement one another, each supporting a unique view of the business processes that would be difficult to achieve with only one fact table type.
A logistics supply chain is an excellent scenario to illustrate all three fact table types working together to support a rich set of business requirements. We’ll use a simplified view of the finished goods logistics pipeline of a large auto manufacturer to help understand the strengths and appropriate use of each fact table type.
Our auto manufacturer has plants where vehicles are assembled. The finished vehicles eventually find their way to dealers where they will be sold to the ultimate owners. Our fictitious auto manufacturer maintains finished goods inventory in a large parking lot – really a warehouse – located just outside the assembly plant doors. Vehicles (inventory) are shipped from the finished goods warehouse via freight train to one of several regional parking lots. From these regional warehouses, the inventory is shipped via carrier truck to dealer locations. Once the vehicle arrives at the dealer, it is prepped and put on the dealer lot (store inventory) for final sale.
The logistics business users need to understand the number of vehicles flowing out of final assembly, in and out of each warehouse, and the final customer demand for various vehicle types, colors, models, and so on. The company also needs to understand and analyze the inventory levels at each stage of the logistics chain. Logistics management wants to understand the time it takes for a vehicle to move from the assembly plant to the final customer, depending on vehicle type, warehouses, and carriers. Moving vehicles more quickly and efficiently through the logistics pipeline helps the company minimize inventory levels and reduce carrying costs.
A robust design to support our auto manufacturer’s finished goods logistics pipeline illustrates all three fact table types.
Transaction Fact Table
A key component of the logistics pipeline is the flow of inventory from one location to another. The flow of vehicles is captured in a series of inventory movement transactions. An assembly plant releases a vehicle into the finished goods inventory via an inventory movement transaction. The vehicle is then shipped via rail to the regional warehouse where it is received into its inventory; later it is removed from inventory and shipped via truck to the dealer where it is received into the dealer inventory. For each of these inventory moves, an inventory movement or shipping/receiving transaction is generated. The inventory flow is a great opportunity for a transaction fact table. The grain of this fact table is one row for each inventory movement transaction for each vehicle. Similarly the final sale of the vehicle should be captured in a sales transaction fact table with one row for each vehicle sold.
Transaction fact tables are an appropriate design response to business requirements that look for an understanding of the intensity or quantity of a business process. Transaction fact tables help answer the “how many?” question. For example, how many white sports utility vehicles (SUVs) were sold last week? What were the dollar sales? How many all-wheel drive vehicles were released by assembly into salable inventory? How many vehicles did we ship with a given carrier? How many vehicles were received by our dealers this month? Compared to last month, last quarter, or last year? There’s a reason transaction fact tables are the workhorse fact table type: they support critically important business requirements. On the other hand, transaction fact tables are less effective in answering questions regarding the state of our inventory levels or the speed/efficiency of the logistics pipeline. To support these business requirements, we look to other fact table types to complement transaction fact tables.
Periodic Snapshot Fact Table
The second requirement is understanding the total amount of inventory at any point in the pipeline. Supporting analysis of inventory levels is a task well suited for a periodic snapshot fact table. At any point in time, each vehicle is in a single physical location such as finished goods inventory at the plant, in a regional distribution center, on a dealer lot, or in-transit on a railcar or truck. To support inventory analysis, the periodic snapshot fact table has a grain of one row per vehicle each day. A location dimension will support the analysis of inventory in each point of the pipeline.
The periodic snapshot fact table does an excellent job of helping understand the volume of vehicles in our pipeline. It answers the “how much?” question. How much total inventory do we have? How much inventory is available in white vehicles? SUVs? Four doors? Sport models? In California? On dealer lots? Compared to prior months, quarters, or years? The periodic snapshot supports trending of inventory levels over time. The inventory movement transaction fact table and inventory periodic snapshot together support a wide range of the business requirements surrounding the logistics pipeline. However, even with both of these fact tables, it will be difficult to address the pipeline efficiency requirements. To complete the picture, an accumulating snapshot fact table will complement the transaction and periodic snapshot fact tables.
Accumulating Snapshot Fact Table
The third set of requirements for the logistics pipeline is supporting analysis of the speed at which vehicles travel through the pipeline (no pun intended). Each vehicle will pass through a series of milestones during its travels to the final owner. To support the analytic requirements for measuring and understanding the efficiencies of our logistics pipeline, an accumulating snapshot fact table will be populated with one row per vehicle. As each vehicle moves through the pipeline, the accumulating snapshot will be updated with the date of each movement and current location of the vehicle. The accumulating snapshot will have numerous date keys, such as date released by assembly, date shipped to distribution center, date received at distribution center, and so on until date of final sale. Fact table metrics will include a series of date lags that measure the time it took for a vehicle to move between pipeline steps .
The accumulating snapshot fact table supports the key efficiency measures of velocity.How quickly do vehicles move through the pipeline? What’s the average length of time from assembly release to final customer sale? Is it different for autos than SUVs? Hybrids versus non-hybrids? White versus red vehicles? Which carriers/railroads are most effective? The accumulating snapshot will be updated daily for vehicles currently in the logistics pipeline. Thus, the accumulating snapshot can also be used to look at the current state of the pipeline to identify “stuck” vehicles, such as find all the vehicles that have been at a regional distribution center or on a dealer lot for more than “n” days. How many vehicles have been in transit via rail or truck for more than “n” days? Where are all the SUVs? This fact table can help the logistics team identify and move the vehicles in highest demand, identify efficiency improvement opportunities, and identify preferred shipping partners.
In the case of our auto manufacturer, it is clear that the three fact table types complement one another. Implementing all three fact table types is an appropriate response to the rich set of business requirements. Implementing only one or even only two of the fact table types would have made it very difficult, if not impossible, to support all the requirements.
