Joining your NetSuite data in PowerBI via Tactical Connect

NetSuite saved searches, while seemingly powerful, come with their limitations. One of the biggest limitation we run across is the ability to easily join multiple objects/tables in one report or visualization. Or as PowerBI calls it, managing relationships. The beauty of using Tactical Connect with PowerBI (other BI tools do this as well) is that we can easily join multiple objects/tables from NetSuite (and/or other data sources) resulting in the ability to produce a single report/dashboard with a 360 view of our operations. A few key steps to follow:

1. Creating a saved search: Create each saved search by object type (i.e. customer, items, sales order). Do not try to join multiple objects in a single saved search. Let your BI tool manage the joining process. Not only is your BI tool a better place to manage joins, but creating saved searches that span across multiple objects in NetSuite will slow down NetSuite system performance.

2. Internal IDs:  Internal ID's are essentially database keys that do not change. In each of your saved searches, make sure to include internal IDs - because we will use these as our field to join on.

3. Creating relationships in PowerBI: When you create relationships in PowerBI, one of your columns must have unique values. For example that means if you want to join customers and sales orders - your sales orders must have your customer internal ID and can't have more customer internal ID's than your customer search. Ensure you have all your customers in your search or the subset is filtered the same way the sales order search is filtered. Additionally, if your customer search shows all your contacts, you will have duplicate customer values - so you'll need to remove duplicates.

4. Client example: We had a client who wanted to better manage their purchasing process. For that, they needed a single report that showed sales (object: sales orders), item details/inventory (object: items) and purchasing (object: purchase orders). They had to create two saved searches in NetSuite, push them to excel, try to combine them and then make business decisions only when they felt comfortable the two reports were consolidated correctly. However, by then, the data was stale. Using Tactical Connect with PowerBI, they were able to create a single report that automatically updated for them to make more real-time purchasing decisions.

Below is an example of what three NetSuite saved searches all joined together looks like in PowerBI: