question

Comanchi-1008 avatar image
0 Votes"
Comanchi-1008 asked CarrinWu-MSFT commented

CUBE Processing taking long time

Hi All,
I have a cube that takes 40 mins to process, i tried processing the corresponding DIMS and then the CUBE, still the same time. The FACT has 60million rows. I looked at the partition and it is TABLE binding, i want to do it as a SQL binding on DATE, but there are multiple dates and i am not sure how to do it . Please advice.
Thanks

sql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered

Hi @Comanchi-1008,

Welcome to Microsoft Q&A!

Have you try to use SQL Server Profiler to get more information? Such as which dimension or measure group takes long time to process, this is the first step to tune cube processing performance. For more information, please refer to Tuning Cube Processing Performance.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Comanchi-1008 avatar image
0 Votes"
Comanchi-1008 answered Comanchi-1008 edited

@CarrinWu-MSFT thanks, i have to ask my DBA to run a trace, but it is definitely not the dimensions as i processed the DIMS and they each one took a minute to 9 minutes. I created a VIEW and replaces the FACT in the DSV with the VIEW ( has some calculations), still no luck.

The FACT in DSV is a named Query that has many calculations, so i crated a VIEW to by pass that in the cube processing , but that did not make it any slower , instead it became slower.

Thanks

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT commented

Hi @Comanchi-1008, have you check the CPU usage and memory? And which model did you created, tabular or multidimensional model? If you are using tabular model, please noted that memory is so important to Tabular. The easiest tool with which to monitor Tabular is the Task Manager. It already provides much information about memory and CPU usage, and it is available on any Windows installation to any user, without requiring special knowledge or administrative rights.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks ,
It is a MD Cube and here is what i did so far. The fact table is a named SQL and has many CAST and CONVERT functions, so i created a VIEW and loaded into another physical table and changed the DSV to replace the old table with the new one. I still have perf issues. I now have access to to the SQL Profiler but when i process the cube i do not see anything in the TRACE.
Thanks

0 Votes 0 ·

Hi @Comanchi-1008, thanks for your replying. I tested SQL Profiler in my side, it works well when I process cube. Do you have enough permission to run it? And you should open SQL Profiler before you process cube. For more information, please refer to Run SQL Server Profiler.


0 Votes 0 ·

Thanks I was able to run the trace and the maxiumum duration is when processing the CUBE and one of the dim which has 14 million rows, i read somewhere that it is better to process dimensions first and then CUBE ,I did that , but even then when I process the CUBE it is processing the underlying dimensions as well. Please advice.
Thanks

0 Votes 0 ·
Show more comments