Ironside Tech Tip: Integrate Salesforce.com with IBM Cognos

Salesforce.com, most commonly implemented for its customer relationship management (CRM) platform, is a well-known industry leader in client relationship management as well as social performance management, sales performance management, and other areas. The IBM Cognos enterprise combined with Salesforce data can help your organization build a single view of your business and create reports, dashboards, and scorecards for analyzing your sales information. A few quick benefits of integrating Salesforce with Cognos are as follows:

• Incorporate critical CRM information into one system.
• View current and historical data about customers side by side.
• Reconcile data from various systems to gain a complete view of customers.
• Use snapshots of data for time-period analysis.
• Supplement data warehouse data with up-to-the-minute information (via SOAP API).
• Enhance Salesforce reporting with other BI capabilities.

As our previous article mentioned, IBM Cognos 10.2.x provides a native dynamic query mode (DQM) connector for Salesforce.com data sources. There is no need to bridge through IBM Cognos Virtual View Manager. This new feature makes consuming data from this source inside IBM Cognos a much easier and more cost-effective solution than it was in the past.

Let’s take a quick look how to create this kind of data source connection.

Preparation within Salesforce.com

1. Enable the web services API in permissions

Salesforce.com API access is based on your subscription level. Ensure you have access to the API by checking with your system administrator. To be able to successfully connect to SFDC with Cognos BI you must set “API Enabled” in the user system permissions for the data source signon within Cognos.

2. Get the security token

Most production Salesforce instances have security tokens enabled, which means you need to append your token to the password string as part of Cognos data source connection configuration. For instance, if your user ID for SFDC is joesmith@salesforce.com and your SFDC security token is abcdefg, then you need to enter joesmith@salesforce.com abcdefg in the password field of the data source signon in Cognos Administration.

Hint: To reset/get your security token from Salesforce.com:

1. At the top of any page in the application, click the down arrow next to your name. From the menu under your name, select Setup or My Settings—whichever one appears.
2. From the left pane, select one of the following:
If you clicked Setup, select My Personal Information | Reset My Security Token.
If you clicked My Settings, select Personal | Reset My Security Token.
3. Click the Reset Security Token button. The new security token will be sent via email to the email address on your user profile.

Steps to Create SFDC as Data Source in Cognos

1. Launch the IBM Cognos Administration console and click on the Configuration tab
2. Select the new data source wizard and choose Salesforce.com from the Type dropdown.

3. Type in your Salesforce URL. You may leave default values for all other parameters. For a testing site, you may use “https://test.salesforce.com/services/Soap/u/20.0”

4. Create sign-ons. Remember to append the security token.

5. Test the connection. Your Salesforce.com data is now available for use!

Common Issues

1. An error about a field that cannot be grouped when executing a report.

Example error message:

[{“message”:”nFROM Opportunity GROUP BY Name, Description LIMIT 2000n ^nERROR at
Row:1:Column:59nfield u0027Descriptionu0027 can not be grouped in a query
call”,”errorCode”:”MALFORMED_QUERY”}]” .

This error is resulting from a documented limitation in the Salesforce.com API whereby the
Group By clause is not permitted on certain fields such as ‘Description’. You can prevent Cognos
from instructing SFDC to perform a Group By for a particular field by wrapping it with a function
in Framework Manager that Cognos will perform locally. All string functions are performed
locally so there are many for you to choose from like:

Cast
Lower/upper(<c1>) (assume when you don’t care about the case of the data)
Trim(<c1>) (assume when spaces are not important to your data)

You can find all operations are permitted on each of the SFDC fields here:
http://www.salesforce.com/us/developer/docs/api/Content/sforce_api_objects_account.htm

Alternatively you can turn off auto-aggregation/summary in Report Studio or Query
Studio to get around this temporarily.

2. Salesforce.com Checkbox data type is not supported. The workaround for this issue is to change the checkbox to a dropdown list in Salesforce.com. All SFDC SOAP API references are available at the following URL:

http://www.salesforce.com/us/developer/docs/api/index.htm

Summary

With just a few clicks you can bring Salesforce.com to the IBM Cognos BI platform, extending the value of your analytics investments and maximizing your ROI. IBM Cognos can quickly enable new insights and perspectives to make better, more informed decisions with integrated sales data. Additionally, business users will be able to access any data or combination of data through IBM Cognos BI capability and through a range of access modes including internet/web, the Salesforce.com portal, Microsoft Office, and mobile/tablet devices. For more hands-on experiences and complex implementation, please contact us.