Changing Direct Query to Import Storage Mode

[jmerencilla]

Hello everyone.

I am currently connected to a dataset (Direct Query) and to a SharePoint file. Looking at my Model View, I cannot change the Storage Mode because it is disabled. Is there a way to change this to Local? I am in the process of migrating this huge report that because it is slow using Direct Query, I am planning to use Import instead. Appreciate the help. Thank you!

[jmerencilla]

Hi @Anonymous,

Thank you again for these options. Unfortunately, these options  still are not available on my side. I will have to dig deeper on this. But in general, I don't think I should have a separate copy of the semantic model for my own use. This semantic model is being used across the company, and any change on it should trickle down on my project as well. 

[jmerencilla]

Hi @Anonymous ,

Thanks for the alternative solutions, but these will not work for me either. 

Regards

jmerencilla

[Anonymous]

Hi @jmerencilla ,

It's not clear how that semantic model in your service has been modified via XMLA endpoints.

In my testing, I used the deployment pipeline to copy the semantic model (Direct Query) that had been modified by the XMLA endpoint in one Premium workspace to another Premium workspace.
Get started using deployment pipelines, the Fabric Application lifecycle management (ALM) tool - Mic...

The PBIX file can then be downloaded from this Premium workspace.
Once the PBIX file is opened, select Discard Changes at the pending changes prompt and change Direct Query to Import.

Please try the above steps.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmerencilla]

HI @Anonymous ,

I tried but I guess it is not possible. 

[Anonymous]

Hi @jmerencilla ,

This prompt is a limitation.

Limitations when downloading a semantic model .pbix file

There are no other actions done that resulted in this behavior. In fact, I created a blank PBI, connect it to the semantic model, and automatically, it is disabled. I am not able to change. 

I am unable to reproduce this step of yours.
I am using the latest version of PBI Desktop.
After connecting to the semantic model (Direct Query) on Service, I have to click "Make Changes to this mode" before I see the storage mode disabled.

According to Microsoft documentation.
Connect to semantic models in the Power BI service from Power BI Desktop - Power BI | Microsoft Lear...
The connection mode used when connecting to a semantic model in Service in Power Bi Desktop is a live connection.

Then, after changing the connection mode to Direct Query by “Make changes to this model”, you can't change to Import mode, which is a limitation.
Solved: PowerBI dataset in import storage mode instead of ... - Microsoft Fabric Community
Solved: Cannot convert from DirectQuery to Import mode - Microsoft Fabric Community

Alternatively, if you have the datasource credentials for the semantic model, then you can bypass the restriction by downloading the semantic model via xmla endpoint and then converting it to Import.
Creating PBIX File from XMLA Endpoint. | Tabular Editor Documentation

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for 123abc's concern about this issue.
Hi @jmerencilla ,

What actions did you perform that resulted in seeing the storage mode disabled in the model view?

I tried the following steps to reproduce your problem.
When I connect to the semantic model (Direct Query) from Power BI Service in Power BI Desktop, instead of seeing the storage mode in the model view it is as follows.

Then, I clicked Make Changes to this model in the lower right corner to add the Direct Query connection to the data source.

Then, the issue you described appears.

Are the above the right steps to reproduce your problem?

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmerencilla]

hi @Anonymous 

I appreciate the help. Thank you!

There are no other actions done that resulted in this behavior. In fact, I created a blank PBI, connect it to the semantic model, and automatically, it is disabled. I am not able to change. 

I also created another blank PBI, connect it to a CSV file first, and connect it to the same semantic model. Storage model now is Mixed, but still disabled. 

In my other project, I am able to see this "Make Changes to this mode". But from Live connection, it only goes to DirectQuery. 

What I need, if possible, is from DirectQuery to Import. But based on my readings, is only available when you are not connected to a semantic model like SQL server or Analysis Services. 

My goal is, if I can import the data from the semantic model, then our reports will be faster because it is not pulling the data directly from the service. 

[Anonymous]

Hi @jmerencilla ,

Have you tried downloading PBIX files from this semantic model.
Download a report from the Power BI service to Power BI Desktop - Power BI | Microsoft Learn
In this PBIX file, the storage model can be changed from Direct Query to Import.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jmerencilla]

Hello! Any other workaround on this? I am pretty sure I am not the only one who has this problem. 😅

[123abc]

Please follow given links ... these link will help you how to solve the issue.

https://community.fabric.microsoft.com/t5/Desktop/Convert-storage-mode-from-direct-query-to-import-d...

https://powerbiconsulting.com/direct-query-vs-import-mode-in-power-bi/

https://www.youtube.com/watch?v=-ip7mKUdwRg

https://community.fabric.microsoft.com/t5/Desktop/Convert-storage-mode-from-direct-query-to-import-d...

https://community.fabric.microsoft.com/t5/Desktop/How-to-change-direct-query-to-import-query/m-p/100...

OR

Switching from Direct Query to Import mode in Power BI can be a bit tricky, especially if the option is disabled in your Model View. Here are some steps you can try to change the storage mode:

1. Check the Status Bar: Sometimes, you can change the storage mode directly from the status bar at the bottom right of Power BI Desktop. Look for a text that reads “Direct Query: On (Click Here To Change)” and click it to switch to Import mode.

1.            Use the Advanced Options:

> Go to Model View.

> Select Advanced from the ribbon.

> If the storage mode option is still disabled, this method might not work for your specific setup.

1.            Create a Calculated Table:

> Create a new calculated table from your Direct Query table. This new table will be in Import mode.

> Use this calculated table for your relationships and visuals instead of the original Direct Query table.

1.            Add a CSV File:

> Create a simple CSV file and load it into your Power BI model.

> When prompted, switch all tables to Import mode.

> Delete the CSV file from your model after the switch.

1.            Recreate the Report:

> If none of the above methods work, you might need to recreate your report by connecting to your data sources again and choosing Import mode during the initial setup.

These steps should help you migrate your report to Import mode, which can significantly improve performance. If you encounter any issues, feel free to ask for more specific guidance!

[jmerencilla]

Hi @123abc,

I have tried the following

1. Advanced Options -> Disabled so I cant change anything
2. Calculated Table -> Quite a Herculean job so this is not an option. The data source is a semantic model in a workspace in the Power BI Service. 
3. CSV File -> Imported successfully, but no prompt to switch all tables to Import Mode
4. Recreate the report -> I created a blank PBI file, and connected to the same semantic model. Storage mode is DirectQuery and is also disabled.

Also, the links that you provided talks about being able to change the storage only when they are not connected to a semantic model. They are mostly connected to SQL. For my case, I am connected to a semantic model

I appreciate the help though. Thank you.