Inventory Star 

Description

The Inventory Star schema is designed to hold data across all your Inventory within ITAS. This reporting star contains information on Commodity, Counterparty, Warehouse, Location, Weights, Packing, Contract Terms, and predominantly reads the phys01 Heritage table.

There is also a possibility that your frozen data can be imported into the star, at an additional cost. 

Star Schema

 

 

Example Query

Below is an example query that will select all of the available data from the fact and associated dimension tables.

 

SELECT               dbo.FactInventory.RecStart, dbo.FactInventory.RecEnd, dbo.FactInventory.SalesInvoiceDateKey, dbo.FactInventory.TitleTransferDateKey,

                               dbo.FactInventory.TitleTransferDateSaleKey, dbo.FactInventory.ContractQty, dbo.FactInventory.NoOfPacks, dbo.FactInventory.ContractKilos,

                               DimAccCounterparty.Acc AS CounterpartyCode, DimAccCounterparty.Title AS CounterpartyTitle, DimAccWarehouse.Acc AS WarehouseCode,

                               DimAccWarehouse.Title AS WarehouseTitle, dbo.DimCommodity.Commod, dbo.DimCommodity.Origin, dbo.DimCommodity.Grade,

                               dbo.DimCommodity.Code4, dbo.DimCommodity.Code5, dbo.DimCommodity.Ctry AS CommodityCtry, dbo.DimCommodity.Descr AS CommodityDescr,

                               DimUnitPackCode.Code AS PackCode, DimUnitPackCode.Descr AS PackCodeDescr, DimUnitPackCode.Conversion AS PackCodeConversion,

                               DimUnitPackCode.Plc AS PackCodePlc, DimUnitWeightUnit.Code AS WeightUnitCode, DimUnitWeightUnit.Descr AS WeightUnitDescr,

                               DimUnitWeightUnit.Conversion AS WeightUnitConversion, DimUnitWeightUnit.Plc AS WeightUnitPlc, DimTermsConTerms.Terms AS ConTermsCode,

                               DimTermsConTerms.Descr AS ConTermsDescr, DimTermsShipStatus.Terms AS ShipStatusCode, DimTermsShipStatus.Descr AS ShipStatusDescr,

                               dbo.DimCompany.SiteCode, dbo.DimCompany.CompanyCode, dbo.DimCompany.CompanyName, dbo.DimInventoryProfile.ContractNo,

                               dbo.DimInventoryProfile.Allocation, dbo.DimInventoryProfile.WarrantNo, dbo.DimInventoryProfile.InWarehouseReport,

                               dbo.DimInventoryProfile.DataSource, dbo.DimCtry.Ctry AS LocationCountryCode, dbo.DimCtry.Descr AS LocationCountryDescr,

                               dbo.DimCtry.ISOCode AS LocationCountryISO, dbo.FactInventory.DateReleasedKey, dbo.FactInventory.DateWarehousedKey

 

 

FROM                          dbo.FactInventory

 

INNER JOIN                 dbo.DimAccount AS DimAccCounterparty ON dbo.FactInventory.CounterpartyKey = DimAccCounterparty.AccountKey

INNER JOIN                 dbo.DimAccount AS DimAccWarehouse ON dbo.FactInventory.WarehouseKey = DimAccWarehouse.AccountKey

 

INNER JOIN                 dbo.DimCommodity ON dbo.FactInventory.CommodityKey = dbo.DimCommodity.CommodityKey

 

INNER JOIN                 dbo.DimUnit AS DimUnitPackCode ON dbo.FactInventory.PackCodeKey = DimUnitPackCode.UnitKey

INNER JOIN                 dbo.DimUnit AS DimUnitWeightUnit ON dbo.FactInventory.WeightUnitKey = DimUnitWeightUnit.UnitKey

 

INNER JOIN                 dbo.DimTerms AS DimTermsConTerms ON dbo.FactInventory.ContractTermsKey = DimTermsConTerms.TermsKey

INNER JOIN                 dbo.DimTerms AS DimTermsShipStatus ON dbo.FactInventory.ShippingStatusKey = DimTermsShipStatus.TermsKey

 

INNER JOIN                 dbo.DimCompany ON dbo.FactInventory.CompanyKey = dbo.DimCompany.CompanyKey

 

INNER JOIN                 dbo.DimInventoryProfile ON dbo.FactInventory.InventoryProfileKey = dbo.DimInventoryProfile.InventoryProfileKey

 

INNER JOIN                 dbo.DimCtry ON dbo.FactInventory.LocationCountryKey = dbo.DimCtry.CtryKey