CODEWITHSUNDEEP
sql-serverexcelpivot-tablemdxssas-tabular
FLICKER
FLICKER

Reputation: 6693

Performance issue on MDX query when moving dimension from row to column

I have a Tabular cube () on SSAS and I create a Pivot in Excel connection to that.

I have 2 measures and 2 dimensions. If I move one of the dimensions to column, it will take a minute or two to process.

The MDX query is different in those cases

Fast - Pivot

--Fast Qry & result set all on rows

SELECT {[Measures].[Sum of FairValue],[Measures].[Sum of TradeAmt]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , 
NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[HedgeValuation].[CloseDt].[All],[HedgeValuation].[CloseDt].[CloseDt].AllMembers}, 
{([HedgeValuation].[TicketNum].[All])}), [HedgeValuation].[CloseDt].[CloseDt].AllMembers, [HedgeValuation].[TicketNum])) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS  FROM [Model] WHERE ([SnapshotDt].[CurrentSnapshotDtFlag].&[Y]) 
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Fast - Result in Management Studio

Slow Pivot

--Slow and result is in column

SELECT NON EMPTY CrossJoin(Hierarchize(DrilldownMember(CrossJoin({[HedgeValuation].[CloseDt].[All],[HedgeValuation].[CloseDt].[CloseDt].AllMembers}, 
{([HedgeValuation].[TicketNum].[All])}), [HedgeValuation].[CloseDt].[CloseDt].AllMembers, [HedgeValuation].[TicketNum])), 
{[Measures].[Sum of FairValue],[Measures].[Sum of TradeAmt]}) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS  FROM [Model] 
WHERE ([SnapshotDt].[CurrentSnapshotDtFlag].&[Y])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Slow - Result in Management Studio

In my view, the second query only moves the rows to columns.

I appreciate if anyone can explain why these 2 different layouts have different performance, and is there any way I can improve performance for the second layout?

Upvotes: 1

Views: 425

Answers (1)

SeeCoolGuy
SeeCoolGuy

Reputation: 81

From my experience, the short answer is, adding many columns to your pivot table, will degrade the performance.

Pivoting your query so you have more rows than columns will return faster results even when the same data is on the screen. I've tested with PowerPivot and SSMS and the results are similar. less columns, more speed

additional info visit: http://tinylizard.com/power-pivot-performance-gotchas/ and https://powerpivotpro.com/2011/08/less-columns-more-rows-more-speed/ which do a better job explaining this than me.. :D

Upvotes: 1

Related Questions