May 14, 2015 at 1:39 pm
HI all,
Im trying to achieve some level of pagination within SSAS for a cube that is queried by a website we are working on. The intail developer wrote a bit of code that was taking upwards of 20 min to execute and returned around 80k records for a simple widget. My approach to speed this up is do some sort of pagination within MDX but Im stuck.
Here is my code:
SELECT
{
[Measures].[Cost]
,[Measures].[Clicks]
,[Measures].[Conversion Rate]
,[Measures].[Revenue]
,[Measures].[Impressions Share]
,[Measures].[Cost Per Click]
,[Measures].[Quality Score]
,[Measures].[Average Position]
,[Measures].[% Cost]
,[Measures].[Lost Impression Share (Budget)]
,[Measures].[Lost Impressions Share Ranking]
,[Measures].[Impressions]
,[Measures].[Click Through Rate]
,[Measures].[Conversions]
,[Measures].[Converted Clicks]
,[Measures].[% Conversions]
,[Measures].[Cost Per Lead]
,[Measures].[Max CPC]
,[Measures].[% Revenue]
,[Measures].[Return On Ad Spend]
} ON COLUMNS
,
TOPCOUNT({
NONEMPTY(([Keyword].[Keyword].Children,
NONEMPTY([Campaign].[Engine].[Engine].ALLMEMBERS) *
NONEMPTY([Campaign].[Campaign].[Campaign].ALLMEMBERS) *
[Campaign].[Ad Group].Children *
NONEMPTY([Campaign].[Branding].[Branding].ALLMEMBERS) )
)},15,[Measures].[Clicks]) ON ROWS
FROM cube
WHERE (..)
This executes in about 1 second which is fine, however the business requirement is that the end user can click to go to any set of 15 results.
I tried to do this with SUBSET and ORDER but it killed performance.
Any advice you could give me would be massively helpful
THANKS!!!!!
May 14, 2015 at 2:01 pm
The only other way I think you could possibly get close (haven't tried it though), is by using a combination of rank() and filter() maybe. You'd also probably have to build the MDX dynamically.
Not sure how close you're going to get though...MDX isn't really geared for that sort of thing natively.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply