Slow Cube Processing: Not due to slow SQL queries, but due to many dimensions/cubes?

Question

I have a single SSAS instance with two SSAS Databases.

Each database has about 350 and 250 dimensions, with about 10 of those dimensions being shared by almost every cube (e.g. DATE, TIME).
Furthermore both databases have about 750 to 500 cubes.

We noticed that when the databases did not contain many dimensions nor cubes, processing was very fast, a simple ProcessAdd on a small dimension would be performed within 0.5 second, for which 0.2 seconds would be the query.

But now with a higher amount of dimensions and cubes, we notice that a simple ProcessAdd takes up to 15 seconds to process, while the query remains 0.2 seconds. 

We are using AMO to perform the processing, does anyone have any idea why we might see this behavior?

Besides that, another problem has started popping up (after the amount of cubes rose), for which I could not find any solution. An operation on a cube does not succeed due to the following error:

Transaction errors:  While attempting to acquire one or more locks, the transaction was canceled. 

Note that I never process multiple cubes at the same time within one SSAS or SQL database, across SSAS or SQL databases this does occur, but I see no reason why that would indicate locking issues.

Answer 1

Hi, You have far too many dimensions. A general guideline is no more than 20-25 dimensions per cube or measure group but it really depends on the number of attributes and members of attributes. A good rule for not confusing the users of your cubes is to have no more than 10-15 dimensions.

HTH

Thomas Ivarsson

Answer 2

I have that many in the Database.

I have about 15 in each cube.

Answer 3

That makes sense. You have a lot of files for all dimensions and I guess that why you see locks. When you process a single cube you get locks in the cúbe database files, in order for SSAS to check what parts of the database that are affected.

There is a server time out property that you can try to extend in management studio. 

Generally it is better to clean up objects that are not used. If you open Management Studio will all these data files it will take a lot of time before you can see anything.

Thomas Ivarsson

Answer 4

Are you talking about the following setting [CommitTimeout] because I have it set on: 0, which indicates wait as long as needed?

I do notice that ForceCommitTimout has 30s, but I thought that setting behaved differently?

I was also wondering, what do you propose, split up in more databases or split the databases in more instances?

Answer 5

I think that you can try to extend the ForceCommitTimeout property but that will not fix the basic problems. A lot of dimensions in a cube database means a lot of SSAS files and metadata that probably can increase the possibility if locks of cube meta data. Try to use more cube databases if that will decrease the number of dimensions per database.

HTH

Thomas Ivarsson

Answer 6

Ok great thanks!

I will experiment a little bit with both and hopefully find a good trade off, which works for our needs..

Answer 7

Ok great thanks!

I will experiment a little bit with both and hopefully find a good trade off, which works for our needs..

Just for information, we experimented with splitting up the cubes over databases, but this would (in our case) be a maintenance nightmare and inherently each database will use more memory as shared dimensions are not shared anymore and cached independently. Therefore we opted for the easiest solution to have multiple instances.

Conclusion: both proposals work, just pick the one that suits your situation best.