Lesson Learned #247: All started with the phrase: In PowerBI Direct Query is slow - Indexed views

[Guest Jose_Manuel_Jurado]

In some situations, customers that are using PowerBI and Direct Query reported performance issues depending how the query has been defined by PowerBI. In this scenarion, I would like to share with you how we fixed this performance issue using Indexed View.

 

 

 

For this example, I download a demo database Release Wide World Importers sample database v1.0 · microsoft/sql-server-samples · GitHub and duplicate rows of a table Fact.Sale until having 234.767.360 rows.

 

 

 

I choose HyperScale Database tier basically as a medium size database for OLAP.

 

Lesson Learned #1: If you need to know how many rows per table instead of using SELECT COUNT() for performance improvements.

 

 

 

 

 

 

SELECT

t.NAME AS TableName,

s.Name AS SchemaName,

max(CASE i.type WHEN 5 THEN si.rowcnt ELSE p.rows END) AS RowCounts,

SUM(a.total_pages) * 8 AS TotalSpaceKB,

SUM(a.used_pages) * 8 AS UsedSpaceKB,

(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB

FROM

sys.tables t

INNER JOIN

sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN

sysindexes si ON t.OBJECT_ID = si.id

INNER JOIN

sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN

sys.allocation_units a ON p.partition_id = a.container_id

LEFT OUTER JOIN

sys.schemas s ON t.schema_id = s.schema_id

WHERE t.is_ms_shipped = 0

AND i.OBJECT_ID > 255

GROUP BY

t.Name, s.Name

ORDER BY

t.Name, s.Name

 

 

 

 

Lesson Learned #2: Using Indexed views

 

 

 

 

In this situation, we have a report where we need to obtain per Fiscal Month Label the Total sales including and excluding Tax.

 

 

 

 

 

 

Lesson Learned #3: PowerBI generates the following TSQL statement:

 

 

 

 

 

 

SELECT

TOP (1000001) *

FROM

(

 

SELECT [t3].[Fiscal Month Label] AS [c43],SUM([t6].[Total Excluding Tax])

AS [a0],SUM([t6].[Total Including Tax])

AS [a1]

FROM

((

select [$Table].[sale Key] as [sale Key],

[$Table].[City Key] as [City Key],

[$Table].[Customer Key] as [Customer Key],

[$Table].[bill To Customer Key] as [bill To Customer Key],

[$Table].[stock Item Key] as [stock Item Key],

[$Table].[invoice Date Key] as [invoice Date Key],

[$Table].[Delivery Date Key] as [Delivery Date Key],

[$Table].[salesperson Key] as [salesperson Key],

[$Table].[WWI Invoice ID] as [WWI Invoice ID],

[$Table].[Description] as [Description],

[$Table].[Package] as [Package],

[$Table].[Quantity] as [Quantity],

[$Table].[unit Price] as [unit Price],

[$Table].[Tax Rate] as [Tax Rate],

[$Table].[Total Excluding Tax] as [Total Excluding Tax],

[$Table].[Tax Amount] as [Tax Amount],

[$Table].[Profit] as [Profit],

[$Table].[Total Including Tax] as [Total Including Tax],

[$Table].[Total Dry Items] as [Total Dry Items],

[$Table].[Total Chiller Items] as [Total Chiller Items],

[$Table].[Lineage Key] as [Lineage Key]

from [Fact].[sale] as [$Table]

) AS [t6]

 

INNER JOIN -- Review avoid using Left Join if possible.

 

(

select [$Table].[Date] as [Date],

[$Table].[Day Number] as [Day Number],

[$Table].[Day] as [Day],

[$Table].[Month] as [Month],

[$Table].[short Month] as [short Month],

[$Table].[Calendar Month Number] as [Calendar Month Number],

[$Table].[Calendar Month Label] as [Calendar Month Label],

[$Table].[Calendar Year] as [Calendar Year],

[$Table].[Calendar Year Label] as [Calendar Year Label],

[$Table].[Fiscal Month Number] as [Fiscal Month Number],

[$Table].[Fiscal Month Label] as [Fiscal Month Label],

[$Table].[Fiscal Year] as [Fiscal Year],

[$Table].[Fiscal Year Label] as [Fiscal Year Label],

[$Table].[iSO Week Number] as [iSO Week Number]

from [Dimension].[Date] as [$Table]

) AS [t3] on

(

[t6].[Delivery Date Key] = [t3].[Date]

)

)

 

GROUP BY [t3].[Fiscal Month Label]

)

AS [MainTable]

WHERE

(

 

NOT(

(

[a0] IS NULL

)

)

OR

NOT(

(

[a1] IS NULL

)

)

 

)

 

 

 

 

 

• Basically, this query joins the table Dimension.Date with Fact.Sale by Delivery Date Key grouping by Fiscal Month Label. In this query Azure SQL Database needs time depending on the data to group this information. Also, I saw that depending how the PowerBI model is (Inner Join or Left Join) could take more time.
  

 

 

 

 

 

 

 

 

• In this situation, I suggested to use an Indexed View that will materalize the data at the moment that every rows in inserted in the table. Of course, that if you are adding a huge amount of rows everytime, in terms of performance, in the similar way that we drop the indexes, the recomendation will be remove the indexed view, insert all this huge amount of data and recreate it again.
  

 

 

 

 

 

CREATE OR ALTER VIEW DameTotalFiscalMonthLabel

with schemabinding

as

SELECT [Fiscal Month Label] ,SUM([Total Including Tax]) AS [a0],SUM([Total Excluding Tax]) AS [a1], COUNT_BIG(*) AS Total

from [Fact].[sale]

inner join [Dimension].[Date] on [Fact].[sale].[Delivery Date Key] = [Dimension].[Date].[Date]

GROUP BY [Fiscal Month Label]

 

 

CREATE UNIQUE CLUSTERED INDEX DameTotalFiscalMonthLabel_X1 ON DameTotalFiscalMonthLabel([Fiscal Month Label])

 

CREATE INDEX FactSaleByInvoiceDay_X1

ON Fact.Sale

(

[Delivery Date Key]

)

 

 

 

 

 

 

 

• Running again the report of PowerBI, Azure SQL will use the indexed views because it covers all the data needed, improving a lot the results of the query in magnitude of seconds.
  

 

 

 

 

 

 

 

 

In another hand, in PowerBI you could use this view directly with the information ready for using.

 

 

 

Enjoy!

 

Continue reading...