Traffic Star 

Description

The Traffic Star schema is designed to hold data across all of your Physicals Traffic. The Traffic Star contains information on Contract, Commodity, Department, Counterparty, Vessel, Ports, Arrival and Departure Dates. This star predominantly uses 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.FactTraffic.TrafficKey, dbo.FactTraffic.RecStart, dbo.FactTraffic.RecEnd,

dbo.FactTraffic.BillOfLadingDate, dbo.FactTraffic.BillOfLandingReference, dbo.FactTraffic.ShippingStatus, dbo.FactTraffic.PortOrigin, dbo.FactTraffic.PortVia, dbo.FactTraffic.PortDest,

dbo.FactTraffic.Consignee, dbo.FactTraffic.EstArrivalDate, dbo.FactTraffic.TranShipVessel, dbo.FactTraffic.TranShipEta, dbo.FactTraffic.LoadEta, dbo.FactTraffic.LoadArrival,

dbo.FactTraffic.EstDepartDate, dbo.FactDepartureDate, dbo.FactTraffic.TranShipVssel, dbo.FactTraffic.TranShipEta, dbo.FactTraffic.LoadEta, dbo.FactTraffic.LoadArrival,

dbo.DimCommodity.Commod, dbo.DimCommodity.Origin,

dbo.DimCommodityGrade.Grade, dbo.DimCommodity.Code4, dbo.DimCommodity.Grade5, dbo.DimCommodity.Ctry AS dbo.CommodityCtry, dbo.DimCommodity.Descr AS dbo.DimCommodityDescr,

dbo.DimAccount.Acc, dbo,DimAccount.Title

dbo.Contract.Contract, dbo.DimContract.ContractType,

dbo.DimCompany.SiteCode, dbo.DimCompany.CompanyCode, dbo.DimCompany.CompanyName,

dbo.DimDept.Descr, dbo.DimDept.Dept, dbo.FactTraffic.VesselReference

FROM                  dbo.FactTraffic

INNER JOIN          dbo.DimAccount  ON dbo.FactTraffic.CounterpartyKey = dbo.DimAccount.AccountKey

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

INNER JOIN          dbo.DimContract ON dbo.FactTraffic.ContractKey = dbo.DimContract.ContractKey

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

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