O'Reilly logo

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX by Denny Guang-Yeu Lee, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Sivakumar Harinath

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required