Cube browse in SSMS slow to start

I have a cube that has a couple hundred million rows and when I begin a cube browse, it takes a very long time for it to start. I profiled it and looked at the query it was running. It was querying for (what looked like) the totals for all measures at the All level for every dimension.

First of all, I would expect this to not take too long on my cube (no aggs) but I stopped the query after 45 minutes. (I know that adding some aggs to the cube would help and I will do that eventually.)

Second, how come does this query need to be run at the beginning of a browse? When the browse starts, there isn't anything in the data section of the pivot table.

How come is this necessary?

Mark

http://mgarner.wordpress.com

Try to see if this situation has anything to do with OWC.

Try to use MDX Editor in SQL Managemet studio. See what is taking most of the time.

Is it loading of metadata?

Try to send simple MDX query to bring only totals from your cube. Something like:

Select measures.members on 0 from [MyCube]

Run Profiler and see where the time is spent. 45 minutes looks bit excessive.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Thanks so much for your response.

I agree that 45 mins is a bit excessive. It could also be something that is wrong with my cube design, but I wanted to get some info about this start-up query anyways.

select filter([Measures].members, [Measures].currentmember.member_caption = [Measures].currentmember.member_caption) on columns from [myCube]

This is the query that is running when I start a browse session in SSMS. I noticed that the query looks the same and the same operations are executed when I run the query in an MDX query window as well. It takes the same amount of time.

Do you happen to know why this query is run each time a browse window is open?

I also noticed (in Profiler) that each time this query is run that it doesn't look like it is ever retrieved from cache. Is there some reason why this query can't be put into cahce? If not, can you think of a reason why my cube design might keep it from being placed in cache?

Also, when I take out the vast majority of my partitions and only leave 4 (which would be amost 10 million fact row lines) with 15% aggregations, the query takes about 1 minute. This also could be due to my server and/or SAN configuration, but I would still expect that a query that just returns the totals for all the measures at the [All] level for every dimension wouldn't take too long.

Thanks in advance,

Mark

http://mgarner.wordpress.com