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
ibanio
New Member

Import data from SQL analysis services database filtered with MDX query

I prepared a report on Power bi desktop that imports data from an SQL analysis services database, filtered with an MDX query as the amount of the data is huge. Everything works fine. 

When I publish the report to the Power BI service and after the first refresh of the data, the data don't seem to be right. It seems as if the MDX query is not applied.

Is this by design? 

 

Thanks,

I

4 REPLIES 4
techtalkcorner
Frequent Visitor

I am having the same problem. Using import mode with MDX from an On-Premsises Tabular model.

 

In Power BI Desktop, it resfreshes.

 

In Power BI Services, through the gateway, it says that it refreshes but it doesn't display the new data. I run SQL Server Profiler and the query gets to Analysis Services On-Premis es but the information doesn't come back to Power BI Services. I don't see any errors in the GW logs.

I am extending the information:

 

Power BI Report using SQL Server Analysis Services 2017 (RTM-CU11) (KB4462262​) with import mode from a MDX query. Using Enterprise Gateway.
 
The following queries work in Power BI Desktop and SQL Management Studio and the information is refreshed correctly. From SQL Management Studio, it doesn't generate any error in the logs.
 
Not any valuable information in the data gateway or analysis services error logs.
 
The Query 1 refreshes the information correctly in Power Bi Service. 
The Query 2 doesn't refresh the information in Power BI Service.
 
Query 1:
SELECT  
NON EMPTY  { [Measures].[RequestVolume] } ON COLUMNS, 
NON EMPTY  { [TaskHistory].[TaskCode].[TaskCode]  }  ON ROWS 
FROM  [model]
 
With only 1 attribute / dimension (it doesn't matter which one), the query works and there are not any issues. It seems that it tries to run the same query 3 times in the engine with different connections / spids?
 
 
Query 2:
SELECT  
NON EMPTY  { [Measures].[RequestVolume] } ON COLUMNS, 
NON EMPTY  { [TaskHistory].[TaskCode].[TaskCode]  *  [RequestReceivedDate].[RequestReceivedDate].[RequestReceivedDate]}  ON ROWS 
FROM  [model]
 
With more than 1 attribute / dimension (it doesn't matter which ones), the query works and there are not any issues. It seems that it tries to run the same query 3 times in the engine with different connections / spids? It also generates an error:
 
 


 

 

 
v-juanli-msft
Community Support
Community Support

Hi @ibanio 

When you publish report to Power BI Server, you need configure gateway for the dataset.

Please follow the reference below.

Manage your data source - Analysis Services

 

If this link doesn't help, please hsow more details:

"SQL analysis services database, filtered with an MDX query"

This means you write SQL statement in the box when importing to Power BI Desktop,right?

When you refresh data, have you ever change the parameter in this SQL statement?

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your response Maggie.

 

I use a personal gateway, not enterprise so there is not much to be configured. Looking on the refresh history I can see that the data refresh works. 

You are right, I wrote an MDX query in the box when importing data to Power Bi desktop and it works fine. On the Power BI service the query is not applied. 

I don't use parameters. 

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.

Top Solution Authors
Top Kudoed Authors