Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
A data analyst is importing sales transaction data into Power BI from an on-premises SQL Server database. The sales data needs to be refreshed daily, but the dataset is very large (over 500 million rows), and users require near real-time analytics for the most recent month's data. Historical data (older than one month) is less critical and can tolerate slightly older refresh cycles. Which data connectivity mode is MOST appropriate for this scenario?
- AImport
- BLive Connection
- CDirectQuery
- DDual
Show answer & explanationAnswer & explanation
Correct answer: D. Dual
Dual mode allows tables to behave as DirectQuery or Import depending on the context. This is ideal for scenarios where a large dataset has different freshness requirements for different parts of the data, combining the benefits of both modes for optimal performance and up-to-dateness.
Why the other options are wrong
- A. Import mode would provide fast performance but daily refreshes for 500 million rows would be resource-intensive and might not meet near real-time requirements for the latest month.
- B. Live Connection is typically used for SSAS, Azure Analysis Services, or Power BI datasets, which is not the primary source in this scenario (on-premises SQL Server).
- C. DirectQuery would provide near real-time data but could be slow for a very large dataset and complex aggregations, impacting user experience.
Dual Storage Mode
Dual storage mode in Power BI allows tables to operate in either DirectQuery or Import mode depending on the query, providing a balance of performance and data freshness.
- Combines benefits of Import and DirectQuery.
- Optimizes for both performance and data freshness.
- Tables can dynamically switch modes based on query context.
Memory trick: Import for speed, Query for fresh, Dual for the best of both.