Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Posts
    56

    Unanswered: HOLAP much faster then MOLAP !?

    When I process a cube with the HOLAP (100% performance gain) storage option the querytime from Excel XP is very fast.
    When I process the same cube with the MOLAP (100% performance gain) storage option the same query takes forever.
    I'm puzzled. Can someone explain this?

    Edwin

    P.S. THe cube has a DistinctCount measure in it. Does this have anything to do with the poor MOLAP performance?

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    You are correct - that is unusual. How big are both of the cubes ? Have you tried duplicating the same behavior with the foodmart sample ? Since you are suspicious of the distinctcount - have you eliminated that measure to see how it affects performance ?

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    Have you tried it under excel 2000 ?

  4. #4
    Join Date
    Apr 2002
    Posts
    56
    Thank for the reply.

    Excel 2000 gives me the same problem, as well as the cube data browser in the Analysis Manager itself.
    I replaced the Distinct Count by a Sum. Performance was OK in both HOLAP and MOLAP, so the Distinct Count causes the pain (but I really need it).
    I couldn't reproduce the same thing in the Foodmart cubes (size perhaps?).
    Maybe I stumbled on some undocumented limit.
    Another thing: In HOLAP mode with 100% performance gain it still computes aggregation data in Oracle. I know this, because when I shut Oracle I get #value errors. I thougt 100% means 'all aggregations in Analysis Services'. Also I'm often exactly 1 above the exact value with the Distinct Counts, because NULL seams to count a distinct value.
    I rfead somewhere that Analysis Services transform a NULL into a 0 when one of the other measures in the source table is not NULL.
    Is that correct, and if so, is there a patch for that? Otherwise I must define fact-views for each Distinct Count measure with "WHERE ... IS NOT NULL".

    Development Server : PIII 500, 512 MB memory
    (should eat this 7.5MB cube for breakfast)
    Relational database: Oracle 8.1.7.3
    Analysis Services : Service Pack 2
    Fact table: : 69000 rows
    Measures: : 1 Distinct Count
    Dimension data : 1 3000 members, 1 level
    2 69000 members, divided in 7 levels
    3 Price-dimension (hierarchy 1)
    4 Price-dimension (hierarchy 2)
    Cube in MB 7.5 MB (in both HOLAP and MOLAP mode)

    Edwin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •