Wednesday, October 30, 2019

Power BI Desktop: How to determine a Connectivity mode for a query? Can I change it?

After you build Power BI dashboard you want to make sure that everything is set as it supposed to be.

Or, you are troubleshooting PBI dashboard performance and trying to figure out what it is doing.

For a Power BI dashboard it is very important if it runs against live data or internally imported data.
Which Connectivity/Storage mode your query is using, "DirectQuery" or "Import"?

That is very easy to determine, just put your cursor above name of your data set, and after about one second an informational label will tell you a Storage mode for that query.
Looks like this:


As you can in my dashboard: data set "Objects" is "Imported", but data set "Partitions" has "DirectQuery" as a source.

Now will take a look on how to change it.
So far I haven't found a menu option for doing this, but it is still possible:

1. First, start "Query editor". Then:
- Highlight your query (Partitions);
- Choose "Transform" tab in a menu;
- Click on "Group By" menu item.

2. You do not have to do anything in the "Group By" interface. Just hit "OK".

3.Than "Switch all tables to Import mode" button should appear at the top of your data:

4. Remember that is the ONE WAY ROAD, you won't be able to get back if you press this button. If you just will try to apply the changes without switching you'll get an error message like this:

5. Whether you decided to switch from "DirectQuery" to "Import" Storage mode you have to clean your "Grouping By" change by deleting it. Just simply click on a cross on the left side of the pane of your query:

 At the end, I want to point again:
You can easily switch from "DirectQuery" to "Import" Storage mode, but you can't go back.
So, be careful with it.

No comments:

Post a Comment