Connecting a source system to a planning, budgeting and forecasting system is rarely straightforward, and usually follows a similar pattern of:
1) Hope and optimism for a beautiful direct integration
2) Your dreams being brought down to earth during design-time
3) Getting the Enterprise Resource Planning (ERP) team to export an extract regularly
4) Hoping the extract worked and scooping it up using a batch script to load
Therefore, when an opportunity arises to ditch the shackles of extract-based integrations and migrate entirely through the Cloud, one must grasp that opportunity with both hands. And that’s what a fabulous joint effort by the team at Brovanture has managed to achieve.
Why should you read this lengthy blog? Cloud-to-Cloud is cool, but it’s especially cool when you combine infinitely customisable source queries in NetSuite ERP with the exceptional mapping capabilities of Oracle Planning and Budgeting Cloud Service (PBCS) Data Management. You really can integrate anything between NetSuite ERP and Oracle Planning and Budgeting Cloud Service (PBCS), enabling you to migrate metadata and data easily, quickly and regularly for a near-seamless system. It really is that good.
This blog is going to walk you through how it works, and how you can set it up for yourself.
- The first step, of course, is to own the leading Cloud ERP and Cloud EPM tools, NetSuite ERP and Oracle PBCS. If you don’t own these tools, why are you reading about integrating them? Contact Brovanture for a quote by contacting us on our website here!
- NetSuite ERP comes with a fabulous PBCS integration add-in. Ask your NetSuite rep to enable your Oracle NetSuite-PBCS Sync add-in. You may need to purchase this depending on your individual circumstances.
- If you have any other add-ins configured for NetSuite you may be able to skip this step, but you will need to go to the SuiteCloud tab and enable the features: Custom Records, Server SuiteScript, Token-Based Authentication, Client SuiteScript. Again, ask your NetSuite vendor for assistance if needed.
- Once you’ve got the bundle enabled, go to Customisation in NetSuite and Install it.
- Setup the Oracle PBCS File Encryption password in NetSuite, noting the requirement to be 16,24 or 32 characters. Select Setup -> Integration -> PBCS File Encryption Password
- Create a NetSuite user with the Oracle PBCS Integration role which will be automatically available when installing the bundle. Give your user the same password as the NS-PBCS file encryption password. We named our user firstname.lastname@example.org for simplicity.
- The integrations are powered using the incredible Saved Searches functionality in NetSuite. This powerful functionality allows you to create extremely customisable queries and save them to be run by other users. There are three sample searches created to help you get started which you can use if you like:
However, you can also create any saved search you like, and provided the ID starts with: customsearch_nspbcs then it will be automatically pulled through to PBCS Data Management later in the process! This means you can connect literally any customisation of NetSuite to PBCS using entirely bespoke queries. I’ll be going further into detail in this in future blogs showing how we used this powerful technique to create specialised metadata extracts and data extracts and support the PBCS Data Management process.
- You can use either token-based authentication or user based authentication to connect the source system. In this tutorial, we’ll be using user-based authentication and using the user we set up in step 6. When the integration is triggered, in the background the saved search will be run using this user account’s access in NetSuite, so bear that in mind and ensure it has the correct level of access for your requirements.
- We’ve finally made it to PBCS and it only took 9 steps! Load up your PBCS instance with an administrator account and open Data Management. In Data Management, go to Setup -> Source System and select Add.
- Select NetSuite under Source System Type and give it a name. You can also set up the basis for a full drill-through URL here but we’ll leave that one for a future blog…
- Once you’ve saved your Source System, select Configure Source Connection. Here you can select Token Based if you prefer but in this tutorial we will select User Based. Enter:
a. Username and password from step 6
b. NetSuite Account Number (usually 7 digits)
c. Data Centre – can be found in your NetSuite URL: https://system.eu2.netsuite.com/
Once you’ve entered these hit Test Connection and you should get a confirmation message.
- Initialise your source system and you’ll be prompted for a prefix. This can be whatever you want and will help group your saved searches together. Hit OK.
- Navigate to Target Application and you will see that all your defined saved searches have come through as individual applications ready to be connected to import formats! You can see all the fields below but don’t worry too much about them.
- Create an Import Format associated with your PBCS cube and your chosen Saved Search. Navigate to Import Format and select Add, and you’ll be prompted to select a source and a target. Your PBCS cube of choice should be the target, and your chosen saved search should be the source. Once you’ve saved, the mappings screen will become available. It could not be simpler to map your NetSuite fields to your dimensions, just use the drop-down box to match up the fields.
I would highly recommend setting up the NetSuite saved search with a separate Month and Year field to avoid Data Managment’s period mappings.
- We’re on the home straight! Create a Location associated with your import format and then navigate to that Location and create a Data Load Rule.
At this point you can create mappings, and any time you run your data load rule it will request the data directly from NetSuite. A few fish later and you’ve got drillable NetSuite Actuals ready to inform your Forecast.This can be automated easily using EPM Automate, but with no extract nonsense.
So that is how you connect NetSuite ERP to Oracle PBCS seamlessly through the Cloud. In future blogs I’ll be walking through a worked example for metadata specifically, since PBCS Data Management is just as powerful when handling metadata.
We’re using this to track our hours, maintain our targets and calculate commission, but what will you use it for? Find out more by contacting us on our website here.
Thanks to Guillaume Slee, whose blog you should already be subscribed to, for putting the framework together for this to be achieved, as well as the rest of the team at Brovanture for contributing to this development.
Until Next Time,