Forum Discussion
Is a Custom SQL Query considered as non-folded?
Hi,
I am playing with Incremental refreshes on a "Ticket Table", Unfortunately, the field that I am using to flag new/changed data is a "date closed" field, which is nullable.
What I have done in this instance is create a custom SQL Query, which I am using in the datasource. The query is similar to this:
SELECT *,ISNULL(TicketCloseDate,GETDATE()) AS IncrementalDateFeed
FROM MyTicketsApart for the RangeStart and RangeEnd filtering, there are no more transformations, but, as expected, this is showing that this query could not be determined as foldable.
While it works, I have not done enough testing to determine whether it is actually gaining me any performance with my refreshes.
Is this a sound approach?
Or, would I be better advised to create and call views on the source database?
Thanks
Anonymous If you use SQL code for importing data then Query Folding breaks, however what you can do is first create a connection to the database in PQ and then add a new step that will use the function Value.NativeQuery
let Source = Sql.Databases ( "autumn\sql2019" ), ContosoRetailDW = Source{[ Name = "ContosoRetailDW" ]}[Data], //Query is now folded EnableFolding = Value.NativeQuery ( ContosoRetailDW, "SELECT * FROM DimProduct", null, [ EnableFolding = true ] ), FilterRows = Table.SelectRows ( EnableFolding, each [ProductKey] <> 3 ) in FilterRowsBut I would suggest you create a View so that you can add an additional layer between tables and PQ, makes it easy to govern and DBA would know if a View depends on a table and will modify accordingly.
3 Replies
Anonymous If you use SQL code for importing data then Query Folding breaks, however what you can do is first create a connection to the database in PQ and then add a new step that will use the function Value.NativeQuery
let Source = Sql.Databases ( "autumn\sql2019" ), ContosoRetailDW = Source{[ Name = "ContosoRetailDW" ]}[Data], //Query is now folded EnableFolding = Value.NativeQuery ( ContosoRetailDW, "SELECT * FROM DimProduct", null, [ EnableFolding = true ] ), FilterRows = Table.SelectRows ( EnableFolding, each [ProductKey] <> 3 ) in FilterRowsBut I would suggest you create a View so that you can add an additional layer between tables and PQ, makes it easy to govern and DBA would know if a View depends on a table and will modify accordingly.
Hi Anonymous ,
I'd recommend to use Profiler to check what is actually going on, as the folding indicators in PQ itself are not reliable.
Usually, once you've used custom code in your initial SQL call, this command itself will fold, but nothing afterwards. So your incremental refresh parameters wouldn't. So using a view looks like the way to go here.
