CODEWITHSUNDEEP
performancecountdistinctmdx
ewan
ewan

Reputation: 73

mdx distinct count performance

I got a MDX query like below which returns average distinct count for the last 30 days and it returns correct result, but performance is not good , in my fact table there will be almost 2,500,000 rows data everyday.

WITH Member Measures.DailyAverageUser AS
    Avg ( EXISTING{[Date].[Date].[Date].Members},
                 [Measures].[Active Tiles Employee Id Distinct Count]
                ),format_String="##.000000"
             SELECT   Measures.DailyAverageUser ON 0,
            NON EMPTY([Action Targets].[Name].Children)  ON 1
            FROM [OLAP Pre]
            WHERE (
                {[Target Types].[Name].&[tile]},{[Employee Statuses].[Status Id].&      [1],[Employee Statuses].[Status Id].&[3],[Employee Statuses].[Status Id].&[4]},
            {[Business Region].[Abbreviation].&[NONE],[Business Region].[Abbreviation].&[AMS],[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA]},{[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[5],[Employee Types].[Bits].&[9],[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[13],[Employee Types].[Bits].&[29]},{[Date].[Date Key].&[20150428]:null})

I changed the query to this， but still same problem， Good thing is that this one will cache data between 20150409 ：null， when do query with 20150410：null， it will be fast.

WITH Member Measures.DailyAverageUser AS
             Avg ( {[Date].[Date Key].&[20150409]:null},
                 [Measures].[Active Tiles Employee Id Distinct Count]
                ),format_String="##.000000"
             SELECT   Measures.DailyAverageUser ON 0,
            NON EMPTY([Action Targets].[Name].Children)  ON 1
            FROM [OLAP Pre]
            WHERE (
            {[Target Types].[Name].&[tile]},{[Employee Statuses].[Status Id].&[1],[Employee Statuses].[Status Id].&[3],[Employee Statuses].[Status Id].&[4]},
            {[Business Region].[Abbreviation].&[NONE],[Business Region].[Abbreviation].&[AMS],[Business Region].[Abbreviation].&[APJ],[Business Region].[Abbreviation].&[EMEA]},
            {[Employee Types].[Bits].&[1],[Employee Types].[Bits].&[5],[Employee Types].[Bits].&[9],[Employee Types].[Bits].&[25],[Employee Types].[Bits].&[13],
            [Employee Types].[Bits].&[29]})

Need suggestion on performance

Upvotes: 0

Views: 215

Answers (2)

whytheq
whytheq

Reputation: 35557

I wonder if this is also slow?:

SELECT   
  [Measures].[Active Tiles Employee Id Distinct Count] ON 0,
  NON EMPTY
  [Action Targets].[Name].Children  ON 1
FROM [OLAP Pre];

OLAP can be slow with large distinct counts.

Upvotes: 0

SouravA
SouravA

Reputation: 5243

Do you want to keep everything in your slicer axis? If not, you can push everything into you member's definition like this - 

WITH Member Measures.DailyAverageUser AS
             Avg (           
             NonEmpty(
                        (
                        {[Target Types].[Name].&[tile]},
                        {[Employee Statuses].[Status Id].&[1],
                         [Employee Statuses].[Status Id].&[3],
                         [Employee Statuses].[Status Id].&[4]},
                        {[Business Region].[Abbreviation].&[NONE],
                        [Business Region].[Abbreviation].&[AMS],
                        [Business Region].[Abbreviation].&[APJ],
                        [Business Region].[Abbreviation].&[EMEA]},
                        {[Employee Types].[Bits].&[1],
                        [Employee Types].[Bits].&[5]
                        ,[Employee Types].[Bits].&[9]
                        ,[Employee Types].[Bits].&[25]
                        ,[Employee Types].[Bits].&[13]
                        ,[Employee Types].[Bits].&[29]},
                        {[Date].[Date Key].&[20150428]:null}
                        ), [Measures].[Active Tiles Employee Id Distinct Count]
                    )            
             ,
                 [Measures].[Active Tiles Employee Id Distinct Count]
                ),format_String="##.000000"

SELECT   Measures.DailyAverageUser ON 0,
NON EMPTY([Action Targets].[Name].Children)  ON 1
FROM [OLAP Pre]

Upvotes: 1

Related Questions