CODEWITHSUNDEEP
performancessassql-server-2014ssas-tabular
dagrun
dagrun

Reputation: 651

Cube processing is too slow

I have five cubes that have to be updated every night. They all use the same big fact table from my data warehouse. My fact table has a un-clustered index on the primary key and a clustered index on the logical key. My fact table is partitioned by year.

I am doing a full process to all my cubes right now as data might change, get deleted or new data might arrive. Only data from the last 730 days(two years) can change, so maybe there is a way to only process this period?

I have tried Processing all my cubes at the same time, and after each other. Both times the Processing took too long.

Upvotes: 1

Views: 7645

Answers (2)

Arnaud Gastelblum
Arnaud Gastelblum

Reputation: 312

Here is some ideas

Partitions: I fully agree with Denny Lee, you should implement partition in your cubes. (It will clearly improve your process time)

Question - 5 Cubes: You told us that you have 5 cubes on the SAME big fact table. My question is: "Do you really need to have 5 cubes instead of one?" <-- If you can implement your needs in one cube, your problems will be reduce.

Cube process in parallel: You tried to run in parallel or in a linear mode, you could maybe try to run it in linear mode but 2 in the same time.

Clustered Column Store Index: You seems to be on SQL Server 2014 - If you have an Entreprise Licence, you can try to change your clustered index into a clustered columnstore index.

Let us know your changes,
Arnaud

Upvotes: 0

Denny Lee
Denny Lee

Reputation: 3254

There are a number of optimizations that you can do to help improve cube processing:

  1. Build periodic partitions for your cube and process those partitions to a particular time period. For example, you can build yearly partitions and then process just those two years so that way it's only accessing that data.
  2. Split processing of the cube partition processing of processFull to processData and processIndexes. Time them so you are not overloading your source or your cube server at the same time.

These are the top two issues with cube processing - also check out Analysis Services Processing Best Practices.

To further debug, you may also want to determine why the processing is so slow - is it at the source server itself or is it with the cubes themselves. A good guide to this is the Analysis Services Operations Guide.

HTH!

Upvotes: 3

Related Questions