Change / disable datasource from Analysis Services

[Anonymous]

Hi!

In order to keep track and control of the our datasets our organization would like to use an architecture as such:

  - we deploy all our datasets from an analysis server to a specific workspace, containing only datasets and no reports.

  - build our reports using the Power BI Datasets, not connecting directly to the analysis service.

Problem is that we already have a lot of reports live with connections to different analysis services databases. And it seems like if you've created a report using a live connection, be it Analysis Services or Power BI Dataset you are not able to change to a different type of connector.

Is there a way to change a connection in a report from Analysis Services to a Power BI Dataset without recreating the report? 

Maybe there is  a possibility to kill the connection to a data source while keeping all the report elements (visualizations, filters, bookmarks etc.), and then reconnect to a new data source?

Do you think this type of architecture is flawed?

Best regards

arefossa

[Anonymous]

Hello, 

You can use : https://docs.microsoft.com/en-us/rest/api/power-bi/reports/rebindreportingroup

Thomas

[AnthonyTilley]

So my method for moving from one data source to onther is dependent on weather the naming of your tables and colunms in the new data source is identical to teh orginal.

in my example below you can see that i have a conection to a simple dataset (SQL server) and have a pulled a table with 4 colunms i have then made a measure and created a visual in the report using this measure

First of you need to move any measures you have to a new table so that they will not be wiped out, unfortunatly calculated colunms will need to be remade.

to move your measures click on the enter data button and create a table called facts add a single colunm but enter no data

Now that you have an empty table. click on your measure and in the modeling tab Select the home table and change this to the new fact table.

This will move your measure to the new table 

i now want to change the data source to pull from an excel file.

to do this i would go to the Query editor

in the Query editor select a new data source and create a connection to your new data source

You will now have two tables, your orginal in my case called test Data and your new source in my case called sheet1

Now rename your orginal table to something new (make sure you take not of exactly how your table was spelled

Then Rename your new data set to match the orginal table name

Onace you have renamed the table you can remove the old table and then click close and apply

when you return to your report the data source will switch to teh new table, provided all of your colunms are the same type and named the same then all your relation ships and measures will be intact and connected to the new source. any calulcated colunms will have been removed 

[Anonymous]

Thanks for your answer! Learned a few new tricks there, but this unfortunately seems to be impossible while using live connections with for example Analysis services input.

[AnthonyTilley · Accepted solution]

Shame did not realise that SSAS could not be swapped 

i would say that your best option is to publish your data sets 

then open a new blank report in desktop, connect to your Dataset and then just copy all of your visuals accross from the old report to the new one

Power bi now allows coping from one desktop file to another and since all of your colunms should be the same you can just copy and paste all of the visuals.

if you group all of your visuals first then you can select them all at once and copy the whole page over to the new report.

[Anonymous]

Yes this is unfortunatly what I believe is the current solution to my issue. The cons of this approach is bookmarks and some other settings will not be automatically brought over.