Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 MyTickets
Apart 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
Solved! Go to Solution.
@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
FilterRows
But 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.
@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
FilterRows
But 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.
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
Fair point. I did think that was going to be the answer. I think I was looking for the easy answer out.
By the way, when you say "profiler" are you suggesting the profiler in SSMS (or Redgate)?