Microsoft Certified: Power BI Data Analyst AssociateDeploy and maintain assetsHard
A Power BI developer needs to create a new report that combines data from an on-premises SQL Server database and a cloud-based Azure SQL Database. The report requires complex data transformations and the ability to define custom measures. What is the most suitable approach for connecting to and combining these data sources in Power BI Desktop?
- ADirectQuery for both sources
- BLive Connection to both sources
- CMixed mode (Composite Model)
- DImport mode for both sources
Show answer & explanationAnswer & explanation
Correct answer: C. Mixed mode (Composite Model)
Mixed mode (Composite Model) in Power BI Desktop allows combining data from multiple sources, including a mix of Import and DirectQuery connections. This is ideal for scenarios where some data needs to be imported for complex transformations and measures, while other data might benefit from DirectQuery for real-time access or large volumes, and it specifically handles combining different types of connections.
Why the other options are wrong
- A. DirectQuery might limit complex transformations and custom measures, and performance can be an issue when joining across different DirectQuery sources.
- B. Live Connection is for connecting to existing Power BI datasets or Analysis Services models, not raw databases for combining data.
- D. Importing all data would require setting up separate gateways for each source and might lead to large dataset sizes if not managed carefully.
Power BI Mixed Mode (Composite Models)
Power BI Mixed Mode (Composite Models) allows a single data model to contain data from multiple sources, including a combination of Import and DirectQuery connections, enabling complex data integration and modeling.
- Combines Import and DirectQuery
- Integrates data from diverse sources
- Enables complex modeling and measures
- Offers flexibility in data handling
Memory trick: Mixed mode mixes and matches for maximum data flexibility.