December 5, 2014 at 8:54 am
Hi
I have been working on tuning this report for a while, still couldn’t make it run fast within 1 minute.
The MDX query (see below) runs 15 seconds in SSAS but took 8 minutes to create a report (+ 6 million rows). To isolate the issue, I removed almost every column and row and column group except this main dataset, but duration time does not decrease.
I really appreciate your help!
----------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH MEMBER [Measures].[Group Name]
AS [Question].[Group - Translated Question].CurrentMember.Parent.NAME MEMBER [Measures].[LocationMemberName]
AS IIF(
[Location Rated].[Location - Wing].CurrentMember.LEVEL.Name = "Wing Name",
[Location Rated].[Location - Wing].CurrentMember.NAME + " Building",
[Location Rated].[Location - Wing].CurrentMember.NAME
)
MEMBER [Measures].[NSS] AS (KPIValue("NPS")) MEMBER [Measures].[Primary Top 2] AS (KPIValue("Top 2 Boxes"))
MEMBER [Measures].[Primary Bottom 4] AS (KPIValue("Bottom 4 Boxes"))
MEMBER [Measures].[Primary Response Count] AS ([Measures].[Response Count])
MEMBER [MinDate] AS DESCENDANTS([Tran Date].[Year - Quarter - Month - Date].currentmember,
[Tran Date].[Year - Quarter - Month - Date].[Date]).ITEM(0).PROPERTIES(("KEY"),
FORMAT_STRING= "MM/DD/YYYY"
MEMBER [MaxDate] AS TAIL (DESCENDANTS([Tran Date].[Year - Quarter - Month - Date].currentmember,
[Tran Date].[Year - Quarter - Month - Date].[Date]),1).ITME(0).PROPERTIES("KEY"), FORMAT_STRING = "MM/DD/YYYY"
SET [Parameter1] AS NONEMPTY(EXCEPT([Patron].[Numberof Stays].children,[Patron].[Numberof Stays].&[No Response]),[Measures].[NSS])
MEMBER [Measures].[Primary Segment] AS
([Patron].[Numberof Stays].currentmember.PROPERTIES ('Caption'))
SET [Parameter2] AS NONEMPTY(EXCEPT([Visit].[Check In Location].children,[Visit].[Check In Location].&[No Response]),[Measures].[NSS])
MEMBER [Measures].[Secondary Segment] AS ([Visit].[Check In Location].currentmember.PROPERTIES('Caption'))
SET [Column List] AS NONEMPTY({[Tran Date].[Year - Quarter - Month - Date].[Month].allmembers}[Measures].[NSS])
MEMBER [Breakout] AS[Tran Date].[Year - Quarter - Month - Date].currentmember.NAME
SELECT NON EMPTY {[Measures].[Primary Segment],
[Measures].[Secondary Segment],
[Measures].[Group Name],
[Measures].[LocationMemberName],
[Measures].[NSS], [Measures].[Primary Top 2],
[Measures].[Primary Bottom 4],
[Measures].[Primary Response Count],
[Measures].[MinDate],
[Measures].[MaxDate],
[Measures].[Breakout]} ON COLUMNS ,
NON EMPTY {([Parameter1]* [Parameter2]*
{STRTOSET("{[Location Rated].[Location - Wing].[Property Name].&[Building1],[Location Rated].[Location - Wing].[Property Name].&[Building2],
[Location Rated].[Location - Wing].[All]}", CONSTRAINED)} * [Question].[Classification Name].[Classification Name].ALLMEMBERS* [Column List]*
[Question].[Group - Translated Question].[Question].ALLMEMBERS* [Question].[MAIN Question].[All].CHILDREN) } ON ROWS
FROM ( SELECT [Score].[Use In Calculations Ind].&[True]
ON COLUMNS
FROM ( SELECT ( { [Question].[Question Type].&[Rated Question] } ) ON COLUMNS
FROM (SELECT
(UNION({[Question].[Classification Name].&[Overall Experience]},
STRTOSET("[Question].[Classification Name].[All]", CONSTRAINED)))ON COLUMNS
FROM (SELECT
(UNION(
[Question].[Group - Translated Question].[Group Name].&[Overall Experience].CHILDREN,
STRTOSET("{
[Question].[Group - Translated Question].[Group Name].&[Theatre Box Office],
[Question].[Group - Translated Question].[Group Name].&[Restaurant Row],
[Question].[Group - Translated Question].[Group Name].&[Pool Deck],
[Question].[Group - Translated Question].[Group Name].&[Banquet Services],
[Question].[Group - Translated Question].[Group Name].&[Connections - Motivations],
[Question].[Group - Translated Question].[Group Name].&[Overall Experience],
[Question].[Group - Translated Question].[Group Name].&[Staff Exceed Expectations]} ",
CONSTRAINED)),
{[Tran Date].[Year - Quarter - Month - Date].[Date].&[2013-09-09T00:00:00]:
[Tran Date].[Year - Quarter - Month - Date].[Date].&[2014-10-09T00:00:00]})
ON COLUMNS FROM [FactTable))))
December 8, 2014 at 1:23 am
With the following you would be selecting everything:
UNION({[Question].[Classification Name].&[Overall Experience]},
STRTOSET("[Question].[Classification Name].[All]", CONSTRAINED)))ON COLUMNS
You are choosing a specific member followed by the All member. Also you seem to be using UNION a lot where simple crossjoins would do (you have some of them too so I'm not sure why it's inconsistent).
I take it this code was generated at least partially by SSRS? It often makes some strange decisions. Try starting from the beginning and writing the code in SSMS to very succinctly produce what you need. Then you can add embellishments such as parameters.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply