Lesson Learned #250: All started with the phrase: In PowerBI Direct Query is slow-ColumnStore Index

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 ColumnStore Index and Partitioning.

We are going to use two techonologies that will help a lot in our performance issues ColumnStore Index  is the standard for storing and querying large data warehousing fact tables. This index uses column-based data storage and query processing to achieve gains up to 10 times the query performance in your data warehouse over traditional row-oriented storage. You can also achieve gains up to 10 times the data compression over the uncompressed data size and Partitioning if we our queries are filtering by date. 

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, City and Stock Item the Total sales including and excluding Tax. Filtering by FY2013-Aug,FY2013-Feb and FY2013-Jan

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 [t1].[City] AS [c8],[t3].[Fiscal Month Label] AS [c43],[t5].[Stock Item] AS [c59],SUM([t7].[Total Including Tax])
 AS [a0],SUM([t7].[Total Excluding 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].[SaleColumnStoreIndex] as [$Table]
) AS [t7]

 INNER JOIN 

(
select [$Table].[City Key] as [City Key],
    [$Table].[WWI City ID] as [WWI City ID],
    [$Table].[City] as [City],
    [$Table].[State Province] as [State Province],
    [$Table].[Country] as [Country],
    [$Table].[Continent] as [Continent],
    [$Table].[Sales Territory] as [Sales Territory],
    [$Table].[Region] as [Region],
    [$Table].[Subregion] as [Subregion],
    convert(nvarchar(max), [$Table].[Location]) as [Location],
    [$Table].[Latest Recorded Population] as [Latest Recorded Population],
    [$Table].[Valid From] as [Valid From],
    [$Table].[Valid To] as [Valid To],
    [$Table].[Lineage Key] as [Lineage Key]
from [Dimension].[City] as [$Table]
) AS [t1] on 
(
[t7].[City Key] = [t1].[City Key]
)
)


 INNER JOIN 

(
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 
(
[t7].[Delivery Date Key] = [t3].[Date]
)
)


 INNER JOIN 

(
select [$Table].[Stock Item Key] as [Stock Item Key],
    [$Table].[WWI Stock Item ID] as [WWI Stock Item ID],
    [$Table].[Stock Item] as [Stock Item],
    [$Table].[Color] as [Color],
    [$Table].[Selling Package] as [Selling Package],
    [$Table].[Buying Package] as [Buying Package],
    [$Table].[Brand] as [Brand],
    [$Table].[Size] as [Size],
    [$Table].[Lead Time Days] as [Lead Time Days],
    [$Table].[Quantity Per Outer] as [Quantity Per Outer],
    [$Table].[Is Chiller Stock] as [Is Chiller Stock],
    [$Table].[Barcode] as [Barcode],
    [$Table].[Tax Rate] as [Tax Rate],
    [$Table].[Unit Price] as [Unit Price],
    [$Table].[Recommended Retail Price] as [Recommended Retail Price],
    [$Table].[Typical Weight Per Unit] as [Typical Weight Per Unit],
    [$Table].[Photo] as [Photo],
    [$Table].[Valid From] as [Valid From],
    [$Table].[Valid To] as [Valid To],
    [$Table].[Lineage Key] as [Lineage Key]
from [Dimension].[Stock Item] as [$Table]
) AS [t5] on 
(
[t7].[Stock Item Key] = [t5].[Stock Item Key]
)
)

WHERE 
(
([t3].[Fiscal Month Label] IN (N'FY2013-Aug',N'FY2013-Feb',N'FY2013-Jan'))
)

GROUP BY [t1].[City],[t3].[Fiscal Month Label],[t5].[Stock Item]
)
 AS [MainTable]
WHERE 
(

NOT(
(
[a0] IS NULL 
)
)
 OR 
NOT(
(
[a1] IS NULL 
)
)

)

Re-define the table to use columnstore index and partitioning.

I created a new table called Fact.SaleColumnStoreIndex using columnstore index and partitioning. Once I have this table I inserted all the rows from the table Fact.Sale to see the impact and compare the results.

CREATE TABLE [Fact].[SaleColumnStoreIndex](
	[Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Bill To Customer Key] [int] NOT NULL,
	[Stock Item Key] [int] NOT NULL,
	[Invoice Date Key] [date] NOT NULL,
	[Delivery Date Key] [date] NULL,
	[Salesperson Key] [int] NOT NULL,
	[WWI Invoice ID] [int] NOT NULL,
	[Description] [nvarchar](100) NOT NULL,
	[Package] [nvarchar](50) NOT NULL,
	[Quantity] [int] NOT NULL,
	[Unit Price] [decimal](18, 2) NOT NULL,
	[Tax Rate] [decimal](18, 3) NOT NULL,
	[Total Excluding Tax] [decimal](18, 2) NOT NULL,
	[Tax Amount] [decimal](18, 2) NOT NULL,
	[Profit] [decimal](18, 2) NOT NULL,
	[Total Including Tax] [decimal](18, 2) NOT NULL,
	[Total Dry Items] [int] NOT NULL,
	[Total Chiller Items] [int] NOT NULL,
	[Lineage Key] [int] NOT NULL,
	 index [SaleColumnStoreIndex_CC] CLUSTERED COLUMNSTORE
) ON myPartitionScheme([Delivery Date Key])

GO

CREATE PARTITION FUNCTION myDateRangePF (date)
AS RANGE RIGHT FOR VALUES ('2013-01-01','2014-01-01','2015-01-01','2016-01-01')
GO

CREATE PARTITION SCHEME myPartitionScheme 
AS PARTITION myDateRangePF ALL TO ([PRIMARY]) 
GO

INSERT INTO [Fact].[SaleColumnStoreIndex]
           ([City Key]
           ,[Customer Key]
           ,[Bill To Customer Key]
           ,[Stock Item Key]
           ,[Invoice Date Key]
           ,[Delivery Date Key]
           ,[Salesperson Key]
           ,[WWI Invoice ID]
           ,[Description]
           ,[Package]
           ,[Quantity]
           ,[Unit Price]
           ,[Tax Rate]
           ,[Total Excluding Tax]
           ,[Tax Amount]
           ,[Profit]
           ,[Total Including Tax]
           ,[Total Dry Items]
           ,[Total Chiller Items]
           ,[Lineage Key])
     SELECT 
           [City Key]
           ,[Customer Key]
           ,[Bill To Customer Key]
           ,[Stock Item Key]
           ,[Invoice Date Key]
           ,[Delivery Date Key]
           ,[Salesperson Key]
           ,[WWI Invoice ID]
           ,[Description]
           ,[Package]
           ,[Quantity]
           ,[Unit Price]
           ,[Tax Rate]
           ,[Total Excluding Tax]
           ,[Tax Amount]
           ,[Profit]
           ,[Total Including Tax]
           ,[Total Dry Items]
           ,[Total Chiller Items]
           ,[Lineage Key]
		   FROM [FACT].[Sale]
GO

CREATE INDEX SaleColumnStoreIndex_City
ON Fact.SaleColumnStoreIndex
(
	[City Key]
)

CREATE INDEX SaleColumnStoreIndex_Date
ON Fact.SaleColumnStoreIndex
(
	[Delivery Date Key]
)

CREATE INDEX SaleColumnStoreIndex_Stock_Item_key
ON Fact.SaleColumnStoreIndex
(
	[Stock Item Key]
)

CREATE INDEX SaleColumnStoreIndex_Customer_key
ON Fact.SaleColumnStoreIndex
(
	[Customer Key]
)

CREATE INDEX SaleColumnStoreIndex_SalesPerson_Key
ON Fact.SaleColumnStoreIndex
(
	[SalesPerson Key]
)

Besides improving the performance and reducing the time taken in the report we could see how we reduce the total size of the table:

Enjoy!