OBIEE Integration with Salesforce.com

Continuing from Part 1, we will also need to install and configure ODBC Driver. There are a few choices for choosing your drivers as follows:

Progress DataDirect Connect – Salesforce ODBC Drivers
Simba Technologies – ODBC Drivers with SQL Connector for use with Salesforce
EasySoft – Salesforce ODBC Driver
Rssbus – CDATA Salesforce ODBC Driver

With the above choices, it really does make life tough to choose which driver to purchase. I actually tried them all and they all work fine with my test environment. So the problem goes down to your SFDC scale. If you are using Force.com applications, then I would highly suggest you go with Progress. Progress is a listed company, well known with its DataDirect series, have been around for long enough with good field knowledge. On top, I’m pretty sure they have the most knowledgeable developers and support within the list. All this is packaged with the price, so decide for yourself. I personally think going with the best is usually the best option if money allows. Second to Progress would definitely be Rssbus (CDATA) and Simba Technolgies. They either have a large client list or certified for certain BI tools. This way, the driver gets updated more often because chances are your bug has already be encountered by others. I’m not saying EasySoft is a bad choice, but if you move further down the road, I think Simba or Rssbus providing JDBC choices for Publisher maybe a good idea. I tried to find JDBC driver on EasySoft website, but wasn’t able to find it.

Anyway, they all provide trial versions, so you might as well test it for your own. They all work similarly the same way.

In this post, I will be using Progress DataDirect for demonstration.

I won’t go through installing the driver since it’s extremely easy, next next next. On the Progress link here, download the trial version.

So after installing, start up Windows ODBC Administrator for adding Salesforce connection as well as configuring the adapter.

Go to the System DSN tab and click Add.

Choose DataDirect 7.1 Salesforce and click finish.

You’ll be brought to the DataDirect configuration screen. Give the Data Source a name.

Go to SQL Engine tab.

Leave it as 0 – Direct for now. As you can see, the default java heap size is set to 256m but you can set anything higher depending on your server configurations. Depending on your environment, you can play around with the heap size.

If set to 0 (Direct), the SQL engine runs in direct mode. The driver and its SQL engine run in a single process within the same JVM. If set to 1 (Server), the SQL engine runs in server mode. The SQL engine operates in a separate
process from the driver within its own JVM.You must start the SQL Engine service before using the driver (see Starting the SQL Engine Server on page 953 for more information). Multiple drivers on different clients can use the same service.

You are also allowed to fill in Proxy information if your connection to Salesforce from OBIEE server requires it to go through a proxy server.

Optionally, you can set SQL Engine Mode to 1 – Server. I think this is the more preferable way since we are using it as a server with OBIEE.

Once changed to Server mode, JVM and other options are dimmed. This is because you will be required to enter all other options within Edit Server Settings. Since the driver will create a Database for storing and caching Salesforce, you have the option to set the Server DB Directory. All other options are the same as 0 – Direct. Once complete, next to Services, click Start, then click OK.

Next screen we will need to configure is the Advanced tab.

Like I said previously, the driver will create a local database for storing metadata and caches. The first option is to allow the driver to create a Database when it does not exist. This would be 2 – NotExist. Optionally, you can set it to 1 – ForceNew which forces it to create a new database every time.

If your system is multi-threaded, check Application Using Threads. If set to 1 (Enabled), the driver works with single-threaded and multi-threaded applications. If set to 0 (Disabled), the driver does not work with multi-threaded applications. If using the driver with single-threaded applications, this value avoids additional processing required for ODBC thread-safety standards.Obviously we would be multi-threaded.

Refresh Schema, if set to 1 (Enabled), the driver automatically refreshes the schema the first time a user connects to the specified database. Any schema objects that have changed since the last time the database map was rebuilt are reflected in the metadata. See Database Configuration File for information about embedded databases and map files. If set to 0 (Disabled), the driver does not automatically refresh the schema the first time a user connects to the specified database.

Refresh Dirty Cache, if set to 1 (Enabled), a dirty cache is refreshed when the cache is referenced in a fetch operation. The cache state is set to initialized if the refresh succeeds. If set to 0 (Disabled), a dirty cache is not refreshed when the cache is referenced in a fetch operation.

Note that Refresh Schema and Refresh Dirty Cache will slow down the initialization since remapping of the database will be performed. For Read Only, unless you are sure you would not writeback, please leave it unchecked. Login Timeout 15 seconds would usually be enough, fine tune it if you encounter problems. Fetch size 100 or 200 would be appropriate. Fetching little data size more frequently is usually better at performance.

In the Config Options, we can also type in Key Value pairs. Valid Key Value pairs as follows:

AuditColumns={All, AuditOnly, MasterOnly, None}

CustomSuffix={Include, Strip}

MapSystemColumnName={0, 1}

UppercaseIdentifiers={true, false}

If you like to know more on the above, feel free to contact me.

The final step now is to test your connection.

Click Test Connect. Fill in your Salesforce User Name and Password. From Part 1, if you used the Security Token option, then you would need to enter the token behind your password.

Click OK and it should say Connection Established. If it doesn’t, try restarting your windows and double check the services is started. Test again.

That’s everything for ODBC Driver configurations. The next topic would be on RPD.

For performance tuning the driver on DataDirect SFDC, feel free to ask me questions. I won’t be covering this today.

Author: Steve Yeung

Being in the EPM & BI field for more than 8 years, it's about time I contribute to newcomers! As a founder of MondayBI.com I wish to give you all the help I can. Feel free to give any suggestions or questions. Hope you will all enjoy this blog! William Wong Essbase Certified Specialist OBIEE Certified Specialist View all posts by Steve Yeung