Microsoft Certified: Power BI Data Analyst AssociatePrepare the dataMedium
You are connecting Power BI to an Azure SQL Database. The database contains sensitive customer information, and you have been granted read-only access to specific tables. To ensure data privacy and compliance, you need to use a secure connection method that encrypts data in transit. Which data connectivity mode in Power BI Desktop should you choose to meet these requirements while allowing for scheduled refreshes?
- AConnect using OData Feed
- BImport
- CDirectQuery
- DLive Connection
Show answer & explanationAnswer & explanation
Correct answer: B. Import
The Import mode is the most common and flexible option, copying data into the Power BI model. It allows for full Power Query transformations, DAX calculations, and scheduled refreshes. When connecting to Azure SQL Database, the connection is encrypted by default, satisfying the security requirement.
Why the other options are wrong
- A. OData Feed is a specific data source type, not a general connectivity mode for Azure SQL Database, and may not have the same performance or security characteristics depending on the implementation.
- C. DirectQuery leaves data in the source. While it's secure in transit, it has limitations on transformations and DAX, and for Azure SQL DB, a gateway might still be needed for scheduled refresh if not using a cloud data source.
- D. Live Connection is typically used for SSAS Tabular or Azure Analysis Services, not directly for Azure SQL Database for this scenario.
Import Mode (Power BI)
A Power BI data connectivity mode where data is loaded into the Power BI Desktop file (PBIX) and stored in the Power BI model. This allows for full Power Query and DAX capabilities.
- Data is cached in Power BI Desktop/Service.
- Offers best performance for reports.
- Supports full Power Query transformations and DAX.
- Requires data refresh for up-to-date data.
Memory trick: Import for speed, DirectQuery for live, Live for cubes, OData for feeds.