Switch to import, delete database

[joglidden2]

A general question, and I'm soliciting opinions about what may happen. Consider the following:

I have some reports, published to a workspace, and the data source for the reports is Azure SQL Server database. I need to delete this database, because it is no longer used. However, I need the reports to continue to function in the workspace, with the last data that was in the database before I deleted it. 

So my idea is to switch all the tables in the reports from storage mode 'Direct Query' to 'Import', refresh the report data, save the report, republish to the workspace, and then delete the database. Will this work? Will the reports continue to function on the last "cached" data from the database before I deleted it? Any opinions or advice on this scenario would be appreciated. 

[v-juanli-msft]

Hi @joglidden2 

Your idea can work.

If you create reports using power bi desktop, you could convert directquery into import in Power bi desktop.

in Power query, right click on the table name, uncheck "include in report refresh", then this table would not be refreshed when you refresh the dataset in power bi desktop or power bi service.

Best Regards

Maggie

[joglidden2]

Yeah wow, that makes a world of difference. Complete disaster if you don't do it. 

[joglidden2]

One more update... I still had to go into the service and switch off scheduled refresh for each datasource, or you will get constant emails announcing failure. Still haven't had the courage to delete the database. Will update here the results when I do. 

[v-juanli-msft]

Hi @joglidden2 

You could refer to my first reply.

since your old database don't use any more, so first switch into import and disable refresh for the table which connecting to the database.

thus when refreshing, it won't update any data from the old database and as it was imported into power bi already, the data is stored inside power bi.

Best Regards

Maggie

[joglidden2]

@v-juanli-msft  Maggie, ok, I did all that. I also went in to the service and switched off refresh (it was still set to refresh, even after I switched the report to 'Import' and republished). Anyway, it's off now. 

I'm going to give it another month before I delete to the datasource, just in case something goes wrong. I'm hoping by then the people who wanted to keep the reports lose interest so I don't have to recreate it all. 

Thanks again for the reply. 

[v-juanli-msft]

Hi @joglidden2 

After you switch or delete datasource in Power bi desktop, please republish the report to Power bi service and replace th original one.

In Power bi service, delete the original data source under gateway.

Add new source undre the gateway and configure refresh settings.

Best Regards

Maggie