OBIEE Integration with Salesforce.com – Part 3 Creating Your First Answers (Analysis) Report

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 2, we will import Objects to the RPD. For demonstration purposes, I will keep this as simple as possible. I will also not go through details of how to build hierarchies or other RPD related issues. I will rather go through specific best practices for SFDC as a data source.

In this tutorial, I will use Salesforce Developer Edition, a free edition for developers, to demonstrate how to create the below report. You can sign up for it here: https://developer.salesforce.com/signup

RPD Setup – Import your first Salesforce ODBC 3.5 data source

Since you have already configured the ODBC from Part 2. In BI Admin, you should import metadata from the ODBC source you have created. Depending on Part 1, the username will be the SFDC username and the password rdepends whether you are on a trusted IP mode or security token mode. If Trusted IP, then just fill in the SFDC password else fill in SFDC password + security token. If you like this to be dynamic, I will cover this in a later part.

On the next screen, leave it default and click next.

Now choose CAMPAIGN and USER column if you are using Developer free license and click finish.

Once imported, it should look similar to the following.

We are also required to join the two Objects.

After joining, please also set CAMPAIGN object as never cacheable. For USER object, set to remove cache every 2 hours or anything reasonable. You may have it never cacheable as well.

Finally, setup your database features by querying DBMS.

Drag the two objects to the BMM layer and set the facts to SUM. Rename them to something you like. Here, you are also allowed to build hierarchies if it is needed but I will not be going through that.

Drag it to the Presentation Layer and remove anything you don’t want to be seen in the Presentation Layer. I’m lazy so I skipped that.

We are now ready to create the report in Answer.

Creating Your First SFDC Answers (Analysis) Report

I will be building a relatively simple report in Answers (Analysis).

I created 2 extra calculated columns for Variance and Variance %. I also set conditional formatting on Actual so that when Variance is negative, it becomes a red cell. Also add a filter for Type to is prompted. We will use this column in Part 4 for passing parameters from SFDC to the report.

In results tab, I added a horizontal stack bar and a table like the following. Relatively simple!

Before we finish off for this post, let’s get things ready for Part 4 as well. Create a Dashboard and Dashboard Prompt.

I will create a new prompt called CampaignPrompt with just Type as the column prompt.

Next, I’ll create a new dashboard called Campaigns with the newly created prompt and the Answers report in it and you will be ready for Part 4.

As you can see, I kept this report relatively simple. If you would like to have a more advanced post, please feel free to ask.

In Part 4, I will continue be embedding this to Salesforce and walk through the required steps.

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