Physical Valuation Star 

Description

The Physical Valuation star is also an ‘accumulating snapshot schema’ meaning it stores data over time. The data held in this star cannot be found anywhere else in ITAS because the values are generated via the business logic as they are saved. This is a significant shift in approach from Hivedome and is highly valued by our clients as it means that positions can be reported on over time to (for example) compare positions over years, quarters of months.

As with the Physical Contract star, data across all Trading Entities is saved in the same star, 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.

The Star contains seven sets of valuation data linked to: Contract, Trading Entity, Department, Currency, Date and Time. The seven sets of data include:

  • Contract Value

  • Total Contract Costs Value

  • Commission Value

  • Polarisation Value

  • Market Value

  • F&O Value

  • FX Value

These seven sets of data are available in three different currency values (making twenty one data points per entry):

  • PnL = Contract P&L currency i.e. phys01_roeccy

  • Contract = contract currency i.e. phys01_ccy

  • Functional = company primary reporting currency i.e. ctrl02_ccy

So as an example the Total Contract Costs Value included three values:

  • CostsValPnL

  • CostsValContract

  • CostsValFunctional

 

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.FactPhysicalValuation.RecStart, dbo.FactPhysicalValuation.RecEnd, dbo.FactPhysicalValuation.ConValPnl, dbo.FactPhysicalValuation.ConValContract,

dbo.FactPhysicalValuation.ConValFunctional1, dbo.FactPhysicalValuation.CostValPnL, dbo.FactPhysicalValuation.CostValContract, dbo.FactPhysicalValuation.CostValFunctional1,

dbo.FactPhysicalValuation.ComsnValPnL, dbo.FactPhysicalValuation.ComsnValContract, dbo.FactPhysicalValuation.ComsnValFunctional1, dbo.FactPhysicalValuation.PolValPnl, dbo.FactPhysicalValuation.PolValContract, dbo.FactPhysicalValuation.PolValFunctional1, dbo.FactPhysicalValuation.MktValPnL, dbo.FactPhysicalValuation.MktValContract,

dbo.FactPhysicalValuation.MktValFunctional1, dbo.FactPhysicalValuation.FOPnL, dbo.FactPhysicalValuation.FOContract, dbo.FactPhysicalValuation.FOFunctional1,

dbo.FactPhysicalValuation.FXPnL, dbo.FactPhysicalValuation.FXContract, dbo.FactPhysicalValuation.FXFunctional1, dbo.DimContract.Contract, dbo.DimContract.ContractType,

DimAccCounterparty.Acc AS CounterpartyAccount, DimAccCounterparty.Title AS CounterpartyTitle, DimAccTrader.Title AS TraderTitle, dbo.DimCompany.SiteCode

dbo.DimCompany.CompanyCode, dboDimCompany.CompanyName, dbo.DimDept.Dept, dbo.DimDept.Descr, 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,

dboDimCtry.Ctry AS CommodityCtryCode, dbo.DimCtry.Descr AS CommodityCtryDescr, dbo.DimCtry.ISOCode AS CommodityCtryISO, DimCcyPnlCcy AS PnLCcyCode,

DimCcyPnLDescr AS PnLCcyDescr, DimCcyContractCcy AS ContractCcyCode, DimCcyContractDescr AS ContractCcyDescr, DimCcyFunctionalCcy AS FunctionalCcyCode,

DimCcyFunctionalDescr AS FunctionalCcyDescr, DimAccTrader.Acc AS TraderAcc

 

FROM                            dbo.FactPhysicalValuation

 

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

 

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

INNER JOIN                   dbo.DimAccount AS DimAccTrader ON dbo.FactPhysicalValuation.TraderKey = DimAccTrader.AccountKey

 

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

 

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

 

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

 

INNER JOIN                   dbo.DimCtry ON dbo.FactPhysicalValuation.CommodityCtryKey = dbo.DimCtry.CtryKey

 

INNER JOIN                   dbo.DimCcy AS DimCcyPnL ON dbo.FactPhysicalValuation.PnLCcyKey = DimCcyPnL.CcyKey

INNER JOIN                   dbo.DimCcy AS DimCcyContract ON dbo.FactPhysicalValuation.ContractCcyKey = DimCcyContract.CcyKey

INNER JOIN                   dbo.DimCcy AS DimCcyFunctional ON dbo.FactPhysicalValuation.Functional1CcyKey = DimCcyFunctional.CcyKey