Futures and Options Star 

Description

The Futures and Options Star schema is designed to hold data across all your F&O trades. This reporting star contains information on Client, Broker, Floor Broker, Department, Trade Type, Trade Terms and Price, and predominantly reads the fut02 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

The below example query will select all of the available data from the fact and associated dimension tables.

 

SELECT                 dbo.FactFutures.RecStart, dbo.FactFutures.RecEnd, dbo.FactFutures.OptionSeries, dbo.FactFutures.OptionSeriesFormatted, dbo.FactFutures.TradePrice,

                                 dbo.FactFutures.TradePriceFormatted, dbo.FactFutures.CommRate, dbo.FactFutures.CommValue, dbo.FactFutures.LotsSigned, dbo.FactFutures.Lots,

                                 dbo.FactFutures.ContractValue, dbo.FactFutures.MarketPrice, dbo.FactFutures.MarketPriceFormatted, dbo.FactFutures.MarketValue,

                                 dbo.FactFutures.PnL, dbo.FactFutures.Delta, dbo.FactFutures.DeltaValue, dbo.FactFutures.PremiumPaid, dbo.FactFutures.PremiumHeld,

                                 dbo.FactFutures.Conversion, dbo.DimFutureUnique.TradeDate, dbo.DimFutureUnique.SerialNo, dbo.DimFutureUnique.Uni, dbo.DimCompany.SiteCode,

                                 dbo.DimCompany.CompanyCode, dbo.DimCompany.CompanyName, DimAccClient.Acc AS ClientAccount, DimAccClient.Title AS ClientTitle,

                                 DimAccBroker.Acc AS BrokerAccount, DimAccBroker.Title AS BrokerTitle, dbo.DimDept.Dept, dbo.DimDept.Descr AS DeptDescr,

                                 dbo.DimTerms.Terms AS TradeCode, dbo.DimTerms.Descr AS TradeCodeDescr, DimCcy.Ccy, DimCcy.Descr AS CcyDescr,

                                 DimCcyCommCcy.Ccy AS CommCcy, DimCcyCommCcy.Descr AS CommCcyDescr, dbo.DimFuturesCmy.Code AS CmyCode,

                                 dbo.DimFuturesCmy.Descr AS CmyCodeDescr, dbo.DimFuturesCmy.Ccy AS CmyCodeCcy, dbo.DimFuturesCmy.Exchange,

                                 dbo.DimFuturesCmy.Conversion AS CmyCodeConversion, dbo.DimFuturesCmy.FutLotRatio, dbo.DimFuturesCmy.PremHeld,

                                 dbo.DimFuturesCmy.FutDenom, dbo.DimFuturesCmy.OptDenom, dbo.DimFuturesCmy.DecPlc, dbo.DimFuturesCmy.SpanStrikeDecPlc,

                                 dbo.DimFuturesProfile.ClientType, dbo.DimFuturesProfile.BuySell, dbo.DimFuturesProfile.TradeType, dbo.DimFuturesProfile.Settled,

                                 dbo.DimFuturesProfile.Internal, dbo.FactFutures.PromptMonthKey, dbo.FactFutures.SettlementDateKey.

 

 

FROM                              dbo.FactFutures

 

INNER JOIN                     dbo.DimFutureUnique ON dbo.FactFutures.FutureUniqueKey = dbo.DimFutureUnique.FutureUniqueKey

 

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

 

INNER JOIN                     dbo.DimAccount AS DimAccClient ON dbo.FactFutures.ClientKey = DimAccClient.AccountKey

INNER JOIN                     dbo.DimAccount AS DimAccBroker ON dbo.FactFutures.BrokerKey = DimAccBroker.AccountKey

 

INNER JOIN                     dbo.DimDept ON Fdbo.actFutures.DeptKey = dbo.DimDept.DeptKey

 

INNER JOIN                     dbo.DimTerms ON dbo.FactFutures.TradeCodeKey = DimTerms.TermsKey

 

INNER JOIN                     dbo.DimCcy AS DimCcy ON dbo.FactFutures.CcyKey = DimCcy.CcyKey

INNER JOIN                     dbo.DimCcy AS DimCcyCommCcy ON dbo.FactFutures.CommCcyKey = DimCcyCommCcy.CcyKey

 

INNER JOIN                     dbo.DimFuturesCmy ON dbo.FactFutures.CmyCodeKey = dbo.DimFuturesCmy.FuturesCmyKey

 

INNER JOIN                     dbo.DimFuturesProfile ON dbo.FactFutures.FuturesProfileKey = dbo.DimFuturesProfile.FuturesProfileKey