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
andyclap
Helper II
Helper II

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

 

 

9 REPLIES 9
andyclap
Helper II
Helper II

Yep, nothing too complicated. We created an postgres database on azure, and then used stitch as a service to propagate the data. Stitch's limits mean it can be a little expensive for a medium size company, but we found it works well for the majority of data that can be replicated by timestamp+Id. For the data that isn't keyed and so can't be efficiently replicated we use a logic app with some azure functions for specific scenarios, but naturally that leads to complication so we try and keep it to a minimum.

 

Once you're using the postgres connector, or probably sql server or any of they other mainstream rdbms, the folding works great.

andyclap
Helper II
Helper II

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

amitchandak
Super User
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.

Any update on this issue?

Hi Jerr,

 

Unfortunately no good news here.

 

The behaviour of the ODBC handler in PowerBI is relied upon by lots of users, so there was no appetite to change the quirks in the behaviour just to accomodate NetSuite's ODBC driver.

Netsuite picked up the support ticket, and eventually we got an engineer who understood our explanation of the issue. Unfortunately they finally said that they didn't have the resources to fix, and couldn't prioritize as this was an edge case.

 

So it's "Will Not Fix".

 

We worked around it by building out a regular (postgres) database and replicating all our data to it. Makes reporting a lot easier than loading data directly, and allows us to add value by simplifying the unweildly suite analytics connect schemas, and improve data access control, so would recommend it.

 

As part of understanding the problem, I did create an ODBC driver wrapper that fixed the  handling of the incorrect metadata. But it needs to be deployed on the server, so that would be difficult to use on the PowerBI service.

 

Anyway, hope you manage to wrangle your data!

Andy

 

 

Hi Andy,

 

May I ask how you make a replicate of your Netsuite data instead of using ODBC?

 

 

Many thanks advanced.

 

 

Gr. Jerr,

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.