Post reply

SSAS2005 Drillthrough action taking HOURS!

  • March 29, 2006 at 1:45 pm

    #189934

    Hello everyone,

    I'm having some issue trying to enable a drillthrough action in a SSAS2005 cube.

    Here's what I did:

    1. Create a cube based on a named query as the fact table (more than 57Milions of records)
    2. Create all the dimension and linked them to the cube (Equipments / Time)
    3. Create a fact dimension (based on the same named query as the fact table) 

    1. with no hierarchies
    2. every attributes are hiden to users
    3. ROLAP storage mode(more than 57Milion of records)

  • Linked the fact dimension to the cube (with a FACT relation)
  • Create a drillthrough action retreiving columns from the time dimension and the fact dimension

    • Cube is processing correctly, but when I do a "drill to detail" at a leaf level, even after waiting over 2hours, I don't get any results!! (normaly I should receive 8 rows)

    And since this morning, I'm getting errors message while processing the fact dimension:

    • Errors in the OLAP storage engine: Execution limit reached

    • Errors in the OLAP storage engine: Maximum number of ROLAP rows to process in one query has been exceeded.

    • Errors in the OLAP storage engine: An error occurred while the 'Detail Message Disparition' attribute of the 'Fct Evenement Stationnement' dimension from the 'ADM' database was being processed.

    • Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.

     

    Any help would be appreciated,

     

    Thanks

     

    Patrick

  • April 3, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • May 2, 2006 at 8:09 pm

    #635489

    Hi Pat,

    How long does it take to retrieve the 8 records when you query the source data directly?  I understand that by setting the storage mode of your fact dimension to ROLAP you are directing drillthrough for that data directly to the underlying fact table.  Can you run a trace to identify the actual query being sent by AS2005?  Is it what you expect?  Perhaps it is not filtering as you would expect and the query is trying to return a lot more than the 8 records you are drilling into. 

    Good luck,

    Jeremy

  • May 3, 2006 at 7:57 am

    #635608

    Hi Jeremy,

    I couln't wait long enough to see the results, I've canceled the drillthrough action after 50min.

    I did run a trace to see what was the query sent to the datawarehouse and at my surprise, there was no filter applied to the query! In fact there is more than 1 query, there's one for each properties used in the drillthrough action coming from the fact dimension. So I get like 4 Select query querying a table containing more than 350M records in it without any filters applied!

    We have now an open case with Microsoft about that issue...

    thx for your concern, let me know if you have any clues!

    Pat.

  • May 3, 2006 at 5:30 pm

    #635817

    I haven't experienced the problem with AS2005, but the same thing would happen in AS2000 when there were changes to the underlying Fact table.  eg adding a new column to the fact table and then reprocessing the cube would cause the drillthrough query to lose its where clause.  The only way I found to get it to query properly was to disable drillthrough, save the cube, enable drillthrough and save the cube again.

    Drillthrough is so different in AS2005...  Maybe you ought to try recreating your data source view, making sure the correct relationships exist between the fact table dimension tables, and then reprocess the cube.

    Good luck Pat - let us know how it turns out.

    Jeremy

  • May 4, 2006 at 10:10 am

    #636016

    Hi Patrick,

    I read this from a blog in my blog role and thought you may find it of interest (can't say useful because it doesn't really *help*).  http://www.mrachek.com/PermaLink,guid,7cb06281-9394-438b-b2aa-8996106ee481.aspx

    Cheers,

     

    Steve.

  • May 16, 2006 at 2:08 pm

    #638269

    Patrick,

    I had the same issue and thanks to your link, at least this problem got fixed.

    SRR

    • Viewing 7 posts - 1 through 6 (of 6 total)

    You must be logged in to reply to this topic. Login to reply