Showing posts with label relatively. Show all posts
Showing posts with label relatively. Show all posts

Sunday, February 12, 2012

Analysis Services Cube Browser utility opening extremely slow

I have a relatively large cube that when I try to open it up via the cube browser utility in SQL Server Management Studio, it takes approximately 25 minutes for the cube browser utility to open up. If I want to query the database via MDX, the MDX writing utility (right-click on the database->New Query->MDX) comes back really fast (within a second).

What does the browser utility do differently that requires it to take so long versus the query utility?

Is there a way to speed it up?

Thanks

Hi there:

This is happening because the default query bein generated by the browser is taking a huge time to execute. Try using a simpler (read:less calculation intensive) measure as your cube default and watch the browser fly.

Hope that helps.

Cheers.

Suranjan Som

Senior BI Architect

Information Managment Group

|||

Unfortunately, this does not solve the problem. I turned on SQL Server Profiler and noticed that for each measure group in the cube (this particular cube has 12) it has to read in each partition. 6 of these measure groups have 48 combined partitions. Why does it need to read in each measure group? Unless there is a setting that I am missing, this is going to be an issue for anybody that has a large cube (billion plus rows) with multiple partitions.

Analysis Services Architecture

We have a large cube (over 500GB) and several other relatively small cubes built using AS 2000. We are now in the process of upgrading the environment. Basically we are going to buy new infrastructure and use AS 2005. And the OS is most likly XP. The large cube is the one that is widely used in the company by at least over 200 people. Small cube has very few users. My question is what is the best architecture we can use

1. Whether to use one large machine and then have one instance of AS. All cubes are to be migrated to this instance. Issue with this option, we can not makesure we give more resources to the important cube.

2. Have multiple instances of the AS running on the server. Use MS Resource manager (new product shipped with XP server) to manage the resources. Has anyone used MS resource manager or aware of any issue with it ?

3. Use clustered environment - I do not know how use this to allocate more resources to the important cube. Any thoughts ?

Much appreciate, any pointers on what we are trying to achieve here .

Thank you

It is hard to make any suggestion based on short description only.
But if you are going to have several smaller cubes, queried not often, it probably Ok to consolidate them with the bigger cube on the same server.
Your risks are whenever smaller cubes are queried, the bigger cube will see some slowdown. Again, you might be Okay with consolidating them together. Just make sure you give it excess capacity. Try it, test it.


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