Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data modeler is preparing a Power BI report that combines sales data from an Azure SQL Database and customer feedback from a SharePoint Online list. The report needs to display near real-time sales data while also allowing for historical analysis of customer feedback, which is updated less frequently. Which storage mode configuration should the data modeler use for these two data sources to optimize performance and data freshness?
- ADirectQuery for Azure SQL Database and Import for SharePoint Online list.
- BImport for both Azure SQL Database and SharePoint Online list.
- CDual for Azure SQL Database and DirectQuery for SharePoint Online list.
- DDirectQuery for both Azure SQL Database and SharePoint Online list.
Show answer & explanationAnswer & explanation
Correct answer: A. DirectQuery for Azure SQL Database and Import for SharePoint Online list.
DirectQuery is suitable for near real-time data from sources like Azure SQL Database, as it queries the source directly. Import mode is appropriate for less frequently updated data like SharePoint lists, as it loads data into Power BI's cache for faster performance.
Why the other options are wrong
- B. Importing both would not provide near real-time sales data and would increase refresh times for the large sales dataset.
- C. Dual mode is not applicable for SharePoint lists, and DirectQuery for SharePoint lists is generally not optimal for performance.
- D. DirectQuery for SharePoint lists can be slow and is generally not recommended for performance-sensitive scenarios due to its nature.
Mixed Storage Mode
A Power BI data model configuration that allows different tables to use different storage modes (Import, DirectQuery, Dual) to optimize performance, data freshness, and resource usage based on data source characteristics.
- Combines benefits of Import (speed) and DirectQuery (freshness).
- Requires careful consideration of data source types and update frequency.
- Tables in DirectQuery mode can impact query performance on the source system.
Memory trick: Import for speed, DirectQuery for fresh, Dual for both, a smart mesh.