Preparing for Direct Connection to OneStream

One of the most common if not the most common requests when setting up OneStream is to have a direct connection from an ERP such as Oracle, Peoplesoft, and/ or Netsuite to OneStream to allow for data to be automatically uploaded on a regular basis.  Below is a link from one of my fellow Perficient consultants that describes the steps and how to setup a Direct connection to OneStream:

Oracle EBS Direct Connect Configuration in OneStream / Blogs / Perficient

This blog is going to focus on the steps that can be done before setting up the direct connection to OneStream and should be done before creating the connector business rule.  This blog is also primarily focused on an ODBC/ SQL connection.  If you follow these steps the direct connection would be setup in the shortest time possible with the lowest possible consultant hours/ dollars spent:

1. Create a query from your ERP using Microsoft SQL server or another program to get a copy of the data that would go into OneStream.
2. Check that the data ties to the numbers you are expecting in the ERP.
3. Set up the direct connection to OneStream
4. Create and run a SQL data adapter in OneStream and tie the data to the ERP.

Steps 1 and 2 do not require a OneStream consultant and should be done by someone who is an expert with that ERP.  Once these steps are done, you are ready to setup the connection to OneStream with a connector business rule, transformation rules, workflow and load the data (see steps 4 to 8 of the attached blog).

Create a query from ERP

If the ERP database supports SQL, then the query would start with a SELECT statement that picks the fields needed and possibly some JOIN statements FROM a table or multiple tables with a WHERE clause.

For example, if all of the data is in 1 table the query might look like this:

SELECT

Entity,

Account,

Period,

Department,

Project,

Amount

FROM

Gl_table

WHERE

Period = ‘Jan 2023’

The results of a query like this may produce a table similar to this:

Entity         Account      Period          Department        Project    Amount

111                10000         Jan 2023       200                     155          1,000.00

111                 30000        Jan 2023       200                      NA         -1,000.00

Revolutionize Your Business With Generative AI

From product design and software development to virtual agents, content creation, and reporting, GenAI is transforming business. Our AI experts help you unlock GenAI’s full potential and drive growth.

Let’s Get Started

Most likely your ERP has multiple tables.  In that case tables would have to be joined.  The queries that I have seen had multiple Inner, outer and Left Joins.  That is why it is so important to have someone that knows your ERP’s tables create the query needed.

Have someone create the necessary query from your ERP using Microsoft SQL server or some other program that is available to query the ERP.  This should be done by either someone in your IT department who is very familiar with the tables in your ERP  or a consultant who knows the tables in your ERP.  Someone in your IT department is not only the least expensive option but is also often the best qualified to create the query.

Check data

If the data is a small enough file have it sent as a comma delimited file and compare to the trial balance or reports currently being used in your ERP.   The data needs to tie exactly to your reports.  That way you can be sure that the data that will be going to OneStream is correct.  This way if there is a difference between OneStream and your ERP then it is not the query but something with the OneStream setup.  If you skip this step, then how can you be sure if the differences you have are the query or the OneStream setup?

Set up Direct Connection

There are several ways to setup the direct connection:

1. Named connection: If your OneStream application is on-premise your setup would be similar to the one in the blog referenced above.  If OneStream is in the cloud, then  OneStream support would have to setup the connection since they are the ones that have access to the server.  Send the driver for your ERP and setup information to OneStream support.
2. Smart Integration Connector (SIC): (available only for version 7.3 and higher) This is only available when software from OneStream is installed on a separate virtual machine of your own to communicate and transfer data between your ERP and OneStream.  The advantage  of the Smart Integration connector is that you control the login and password for the connection to OneStream.
3. REST API connection: Representational State Transfer (REST) API is the preferred connection method for some clients.  This will require someone to write custom business rules for the connection to be successfully setup.

Create SQL data adapter

Setting up the SQL data adapter takes very little time and can even be put in a dashboard so the data can be downloaded.  Here are the steps in OneStream:

Create a blank dashboard maintenance unit:

I created a new dashboard maintenance unit, called it “Test_Data_adapter” and it automatically adds all the types of objects needed to do a dashboard:

Add a Data adapter for your connection (this example has a named connection called “Netsuite”):

Copy your SQL into the data adapter:

Run the SQL:

Create a Grid View component:

Add the SQL data adapter:

Create a new dashboard and add the Grid View component:

View dashboard:

Export the data so that it can be tied:

This setup should take less than 1/2 hour to do.  Once this data has been checked, you’re ready to create the connector business rule and data source.