none
Excel MDX Query

    Question

  • Hi,

     

    How can I get the MDX query sending to SSAS from the excel report?  I've the excel report that is connected to SSAS.  It's running slow when I add more dimension attributes or measures to the report.  I want to capture the MDX query to troubleshoot.

     

    When I browse the cube thru MS SQL Server Management Studio, I create the same report which generates the data very fast.  How can I get the MDX query from this?

     

    Thanks,

     

    Ash

    Friday, October 24, 2008 1:09 AM

Answers

All replies

  • You could start a profiler session and trace the Analysis Server to capture this query. You may have to reconstruct the query from a couple of commands as sometimes Excel uses session scoped sets.

     

    You can also use the following free addin for Excel 2007

    http://www.codeplex.com/OlapPivotTableExtend

     

    Friday, October 24, 2008 1:49 AM
  • I got this mdx query from the profiler.  This query only takes 20 seconds.

     

     

    SELECT {[Measures].[Pixel],[Measures].[Valid],[Measures].[Sold],[Measures].[Invalid],[Measures].[Trash],[Measures].[Duplicate],[Measures].[Collected],[Measures].[Sellable],[Measures].[TTV]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownLevel({[Source].[Owner - Group - Source].[All]})}}, {[Source].[Owner - Group - Source].[Source Owner].&[DM]})}}, {[Source].[Owner - Group - Source].[Source Owner].&[DM].&[180solutions.com]})), Hierarchize({DrilldownLevel({[Campaign].[Campaign].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Mail_SmallTxt], [Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Mail], [Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Small_Mail]}) ON COLUMNS  FROM [Lead]) WHERE ([Collect Point].[Collect Point Group].[All],[Collect Date].[Week Group].&[Last Week],[Lead Type].[Lead Type].[All],[Collect Site].[Collect Group].[All],[Collect Site].[Collect Site].[All],[Ad].[Ad Name].[All],[Ad Size].[Ad Size].[All]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS


     

    I just add one attribute to the row labels, and the query takes 25 minutes.  The attribute only has 4 values.

     

     

    SELECT {[Measures].[Pixel],[Measures].[Valid],[Measures].[Sold],[Measures].[Invalid],[Measures].[Trash],[Measures].[Duplicate],[Measures].[Collected],[Measures].[Sellable],[Measures].[TTV]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(CrossJoin(Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownLevel({[Source].[Owner - Group - Source].[All]})}}, {[Source].[Owner - Group - Source].[Source Owner].&[DM]})}}, {[Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Mail_SmallTxt],[Source].[Owner - Group - Source].[Source Owner].&[DM].&[180solutions.com]})), Hierarchize({DrilldownLevel({[Campaign].[Campaign].[All]})})), Hierarchize({DrilldownLevel({[Lead Type].[Lead Type].[All]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS  FROM (SELECT ({[Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Mail_SmallTxt], [Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Mail], [Source].[Owner - Group - Source].[Source Owner].&[DM].&[System_Txt_Small_Mail]}) ON COLUMNS  FROM [Lead]) WHERE ([Collect Point].[Collect Point Group].[All],[Collect Date].[Week Group].&[Last Week],[Collect Site].[Collect Group].[All],[Collect Site].[Collect Site].[All],[Ad].[Ad Name].[All],[Ad Size].[Ad Size].[All]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

     

     

    Is there a number of row labels limitation that Excel allowed?  I've been doing some testing.  The query seems to run fine if I add more measures.  But when I bring in more than 4 attributes (4 drilldowns), it will take forever.

     

    Any help is appreciated.

     

     

    Friday, October 24, 2008 9:56 PM
  • There is no hard limit in Excel that I am aware of. Adding attribute from different dimensions does increase the complexity of the query with the number of potential cells that have to be evaluated as each attribute multiplies the potential subcube space.

     

    eg. if you have 2 dimensions, one with 10 members and another with 15, you have 150 potential combinations to evaluate. If you add another attribute with 4 members you have 600 potential combinations, add another attribute with 10 members and you have 6000 potential combinations.

     

    But your slow down does seem a little extreme, have you checked the performance monitor counters on the server? Is Analysis Services possibly paging memory out to disk? Are you using any calculated measure that could be accessing other information?

     

    Monday, October 27, 2008 4:39 AM
  • I'm seeing the slow down on some calculated measures; they have expressions like this:

    ([Status].[Status Code].&Coffee,[Measures].[Total])

     

    I just have the Total checked on "Non-empty behavior" and the report seems to run a bit faster.  Is there any other logic you think I should add to the expression?

     

    Thanks,

     

    Ash

    Monday, October 27, 2008 5:12 PM
  • Adding a "Non Empty Behaviour" is probably the only way to tweak a simple expression like that. (in SSAS 2008 the non-empty detection is a lot better and this is not necessary in a lot of cases)

     

    The only other option I can think of might be to add a column in the DSV which does similar filtering. (assuming that Total is a "raw" measure) and returns nulls when the status is not "C".

     

    Monday, October 27, 2008 8:29 PM
  • Darren,

     

    Thank you for your help.  It works fine now that I use the non-empty behavior on the measures.

    Monday, October 27, 2008 10:01 PM