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

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 scenario, 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. 

In every analysis of performance with PowerBI, if you need to know how many rows we have per table use the following TSQL, 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

Define the report. 

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

PowerBI generates the following TSQL statement.

Using Azure Data Studio and SQL Server Profiler extension we could see the query.

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 view because it covers all the data needed, improving a lot the results of the query in magnitude of seconds.

Enjoy!