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

OBIEE_Campaign

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.

Import_Metadata_1

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

Import_Metadata_2

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

Import_Metadata_3

Once imported, it should look similar to the following.

Import_Metadata_4

We are also required to join the two Objects.

Physical_Layer_1

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.

Physical_Layer_2 Physical_Layer_3

Finally, setup your database features by querying DBMS.

Database_Features

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.

BMM_1

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.

Presentation_1

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).

SFDC_Campaign_Answers_1

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.

SFDC_Campaign_Answers_2

SFDC_Campaign_Answers_3

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

SFDC_Campaign_Answers_4

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.

CampaignPrompt

CampaignPrompt2

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.

CampaignsDashboard

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
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

6 thoughts on “OBIEE Integration with Salesforce.com – Part 3 Creating Your First Answers (Analysis) Report”

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