locked
SSAS Tabular processing, ways to improve duration RRS feed

  • Question

  • I had built this same cube both in multidimensional and tabular mode. For multidimensional I created 8 equal sized partitions and the processing time was slight over an hour and using the 8 cores of the server at 100% most of the time during processing. I have not created partitioning for tabular (I did but did not make a difference) and the processing takes about 4 hours but during processing cpu's are not at 100% (usually less than 20% overall on 8 cores). There is no paging going on. I have read through "Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services" but I did not find anything there that could speed up processing and maybe bring it down to the duration of a multidimensional processing.

    The fact table row compressed is about 55GB row compressed, the rest of the dimension tables don't make 1Gb in total. There are 280 foreign key relations (dimensions ) in the cube. The compressed backup of multidimensional cube was about 20GB and the compressed backup of tabular is 10GB. My desktop has Intel Xeon 3.6GHZ with 35GB ram and 500GB SSD drive. Attached image is typical during processing in tabular.

    Any ideas?

    Thank you


    Gokhan Varol

    Thursday, August 8, 2013 2:36 AM

Answers

  • This is one of the differences between multi-dimensional and tabular.

    MD can process multiple partitions in parallel.

    Tabular can process tables in parallel, but partitions within the one table have to be processed serially.

    So the only way to reduce the processing time on a single table from the SSAS side (assume that the source system is not a bottleneck) is to reduce the amount of data that you are processing. Tabular is better with this in some regards than MD as you can full process a "dimension" without needing to re-process your "fact" tables.

    So we are currently using partitions in tabular so that we only reprocess the current month of data.


    http://darren.gosbell.com - please mark correct answers

    • Proposed as answer by Elvis Long Monday, August 12, 2013 3:27 AM
    • Marked as answer by Elvis Long Sunday, August 18, 2013 12:31 PM
    Friday, August 9, 2013 3:02 AM