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

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

FREE Membership Required to View Full Content:

Become a MemberLogin

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates. 
Learn more about us here