cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

 

 

5 REPLIES 5
Helper I
Helper I

Just to follow up for public knowledge -

 

The netsuite ODBC driver reports incorrect data type names for column schema info, which means power BI cannot assume a match with the general type info (it looks up by type-name, not id), and therefore has to assume that all columns are not searchable.

 

This leads to all rows being retrieved before filters can be applied which isn't acceptable.

 

Netsuite have no appetite to support this, and have refused to fix.

 

As part of the investigation I created an ODBC driver wrapper based on the snowflake sample, that replaces the column info data types with matching names. This works reasonably.

 

However given the general speed of netsuite SuiteAnalytics connect, there is no way that it can be used as a reporting source for any significant volume of data.

 

I am now investigating building a data warehouse on a cloud database using Stitch to synch data, removing reporting dependencies on NetSuite (and removing dependencies on power BI to maintain the reporting data store).

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!

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.

 

 

 

 

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!

 

 

 

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

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors