Developing a Data Load Script for Inventory Analysis
In this chapter, we develop the Data Model for our Profitability Analysis application. In doing so, we describe how to:
- Generate a running balance, using the Inter-Record functions
- Distribute a single inventory balance into buckets by aging date.
IntervalMatch to match distinct dates with continuous periods.
- Combine multiple fact tables into a single Concatenated Fact.
- Populate missing attributes across multiple slices of the Concatenated Fact.
- Add a generic key with a Generic Link Table to create or restore a missing association.
- Use advanced Set Analysis as an alternative to Generic Link Tables.
- Allow advanced date analysis with the As of Date table.
- Use the load script and text files to manage expression definitions stored in variables.
Review of the Business Requirements and the Data Sources
Before we get to the task of building the data model, let’s summarize the business requirements and the data sources for this application. The main goal of this particular implementation of Inventory Analysis is to analyze which products have excess balances in the inventory, in comparison to historical sales. The business sponsors of the project outlined the following required features. The application should include:
- Detailed drill-down analysis of the current on-hand balances, as well as a 12-month history of inventory.
- Calculations of months on hand and inventory turns, made by comparing ...