Skip to main content


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:











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

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

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:

Application DashboardDashboard 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:

Test Dashboard Maintenance Unit

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

Data Adapter Object

Data Adapter Icon

Sql Data Adapter

Copy your SQL into the data adapter:

Sql Query Copy

Run the SQL:

Run Sql

Create a Grid View component:

Dashboard Component

Dashboard Component Button

Grid View Component

Add the SQL data adapter:

Add Data Adapter

Create a new dashboard and add the Grid View component:

Create Dashboard

Add Grid View

View dashboard:

View Dashboard

Export the data so that it can be tied:

Gl Transactions Sample

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Phillip Oswald

CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. Recently, I've had 2 different projects for Budgeting and Forecasting with one of them using Thing Planning.

More from this Author

Follow Us