Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danielbyerley
Frequent Visitor

Should I use a parameter in my Import Query (and forfeit query folding)?

Hello, 

 

I am working on restructing my queries and stumbled upon the ability to customize a query based on a user input parameter. While extremely useful.. this forces the query to forfeit any query folding capabilities. While my parameter filters down the dataset (and can universally be used to filter all of the data sets I am bringing in), I have a feeling that it slows down the performance as all of the transformations on the data set are all on my machine (vs. at the server). 

 

The parameter is very helpful because it limits my data, and I can easily switch my target data vs. bringing in all of the data (millions of rows) and filtering it at the report level, or creating X different pbix files for each of the districts of the company. 

 

So my question is.. as a rule of thumb, would it be better to 1. place my parameter filter in the query (assume to lose all query folding capabilties because no native query is visible) and complete all transformations at the client level? 2. Load the full data set and place my parameter filter within the transformations? (would lose some query folding capability) Or 3. Load the full data set and just use a slicer at the report level? 

 

Also as a side note... is there any combination of reference query and/or create function (using parameters) to improve performance?

5 REPLIES 5
ImkeF
Super User
Super User

In most of the cases, using a parameter will not stop the query to fold.

Have you tried it? Pls give more details (datasourse, your code so far)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

I had misspoken in my post. My source code with a parameter filter causes the ability to view the native query to disappear. My general code is below with my parameter in bold. 

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

let
Source = Sql.Database("STLV-SQLPROD01", "bss warehouse", [Query="SELECT tWarehousePart.MajorBranchID, tMajorBranch.MajorBranchName, tWarehousePart.WarehouseID, #(lf)FROM ""bss warehouse"".dbo.tWarehousePart LEFT JOIN tMajorBranch ON tWarehousePart.MajorBranchID = tMajorBranch.MajorBranchID#(lf) WHERE (tMajorBranch.MajorBranchName = '" & MajorBranchName & "')"]),

#"Filtered Rows" = Table.SelectRows(Source, each [Level1Cost] > 10)

in
#"Filtered Rows"

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

I know that the source doesnt support query folding when using this parameter because 1. "Permission is required to run this native database query" 2. I should be able to view the native query statement of the applied step #"Filtered Rows" (a transformation commonly performed in query folding) yet cannot as it is greyed out. 

 

This tells me that the parameter used in my source query prevents query folding of my transformations to fail?

try it without the #(lf)

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Removing the #(If) does not appear to do anything

I know that in SSAS query folding on native code is pretty picky with syntax. So maybe a bit of cleanup here could also help:

 

Sql.Database("STLV-SQLPROD01", "bss warehouse", [Query="select [_].MajorBranchID, [_].MajorBranchName, [_].WarehouseID, from [dbo].tWarehousePart left join [dbo].tMajorBranch on tWarehousePart.MajorBranchID = tMajorBranch.MajorBranchID where tMajorBranch.MajorBranchName = '" & MajorBranchName & "'"]),

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.