Forum Discussion
Power ParticipantGet Data from SSAS very slow
Hi,
I have been using powerbi for a few months and we use get data to load data into our pbix files via SQL and MDX. This has always worked perfectly until today.
I have an mdx query that takes around 45 seconds to run in SSMS. It's not the quickest but it is working out several moving averages so it I don't expect it to be lightning fast.
However when I use this query to load into powerbi it takes nearly 20 minutes to evaluate and load the data?
Has anyone else experienced this? The query only returns about 30,000 rows.
6 Replies
gooranga1 Have you used this query to load data into Power BI previously? How long does it normally take, and when is the last time you ran it. Have you popped open SQL profiler to see if you can determine any additional info?
gooranga1Hi,
No this is the first time I have used the query in power bi.
I just ran the query in ssms and then in power bi with SQL Server profiler on. In SSMS I see a few thousand "Query Subcube 1 - Cache Data" events and it completes in under a minute.
When I run it via power bi I had to stop it when it got to 5 million of these. It was still capturing the events minutes after the query completed. The refresh took about twenty minutes.
gooranga1 My initial guess is that it has something to do with how PBI is actually executing your MDX code, from my understanding the executed statements from Power BI are translated to DAX, thus why I asked you to check out what is being executed in profiler. The two queries should be vastly different. The issues you are having are most likely due to this "translation" layer and how Power BI is extracting the information into it's tabular structure.
Here is more info on the subject, I don't have a specific answer to your question - but I'd imagine the above is the cause.
