OBIEE Integration with Salesforce.com – Part 2 Setting Up ODBC Driver

OBIEE Integration with Salesforce.com

  1. Part 1 Setting Up SFDC
  2. Part 2 Setting Up ODBC Driver
  3. Part 3 Creating Your First Answers (Analysis) Report
  4. Part 4 Embed OBIEE to SFDC

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:

  1. Progress DataDirect Connect – Salesforce ODBC Drivers
  2. Simba Technologies – ODBC Drivers with SQL Connector for use with Salesforce
  3. EasySoft – Salesforce ODBC Driver
  4. 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.

Progress_Website

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.

ODBC_Data_Source_Administrator

 

Choose DataDirect 7.1 Salesforce and click finish.

Create_New_Data_Source

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

SFDC_Driver_General

Go to SQL Engine tab.

SFDC_Driver_SQL_Engine

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.

SFDC_Driver_SQL_Engine_Server

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.

SFDC_Driver_SQL_Engine_Server_Advance

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.

SFDC_Driver_Advanced

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.

Test_Connect

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.

Connection_Established

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.

  1. Part 1 Setting Up SFDC
  2. Part 2 Setting Up ODBC Driver
  3. Part 3 Creating Your First Answers (Analysis) Report
  4. Part 4 Embed OBIEE to SFDC
Advertisements

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

4 thoughts on “OBIEE Integration with Salesforce.com – Part 2 Setting Up ODBC Driver”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s