Change data source from SQL Server Analysis Services Database to Power BI Datasets

[Anonymous]

Hi, I have reports that have a live connection to a tabular model on SQL Server Analysis Services Database, and now I want to change the data source to use Power BI datasets. These reports have a lot of tabs, bookmarks, and visuals, so I'm trying to avoid to have to create an empty report and copy and paste all visuals. Is there a way to do this in Desktop?

Thank you!

[fgoussou]

This works for Semantic Models in March 2024:

• Create a new empty report to serve as the connection source:

1. Home -> New -> Report
2. Connect to the desired semantic model
3. Save the report as source.pbix

• Create a copy of the SSAS .pbix file. Let’s call this Destination.pbix
• Make sure Destination.pbix is not ready only (in widnows, right click -> Properties ->Attributes).
• Rename Destination.pbix by changing the extension from .pbix to .zip.

Note: If you are on Windows, you will need to have the “File name extensions” checkbox checked in Windows Explorer as shown below:

• Rename the source.pbix by changing the extension from .pbix to .zip.
• Use Beyond Compare to compare the two .zip files and copy over the contents of the “Connections” file from source.pbix to the other file.
• Rename the resulting file from step 6 by changing the extension from .zip to .pbix.
• When you open the converted file, you might notice that the hidden tables and hidden measures are being shown. Right click on one of these tables and uncheck the “View Hidden” option. The visibility settings for all tables and measures will now work as expected.

[MarkPaul]

Superb. Thanks for sharing 👏

[Anonymous]

This is pretty ridiculous. SO you can repoint nearly every other data source to a different data source by going into power query or get data but you can't repoint an on premise Analysis Services cube to an identical Power BI dataset without "Copying and pasting"...just poor.

[Anonymous]

I agree.. 😅

[collinq]

Hi @Anonymous ,

Unless the Power BI dataset is identical to the SQL Server Analysis Services Database, you will have to get new connections.  BUT, you stated that you are using a dataset and not a "different SQL Server" so that sounds to me like you have a totally different connection and datasource and so you will have to rebuild everything.  In that case, copy and paste visuals won't work either if your data sources in the dataset are different.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Anonymous]

Thank you for the quick response. The content in the Power BI dataset is identical to the content in the tabular model in the SSAS database. We're doing this test because we're planning to move our data model from SSAS to Power BI. I've confirmed that copy and paste works since everything is the same, but I do wonder if there is an easier way. Thank you again!

[collinq]

HI @Anonymous ,

If it is truly identical, you can open the report you have with SQL, then do a save as so you have a copy.  Then, go to files- options and settings - data settings.  Then, select the datasource that you have now and replace it with the new one.  That is how we do it when we convert from a DEV to PROD system so we don't have to copy/paste!

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Anonymous]

Thank you again!

Right, that is also what we do to convert from dev to prod. However, since the report is connected to a 'SQL Server Analysis Services Database', when I go to Files>Options and settings>data source settings, it gives me this window to change to a different ssas database:

I can only change it to a different SSAS database. It doesn't allow me to connect using the Power BI dataset option. Can I change what I see here?

Thanks!

[kristianjay]

What did you end up doing in this case? 

[collinq · Accepted solution]

HI @Anonymous ,

No, connecting to the dataset and then copy/paste is going to be your best bet.

I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!

[Anonymous]

Thank you again!