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

Question

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?

v-yanjiang-msft · Answer

Hi&nbsp;jtrock&nbsp;,
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.&nbsp;
Select Get data&gt;Published datasets.

More reference:&nbsp;Create reports based on datasets from different workspaces - Power BI | Microsoft Docs
Best Regards,Community Support Team _ kalyj
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

jtrock · Answer

Hi kalyj,&nbsp;Thank you for the response.&nbsp; I already understand that the only option presented to me after deprecation is to select an existing published dataset.&nbsp; When that happens, it gives me the list of already published datasets from each workspace.&nbsp; 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.&nbsp;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.)&nbsp;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.&nbsp; Did Microsoft deprecate the only way to get NEW SSAS connection into a workspace as a dataset?&nbsp;To be clear:&nbsp;(1)&nbsp; SSAS Model A is set up as a dataset in our existing workspaces before the deprecation of this functionality.(2)&nbsp; 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)&nbsp; 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)&nbsp; 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)&nbsp; 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?

v-yanjiang-msft · Answer

Hi&nbsp;jtrock&nbsp;，
Power BI Service&nbsp;mainly used to share and&nbsp;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&gt; Database&gt; SQL Server Analysis Services database.
&nbsp;

After you create a report based on the SSAS data model, publish it to Power BI Service, it will produce the corresponding published dataset.
&nbsp;
Best Regards,Community Support Team _ kalyj
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

jtrock · Answer

Hello,&nbsp;I guess I am not explaining myself correctly?&nbsp;We have 100+ existing reports that were created in Power BI Desktop by initially selecting a data source using Get Data -&gt; 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.&nbsp;Now we have SSAS Model B, which is a development model.&nbsp; How can we switch a subset of existing reports to use SSAS Model B as a data source?&nbsp; I can go into Options and settings -&gt; 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.&nbsp;If SSAS Model B were a published data set already in a workspace, this would be no problem.&nbsp; But at this point, adding it as a data set to a workspace has been deprecated.&nbsp;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?&nbsp;Is there a gap here that Microsoft hasn't addressed, and I am stuck re-creating reports from scratch by going to Get Data -&gt; 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.?&nbsp;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.&nbsp;When I go to Options and settings -&gt; 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.&nbsp; That would also resolve my issue.

v-yanjiang-msft · Answer

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

Best Regards,Community Support Team _ kalyj

Forum Discussion

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

6 Replies

Related content

Cross workspace semantic model access

Moving Semantic model from one workspace to other workspace

Workspace, semantic model, report, users and dataset lifecycle management

Semantic models not appearing in workspace

Premium_ASWL_Error – Workspace Identity authentication blocking semantic model refresh