Tableau quick integration in Salesforce

.

Salesforce is one of the leaders in the CRM sector and it is used in thousands of companies around the world. It is really useful software for managing the data of a sales department and it performs even better if you can combine it with Tableau. If you integrate Tableau into Salesforce you will be able to have the departmental reporting on the same page, saving time and making better analysis of the data.

Tableau offers you a solution with the Salesforce Canvas Adapter, but depending of the level of integration needed you can implement the following very easily. With the method explained here you could save time developing the integration and therefore save money too.

This method takes advantage of the flexibility offered by Tableau with the URL parameters and the facility to embed Tableau in an iframe. Also, Salesforce allows us to create some flexible objects with Apex programming language. Now we are going to explain the procedure for integrating Tableau with Salesforce in four quick and easy steps. Take into account that these actions need to be done in the dev version of Salesforce (sandbox) and then deploy it to production.


Tableau Salesforce 1
Step 1 – Tableau dashboard

Create a dashboard in Tableau and publish it to the server. This Dashboard has to be filtered with an available field in Salesforce. For instance, the ID for a certain account can be the perfect candidate if you are sure that exists in the EDW and in Salesforce. Once published, the URL provided by Tableau Server will be useful in the next step.

Step 2 – Visualforce page

Create a Visualforce Page in Salesforce:

This part can seem scary if you are not used to Salesforce development, but following this step by step guide can help you without needing previous knowledge in this area. All that is necessary to do this is to access the developer console in the sandbox environment.

Tableau Salesforce 2

Once inside the developer console is time to add the apex code below. The URL copied in the previous step needs to replace the red one. The filtering field in Tableau must replace the blue variable and the ID field of Salesforce replaces the green one. Name it and save it as a visualforce page.


<apex:page standardController="Account">

<apex:iframe src="http://tableauserver/views/workbook/dashboard?:embed=yes&CRMID={!account.AccountID__c}&:toolbar=no"

height="1110px" width="1160px" scrolling="true"/>

</apex:page>


Now we are going to analyse the relevant parts of this code:

  1. <apex:iframe – apex code to create an iframe where the Tableau dashboard will appear.
  2. http://tableauserver/views/workbook/dashboard - URL of the published dashboard in Tableau server.
  3. ?:embed=yes – parameter to embed the dashboard into the iframe.
  4. &CRMID={!account.AccountID__c} – part of the URL to filter the view. CRMID is the field used in Tableau and has to be set as a filter in the dashboard. !account.AccountID__c is the ID of the accounts used in Salesforce. Both fields have to be common.
  5. &:toolbar=no – Parameter to hide the toolbar.
  6. height="1110px" width="1160px" – height and width of the iframe. It is advisable to be the same as the Tableau dashboard (unless using automatic size).
  7. scrolling="true" – enables the option of having a scroll in case it does not entirely fit.

The console will look like this:

Tableau Salesforce 3

(!) The common fields for which Tableau has to filter have to be defined in {!account.AccountID__c} section of the code. To detect the proper name of the Salesforce field it is advisable to go to the fields section just to ensure that you are picking the correct name and not the alias that is used in the view.

Tableau Salesforce 4

Step 3 – Create Salesforce section

To create a section to place the Tableau dashboard you will have to edit the page layout. To enter the editor mode press the “Edit Layout” link.

Tableau Salesforce 5

Afterwards, drag and drop the section object to the desired emplacement and name it.

Tableau Salesforce 6

Step 4 – Add Visualforce page

The next step consist in drag and drop the Visualforce page that we have created in step 2 to the “Tableau Reporting” section added in step 3.

Tableau Salesforce 7

Once added, it is possible to edit the dimensions of the Visualforce page pressing the settings button.

Tableau Salesforce 8

Finally, you will have to save the layout and the Tableau report will appear in your account view filtered by the account that you are visualizing. Each time that you change the account in Salesforce the Tableau report will also change. The final result will look something similar to this:

Tableau Salesforce 9

 

 

Enjoy your integration!

 

OBIEE 11g training for Kenyan Oracle BI Partners

