Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gooranga1
Power Participant
Power Participant

Get 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 6

@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?


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Hi,

 

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.


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Thanks.

 

This is frustrating. It's odd it is just one mdx query that causes it. The exact same query but without a company name runs in under 10 seconds. As soon as I add the company name to the query which is used for more detailed reporting the query takes an age.

 

Doesn't look like this is fixable at all as far as I can see especially if it's the translation under the bonnet from mdx to dax.

Very strange. Did you try changing the order of the MDX-filters?

If this all doesn't help, you could instead put in the filter afterwards in the query editor / M - would all be faster anyway...

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

It eventually came down to a pair of curly brackets. I was selecting several attributes from the same heirarchy and I hadn't enclosed them in a separate set of {}. Once I had done that the query ran okay in power bi. It didn't seem to affect the performance in SSMS at all.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.