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>
</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.
Jesse Orosz: SQL Server Analysis Services Blog 
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
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.
Gave it a shot with Proclarity 6.2. Problem solved. This will make the users happy…
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] )