How to enable Query Folding for Native Query in Power BI

How enable to Query Folding for Native SQL Query in Power BI

Query folding refers to the ability of Power Query to push data transformation logic back to the data source, which can perform the transformations more efficiently. 

When query folding does not occur, all the data is pulled into Power BI and transformations are applied locally, which can be resource-intensive and lead to running out of resources, especially with large datasets.

For a DirectQuery or Dual storage mode table, the Power Query query must achieve the query folding. For an Import mode table, it may be possible to achieve query folding.

When a query is from a relational source and it is constructed with a single SELECT statement, we can achieve best data refresh performance by ensuring that query folding occurs.

If the Power Query mashup engine is still required to process transformations, you should strive to minimize the work it needs to do, especially for large datasets.

Scenario:

Suppose we are reading data from SQL Server Database into Power BI using the native query.

In this case, we can enable the Query Folding in Power BI, by using Value.NativeQuery function as shown in below Example:

Value.NativeQuery(db_Name, vSrcQuery , null, [EnableFolding=true])

The following Parameters and Variables are used in the following example::

Power BI Parameters:

ServerName = tpreddy-pc

DatasbaseName = db_analytics

Power Query Variable:

vSrcQuery = "Select * From [dbo].[vw_SalesSummary]"

1). Connect to the SQL Server:

Source = Sql.Databases(ServerName)

2). Connect to the Database (using the Power Query step):

Please note that, we should use the below Power Query step to connect to Database, instead of using the default Navigation method.

db_Name = Source{[Name=DatabaseName]}[Data],

3). Enable the Query Folding on Native Query (using the Power Query step):

QryFolding = Value.NativeQuery(db_Name, vSrcQuery , null, [EnableFolding=true])

After Loading the Data using the Native Query, perform the other required Operations, for which the Query folding will be applied.

The following is the complete Power Query of the dataset ds_SalesSummary :

let

vSrcQuery = "Select * From [dbo].[vw_SalesSummary]",

Source = Sql.Databases(ServerName),

db_Name = Source{[Name=DatabaseName]}[Data],

    

// Applying Query Folding to view Native Query

QryFolding = Value.NativeQuery(db_Name, vSrcQuery , null, [EnableFolding=true]),

RemoveColumns = Table.RemoveColumns(QryFolding,{"CntryNam", "Gross_Sales", "NetSales"}),

FilterRows = Table.SelectRows(RemoveColumns, each ([FY_Year] = "FY2015")),

MergeQueries = Table.NestedJoin(FilterRows, {"Row_Id"}, ds_SalesReference, {"Row_Id"}, "ds_SalesReference", JoinKind.Inner),

    

ExpandColumns = Table.ExpandTableColumn(MergeQueries, "ds_SalesReference", {"CntryNam", "Gross_Sales", "NetSales"}, {"CntryNam", "Gross_Sales", "NetSales"}),

GroupRows = Table.Group(ExpandColumns, {"QTR_Year", "ProdNam", "CntryNam"}, 

{{"Total Sales", each List.Sum([NetSales]), type nullable number}}),

    

ChangeType = Table.TransformColumnTypes(GroupRows,{{"QTR_Year", type text}, {"ProdNam", type text}, {"CntryNam", type text}, {"Total Sales", type number}})

in

    ChangeType

----------------------------------------------------------------

The Native Query version for above Power Query:

select [rows].[QTR_Year] as [QTR_Year],

    [rows].[ProdNam] as [ProdNam],

    [rows].[CntryNam] as [CntryNam],

    sum([rows].[NetSales]) as [Total Sales]

from 

(

    select [$Outer].[QTR_Year],

        [$Outer].[ProdNam],

        [$Inner].[CntryNam],

        [$Inner].[NetSales]

    from 

    (

        select [_].[Row_Id],

            [_].[FY_Year],

            [_].[QTR_Year],

            [_].[ProdNam]

        from 

        (

            select [Row_Id],

                [FY_Year],

                [QTR_Year],

                [ProdNam]

            from 

            (

                Select * From [dbo].[vw_SalesSummary]

            ) as [$Table]

        ) as [_]

        where [_].[FY_Year] = 'FY2015' and [_].[FY_Year] is not null

    ) as [$Outer]

    inner join 

    (

        select [_].[Row_Id] as [Row_Id2],

            [_].[FY_Year] as [FY_Year2],

            [_].[QTR_Year] as [QTR_Year2],

            [_].[CntryNam] as [CntryNam],

            [_].[ProdNam] as [ProdNam2],

            [_].[Gross_Sales] as [Gross_Sales],

            [_].[NetSales] as [NetSales]

        from 

        (

            Select * From [dbo].[vw_SalesSummary]

        ) as [_]

        where [_].[FY_Year] = 'FY2015' and [_].[FY_Year] is not null

    ) as [$Inner] on ([$Outer].[Row_Id] = [$Inner].[Row_Id2] or [$Outer].[Row_Id] is null and [$Inner].[Row_Id2] is null)

) as [rows]

group by [QTR_Year],

    [ProdNam],

    [CntryNam]

----------------------------------------------------------------

Notes:

To enable query folding, we can perform the Power Query operations (only if applicable) in the following order, if possible:

1) Remove Columns

2) Filter Rows

3) Merge Queries 

4) Expand Columns

5) Group Rows 

6) Change Data Type : This step will not support the Query Folding as per my understanding. Hence we can keep (skip this step for joining/reference table) this step at last.

Please note that, if you are using Merge/Joins, with other dataset (eg: ds_SalesReference), make sure that the query folding is enabled for the Joining table/reference table as well. Otherwise, it will break the query folding for the steps next to the Merge.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------