Microsoft Certified Power Bi Data Analyst Associate · Free Practice Question Hard
Question 7
A Power BI Data Analyst is working with a large dataset containing 10 million rows of transactional data in Power BI Desktop. The dataset is sourced from a SQL Server database, and users report slow report performance. The analyst needs to optimize the dataset to improve query performance while maintaining data accuracy and minimizing refresh times.
Which approach should the analyst take to manage this large dataset effectively?
-
A
Enable incremental refresh with a date-based filter, reduce the dataset to the last 2 years of data, and use DirectQuery mode for real-time updates.
-
B
Import the entire dataset, apply aggregations in Power Query, and use composite models to combine with DirectQuery for recent data.
-
C
Configure incremental refresh with a date-based filter, import the last 5 years of data, and apply query folding to optimize data transformations.
-
D
Switch to DirectQuery mode for the entire dataset, apply aggregations in the report, and disable query caching to reduce memory usage.
-
E
Use Import mode with no incremental refresh, reduce the dataset size by removing unnecessary columns, and enable query caching for faster report rendering.
Reveal correct answer
Correct answer: C
Explanation
The analyst is working with a large dataset (10 million rows) from a SQL Server database in Power BI Desktop. The requirements are:
Improve query performance for reports.
Maintain data accuracy.
Minimize refresh times.
Why This Configuration?
Incremental Refresh with Date-Based Filter:
Incremental refresh allows Power BI to refresh only new or updated data based on a date range (e.g., SaleDate), significantly reducing refresh times for large datasets. Instead of refreshing all 10 million rows, only recent data (e.g., the last month) is processed.
A date-based filter (e.g., RangeStart and RangeEnd parameters) is applied in Power Query to partition the data, enabling efficient refresh.
Import the Last 5 Years of Data:
Importing only the last 5 years reduces the dataset size compared to the full 10 million rows, improving query performance while maintaining relevant data for analysis. This balances data coverage with performance.
Import mode is suitable for large datasets when real-time data isn’t required, as it leverages Power BI’s in-memory engine for faster queries compared to DirectQuery.
Apply Query Folding:
Query folding pushes data transformations (e.g., filters, joins) to the SQL Server database, reducing the data processed in Power Query. This optimizes performance by leveraging the database’s processing power and minimizes refresh times while ensuring data accuracy.
How It Works
In Power BI Desktop, connect to the SQL Server database and load the transactional data.
In Power Query, create RangeStart and RangeEnd parameters for incremental refresh (e.g., DateTime type).
Apply a filter on the date column (e.g., SaleDate >= RangeStart and SaleDate < RangeEnd) to partition data.
Configure incremental refresh in the Modeling tab:
Set the table to store 5 years of data and refresh the last 30 days (or as needed).
Ensure transformations (e.g., filtering, grouping) support query folding by checking the View Native Query option in Power Query.
Publish to the Power BI Service and configure a refresh schedule.
Why Not the Other Options?
Incremental Refresh with DirectQuery for 2 Years:
Combining incremental refresh with DirectQuery is not supported, as incremental refresh applies to Import mode. DirectQuery also slows report performance due to live database queries.
DirectQuery with Aggregations and No Caching:
DirectQuery is slower for large datasets, as it queries the database for every report interaction. Disabling query caching increases latency, worsening performance.
Import with Aggregations in Power Query and Composite Models:
Aggregations in Power Query reduce granularity, potentially affecting accuracy. Composite models (mixing Import and DirectQuery) add complexity without clear benefits for this scenario.
Import with No Incremental Refresh and Column Removal:
Importing all 10 million rows without incremental refresh leads to long refresh times. While removing columns helps, it’s less effective than incremental refresh for large datasets.
Practical Considerations
Incremental Refresh Setup:
Ensure the date column (e.g., SaleDate) is consistent and has no nulls for incremental refresh to work.
Test the refresh policy in Power BI Desktop before publishing.
Query Folding:
Avoid transformations that break query folding (e.g., complex custom columns). Use View Native Query to confirm folding.
SQL Server Optimization: Index the date column and other frequently filtered columns in SQL Server to enhance query folding.
Performance Monitoring: Monitor refresh times and report performance in the Power BI Service, adjusting the refresh window if needed.
References:
A.
Combining incremental refresh with DirectQuery mode is not supported in Power BI, as incremental refresh requires Import mode. Reducing to 2 years may also limit historical analysis.
B.
Importing the entire dataset without incremental refresh is inefficient for 10 million rows. While composite models can combine Import and DirectQuery, applying aggregations in Power Query may break query folding, reducing efficiency.
C.
Configuring incremental refresh with a date-based filter reduces the volume of data loaded during refreshes, improving performance for large datasets. Importing the last 5 years balances historical analysis needs with efficiency, and query folding ensures transformations are pushed to the SQL Server, optimizing data retrieval.
D.
DirectQuery for 10 million rows can lead to slow query performance, especially without proper indexing. Disabling query caching increases query execution time, worsening performance.
E.
Importing the entire dataset without incremental refresh is inefficient for large datasets, even with column removal. While query caching helps, it does not address refresh performance for large datasets.
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
