Preparing for Direct Connection to OneStream

[poswald]

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. 

This blog is going to focus on the steps that should be taken before setting up a direct connection, and should be done before creating the connector business rule. We will assume the connection is via ODBC or other SQL systems.  Undergoing these activities in advance will allow customers to maximize the value they get from specialist partners hired to develop the integration.

The overall process for integrations will roughly look like this:

1. Create a query from your ERP, using Microsoft SQL Server or equivalent, to retrieve 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 really require a OneStream consultant and can be performed by an ERP specialist. Only after these steps are done, we will be ready to setup all elements of a OneStream integration (Connector business rule, Transformation Rules, Workflow Profile, actual data loads, etc). Step 4 can be performed by internal OneStream administrators with minimal reporting skills.

Let's examine what these steps actually involve.

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, 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

Most likely your ERP will have multiple tables, in which case they would have to be joined. Queries typically seen in the field have multiple INNER, OUTER and LEFT JOINs. This is why it is very important for the tables to be created by someone who knows the actual ERP system in detail. It could be someone in the IT department who is very familiar with that system; or a consultant specialized in that type of ERP. In all honesty, the first option is not only the least expensive one, but often also the most qualified one.

Check data

If the data is small enough, it can be obtained as comma-separated file (.csv) and compared to the trial balance or reports currently being used in the ERP. The data must align to what we find in the existing reports. That way we can be sure that the data that will be going to OneStream is correct; so we can be assured later on that, if we see a difference between OneStream and ERP then the problem is not with the query. If you skip this step, how will you know if the differences you have are due to the query or the OneStream setup?

Set up Direct Connection

There are several ways to setup the direct connection:

1. Named connection. If OneStream is in the Cloud, then OneStream Support will setup the connection for us. We will have to provide the ERP driver and any necessary setup information to OneStream Support. If on-premise, then we will need to configure it ourselves in the Application Server Configuration file of all our appservers.
2. Smart Integration Connector (SIC), available in version 7.3+. This is only possible when software from OneStream is installed on a separate virtual machine of your own, in order 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, effectively transporting data over HTTPS. This will require custom business rules for the connection to be successfully setup.

Create SQL data adapter

Setting up a SQL Data Adapter takes very little time. The Adapter can then be used in Dashboards for reporting or even data extract. The steps are:

1. Create a blank dashboard maintenance unit (note: in version 7.3+, they will be found on the Workspaces page rather than Dashboards😞
   
   
   Creating a new Dashboard Maintenance Unit, like the "Test_Data_adapter" seen below, will list all elements that can be used for Dashboards:
   
2. Select Data Adapters and create a Data Adapter for our connection (this example shows a named connection called "Netsuite"):
   
   
   
    
3. Copy your SQL into the data adapter:
   
4. Run the Adapter to check the output is correct:
   
5. Select Components and create a Grid View component:
   
   
   
6. Switch to the Data Adapter tab and add our Adapter to the Component:
   
7. Create a new dashboard and add the Grid View component:
   
   
8. View the resulting Dashboard:
9. Export the data so that it can be compared with the original ERP values:
   

Once the data has been checked, you're ready to create the Connector business rule and use it in a Data Source.

Conclusion

These steps should take very little time, and will ensure that customers can maximize the amount of time that OneStream specialists will spend on the actual complex parts of the integration. Leveraging in-house resources for simple tasks will likely be the most cost-effective strategy, ensuring a successful implementation.

About the Author

Phillip is a CPM consultant with 20 years of experience focused on making continuous improvements in the efficiency and accuracy of organizational accounting systems. His recent engagement include several Budgeting and Forecasting projects, some involving Thing Planning.

Philip on LinkedIn 
Perficient Inc.