Data source change: SSAS to Excel?

[Andy__]

Hello

We have a Power BI Desktop file, and we host our reports in Power BI Report Server.

One of the files is using SSAS (live connect) as its data source.  We now want to change it to using Excel.  I tried changing its data source, but it only allows me to change to another SSAS.  My initial research indicated that others had asked the same question before, and the solution was to rebuild it.

Is it correct we have to start from scratch?

Thanks

[RossEdwards]

You won't need to rebuild it but there is a trick to getting it to unhitch from "live SSAS connection".

You can't have a live connection to Excel, so that means once you remove the live connection, you will be left with a report that contains no data model.  You'll need to redo the modelling portion.  If you make the same table structure, with all the same names for the columns and measures, the report should start working again.

If you already have a model in the service that uses this excel source, you can connect live to that model using the steps below.

The remove the live connection:

1. Click Transform Data.  This will ask you to switch from 'Live Connection' to 'Direct Query'.  Press the "Add a Local Model"

2.  On the Connect to your data screen, just select a single table. Pick a small table for speed.

3. Your model should now look like you have just 1 table.  On the table relationships screen, delete this table.

4. Now that you have an empty model, you should be able to connect to any source you wish.

[Andy__]

Thanks for your information.  Under the transform data menu. only the data source setttings is available, and not the transform data.  The data source settings brought me back to the screen that has SQL Server Name and database name.  When I deleted both, it did not like it.  I needed to enter at least the SQL Server Name.  With only the server, it displayed another screen with a list of database.

Perhaps it is the version of Power BI Desktop we are using, so it is different from what you described?  We are using May 2021.

Thanks 

[RossEdwards]

Yes that version likely does not allow you to do what I have described.  May 2021 is very very old.  I would advise updating to the latest version.