CODEWITHSUNDEEP
reporting-servicesssrs-2008ssasmdx
Perplexed
Perplexed

Reputation: 877

MDX times out - Bad MDX or Cube design?

I am trying to work out why an SSRS 2008 R2 report keeps timing out.

Unfortunately I am not familiar with MDX (although I did read up little about it).

So basically I would like to know whether the MDX is not good (because it might have been generated by the designer etc) or because there could be an issue with the cube design. I would lean towards cube design because I am told that at one point this query ran fine, but then it may be because there's more data now - i dunno.

SELECT NON EMPTY { [Measures].[Unpaid Sick Hours], [Measures].[Paid Sick Hours], 

[Measures].[All Timecard Hours]
            , [Measures].[Paid Sick Incidents], [Measures].[% Sick Time] } 
ON COLUMNS, NON EMPTY { ([Position].[Position Number].[Position Number].ALLMEMBERS 
            * [Position].[Position Title].[Position Title].ALLMEMBERS 
            * [Union].[Union].[Union].ALLMEMBERS 
            * [Time].[Day Of Week].[Day Of Week].ALLMEMBERS 
            * [Employee].[Employee Type].[Employee Type].ALLMEMBERS 
            * [Employee].[Employee Number].[Employee Number].ALLMEMBERS 
            * [Employee].[Employee First Name].[Employee First Name].ALLMEMBERS 
            * [Employee].[Employee Last Name].[Employee Last Name].ALLMEMBERS ) } 
            DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME 

ON ROWS FROM ( SELECT ( STRTOSET("[Time].[Fiscal Year-Quarter-Month].[F Quarter].&[20124]", CONSTRAINED) ) 
ON COLUMNS FROM ( SELECT ( STRTOSET("[Cost Centre].[Cost Centre Hierarchy].&[1002]", CONSTRAINED) ) 
ON COLUMNS FROM [Contoso HR]))

Appreciate the help.

thanks, KS

Upvotes: 1

Views: 365

Answers (2)

TerryB
TerryB

Reputation: 629

A few things to consider:

  1. As mentioned earlier * means CROSS JOIN, and CROSS JOINS do have performance penalties. Having as many as you have is going to give you headaches. A short term option would be to try NonEmptyCrossjoin.

  2. You are doing a CROSS JOIN on the [Position] and [Employee] multiple times. Can you rather just CROSS JOIN the [Employee] once using [Employee Number], and using member properties to get the [Employee Type],[Employee First Name],[Employee Last Name]

  3. Are the cubes sourced from a SQL Server database? If so, given that the output of this report seems rather flat. I would forgo MDX and simply use SQL to gather the data.

Upvotes: 2

Bill Anton
Bill Anton

Reputation: 2970

the MDX looks fairly straight forward...how much data are we talking about here? how complex are the measures? Are they all base-measures except for [%sick time]?

one of the first things I do when I have an MDX query that's not performing is to run it in MDX studio and see how it's being processed by the SSAS engine.

Upvotes: 1

Related Questions