locked
Inconsistent results RRS feed

  • Question

  • Hi,

    I am building a report with a recursive hierarchy for drill-down purposes. The hierarchy is built by querying a SSAS OLAP cube and defining a details grouping for the table/matrix.

    Every time I run the report one or more of the leaf members in the recursive hierarchy "jumps" up to the highest level. First I thought that this may be due to the fact that the leafs parents are not part of the returned dataset. However, the queries makes sense and the "offending" members does never contain any data (while the query should return only non empty members) which is why this is a very strange behavior. Furthermore, the "offending" member differs between different executions of the report, despite the fact that the parameters is exactly the same and the cube is untouched between executions.

    I am actually pressing "View Report", waiting for the report to execute and when I press "View Report" again, the returned datasets seem to differ, yielding different "offending" members in the report.

    When I run the queries individually in the Data-tab in BIDS, the returned datasets are always the same. Execution caching is turned off for the report.

    Checking against SSRS's ExecutionLog, the RowCount for consecutive executions with the exact same parameters differ. For example, RowCount:
    3094
    3080
    3079
    3088
    3087

    Why does SSRS behave such inconsistently? Any tips or tricks?
    Monday, May 5, 2008 1:53 PM

Answers

  • I found the problem. The problem is not in SSRS but in SSAS.

    When SSRS queries SSAS, it does so by sending all queries (more than 10) at approx. the same time.
    Then, something goes wrong in SSAS and incorrect results are returned.

    The "fix" was to lower the parameter "ThreadPool \ Query \ MaxThreads" from 10 to 1 in SSAS (using Management Studio).
    This problem might be addressed in Hotfix 950969 http://support.microsoft.com/kb/950969/  (havent tried it yet) which is part of SQL Server Cumulative Update 7 (and maybe earlier versions as well) at http://support.microsoft.com/kb/949095

    Will post again as soon as I confirm that CU7 actually resolves the problem.
    Tuesday, May 6, 2008 1:54 PM

All replies

  • I found the problem. The problem is not in SSRS but in SSAS.

    When SSRS queries SSAS, it does so by sending all queries (more than 10) at approx. the same time.
    Then, something goes wrong in SSAS and incorrect results are returned.

    The "fix" was to lower the parameter "ThreadPool \ Query \ MaxThreads" from 10 to 1 in SSAS (using Management Studio).
    This problem might be addressed in Hotfix 950969 http://support.microsoft.com/kb/950969/  (havent tried it yet) which is part of SQL Server Cumulative Update 7 (and maybe earlier versions as well) at http://support.microsoft.com/kb/949095

    Will post again as soon as I confirm that CU7 actually resolves the problem.
    Tuesday, May 6, 2008 1:54 PM
  • CU7 does NOT resolve this problem.

    It seems as if the problem is a combination with calculated members and many simultaneous queries executing in many threads at the same time.

    Lowering the MaxThreads to 1 seriously impacts performance (of course). So I'm still looking into it...
    Friday, May 23, 2008 8:34 AM