Dual Mode Storage mode Setting in Power BI

Added July 2018 as part of the composite model. See https://debbiesmspowerbiazureblog.home.blog/2019/09/10/power-bi-composite-models/

One of the great new features is you can now hover over a table and see the Storage mode and last refresh date.

All the tables in the current report are set as Import. The data is imported into power BI memory and we have full functionality using the Vertipaq engine.

Dual mode can be Import OR direct query (BUT) the functionality for both is at direct Query level. You will lose some functionality if you move to Dual mode. for example, time period DAX cant be used in this mode.

Once you change to Dual Mode, its irreversible. Best to check on a test copy before you go for it

Why Use Dual Storage?

Firstly, the new Composite storage mode allows you to set different types of storage per table. You don’t have to have the entire model set as Import or Direct Query.

With Dual Storage set Power Bi chooses which way to go, although you lose full functionality of Import mode if you are set to Dual storage.

Dual Tables are only ever on the One side of a many to many relationship

For example, If you are taking advantage of aggregation, and you were to run a query against the higher level aggregated Fact table (Import) with Date (Dual) and Product (Dual) Date and Product would be used as a direct import so the join is done in Power Query

if you then run a query that you cant use the aggregated table for and the Main Fact table at the lowest level of granularity is used , the Dual Table runs as Direct Query so the joins between the fact and dim are set at source level (In SQL) This is much more viable than trying to query data in SQL database and data in the in memory vertipaq engine.

Personally, I’m still unsure of Direct Query and Dual mode because you lose so much functionality. the time functionality is always my first go to’s when I create any report. Any measures and calculated columns that are too complex for SQL aren’t included and for me, these are the most powerful.

One Reply to “Dual Mode Storage mode Setting in Power BI”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: