Question
You plan to create the Power BI model shown in the exhibit.
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?
Explanation
Sales must be near real time, so it must use DirectQuery; an imported copy would only be as fresh as the last refresh. SalesAggregate is refreshed only weekly and holds summarized data, so Import caches it in memory for the fastest visuals. Customer and Date are dimension tables related both to the DirectQuery Sales table and to the imported SalesAggregate table. Setting them to Dual lets Power BI answer queries from the cache when they join to SalesAggregate and push them to the source when they join to Sales, which keeps the relationships regular and avoids the limited relationships that Import or DirectQuery dimensions would create.
