Getting the list of dimension members in the UI takes minutes

In one of our OLAP databases we have a large dimension which has nearly ten million members in the lowest level (out of three).  Users were complaining that it was taking ten minutes to drill down into this dimension via the UI (we use Proclarity). The same behavior was also being reported on smaller dimensions, though "only" taking a minute or two to come back.  
 
I decided to run a trace to see what query was being executed. This is what I got:

 
SELECT { SUBSET( ADDCALCULATEDMEMBERS( { [Sales].[Item].[All Item].CHILDREN }), 0, 200 ) }    PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS FROM [Sales]

 
 
The query was bringing back the total sums for that dimension.  To solve this I manually added an aggregation for the lowest level attribute in the dimensions. 
 
 
This aggregation is: dimItem.Item
<Aggregation>
  <ID>Aggregation_01</ID>
  <Name>Aggregation_01</Name>
  <Dimensions>
    <Dimension>
     <CubeDimensionID>dimDate</CubeDimensionID>
    </Dimension>
    <Dimension>
      <CubeDimensionID>dimSalesType</CubeDimensionID>
    </Dimension>
    <Dimension>
      <CubeDimensionID>dimLocation</CubeDimensionID>
    </Dimension>
    <Dimension>
      <CubeDimensionID>dimItem</CubeDimensionID>
      <Attributes>
        <Attribute>
          <AttributeID>Item</AttributeID>
        </Attribute>
      </Attributes>
    </Dimension>
   </Dimensions>
</Aggregation>
 
 
This solved the issue; ten minutes turned into ten seconds. I ended up adding aggrgegations for the lowest level attribute in every dimension in the large cubes.
 
Normally this isn’t something you’d have to worry about, however we deal with very large recordsets (this cube has five billion rows) and when a query doesn’t hit an aggregation it can take awhile.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Getting the list of dimension members in the UI takes minutes

  1. Chris says:

    Hi Jesse,
     
    This was actually a bug which was fixed in Proclarity 6.2; Mosha dealt with this problem in this blog entry:
    http://sqljunkies.com/WebLog/mosha/archive/2006/10/11/query_dimensions_mdx.aspx
    No need to build an aggregation – just put an empty set on the opposing axis.
     
    Chris

  2. Stefan says:

    I have the same problem. As Chris mentions above there was a bug in 6.1, that should be fixed in 6.2. In my case 6.2 fixed the "connection time"(when you open a cube and it loads all the dimensions, which could be up to 4-5 minutes sometimes). But if you browse the dimension in the in the setup panel pressing  the plus sign it still takes some time. Not ten minutes, but  20-30 seconds in dimensions with maybe 100000 members at the lowest level.   

  3. Jesse says:

    Gave it a shot with Proclarity 6.2. Problem solved. This will make the users happy…

  4. Ronald says:

    When I do a hide in Proclarity 6.3, I get no data returned. I captured the mdx(below) and run it in management studio, I got nothing also. I then remove the empty set section(…,{ } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS …) and then it worked. What I am doing wrong here?
    SELECT NON EMPTY { EXCEPT( { EXTRACT( HIERARCHIZE( { [Dim Time].[Year – Month – Day – Hour].[Year].&[2007].CHILDREN }), [Dim Time].[Year – Month – Day – Hour] ) }, { [Dim Time].[Year – Month – Day – Hour].[Month].&[7]&[2007] } ) } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON COLUMNS ,{ } PROPERTIES PARENT_UNIQUE_NAME, MEMBER_TYPE ON ROWS
    FROM [PATROL]
    WHERE ( [Dim App].[Hierarchy].[Parameter Name].&[CPUprcrProcessorTimePercent]&[CPU__Total]&[NT_CPU], [Measures].[Average] )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s