Fact Aggregate Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Fact Aggregate Tables

Author

Topic

sauce1979
Starting Member

47 Posts

Posted - 2011-12-12 : 07:45:58

In our trading data warehouse we currently have a Fact table that shows the commsissions and and gross turnover per trader, broker, portfolio per instrument type per asset class.

One of the requirements from our buisiness users is to produce what is essentially a crosstab report that shows the commission, gross amounts and orders per month. I have currently created a procedure to do this but the procedure takes over 3 mins to run. The bulk of the time is taken up by getting the base data and then doing a pre aggregrate. User will access this data via a macro from execl. ssas or something similar would be best for this role but at the moment it is not an option

Therefore I would like to create a summary fact table which will be loaded after all the facts and dimensions are processed at the end of the month. The structure of the fact table is as follows


CREATE TABLE [dbo].[FactTradeOrderAllocation](
	[DataSourceKey] [int] NOT NULL,
	[TradeDateKey] [int] NOT NULL,
	[SettlementDateKey] [int] NOT NULL,
	[PortfolioKey] [int] NOT NULL,
	[BrokerKey] [int] NOT NULL,
	[InstrumentTypeKey] [int] NOT NULL,
	[InstrumentKey] [int] NOT NULL,
	[TraderKey] [int] NOT NULL,
	[ManagerKey] [int] NOT NULL,
	[ExchangeKey] [int] NOT NULL,
	[AuditKey] [int] NOT NULL,
	[InstrumentAttributes] [varchar](120) NOT NULL,
	[TradeOrderNumber] [varchar](50) NOT NULL,
	[TradeTransactionNumber] [varchar](50) NOT NULL,
	[TradeCurrency] [char](3) NOT NULL,
	[QuoteCurrency] [char](3) NOT NULL,
	[TransactionType] [varchar](20) NOT NULL,
	[TransactionSide] [varchar](20) NOT NULL,
	[Quantity] [numeric](18, 6) NOT NULL,
	[MarketPrice_EUR] [numeric](18, 6) NOT NULL,
	[GrossAmount_EUR] [numeric](22, 6) NOT NULL,
	[Commission_EUR] [numeric](18, 6) NOT NULL,
	[Fees_EUR] [numeric](18, 6) NOT NULL,
	[NetAmount_EUR] [numeric](22, 6) NOT NULL,
	[DataSourceCountry] [char](3) NOT NULL,
	[AssetClassCode] [char](1) NOT NULL,
	[OTC] [char](1) NOT NULL,
 CONSTRAINT [PK_dbo.FactTradeorderAllocation] PRIMARY KEY CLUSTERED 
(
	[DataSourceKey] ASC,
	[TradeDateKey] ASC,
	[SettlementDateKey] ASC,
	[PortfolioKey] ASC,
	[BrokerKey] ASC,
	[InstrumentTypeKey] ASC,
	[InstrumentKey] ASC,
	[TraderKey] ASC,
	[ManagerKey] ASC,
	[InstrumentAttributes] ASC,
	[TradeOrderNumber] ASC,
	[TransactionType] ASC,
	[TradeTransactionNumber] ASC,
	[TradeCurrency] ASC,
	[QuoteCurrency] ASC,
	[DataSourceCountry] ASC
)

My proposed Summary fact table structure is as follows



CREATE TABLE [dbo].[FactTradeAllocations_Agg](
	[DataSourceKey] [int] NOT NULL,
	[TradeDateKey] [int] NOT NULL,
	[SettlementDateKey] [int] NOT NULL,
	[PortfolioKey] [int] NOT NULL,
	[BrokerKey] [int] NOT NULL,
	[InstrumentTypeKey] [int] NOT NULL,
	[InstrumentKey] [int] NOT NULL,
	[TraderKey] [int] NOT NULL,
	[ManagerKey] [int] NOT NULL,
	[ExchangeKey] [int] NOT NULL,
	[AuditKey] [int] NOT NULL,
	[InstrumentAttributes] [varchar](120) NOT NULL,
	[TradeOrderNumber] [varchar](50) NOT NULL,
	[TradeTransactionNumber] [varchar](50) NOT NULL,
	[TradeCurrency] [char](3) NOT NULL,
	[QuoteCurrency] [char](3) NOT NULL,
	[TransactionType] [varchar](20) NOT NULL,
	[TransactionSide] [varchar](20) NOT NULL,
	[Quantity] [numeric](18, 6) NOT NULL,
	[MarketPrice_EUR] [numeric](18, 6) NOT NULL,
	[GrossAmount_EUR_Jan] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Feb] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Mar] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Apr] [numeric](38, 6) NULL,
	[GrossAmount_EUR_May] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Jun] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Jul] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Aug] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Sep] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Oct] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Nov] [numeric](38, 6) NULL,
	[GrossAmount_EUR_Dec] [numeric](38, 6) NULL,
	[GrossAmount_EUR_YTD] [numeric](38, 6) NULL,
	[Commission_EUR_Jan] [numeric](38, 6) NULL,
	[Commission_EUR_Feb] [numeric](38, 6) NULL,
	[Commission_EUR_Mar] [numeric](38, 6) NULL,
	[Commission_EUR_Apr] [numeric](38, 6) NULL,
	[Commission_EUR_May] [numeric](38, 6) NULL,
	[Commission_EUR_Jun] [numeric](38, 6) NULL,
	[Commission_EUR_Jul] [numeric](38, 6) NULL,
	[Commission_EUR_Aug] [numeric](38, 6) NULL,
	[Commission_EUR_Sep] [numeric](38, 6) NULL,
	[Commission_EUR_Oct] [numeric](38, 6) NULL,
	[Commission_EUR_Nov] [numeric](38, 6) NULL,
	[Commission_EUR_Dec] [numeric](38, 6) NULL,
	[Commission_EUR_YTD] [numeric](38, 6) NULL,
	[AllocationsJan] [int] NULL,
	[AllocationsFeb] [int] NULL,
	[AllocationsMar] [int] NULL,
	[AllocationsApr] [int] NULL,
	[AllocationsMay] [int] NULL,
	[AllocationsJun] [int] NULL,
	[AllocationsJul] [int] NULL,
	[AllocationsAug] [int] NULL,
	[AllocationsSep] [int] NULL,
	[AllocationsOct] [int] NULL,
	[AllocationsNov] [int] NULL,
	[AllocationsDec] [int] NULL,
	[Allocations_EUR_YTD] [float] NULL,
	
	 CONSTRAINT [PK_dbo.FactTradeorderAllocationAggregate] PRIMARY KEY CLUSTERED 
(
	[DataSourceKey] ASC,
	[TradeDateKey] ASC,
	[SettlementDateKey] ASC,
	[PortfolioKey] ASC,
	[BrokerKey] ASC,
	[InstrumentTypeKey] ASC,
	[InstrumentKey] ASC,
	[TraderKey] ASC,
	[ManagerKey] ASC,
	[InstrumentAttributes] ASC,
	[TradeOrderNumber] ASC,
	[TransactionType] ASC,
	[TradeTransactionNumber] ASC,
	[TradeCurrency] ASC,
	[QuoteCurrency] ASC,
	[DataSourceCountry] ASC

I just wanted to know if this is the best way to create the desired result. If i run the query of this table I can get the data in less than 5 seconds.

Any ideas or comments?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-12 : 08:14:31

If most queries are per month (or any date interval) set the date column first in the clustered index.
Also, does the clustered index *really* need to be 16 columns?

N 56°04'39.26"
E 12°55'05.63"

sauce1979
Starting Member

47 Posts

Posted - 2011-12-12 : 08:34:17

that was another thing I was thinking about. I just kept the clustered index the same as the detailed fact table. This does not have to be the case?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-12-13 : 03:06:44

No, absolutely not.
In your case, if the queries "always" include the date part in the query, just make the clustered index over the datetimecolumn and the surrogate key to make it unique.

CREATE TABLE	dbo.FactTradeAllocations_Agg
		(
			AggID INT IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED,
			[DataSourceKey] [int] NOT NULL,
			[TradeDateKey] [int] NOT NULL,
			[SettlementDateKey] [int] NOT NULL,
			[PortfolioKey] [int] NOT NULL,
			[BrokerKey] [int] NOT NULL,
			[InstrumentTypeKey] [int] NOT NULL,
			[InstrumentKey] [int] NOT NULL,
			[TraderKey] [int] NOT NULL,
			[ManagerKey] [int] NOT NULL,
			[ExchangeKey] [int] NOT NULL,
			[AuditKey] [int] NOT NULL,
			[InstrumentAttributes] [varchar](120) NOT NULL,
			[TradeOrderNumber] [varchar](50) NOT NULL,
			[TradeTransactionNumber] [varchar](50) NOT NULL,
			[TradeCurrency] [char](3) NOT NULL,
			[QuoteCurrency] [char](3) NOT NULL,
			[TransactionType] [varchar](20) NOT NULL,
			[TransactionSide] [varchar](20) NOT NULL,
			[Quantity] [numeric](18, 6) NOT NULL,
			[MarketPrice_EUR] [numeric](18, 6) NOT NULL,
			[GrossAmount_EUR_Jan] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Feb] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Mar] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Apr] [numeric](38, 6) NULL,
			[GrossAmount_EUR_May] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Jun] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Jul] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Aug] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Sep] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Oct] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Nov] [numeric](38, 6) NULL,
			[GrossAmount_EUR_Dec] [numeric](38, 6) NULL,
			[GrossAmount_EUR_YTD] [numeric](38, 6) NULL,
			[Commission_EUR_Jan] [numeric](38, 6) NULL,
			[Commission_EUR_Feb] [numeric](38, 6) NULL,
			[Commission_EUR_Mar] [numeric](38, 6) NULL,
			[Commission_EUR_Apr] [numeric](38, 6) NULL,
			[Commission_EUR_May] [numeric](38, 6) NULL,
			[Commission_EUR_Jun] [numeric](38, 6) NULL,
			[Commission_EUR_Jul] [numeric](38, 6) NULL,
			[Commission_EUR_Aug] [numeric](38, 6) NULL,
			[Commission_EUR_Sep] [numeric](38, 6) NULL,
			[Commission_EUR_Oct] [numeric](38, 6) NULL,
			[Commission_EUR_Nov] [numeric](38, 6) NULL,
			[Commission_EUR_Dec] [numeric](38, 6) NULL,
			[Commission_EUR_YTD] [numeric](38, 6) NULL,
			[AllocationsJan] [int] NULL,
			[AllocationsFeb] [int] NULL,
			[AllocationsMar] [int] NULL,
			[AllocationsApr] [int] NULL,
			[AllocationsMay] [int] NULL,
			[AllocationsJun] [int] NULL,
			[AllocationsJul] [int] NULL,
			[AllocationsAug] [int] NULL,
			[AllocationsSep] [int] NULL,
			[AllocationsOct] [int] NULL,
			[AllocationsNov] [int] NULL,
			[AllocationsDec] [int] NULL,
			[Allocations_EUR_YTD] [float] NULL
		)

CREATE UNIQUE CLUSTERED INDEX UIX_Agg ON dbo.FactTradeAllocations_Agg (TradeDateKey, AggID)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources