Cube processing either takes too long or fails

Question

I have a SSAS Cube with 35 dimensions and 10 measures.

• Some of the dimensions are quite large in size.
• In almost all dimensions data either get updated and inserted.
• Measures have high volume of data.

When I run from SSMS (by right click on SSAS Database) "process full" on database it takes around 1 Hr 30 min.

When I am Processing cube (process full) from SQL Agent job via XMLA script it takes around 1 Hr 20 min.

When I processing dimensions and measures (Process Full) separately via SSMS it is taking 1 hr 50 min.

But when I am processing dimensions and measures from SSIS (DML Task via XMLA) it is taking more than 5 hours. [Memory consumption went to almost 100%]

So my questions are:

1. Does processing dimensions and measure from SSIS can cost that much?
2. What kind of processing should I choose for fast cube processing. I am doing Process full.
3. When I am processing cube from first two options then most of the time cube is getting failed by saying "attribute key not found" but it works well when I process dimensions and measure separately. Doesn't process full cube ensure proper processing of dimension and measures?

Answer 1

Oh my dear..

First of all. In your case I would like to suggest you to choose the "Process Default" option. If you are using Process Full always you are dropping all data and objects and recreating all of your stuff. You don't need them. Very often the proposal option is to use ProcessData followed by ProcessIndex.

I didn't proces the cube from within the SSIS, but probably SSIS is checking something and locking something on the database. The best option is to use XMLA. If you want to process your cube from within the SSIS you can write a script task to perform the processing - you can find a scripts on the net.

You need to also check if you have a clean data. Check twice in your project and in your data if you have all the keys and so on. If you are processing separately dimension the data is not comparing between measures and dimensions. I am pretty sure that you have some keys in fact tables that have no corresponding values in your dimension. Make an order with this. If you have no values in dimension and it's correct (doesn't exist in the source tables) you can add to each dimension one extra value with i.e. key "-1" and all values like "N/A" (not applicable) and point the not founded values to this record in the dimensions.

And at the end. If you have a big SSAS database please think about a partitioning.

It's a lot to talk about. If you have some specific questions feel free to ask.

Answer 2

I highly suggest that you partition your cube. Then the server can parallelize the processing of the fact table, which likely will give you the biggest performance gain.

Answer 3

When processing all dimensions and facts in SSIS separately you do it in more than one transaction. Processing the whole cube in one transaction is faster when the server can handle it.