Cost Star 

Description

The Cost Star schema is designed to hold data across all your costs. This reporting star contains information on Supplier, Price, Currency, Contracts, Polarisation and Rates, and predominantly reads the phys03 Heritage table for Costs and the phys02 Heritage table for Commissions.

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.FactCost.RecStart, dbo.FactCost.RecEnd, dbo.FactCost.Code, dbo.FactCost.PayRec, dbo.FactCost.RateAmount, dbo.FactCost.RateType,

                                  dbo.FactCost.Uni, dbo.FactCost.RoeType, dbo.FactCost.Roe, dbo.FactCost.Pl, dbo.FactCost.PercentActual, dbo.FactCost.MDate,

                                  dbo.FactCost.InvQty, dbo.FactCost.DocValue, dbo.FactCost.DocRef, dbo.FactCost.Type, dbo.FactCost.Pole, dbo.FactCost.DocDate,

                                  dbo.FactCost.OrigRateAmount, dbo.DimCompany.SiteCode, dbo.DimCompany.CompanyCode, dbo.DimCompany.CompanyName,

                                  dbo.DimCcy.Ccy AS CcyCode, dbo.DimCcy.Descr AS CcyDescr, dbo.DimAccount.Acc AS BrokerSupplierAccount,

                                  dbo.DimAccount.Title AS BrokerSupplierTitle, dbo.DimUnit.Code AS WeightCode, dbo.DimUnit.Descr AS WeightCodeDescr,

                                  dbo.DimUnit.Conversion AS WeightCodeConversion, dbo.DimUnit.Plc AS WeightCodePlc, dbo.DimCollection.Contract,

                                  dbo.DimCollection.ContractType, dbo.DimTerms.Terms AS PriceCode, dbo.DimTerms.Descr AS PriceCodeDescr

FROM                             dbo.FactCost

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

INNER JOIN                    dbo.DimCcy ON dbo.FactCost.CcyKey = dbo.DimCcy.CcyKey

INNER JOIN                    dbo.DimAccount ON dbo.FactCost.BrokerSupplierKey = dbo.DimAccount.AccountKey

INNER JOIN                    dbo.DimUnit ON dbo.FactCost.WtCodeKey = dbo.DimUnit.UnitKey

INNER JOIN                    dbo.DimCollection ON dbo.FactCost.ContractKey = dbo.DimCollection.CollectionKey

INNER JOIN                    dbo.DimTerms ON dbo.FactCost.PriceCodeKey = dbo.DimTerms.TermsKey