Physical Contract Star 

Description

The Physical contracts star schema is designed to hold data across all your contracts. This reporting star contains information on Commodity, Trader, Supplier, Counterparty, Primary Agent, Weights, Ship Weights, Bags, Lots, Payment Terms, Contract Terms, Arbit Terms and Admin Terms, and predominantly reads the phys01 Heritage table. 

There are two significant upgrades delivered in this star:

• Firstly Trading Entity data is no longer segregated – this star holds data for all your trading entities or companies.

• Secondly the star saves data across time (also known as an accumulating snapshot star schema). This means you will be able to create time based reports to (for example): comparing data between years or across quarters.

There is 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 FactPhysical.RecStart, dbo.FactPhysical.RecEnd, dbo.FactPhysical.Weight, dbo.FactPhysical.Bags,

                              dbo.FactPhysical.LotsToFix, dbo.FactPhysical.TotalLots, dbo.DimCompany.SiteCode, dbo.DimCompany.CompanyCode,

                              dbo.DimCompany.CompanyName, dbo.DimDept.Dept, dbo.DimDept.Descr AS DeptDescr,

                              DimAccountTrader.Acc AS TraderAccountCode, DimAccountTrader.Title AS TraderAccountTitle,

                              DimAccountBuyer.Acc AS BuyerAccountCode, DimAccountBuyer.Title AS BuyerAccountTitle,

                              DimAccountSeller.Acc AS SellerAccountCode, DimAccountSeller.Title AS SellerAccountTitle,

                              DimAccountPrimaryAgent.Acc AS PrimaryAgentCode, DimAccountPrimaryAgent.Title AS PrimaryAgentTitle,

                              DimTermsShip.Terms AS ShipTermsCode, DimTermsShip.Descr AS ShipTermsDescr, DimTermsConTerms.Terms AS ConTermsCode,

                              DimTermsConTerms.Descr AS ConTermsDescr, DimTermsPayTerms.Terms AS PayTermsCode,

                              DimTermsPayTerms.Descr AS PayTermsDescr, DimTermsArbit.Terms AS ArbitTermsCode,

                              DimTermsArbit.Descr AS ArbitTermsDescr, DimTermsAdmin.Terms AS AdminTermsCode,

                              DimTermsAdmin.Descr AS AdminTermsDescr, DimUnitWeight.Code AS WeightCode, DimUnitWeight.Descr AS WeightCodeDescr,

                              DimUnitWeight.Conversion AS WeightCodeConversion, DimUnitWeight.Plc AS WeightCodePlc, DimUnitBags.Code AS BagsCode,

                              DimUnitBags.Descr AS BagsDescr, DimUnitBags.Conversion AS BagsConversion, DimUnitBags.Plc AS BagsPlc,

                              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,

                              DimColCon.Contract, DimColCon.ContractType, DimColCrop.CropYear

 

FROM                          dbo.FactPhysical

 

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

 

INNER JOIN                 dbo.DimDept ON dbo.FactPhysical.DepartmentKey = dbo.DimDept.DeptKey

 

INNER JOIN                 dbo.DimAccount AS DimAccountTrader ON dbo.FactPhysical.TraderAccountKey = DimAccountTrader.AccountKey

INNER JOIN                 dbo.DimAccount AS DimAccountBuyer ON dbo.FactPhysical.BuyerAccountKey = DimAccountBuyer.AccountKey

INNER JOIN                 dbo.DimAccount AS DimAccountSeller ON dbo.FactPhysical.SellerAccountKey = DimAccountSeller.AccountKey

INNER JOIN                 dbo.DimAccount AS DimAccountPrimaryAgent ON dbo.FactPhysical.PrimaryAgentAccountKey = DimAccountPrimaryAgent.AccountKey

 

INNER JOIN                 dbo.DimTerms AS DimTermsShip ON dbo.FactPhysical.ShipWeightKey = DimTermsShip.TermsKey

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

INNER JOIN                 dbo.DimTerms AS DimTermsPayTerms ON dbo.FactPhysical.PaymentTermsKey = DimTermsPayTerms.TermsKey

INNER JOIN                 dbo.DimTerms AS DimTermsArbit ON dbo.FactPhysical.ArbitKey = DimTermsArbit.TermsKey

INNER JOIN                 dbo.DimTerms AS DimTermsAdmin ON dbo.FactPhysical.AdminKey = DimTermsAdmin.TermsKey

 

INNER JOIN                 dbo.DimUnit AS DimUnitWeight ON dbo.FactPhysical.WeightUnitKey = DimUnitWeight.UnitKey

INNER JOIN                 dbo. DimUnit AS DimUnitBags ON dbo.FactPhysical.BagsUnitKey = DimUnitBags.UnitKey

 

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

 

INNER JOIN                 dbo.DimCollection AS DimColCon ON dbo.FactPhysical.ContractKey = DimColCon.CollectionKey

INNER JOIN                 dbo.DimCollection AS DimColCrop ON dbo.FactPhysical.CropYearKey = DimColCrop.CollectionKey