Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

23.3. Understanding Inventory

We have reviewed the calculation of orders and accumulated totals (to understand Inventory Control) as well as un-weighted and weighted rolling averages, but we have yet to address inventory itself. In order to do this, we have modified the Adventure Works sample to include sample inventory data for the data warehouse and modifications for the Adventure Works cube to include this data. For the purpose of this chapter, we have sample generated clothing inventory data for June 2004. To run the tests yourself, you can execute the sample file FactSampleInventory.sql against the Adventure Works Data Warehouse SQL database while deploying the modified Adventure Works database.

Within inventory and many other data warehousing scenarios, you can look at the data from the standpoint of transactions and snapshots. We will now review these concepts.

23.3.1. Transactions

When reviewing the FactSampleInventory table, its structure is similar to the FactInternetSales table (within the Adventure Works DW SQL database) where each row represents some transaction that has occurred. For example, when executing the following SQL statement:

select f.InventoryID, t.FullDateAlternateKey as OrderDate, s.EnglishProductSubcategoryName, f.SalesOrderNumber, f.SalesOrderLineNumber, f.OrderQuantity from FactSampleInventory f inner join DimProduct p on p.ProductKey = f.ProductKey inner join DimProductSubCategory s on s.ProductSubCategoryKey = p.ProductSubCategoryKey inner join ...