cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gooranga1 Senior Member
Senior Member

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
Super User
Super User

Re: Get Data from SSAS very slow

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


Near SE WI? Join our PUG Milwaukee Brew City PUG
gooranga1 Senior Member
Senior Member

Re: Get Data from SSAS very slow

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.

 

 

Super User
Super User

Re: Get Data from SSAS very slow

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


Near SE WI? Join our PUG Milwaukee Brew City PUG
gooranga1 Senior Member
Senior Member

Re: Get Data from SSAS very slow

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.

Super User
Super User

Re: Get Data from SSAS very slow

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...

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




gooranga1 Senior Member
Senior Member

Re: Get Data from SSAS very slow

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 52 members 954 guests
Please welcome our newest community members: