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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mike282
Helper III
Helper III

SQL Server Analysis Services as a Dataflow

Hi I'm trying to create a dataflow from SQL Server Analysis Services. I can connect to it fine as a dataset in Power BI Desktop, which I've got all the M script transformations stored. 

 

I then tried to copy the M script transformations into a "Blank Query" in dataflows, whilst selecting our on-premise gateway which we've setup.

 

image.png

Figure 1. Selected "Blank query" as a datasource.

 

image.png

Figure 2. Copied and pasted my M script data transformation from Desktop to the advanced find box in dataflow

 

It then asked me to enter my credentials, which I then chose the "windows" credentials and entered my domain/username with password and got a "Invalid credentials".

 

image.png

 

The thing is though, I tested this connection type on desktop using SQL Server Analysis Services as a dataset and the windows username and password and it worked fine.

 

Does Dataflows not support SQL Server Analysis Services as a datasource?

 

Kind regards,

Mike

2 ACCEPTED SOLUTIONS
v-xicai
Community Support
Community Support

Hi @Mike282 ,

 

From the article , we know that the Azure analysis Services instead of SQL Server Analysis Services connector is supported in dataflow.

 

While your demand is a good idea, you can post your new idea in Idea Forum , to improve the Power BI.

 

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

Thanks @v-xicai .

 

So the answer at the moment is it's not supported for dataflows even with using a Blank Query in Dataflows and copying over the M script. 

 

I've logged the idea and it would be great of others can vote for it.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38903335-sql-server-analysis-serv...

View solution in original post

8 REPLIES 8
v-xicai
Community Support
Community Support

Hi @Mike282 ,

 

From the article , we know that the Azure analysis Services instead of SQL Server Analysis Services connector is supported in dataflow.

 

While your demand is a good idea, you can post your new idea in Idea Forum , to improve the Power BI.

 

It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

gfawcett
Microsoft Employee
Microsoft Employee

My workaround (which really only works if your data refresh cycle is infrequent) is to pull the on-prem AS data into an XL file stored on OneDrive and have your Dataflow use that as it's source.  It's a pain, because it's a two-step refresh.  If Dataflows could prompt an XL-online file to refresh and wait for it, then refresh against it, that would be handy. Maybe could do that with Power Automate, but I don't have the patience to figure it out.

Thanks @v-xicai .

 

So the answer at the moment is it's not supported for dataflows even with using a Blank Query in Dataflows and copying over the M script. 

 

I've logged the idea and it would be great of others can vote for it.

 

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/38903335-sql-server-analysis-serv...

Anonymous
Not applicable

Hi @Mike282 did you ever figure out a workaround for this issue?

Unfortunately no. It's an available feature. They support Azure SSAS but not anything on premise. You'll see in this post I posted a link to a feature suggestion I made for this. If you can vote on it, I'm hoping if enough people kick up a fuss they'll finally do it.

 

Understand why they'd do it because with their cloud SSAS they'd make money on it.

Did you find any luck on this?
I have this requirement as well.

No it just doesn't work unless it's an Azure SSAS. We're basically now questioning whether the data even needs to be in SSAS. Having it in a normal SQL server database will work just as well and you set the cardinality and create the measures in Power BI. Plus you'll be able to link it to a dataflow no problems.

So basically onprem SSAS cannot be used in dataflow?

I've been researching for awhile and havent found any luck.


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.