.

In our post OBIEE 11g training in Nigerian, we were talking about our virtual training capabilities and how we were putting our efforts to promote this training course format. I'm glad to say that the efforts were rewarding.

Last week, the ClearPeaks Academy  provided a virtual training to Oracle BI Partners in Kenya.  Providing a virtual training is an added challenge to the usual mix of profiles we find in this type of training courses. However, our experience providing virtual training courses made this one another successful training course.

If you want to get information about the ClearPeaks Academy training courses, please visit our Academy page or contact us via our web page form or by sending an email to academy@clearpeaks.com.

New Oracle release: OBIEE 11.1.1.9.0 version

.

Oracle released the OBIEE 11.1.1.9.0 version on May 14th. This should be considered the last minor version before OBIEE 12c is released this fall.

The new version adds some new features across the different sub-products. They are summarized in the following post. The most relevant innovations are highlighted.

OBIEE

New features for OBIEE developers aka Answers (full notes here)

  • Key Terminology Changes
  • Enhancements in Oracle Scorecard and Strategy Management
  • Enhancements in Dashboards (custom layouts for printing and exporting Pages through BIP)
  • Enhancements in Managing Accounts
  • Enhancements in Selection Steps (variable override)
  • Enhancements in the Export Functionality (several changes, it is yet to be seen whether HTML export works…)
  • Enhancements in Analyses (save custom formula columns in the catalog for future reuse, yai!)

New features for Repository builders (full notes here)

  • Whole Rpd Checkout Option Added to Multiuser Menu
  • Improvements to Aggregate Persistence
  • Initialization Block Written in JSON Syntax
  • Translation Keys
  • New Administration Tool Options
  • Script Added to Upgrade DataDirect Drivers
  • Cloudera Impala Supported
  • Access to Hyperion Planning Data Sources
  • Expanded Oracle 12c Database Support

New features for Security (full notes here)

  • New privileges (EVALUATE_PREDICATE)

New features for Integration (web services, full notes here)

  • SchedulerService Web Service

New features for Administrators (full notes here)

  • New Properties for Full-Text Search
  • New or Enhanced Configuration Elements (some of the configuration elements might be useful)
  • New NQSconfig.INI Settings
  • Debugging Agents Using Fusion Middleware Control

 

BI PUBLISHER

New features for BI Publisher Model Design (full notes here)

  • Oracle Endeca No Longer Supported as a Data Source
  • New Properties Added (interesting query_time_out property that will error a BI publisher report after X seconds)
  • New Custom Metadata Component to Support Oracle WebCenter Content Server
  • Oracle WebCenter Content Server Supported as a Bursting Destination

New features for BI Publisher Report Design (full notes here)

  • Support for Microsoft Word (.docx)

Kerberos authentication and delegation with Tableau and MSSAS

.

Introduction

 

Kerberos is a three-way authentication protocol developed by the Massachusetts Institute of Technology (MIT). As of Windows 2000, it has been the default authentication method in Microsoft Windows systems. Tableau Server version 8.3 has supported Kerberos since the release of version 8.3.

In this article we will explain how to configure Tableau Server with Kerberos to enable:

- Single Sign-On (SSO) authentication of Active Directory (AD) users.

- Authentication delegation to Microsoft SQL Server Analysis Services (MSSAS).

Kerberos configuration

Kerberos is configured using the “Configure Tableau Server” application. The first step is to enable it in the “Kerberos” tab as shown below:

Kerberos

After enabling Kerberos, you must create the configuration script. In most cases you can simply use the default script generated by Tableau when you click the “Export Kerberos Configuration Script” button of the “Kerberos” tab:Kerberos

This script has two steps:

  • SPN registration: The Service Principal Name (SPN) registers the host in the Kerberos environment, so that services running on it can authenticate against other services.
  • Keytab generation: The Keytab file contains the shared password provided by Kerberos that is used to authenticate against other services. Example:

The default script registers the SPNs and generates the Keytab for the HTTP service in the host where Tableau Server is installed for the “Run as User” account using the setspn and ktpass commands. For example:


