This post’s purpose is to develop an OBIEE 11g solution that allows users the ability to develop dashboards and reports based on Real-time Salesforce data. To demonstrate, we have used Contacts, Accounts and Opportunities.
• Windows 7 Professional 64 bit with 4 GB memory
• Microsoft SQL Server 2008 R2 (but any OBIEE supported database would do)
• Salesforce User Account
• Salesforce ODBC driver (Data Direct 7.1 Salesforce) http://www.datadirect.com/products/odbc/salesforce_crm_odbc_driver/index.html
• OBIEE 11g (184.108.40.206) http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bi-downloads-1525270.html
1. Modified the 64 bit Data Direct ODBC driver to use 1-Server mode (click Edit Server Settings), started the ODBC service
2. Test the connection by specifying the Salesforce Database, User Name and Password
Make sure the Salesforce password includes the security token
Link to http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_concepts_security.htm
3. Build the OBIEE Repository for Salesforce
4. Develop Dashboards
1. Recommend that all aggregation logic be done in Salesforce (using formula fields or rollup fields)
2. The more joins, the slower the response
3. Make sure that OBIEE Caching is turned off
The Salesforce to OBIEE integration worked well using the Data Direct ODBC driver. The response times for the limited data trial were better than expected, even with queries that included up to 5 joins. The ODBC driver can be tuned for greater performance if needed. The ability to generate custom dashboards with drilldowns, action agents and deliver the content to various media was a major benefit.
I hope that the screen shots and explanations provided will assist you in your OBIEE integration and help you avoid some of the issues that I faced in development.
- There are other mechanisms and/or tools which can be used to achieve the same, we have tried to cover only the integration between Salesforce and OBIEE
- We are not trying to compare other tools or test with huge data volumes.