Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Medium
Question 1
A Power BI Data Analyst is building a report to visualize monthly sales trends from an Azure SQL Database containing a Sales table (SaleID, SaleDate, Amount, Region). The report should be published to the Power BI Service for team access and refreshed daily. The analyst wants to minimize setup complexity while ensuring secure data access.
Which configuration should the analyst implement?
-
A
Use DirectQuery mode to connect to the Azure SQL Database, build the report in Power BI Desktop, and publish it to a shared workspace without a refresh schedule.
-
B
Use Import mode to connect to the Azure SQL Database, build the report, and share the .pbix file with the team via email.
-
C
Export the Sales table to an Excel file, import it into Power BI Desktop, create the report, and publish it to the Power BI Service with manual refresh.
-
D
Connect to the Azure SQL Database using a custom API connector, build the report, and publish it to the Power BI Service with a daily refresh.
-
E
Connect to the Azure SQL Database using Import mode, create the report in Power BI Desktop, publish to the Power BI Service, and configure a daily refresh with SQL Server credentials.
Reveal correct answer
Correct answer: E
Explanation
Problem Breakdown
The analyst needs to build a Power BI report to visualize monthly sales trends from an Azure SQL Database with a Sales table (SaleID, SaleDate, Amount, Region). The requirements are:
Publish the report to the Power BI Service for team access.
Refresh daily.
Minimize setup complexity.
Ensure secure data access.
Why This Configuration?
Import Mode for Azure SQL Database:
Import mode loads the Sales table data into Power BI’s in-memory model, which is ideal for reporting on monthly trends due to faster query performance compared to DirectQuery. Since the requirement is for daily refreshes (not real-time), Import mode is sufficient and simpler to set up.
Azure SQL Database is a cloud-based source, so no on-premises data gateway is required, reducing setup complexity.
Create Report in Power BI Desktop:
Power BI Desktop is the standard tool for building reports, allowing the analyst to create visualizations (e.g., line charts for monthly trends) using the Sales table data.
Publish to Power BI Service:
Publishing the report to a Power BI workspace enables team access through sharing or workspace permissions, meeting the requirement for team accessibility.
Daily Refresh with SQL Server Credentials:
Configuring a daily refresh in the Power BI Service ensures the dataset is updated with the latest data from the Azure SQL Database.
SQL Server credentials (e.g., Azure AD or database credentials) provide secure access to the database, ensuring data security.
Why Not the Other Options?
DirectQuery Mode Without Refresh:
DirectQuery provides real-time data access but is unnecessary since the requirement is for daily refreshes, not real-time updates. It also increases query latency and complexity, and skipping a refresh schedule doesn’t meet the daily refresh requirement.
Export to Excel with Manual Refresh:
Exporting to Excel introduces manual steps, which increases complexity and doesn’t support automatic daily refreshes in the Power BI Service.
Custom API Connector:
A custom API connector is unnecessary since Power BI has a native Azure SQL Database connector. This option adds complexity without benefits.
Share .pbix File via Email:
Sharing a .pbix file via email doesn’t leverage the Power BI Service for team access, lacks automatic refresh capabilities, and is insecure for data sharing.
Practical Considerations
Data Source Credentials: Ensure SQL Server credentials are configured in the Power BI Service under dataset settings for secure access.
Refresh Scheduling: Set up a daily refresh schedule in the Power BI Service, verifying the Azure SQL Database connection.
Workspace Access: Assign team members appropriate permissions in the Power BI workspace (e.g., Viewer or Member roles).
Performance: Optimize the Azure SQL Database with indexes on SaleDate and Region for efficient data retrieval.
References:
A.
DirectQuery mode provides real-time data but is unnecessary for a daily refresh requirement and may slow report performance. Not configuring a refresh schedule also violates the requirement.
B.
Sharing the .pbix file via email doesn’t support publishing to the Power BI Service for team access or automatic daily refresh, and it’s less secure.
C.
Exporting to Excel adds unnecessary manual steps and doesn’t support automatic daily refresh in the Power BI Service, making it less efficient and secure.
D.
A custom API connector is unnecessary, as Power BI has a native Azure SQL Database connector, increasing setup complexity without benefit.
E.
Using Import mode to connect to the Azure SQL Database allows data to be loaded into Power BI for fast report performance. Publishing to the Power BI Service and configuring a daily refresh with SQL Server credentials ensures secure access and meets the refresh requirement with minimal setup.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