setspn -s HTTP/tableauhost DOMAIN\runasuser

setspn -s HTTP/tableauhost.domain DOMAIN\runasuser

ktpass /princ HTTP/tableauhost.domain@DOMAIN /pass runasuserpass /ptype KRB5_NT_PRINCIPAL /out keytabs\kerberos.keytab


There are some scenarios where the default configuration can be problematic:

  • Scenario 1: Duplicate SPN

In some environments, another service might have already registered an SPN for the HTTP service in the host where Tableau Server is installed. An example could be a web server installed in the same host. If this already-existing SPN has been registered for a user other than the Tableau Server “Run as User” you will get an error, since duplicate SPNs for the same service/host pair are not allowed:

Kerberos

There are two ways to overcome this problem. If the existing SPN is not used anymore, you can delete it using the setspn –D command, and then create the new SPN without any problem. If the old SPN is still being used, it will be mandatory to use the user for which the existing SPN was created as the Tableau Server “Run as User”.


  • Scenario 2: Multiple host aliases

It is common to use an alias that is different from the host name to access Tableau Server. For example, you might connect to http://tableau.domain.com, instead of using http://tableauhost. If using an alias registered in your Domain Name Server (DNS), you will have to create SPNs for all of them. For example:


setspn -s HTTP/tableau DOMAIN\runasuser

setspn -s HTTP/tableau.domain.com DOMAIN\runasuser


In this case, you will also need to generate a Keytab for each alias. However, Tableau Server only allows one Keytab file. Therefore, you will have to nest all the Keytab files, one by one (for each alias). To do so, you can use the option \in of the ktpass command. For example:


ktpass /princ HTTP/tableauhost.domain@DOMAIN /pass runasuserpass /ptype KRB5_NT_PRINCIPAL /out keytabs\kerberos_1.keytab

ktpass /princ HTTP/tableau.domain.com@DOMAIN.COM /pass runasuserpass /in keytabs\kerberos_1.keytab /ptype KRB5_NT_PRINCIPAL /out keytabs\kerberos_1.keytab


The intermediate file names are not important, but the last Keytab file must keep the name “kerberos.keytab”.


 

The script will have to be executed from the Command Prompt (CMD) by a user with domain administration rights. After running the script it is important that no error messages appear while registering the SPNs and that the Keytab file has been generated.

Finally, import the Keytab file into Tableau Server and test the configuration. If everything is correct, you will receive an OK message after the test.

Kerberos

If you restart Tableau Server now, the users of Active Directory will be able to use the SSO feature

Authentication delegation to MSSAS

The use of Kerberos delegation to Microsoft SQL Server Analysis Services (MSSAS) is very useful from a security point of view. If you have security roles defined in your OLAP cubes for the Active Directory users, the users will only see the information they are allowed to see without including any extra security in the Tableau workbooks.

In order to enable the delegation to MSSAS, first configure the “Run as User” account to act as part of the operating system. For that purpose, open the Local Security Policy application from “Start -->Administrative Tools --> Local Security Policy”.

Kerberos

Once there, go to “Local Policies -->User Rights Assignment --> Act as part of the operating system (right click) --> Properties”.

Kerberos

Finally, click on “Add User or Group…” and add the “Run as User” account.

Kerberos

After this step, you will have to configure the services that will be delegated. This has to be done by an Active Directory administrator using the Active Directory Administrative Center. Find the “Run as User” account in the list and right click to enter the “Properties” menu. From there go to the “Delegation” tab and select the options “Trust this user for delegation to specific services only” and “Use any authentication method”. Finally, click on “Add…” and select the MSSAS service from the list of available services. The MSSAS services will have names like “MSOLAPSvc.3”.

Kerberos

It is important to keep in mind that, whenever you upload a workbook to Tableau Server, if you want the Kerberos delegation to be used, you will have to configure the authentication options. In this case, select “Viewer credentials” as the authentication method for the MSSAS data source.

Kerberos

OBIEE 11g Installation in Silent Mode

.

At ClearPeaks we recently received a request to perform an OBIEE installation on an Oracle Enterprise Linux (OEL) server without Graphical User Interface (GUI).

The Repository Creation Utility (RCU) and the Oracle Universal Installer (OUI) offer the capability of being executed without a graphical assistant. It will be necessary to run them in SILENT MODE.

Since a database was already installed, only the RCU and OBIEE silent installation process is described in this post.

1. Schema Creation with RCU

1.1 Prerequisites

Make sure that Database and listener are running

1.2  Schema creation

1.2.1  Passwords file creation

As it is a silent installation, the RCU installer will require a text file containing the following passwords (with this sorting):

  • Database password
  • Component 1 schema password (BIPLATFORM)
  • Component 2 schema password (MDS)

vi rcu_passwords.txt


OBIEE Silent Mode

Ensure that the file belongs to Oracle before running the rcu command.

1.2.2 Execution in silent mode

As in every schema creation through RCU, it will be necessary to obtain the software from the Oracle site and extract it. The executable is located at rcuHome/bin/

Execute the following command to start the installer in silent mode:


./rcu -silent -createRepository -connectString localhost:1521:orcl -dbUser SYS -dbRole SYSDBA -schemaPrefix DEV -component BIPLATFORM -component MDS -f < ../../rcu_passwords.txt


After a while, this should be the result:

OBIEE Silent Mode

2. OBIEE Installation

2.1  Prerequisites

2.1.1   Database and listener

As in the RCU execution, the database and the listener need to be started and working before starting the OUI.

2.1.2  Schemas created through RCU

BIPLATFORM and MDS schemas must be created during the RCU installation.

2.1.3  Unset ORACLE HOME variable

If you have already installed an ORACLE database within the same server where you are going to install the OBIEE server, the ORACLE_HOME environment variable must be disabled. Bear in mind that the variable remains disabled only in the terminal session.

Execute the following command (as root):


unset ORACLE_HOME


OBIEE Silent Mode

2.1.4  Set Kernel Parameters

The last step is to modify the Kernel Parameters (as root):

The next lines must be added in the limits.conf file

  • oracle hard nofile 4096
  • oracle soft nofile 4096

vi /etc/security/limits.conf


OBIEE Silent Mode

2.2  Silent validation

2.2.1  Response file creation

If you don’t have GUI in your server, you can edit the response file I used for this installation:

response_file

It will be necessary to replace the <SECURE_VALUE> for your actual passwords.

2.2.2  Silent validation execution

Before installing OBIEE, a silent validation is required. OUI needs the response file to be executed in silent mode.

Ensure that the response file belongs to Oracle before running the installer.

Execute the following command as an Oracle user (the full path of the response file is required).


./runInstaller -silentvalidate -response /home/oracle/Desktop/bi_binaries/obiee_binaries/bishiphome/Disk1/response_file.rsp


You can ignore the following error:

OBIEE Silent Mode

2.3  Silent installation

2.3.1  Location file

If you already have an oraInst.loc file in your system, you can use it:


vi /home/oracle/app/oracle/product/11.2.0/dbhome_1/oraInst.loc


OBIEE Silent Mode

If this file does not exist on the system, the installation program creates it automatically.

2.3.2  Silent installation execution

This is the last and most critical step of the installation. Please make sure that all the previous steps have been performed successfully.

Execute the OUI in silent mode (as an Oracle user):


./runInstaller -silent -response /home/oracle/Desktop/bi_binaries/obiee_binaries/bishiphome/Disk1/response_file.rsp -invPtrLoc /home/oracle/app/oracle/product/11.2.0/dbhome_1/oraInst.loc


This step will take several minutes. If the previous steps have been performed correctly, the installation should end successfully.

Conclusion

This post outlines how to fully install OBIEE 11g on a Linux server without GUI.

Advantages of the silent mode installation include:

  • No need to consume extra resources with the graphical user interface.
  • The whole installation could be automatically executed by a script.
  • Possibility to perform equal installations if the response files don’t change.
  • No need to spend more time executing the graphical wizard manually.

For more information, consult the official Oracle documentation:

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav