locked
Troubleshooting a slow Cube RRS feed

  • Question

  • Hi

    I am new to SSAS so please bear with me.

    I have created a very simple cube with the following data:

    - FactEODSnapshot      1.7M rows
    - DimProduct               105 rows
    - DimCustomer            97,000 rows

    When I drag/drop the DimCustomer.CustomerID (primary key) onto the cube, there is a very lengthy delay (5 minutes or so) before the ID appears. I get the same problem with the other attributes e.g. CustomerName. DimProduct is fast.

    Running the same query (i.e. SELECT CustomerNumber FROM DimCustomer) in Management Studio against the relational datawarehouse is quick.

    How do I get about debugging this issue please?

     


    Amir
    Wednesday, July 6, 2011 2:34 PM

Answers

  • You do not have much control on how the BIDS browse would render the members. Hence i found setting the default value as one of the efficient ways to handle this problem.
    vinu
    • Marked as answer by amir tohidi Thursday, July 7, 2011 7:30 AM
    Thursday, July 7, 2011 3:40 AM

All replies

  • You can set the default member for the dimension so that it does not get all the members. And then you can select the required members via the filters. This way your initial dimension drop to the pivot are will be fast. The default member could be one that you think will have the least data or could be the All member
    vinu
    Wednesday, July 6, 2011 2:39 PM
  • Hi Vinuthan

    Thanks but shouldn't a cube be able to handle 97,000 rows quickly? Books talks about cubes with millions and billions of rows in them.

    If I filter, then users will not get the overall view of all Product ordered by all Customers.

    I have reduced my DimCustomer to one attribute (CustomerNumber) and I still get the performance issue.

    Am I missing something?


    Amir
    • Edited by amir tohidi Wednesday, July 6, 2011 2:50 PM Added more info
    Wednesday, July 6, 2011 2:48 PM
  • More often than not i have found the cube browser to hang for large dimensions or dimensions with accessive hierarchies. But when used in query as we use appropriate NonEmpty() and filters, it becomes fast
    vinu
    Wednesday, July 6, 2011 3:17 PM
  • Vinu

    I just used the SQL Profiler and noticed that the actual query run quickly. But, BIDS 2008 takes ~ 5 minutes to render the 97,000 numbers!

    Is there anyway around this or is it another "feature"?


    Amir
    Wednesday, July 6, 2011 4:26 PM
  • You do not have much control on how the BIDS browse would render the members. Hence i found setting the default value as one of the efficient ways to handle this problem.
    vinu
    • Marked as answer by amir tohidi Thursday, July 7, 2011 7:30 AM
    Thursday, July 7, 2011 3:40 AM