SSAS MDX Cube partitions processing

Question

We have an MDX cube which is taking more time to process due to the huge amount of data. It has five regions partitioned by company and holds data from 25 years. To reduce the processing time , we plan to refresh only the last 3 years of data. So we pushed before 2019 to existing partitions and after 2019 to new partitions.

We process it from SSIS, and on weekends we do full processing, and during weekdays we do incremental processing, which means processing after 2019 data.
To implement how the SSIS package should be

Full Load > For the database, we do process full
Incremental > Process update for dimensions & Process Data for newly created partitions(data after 2019).

Is this approach correct or is there another effective way to reduce it? In our case, dimension processing takes a lot of time.

Answer 1

Hi @Prabu, Chandran ,

Welcome to Microsoft Q&A!
As your SSAS cube gets bigger, cube processing time will become a problem. In order to reduce the process time, we need to understand Cube processing firstly, and then identifying the Bottleneck, at last to take the measure to reduce the process time. Here are some blogs that describe reduce process time, please refer to the links below.
http://www.jamesserra.com/archive/2012/01/improving-cube-processing-time/
Your plan is consistent with the best possible processing strategy. What you may need to do is to do a Process Data and Process Index separately instead of a Process Full, because: it is a bit faster, it reduces the stress on the server, it makes data available to end-users sooner (while a Process Index is happening, users can still query cube), and you can just run Process Index if Process Data completes but Process Index bombs.
Another thing to note is that a Process Incremental internally creates a temporary partition, processes it with the target fact data, and then merges it with the existing partition. Process Incremental doesn’t drop aggregations and indexes. Note a big point of confusion is that when you choose “Process Incremental” in the GUI, it really translates it into a Process Add, and it only works for partitions and dimensions (yet for some reason the Process Incremental option does not show up on the GUI for dimensions – you need to fire it using XMLA). Even though the GUI has the Process Incremental option available for Cube and Measure Group, it scripts those to work only on the partitions.

Finally, you can watch this video to get some detailed steps about Automate SSAS Cube Process using SSIS | schedule cube processing:
https://www.youtube.com/watch?v=UebBb64MAT4

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.