Forum Discussion
DAX Studio and SQL profiler
Hi,
I ran a refresh of a dataset for the first time. It took 4.5 hours.
The main query in this is a view in SQLServer, being an aggregate on a table. When I run this view in SQLServer it takes 40 minutes, going from 1.3 billion rows to 9 million.
So refreshing takes long,also the second refresh. 4 hours. Incremental refresh keeps 4 years of history, refreshes the last 13 months (based on creation-date), but only if changed (detect data change on last-change-date).
Becaise refresh takes so long I use SQL Profiler in DAX Studio for the first time. Connecting to the shared dataset in the service goes fine, but once I start SQL Profiler I get an error
It says trace with id doesn't exist in the server or the user is not authorized for having access to the object.
How to solve this?
smpa01
lbendlin
Regards
Ron
9 Replies
Anonymous if I were you I would start with SQL query first. Why does it take 40 minutes to run a query ? Are the SQL tables missing any index? How can the SQL query be optimized.
agree with smpa01 - you should focus on the Power Query part, not on DAX Studio. Also be aware that you are dangerously close to hitting the hard limit of 300 minutes - after that any dataset or dataflow refresh on the service will be killed. You want to get your duration down to not more than 2.5 hrs (to be on the safe side). Keep in mind that your refresh is blocking a renderer for the entire time, which means that renderer is not available for other users either.
Read about bootstrapping for incremental refresh partitions.
I completely agree lbendlin and smpa01 . Just ran the view again in SQLServer. Took 23 minutes. But we are planning to add an index. The main issue for now is that I get that SQL Profiler error. So I can't get insights in why it takes so long.
Furthermore I will dive into bootstrapping. Never heard of it 🙂
I will lbendlin but I presume refreshing does more than only running the query in SQL ...
If you want you can show a sanitized version of your Power Query code. Unless you are doing lots of merge operations the M transforms usually take much less time than the actual source query. In any case you can use the Power Query Diagnostics options to figure that out.
