Sometimes, when capturing data in a data warehouse, we need to store time-variant pieces of data about a transaction. This somewhat blurs the lines between a traditional fact table and a dimension, since in the traditional model, time-variance is mainly the domain of a dimension.
Take the example of a production backlog at a manufacturer. When an order is made, particularly in an organization that manufactures large and/or complex goods, it may take some time to fulfill. Maintaining a consistent backlog is also a key to ensuring consistent production planning that’s not beset with shutdowns, inefficiencies, or missed delivery dates.
Keeping a backlog at a granular level generally requires tracking backlog on an order-by-order basis. That way, anything about an order (that’s in your warehouse) can be analyzed to look for trends in the business. There’s just one issue: keeping a snapshot of every order in backlog for the full amount of time it’s in backlog can take up a lot of space. For instance in a mid-size company: if the average order is in backlog for three months and the company receives 10,000 orders per year, that’s nearly a million records per year in a daily snapshot. After a while, that can really add up. It’s no wonder Bill Inmon said, “The management of these every day, ongoing changes can consume an enormous amount of resources and can be very, very complex. It is this type of load that rivets the attention of the data architect.” (Snapshots in the Data Warehouse, pg. 2, white paper at http://inmoncif.com/inmoncif-old/www/library/whiteprs/ttsnap.pdf)
This example could also apply to general A/R snapshots by account, though in many organizations, this snapshot is taken on a monthly basis, so the problem is less imperative.
As the snapshot grows, a simplified version may look something like this:
|Order Key||Date Key||Item Key||Past Due Backlog Amount||Future Backlog Amount|
|50||20131202 – 20131231||…||…||…|
|50||20140103 – 20140114||..||..||..|
In this example, an order for $65,000.00 was placed on December 1, 2013. $35,000.00 worth of product is for delivery on December 31, while $30,000.00 worth of product is scheduled for later. So, all $65,000 goes into future backlog when the order is received.
To download PDF and Continue Reading…
About Britton Gray
Britton has been working in software development and Business Intelligence for the past fourteen years, and has been working with SAP BusinessObjects reporting, analytics, and information management tools for six years. He is certified in Data Integrator and Web Intelligence, and enjoys developing end-to-end BI solutions that enable customers to gain new insights into their data.