Power BI Insights: Partition rebuilds; Incremental refresh; SQL query runs; Risk assessment

July 8 2020

Microsoft Power BI pros share their latest insights on automating index partition rebuilds, incremental refresh, SQL queries running twice and assessing Power BI risks in an organization.

Automating index partition rebuilds

Brett Powell, writing on Insight Quest took a look at how to leverage a stored procedure that IDs the last two partitions for an Azure Synapse Analytics SQL pool table and then rebuilds the index for two partitions. He wrote:

Large fact tables in data warehouses containing hundreds of millions or billions of rows are processed incrementally. For example, a nightly data warehouse load/processing job may impact only the last 15 days of data whereas older data does not change. Queries accessing the partitions for this recent data, such as Power BI in DirectQuery storage mode, will perform better if the indexes for the given partitions are rebuilt/optimized.

Powell added  that automating index rebuilds helps avoid manual work running rebuild scripts. The logic for the rebuild retrieves past partition and rowgroup information, with Unicode string variables setup to contain ALTER INDEX SQL statements. Users can make a more flexible table by parameterizing table names and schema.

Running incremental refresh operations with Power Query

About MSDW Reporter

More about MSDW Reporter