cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
danielbyerley Frequent Visitor
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
Super User
Super User

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

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)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




danielbyerley Frequent Visitor
Frequent Visitor

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

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?

Super User
Super User

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

try it without the #(lf)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




danielbyerley Frequent Visitor
Frequent Visitor

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

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

Super User
Super User

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

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 & "'"]),

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 239 members 3,031 guests
Please welcome our newest community members: