Add an external database connection in OneStream – Quick Tip # 5


In this quick tip blog post, we are going to discuss “How to add external database connections in OneStream?”

We are going to look at adding two external connections

  • An Oracle database
  • A Microsoft SQL server database

Databases other than MS SQL

OLE DB Connection string

In-order to add databases other than SQL Server, you must install the required drivers for those on the OneStream Application servers.

To add an Oracle database, I’m going to perform a custom Oracle client install on OS App Server.

Select Oracle objects for OLE and Oracle Provider for OLE DB and proceed with the installation.

Once the installation gets completed, you can check the install by creating a test UDL file. Create a new file with an extension as UDL

You can double click the file, and it’ll open the data link properties window. Navigate to the Provider tab, and Oracle Provider for OLE DB must be listed there.

You can add the connection details and test the link to make sure that we can connect to the external DB. I’m using TNS less connection entry as my data source which is shown below.

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-HRQ5NVJ)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)))

You can provide the name in tnsnames.ora file as the data source if you’ve the client installed.

After saving the data link, you can open the UDL file with a text editor and view the connection string.

Encrypting the connection string

If you don’t want an encrypted connection string, you can proceed directly to adding the connections section.

If you want to encrypt the connection string, you can use OneStream Database Configuration Wizard to do so. Navigate to Tools->Encrypt Connection String to Clipboard

Copy the connection string from the UDL file here. There is no encryption completion indication provided. However, you can get the encrypted string by performing a paste operation in a text editor.

Adding connections

To add the connection, you are going to make use of another configuration utility. In comes OneStream server configuration utility.

Open application server configuration file by navigating to the config folder (you can place the file at any location).

Once the file is open, we are going to look at Database Server connections by clicking on the ellipses.

You can update existing/add new connections here.

Click Add to add a new connection

Make sure the following details are updated.

  • Is External Database – True
  • Database Provider Type – OLEDB
  • Connection String – Encrypted String or Connection string from UDL file
  • Is Connection String Encrypted – True/False
  • Name – Name of the connection, this is the name that shows up in OneStream

Close and Save the file. Once done, restart IIS.

You can test the connection by logging into OneStream and creating a data adapter in Dashboards.

Select the Command Type as SQL

Select the database location as External

Make sure that the newly added connection is listed in External Database connection

You can write an SQL query and test whether the data is showing up in OneStream.

Easy Peasy 🙂

Data from Oracle in OneStream 🙂 🙂

Just for giggles, I went ahead and created a dashboard using the BI viewer component in OneStream 5.2 version. Isn’t it pretty :)?

SQL Server connection

Adding SQL server connection doesn’t require you to install any clients, all you got to do is update the connection string

Data Source=servername;Initial Catalog=databasename;Persist Security Info=True;User ID=username;Password=password

Perform the steps described in Encrypting the connection string and Adding Connections.

Leave a comment

Your email address will not be published.