Learn how to select the optimal storage modes for tables in a Power BI data model to minimize visual load times and meet specific data refresh needs. Expert tips for DirectQuery, Dual, and Import modes.
Table of Contents
Question
You plan to create the Power BI model shown in the exhibit. (Click the Exhibit tab.)
The data has the following refresh requirements:
- Customer must be refreshed daily.
- Date must be refreshed once every three years.
- Sales must be refreshed in near real time.
- SalesAggregate must be refreshed once per week.
You need to select the storage modes for the tables. The solution must meet the following requirements:
- Minimize the load times of visuals.
- Ensure that the data is loaded to the model based on the refresh requirements.
Which storage mode should you select for each table? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Customer:
- DirectQuery
- Dual
- Import
Date:
- DirectQuery
- Dual
- Import
Sales:
- DirectQuery
- Dual
- Import
SalesAggregate:
- DirectQuery
- Dual
- Import
Answer
Customer: Dual
Date: import
Sales: DirectQuery
SalesAggregate: Import
Explanation
To meet the stated requirements of minimizing visual load times and loading data based on the specified refresh cadences, I recommend setting the following storage modes for each table:
Customer: Dual
Setting the Customer table to Dual storage mode will allow it to be queried from the imported data when possible for better performance, while still supporting the daily refresh cadence through DirectQuery.
Date: Import
Since the Date table only needs to be refreshed once every three years, Import mode is sufficient and will provide the best query performance. There is no need for the complexity of Dual or DirectQuery.
Sales: DirectQuery
With Sales requiring near real-time refreshes, DirectQuery is the only viable option. This will run queries directly against the source database to get the latest data, at the cost of somewhat slower performance compared to Import.
SalesAggregate: Import
Refreshing the SalesAggregate table once per week is easily achievable with Import mode. This will provide much faster query speeds than DirectQuery while still supporting the weekly refresh cycle.
In summary, using Dual for slowly changing dimensions like Customer, Import for infrequently updated or aggregated tables, and DirectQuery for volatile real-time data allows you to optimize the tradeoffs between query performance and data freshness. The key is understanding the refresh requirements and user experience needs for each table.
Microsoft PL-300 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Microsoft PL-300 exam and earn Microsoft PL-300 certification.