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.