Netsuite to Planning Cloud - Part 3: Setting up Data Management

Welcome to Part 3 of this 5-part blog where I share my findings and tips on how to configure the integration between Netsuite and PBCS using the in-built adapter.

      Part 1 - User based vs Token Based Authentication       

      Part 2 - Tips on setting up your Saved Search in Netsuite

      **Part 3 - Tips on setting up Data Management in Planning Cloud**

      Part 4 - How to set up Drill through from Planning Cloud to Netsuite

      Part 5 - How to give your end users access to run the integrations

   Please note: This blog post assumes you have completed the Netsuite set up and configured your Source Connection to your Netsuite environment in the Source System section in Planning Cloud. Please see Part 1 where I share Mike’s blogs if you need more information on how to set this up.

Setting up Data Management in Planning Cloud

Bringing in your saved searches to Planning Cloud

Now your Saved Search is correctly set up in Netsuite, you need it to be available in Data Management in Planning Cloud so that you can use it as a source for the data load.

You can set up multiple Netsuite environments e.g. below we have Production, Sandbox 1 and Sandbox 2.

To pull the Saved Searches into Planning, click on Initialize and enter a prefix to tag onto the front of all of saved searches – this is an important step if you will be connecting to multiple Netsuite environments as it allows you to identify which saved searches came from which Netsuite environment. E.g. I enter the prefix PRD- when initialising Netsuite Production.

The Saved Searches will then appear in the Target Application section. You can track the progress of the initialise process by going to Workflow tab > Process Details

Filters

In Target Application > Application Filters – you will see the options that were set as the Criteria on your saved search.

I recommend leaving these as they are as you will also be able to set them in the Data Load Rule later.

Note the following:

•         If you add any Criteria to the Saved Search, you must reinitialise the connection as above.
•         If you remove any Criteria from the Saved Search, these may not be removed by reinitialising in which case you should remove them manually here. This is what we had to do for the default Mainline filter

To manually remove a filter, click on Edit and delete the filter.

To complete the setup, go ahead and create

•         Import format (Source: saved search; Target: Planning App)
•         Location
•         Period mappings (more on this next)
•         Category mappings 

Period Mappings

Since it is the Netsuite Period number which identifies the period to load, we need to set up the period mappings so that PBCS knows which month matches the number.

This can be achieved through creating Source Mappings.

Create the Source mappings:

1.          Click Add – this shows the list of periods from Global mappings
2.          Choose the Period Name – e.g. Jan-18
3.          Give the Calendar a name – e.g. Standard Fiscal
4.          Enter the GL Year – this should match the year column in the saved search
5.          Enter the GL Period – this should match the period column in the saved search
6.          Enter the GL Period Number – this should match the internal ID of the period in Netsuite and will also be used in the filter in the Data Load rule (more on this next)

Data Load rule filters

Once you have set up your rule, you will see the same filters again here, this is where you can specify the Period ID that you wish to load.

In my opinion, this is a bit of a pain point of the integration. Although I believe you can use ‘This Period’ option – this is dictated by the Netsuite current period and may not be the same period you need to be loading. So the administrator must manually update this period ID each month.

In addition, there is no way to set the filter through EPM Automate so it really is a manual job to update it when the Actual month is rolled forward.

Mapping blanks to default members in PBCS

It can be the case that Netsuite segments which are dimensions in Planning Cloud are not mandatory; if this is the case they will come through as blank. Clearly this data still needs loading into Planning Cloud so we need to map blank rows to default members in PBCS. To achieve this, use a Like mapping where the source is * and target value is an #SQL statement.

That's all for Part 3, stay tuned for Part 4 - How to set up Drill through from Planning Cloud to Netsuite.

Thanks for reading :) 

Ciao for now.