cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Netsuite ODBC filter not folding

Hi

 

I'm using Power BI to report on a NetSuite database using ODBC.

I'm filtering on a table with a few million rows to select a few hundred for a report.

It seems that the row filter is not being folded and the ODBC driver is being asked for all rows, which are then filtered locally. The "View Underlying Query" is greyed out on the filter step. This is understandably slow.

 

I can reproduce with a simple bit of M:

let
Source = Odbc.DataSource("dsn=NetSuite", [HierarchicalNavigation=true]),
#"Our_Database" = Source{[Name="Our Database",Kind="Database"]}[Data],
Administrator_Schema = #"Our_Database"{[Name="Administrator",Kind="Schema"]}[Data],
PRODUCT_TYPE_Table = Administrator_Schema{[Name="PRODUCT_TYPE",Kind="Table"]}[Data],
#"Filtered Rows" = Table.SelectRows(PRODUCT_TYPE_Table, each [IS_RECORD_INACTIVE] = "F")
in
#"Filtered Rows"

Here the #"Filtered Rows" step has "View Underlying Query" greyed out.

 

Note - column restrictions and joins are getting folded correctly, and I can see the underlying queries are optimised.

 

I don't want to revert to using an SQL query as this is a user-maintained reporting suite.

 

If I have to report a problem with the NetSuite driver, what would be a good way to report this technically - e.g. what particular thing is their ODBC driver doing incorrectly that makes Power BI beleive that folding even simple predicates is not supported?

 

Any assistance appreciated,

Andy

 

 

4 REPLIES 4
Highlighted
Super User IV
Super User IV

@andyclap , this no same. But see if, the steps given in this topic can help

https://community.powerbi.com/t5/Desktop/Dataflows-don-t-support-Query-Folding/m-p/673500



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

Thanks Amit,

 

however as you suggested, this isn't quite the same.

I am attempting to follow the advice given around query folding (as per the links in the related question), and in theory, predicates should fold correctly improving query performance as this is a relational database source. Joins fold correctly indicating relational database folding is active. However filters are not folding, even in the simplest example which suggests an underlying problem.

 

 

 

 

Highlighted

I've just tested this against another NetSuite ODBC driver (CDATA), and then ODBC to a local MySQL installation.

It seems ODBC doesn't fold filters for any of these either - this is bad!

 

Is this broken? Or is ODBC just not supported for query folding of predicates?

This is really peculiar, as the one source that can handle folding demands most efficiently is a relational database!

 

 

 

Highlighted

Note - I've raised a support ticket for this.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors