Saved searches are an intrinsic part of using NetSuite. Many people use them as a way to run basic queries on their data. Others are able to perform complex analysis using formulas, then integrate the results into KPI scorecards and workflows. We’ve also found that, prior to finding iCharts, many people run saved searches to export their data into Excel for cumbersome, manual reporting.

Many people wonder why a particular saved search doesn’t perform as fast as they expect. Perhaps it seems to run quickly within NetSuite, but not within a BI platform. Perhaps a seemingly simple change to the criteria or results has somehow made it slower. Or maybe a search that ran recently with no problems is now grinding to a halt. Factors like the nature and size of the data set, along with formula syntax and end goals, can unintentionally make saved searches slow and cumbersome.

Unfortunately, there is no one single answer that will cure all of your NetSuite saved search woes. The good news is you can get back to speedy saved search bliss with the help of several different solutions. In this series, we’re going to explore the different issues to look out for when experiencing slow performance with saved searches and BI.

Fundamentals of Basic BI Reporting

The first thing to understand about NetSuite’s saved searches is that they are very much like running an SQL query on a database. In fact, as NetSuite runs Oracle as its database backend, several of the capabilities and formulas that you can take advantage of actually seem to go down to the Oracle database layer. This means that there is a lot of power under the covers of NetSuite saved searches. You can access pretty much the full breadth of Oracle SQL functions like CASE/WHEN, EXTRACT, TRUNC, TO_CHAR, SIN, COS, TAN, etc. It also means that sometimes, if you’re unable to get help with a formula or issue in one of the many NetSuite forums, you can look towards the Oracle (and greater SQL) communities for help. Finally, it also means that many of the fundamental relationships and processes between SQL databases and BI also apply to NetSuite saved searches.

When you run a saved search for the purposes of BI, there are several co-related steps that take place to provide you with your results. You can combine most of these with each other in many different ways to produce more powerful and insightful results.

  • Calculations: These can be your basic mathematical formulas like VALUE * VOLUME but can also get as complex as any mathematical expression can. These are most often combined with aggregation and analysis, but more often than not people will experience accuracy issues as a result of an incorrect formula, rather than performance issues.
  • Transformations: When you use a formula to extract the month from a date, perform live categorization of data, combine text from two different fields, or change text in one way or another, that’s a transformation. Most often, this is combined with segmentation to do things like show trends over broader periods of time. When done incorrectly though, they can lead to major performance issues. We’ll be covering some of these issues in an entire article, but in the meantime, look for Stephen Peake’s article on BI Readiness. (https://icharts.net/blog/data-visualization-guides/bi-readiness-preparing-your-data-for-visualization)
  • Filtering: In SQL parlance, this is your “WHERE” clause. In NetSuite’s search UI, it is your criteria tab. Basic use of filtering is just looking for specific values in fields, but you can also use formulas to narrow your results. This means that the issues you have with calculations and transformations can also affect your filters and slow down your search. NetSuite also has the additional capability of “Summary Criteria” which can filter on the results of an aggregation, again including calculations and transformations.
  • Segmentation: In SQL, segmentation is known as “GROUP BY.” In NetSuite’s search UI, this will show up in the “Results” tab under the “Summary” column as “GROUP.” When you want to combine all the results of a particular field into a single row, this is where you do it. As mentioned, this is also one of the primary places to look for performance issues.
  • Aggregation: These are used in relation to Segmentation, and are actually located in the same place (the “Summary” column in the “Results” tab). This is what you use to get a SUM, MIN, MAX, AVERAGE, or COUNT of a particular field, calculation, or transformation. Just like any calculation though, they can get incredibly complex. If they also include some kind of transformation, they can result in a slower saved search.
  • Joins: NetSuite’s join capabilities are, of course, not going to be as rich as a standard SQL database. At best, they are similar to a “LEFT JOIN,” with a lookup table using a primary key. But this does not mean they are not powerful. In fact, pretty much every standard NetSuite record type has multiple join columns that enable you to get information on a record’s related customer, item, transactions, etc. Joined fields are accessible through the search UI, just as any other fields are, but at the bottom of the list of fields. Just like any JOIN in SQL though, they can suffer the same primary issues. The first would be a multiplying factor, where every row in the primary record will return multiple results based on the number of results that are matched in the join. The second is performance, often caused by the first issue, but can also be caused by the number of joined columns being used, and if there are any transformation and segmentation being used on them. We’ll go into these issues in an article to come.
  • Analysis: Also known as “Analytical Functions” in Oracle’s documentation, but also known as “Windowing Functions” in general SQL terminology, these functions are very closely related to aggregations, but they are performed on the result set of a query. They can be incredibly powerful and can produce results like cumulative totals, moving averages, and even linear regressions. Unfortunately, the use of them is not very well documented in NetSuite. This also means you may not run into them very often, and if you do experience a performance issue with them, typically it is because of a calculation or transformation being performed within the function.

BI Reporting Fundamentals in Action

As a basic example, imagine you have a record type that contains sales transactions and looks like the following:

Date Sales Rep Customer Item Price per Item Number of Items in Order
January 5, 2017 Jane Apex Sprockets $5.00 5,000
January 15, 2017 Jim Apogee Gears $3.00 7,000
January 18, 2017 Steve Epitome Cogs $10.00 3,000
February 3, 2017 Sally Pinnacle Pinion $7.00 4,000

Let’s say you wanted to get a monthly view on the total amount of sales. You would use a combination of transformation and segmentation with a combination of calculations and aggregation. The resulting saved search would look like the following:

  • GROUP (segmentation) on Formula Date (transformation) of TRUNC({trandate}, ‘MONTH’)
  • SUM (aggregation) on Formula Numeric (calculation) of {value} * {volume}

This is exactly what iCharts does automatically when taking a saved search and turning it into a gorgeous visualization that helps you make decisions that drive your business. We take the fields that you’ve chosen from the saved search using our easy drag-and-drop interface and we generate a query that runs directly back on NetSuite via their API. This means that people can also spend time troubleshooting directly on NetSuite using the saved search interface and pinpoint the true cause of the slow performance.

Now, while the above is a very simple example, there are a few steps we can take to troubleshoot slow performance. First and foremost, how big is the data set being queried? Are there thousands, millions, or even billions of rows? You can find this out by removing the “Summary Type” within the saved search, running it, and then looking on the top right to see the total number of results. If there are a lot of results, the quickest thing to try is to add filter criteria to remove any results that are not necessary for your report. Perhaps you only need to see data from 2017 and onward. Don’t query more data than you need.

In the same vein, something else to look at is the number of columns that are in the results tab. Even if you don’t use those columns within the summary types, they still might affect the performance of the saved search. Finally, the formulas for transformation and calculation in this example are really basic, probably more basic than you would typically use. If you have a formula with multiple lines in it, with multiple CASE/WHEN statements and date transformations, you are likely going to have a problem. NetSuite’s character limit for formulas is 4,000 characters, but more often than not, you should not be using the full 4,000 characters for a formula.

With these basic fundamentals in place, you’re well on your way back to saved search performance bliss and faster BI reporting. We’ll be following up shortly with some detailed examples that we’ve seen in the field as well as how to get additional help directly from NetSuite, where they can optimize the application and database layers directly.

Stay tuned!

For more helpful tips for NetSuite and iCharts, head to our resources page.