Query folding refers to the ability of Power Query to translate the operations/transformations into source queries and sent back them to execute at the Source. This will help to 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 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])
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],
*/
*/
Source = Sql.Database(ServerName, DatabaseName)
/* Applying Query Folding to view Native Query */
QryFolding = Value.NativeQuery(Source, 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) Add Index
6) Add Index
7) Change Data Type:
Here are the list of operations and their impact on Query Folding:
Disclaimer:
Query folding behavior can vary depending on the data source and connector. The flags shown here are general guidelines, not absolute rules. Always verify folding in your own environment using View Native Query in Power Query.
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. If you wish, please share your genuine Feedback or comments only related to this Blog Posts. It is my humble request that, please do not post any Spam comments or Advertising kind of comments, which will be Ignored.