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
Anonymous
Not applicable

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 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

1 ACCEPTED SOLUTION
AntrikshSharma
Community Champion
Community Champion

@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.

View solution in original post

3 REPLIES 3
AntrikshSharma
Community Champion
Community Champion

@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.

ImkeF
Super User
Super User

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

Anonymous
Not applicable

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)?

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.

Top Solution Authors
Top Kudoed Authors