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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jtrock
Frequent Visitor

Adding a SSAS data model as a data set in a workspace

I am not quite familiar with all of the terminology, so please forgive me if I misrepresent any of the details.

 

A few years ago, my organization created several departmental workspaces in the Power BI service, and at some point there was the option to select our SSAS data model as a dataset, so each workspace had its own dataset that pointed to our data model.  Then, when creating a report in Power BI Desktop, you would start by selecting Power BI Datasets, choose which workspace you intended to publish the report to, and then selected the SSAS data model that was a dataset for that workspace.  So nearly our entire library of reports are set up this way.

 

If you need to change the data source of a report, you can go into PBI Desktop and go to Options and settings -> Data source settings, and you get the "Select a dataset to create a report" dialog box with the list of all the datasets in each workspace you have access to.  In the lower right corner of PBI Desktop, it says I am connected live to the Power BI dataset: (name of our dataset in the workspace) in (workspace), in case that helps.

 

That has been fine for us up until we have a need to create a new workspace.  I feel like the option I had in the past to set up our SSAS data model as a dataset for any newly created workspace has been deprecated.

 

This is becoming an issue for two reasons:

 

1) Because we are moving our data model off the current server onto a new one, and at some point I will have to re-point the existing datasets that are using our model to the new server.  I think I see where to do that in the Workspace Dataset settings (I believe it has something to do with the gateway?)

 

2) We also have stood up a development model to which I want to be able to switch certain existing reports to use as a data source to validate that results match our current production model.  I can't change the data source on an existing report to use an Analysis Services data source so I can just type in the new server and SSAS model name and connect.... it only gives me the option to use a Power BI dataset.  So in the past I would have just created a workspace and added this new development model as a dataset, so it would be an option to which I could change my data source for an already existing report -- but this ability appears to be deprecated.

 

Short of re-building 100+ reports from scratch in PBI Desktop and starting with Get Data -> Analysis Services in each to be able to directly specify the server and model, is there any way to add a SSAS data model as a dataset to a workspace?

6 REPLIES 6
v-yanjiang-msft
Community Support
Community Support

Hi @jtrock ,

So far directly connecting to the SSAS data source from the Power BI service is deprecated. To create dynamic reports with data and metrics you already have in your Analysis Services solutions, use a published dataset instead. 

Select Get data>Published datasets.

vkalyjmsft_0-1645768517168.png

vkalyjmsft_1-1645768782493.png

More reference: Create reports based on datasets from different workspaces - Power BI | Microsoft Docs

Best Regards,
Community Support Team _ kalyj

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

 

Hi kalyj,

 

Thank you for the response.  I already understand that the only option presented to me after deprecation is to select an existing published dataset.  When that happens, it gives me the list of already published datasets from each workspace.  The issue is that the new development model we are standing up is not a published dataset in any of the workspaces, so I cannot follow your advice to select it.

 

How do I make our new development SSAS model into a published dataset in a new workspace that I can select it during the step you're describing (in which case I wouldn't need to select it to add to a workspace, because then at that point it would already be a published dataset that I would be able to switch to when changing data sources in Power BI Desktop for an existing report.)

 

Basically, from I can tell, your answer is "In order to make it a published dataset in a new workspace, you need to already have it as a published dataset in a different workspace." and that does not help.  Did Microsoft deprecate the only way to get NEW SSAS connection into a workspace as a dataset?

 

To be clear:

 

(1)  SSAS Model A is set up as a dataset in our existing workspaces before the deprecation of this functionality.

(2)  We want to switch existing reports from using SSAS Model A to newly-created SSAS Model B for testing and validation of SSAS Model B

(3)  The only option to switch data sources in an existing PBIX file is to switch to using another published dataset in a workspace (you can't switch from a published dataset in a workspace to specifying the SSAS server/model directly, for example)

(4)  So SSAS Model B needs to be a published dataset in a workspace in order to fulfill step (3), but the way to do that has been deprecated?

(5)  What is the current post-deprecation Microsoft process to take an existing report in Power BI Desktop and swap it from using a direct-connection SSAS Model A set up as a published dataset in a workspace to using a new direct-connection SSAS Model B that is not set up as a published dataset in a workspace because the way to do that no longer exists?

Hi @jtrock ,

Power BI Service mainly used to share and consume business insights, it can only connect to a very limited number of data sources.

However, Power BI Desktop can connect to other large variety of data sources. For SSAS, in Desktop, select Get Data> Database> SQL Server Analysis Services database.

 

vkalyjmsft_0-1646040011483.png

After you create a report based on the SSAS data model, publish it to Power BI Service, it will produce the corresponding published dataset.

 

Best Regards,
Community Support Team _ kalyj

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

Hello,

 

I guess I am not explaining myself correctly?

 

We have 100+ existing reports that were created in Power BI Desktop by initially selecting a data source using Get Data -> Power BI Data Sets, and then the content creator selected our SSAS model (let's call it SSAS Model A) from one of many workspaces that had it as a published data set back before that was deprecated.

 

Now we have SSAS Model B, which is a development model.  How can we switch a subset of existing reports to use SSAS Model B as a data source?  I can go into Options and settings -> Data source settings, but it will only let me change to a different published data set within the list of workspaces I have access to.

 

If SSAS Model B were a published data set already in a workspace, this would be no problem.  But at this point, adding it as a data set to a workspace has been deprecated.

 

So what are the recommended steps to point existing reports already using SSAS Model A as a published data set in a workspace to SSAS Model B that is not a published data set in a workspace because Microsoft took away that ability?

 

Is there a gap here that Microsoft hasn't addressed, and I am stuck re-creating reports from scratch by going to Get Data -> Analysis Services and typing in the name of the server and the SSAS Model B model and then having to re-create everything, including custom measures, etc.?

 

There used to be a way where you could change the .pbix extension to a .zip extension and then open it as a ZIP archive and see all of the internal XML files and maybe hack a data source change that way -- not sure if that still works or not.

 

When I go to Options and settings -> Data source settings, instead of just assuming I want to select another data source from Power BI Datasets, it should give us the option to reselect the type of data source, so at that point I can choose Analysis Services instead, and then get a prompt to enter the server name and then select the model.  That would also resolve my issue.

Hi @jtrock ,

Thanks for the detailed explanation. I have a doubt, even if Model B is a published dataset, still need to  re-create reports including custom measures, because when you click Transform data>data source setting, choose another dataset, all the visuals are broken, and measures created before are missing.

vkalyjmsft_0-1646101932200.png

Best Regards,
Community Support Team _ kalyj

That doesn't happen if you have the same Tabular data model as a dataset in different workspaces... you can freely switch between them and the visualizations/measures do not break because all of the same tables/columns/measures are in each data set because you'd be using the same data model.

 

Thank you for your continued help, but I'll just submit a support ticket in to Microsoft to identify this gap.  I find it challenging to believe we're the only one experiencing this roadblock.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors