Configure OBIEE in Cluster Mode

Deploying OBIEE for High Availability (Cluster Mode)

Oracle Business Intelligence in a high availability environment. It outlines the installation and configuration procedures for the BI components needed to achieve end-to-end availability. The Oracle BI components are configured to use the native clustering, load balancing and failover mechanisms. BI components are deployed in

active-active or active-passive mode to maximize availability. Following diagram shows the deployment of Oracle Business Intelligence for high availability.

· A Load Balancer serves as the entry point and load balances Oracle BI web requests to multiple web servers. Two scenarios for the web tier are shown.

· The first scenario shows IIS as the web server with BI Presentation Services Plug-in (ISAPI) deployed.

· The second scenario shows a J2EE based application server with BI Presentation Services Plug-in

deployed in a web container in the J2EE server. Deploying the BI web tier in a De-Militarized

Zone (DMZ) and details on communication over firewalls.

· Multiple instances of BI Presentation Services, BI Servers and BI Scheduler components are installed and the Oracle BI environment is configured for clustering, load balancing and failover Of its components using native capability.

Diagram: Deployment of OBIEE for High Availability

OBIEE Installation

This topic provides general guidelines to help you plan the installation of Oracle BI for high availability.

Determine the number of instances of each Oracle BI component that will form part of the deployment

based on your requirements. The maximum number of BI Servers that can participate in a cluster is 16.

BI Scheduler instances participate in the cluster in an active-passive configuration. Only one BI Scheduler

instance is active and processing requests at a given time; the other instances are passive.

Determine which BI components will be co-located. For example, the Primary Cluster Controller, one BI Server

node and BI Scheduler may be installed on one machine. Identify the machines on which you will deploy the

Oracle BI components. Identify a shared network location for the Presentation Catalog, Repository Publishing Directory, Cluster-Aware Cache and Scheduler Scripts.

For deployments on Windows, identify a Domain account under which the BI services will run. This

Domain account must also have the Log on as a service right. Refer to the appendix Granting the

Oracle BI Log On as Service Right for procedures to perform the task of granting the Log on as a

service right based on your specific Windows platform.

Note the following requirements:

· All BI Servers participating in the cluster need to be within the same domain and on the same

LAN subnet. Geographically separated computers are not supported.

· The clock on each server participating in a cluster must be kept in synchronization. Out-of-sync

clocks can skew reporting.

Installing Oracle BI Cluster Controllers

Install Oracle BI Cluster Controller on the machines that you have identified to host this component by

following the installation steps identified in this topic. For high availability, install two instances of Cluster

Controller on different machines. One instance will serve as the Primary Cluster Controller; the other as the Secondary Cluster Controller.

Oracle BI components are installed using the Oracle Business Intelligence Installer:

1. For Oracle BI Cluster controller, select either Basic or Advanced installation type, depending on

deployment

2. For Oracle BI Cluster controller, select the setup type Custom:

· Select the feature Oracle Business Intelligence Cluster Controller for installation.

If you are co-locating other BI components on this machine (for example, BI Server, BI

Scheduler, or both) select the other desired components for installation.

3. On Windows machines, the Oracle BI Services screen is part of the installation.

· Enter a domain account to run the BI services. Do not specify a Local System account.

· Select the start up type for the services—either manual or automatic.

Installing Oracle BI Server

Install BI Server instances on each of the machines that you have identified to host this component by following

the installation steps identified in this topic. You can install a maximum of 16 BI Servers to participate in the BI Cluster.

Oracle BI components are installed using the Oracle Business Intelligence Installer:

1) For Oracle BI Server, select either Basic or Advanced installation type, depending on your

Deployment

2) For Oracle BI Server, select the setup type Custom.

· Select the feature Oracle Business Intelligence Server for installation.

· If you are co-locating other BI components on this machine (for example, BI Scheduler)

select the other desired components for installation.

3) On Windows machines, the Oracle BI Services screen is part of the installation.

· Enter a domain account to run the BI services. Do not specify a Local System account.

· Select the start up type for the services—either manual or automatic.

The installed BI Servers will be configured to participate in the BI Cluster by setting parameters in

NQSConfig.INI and NQClusterConfig.INI files on each machine that hosts the BI Server.

Installing OBIEE Scheduler

Install BI Scheduler instances on each of the machines that you have identified to host this component by

following the installation steps identified in this topic. The BI Scheduler component participates in the BI Cluster

in active-passive mode. Install BI Scheduler on two machines, one will be identified as the active node and the

other as the passive node.

Oracle BI components are installed using the Oracle Business Intelligence Installer:

1) For Oracle BI Scheduler, select either Basic or Advanced installation type, depending on your

deployment

.

2) For Oracle BI Scheduler, select the setup type Custom.

· Select the feature Oracle Business Intelligence Scheduler for installation.

· If you are co-locating other BI components on this machine (for example, BI Server)

Select the other desired components for installation.

3) On Windows machines, the Oracle BI Services screen is part of the installation.

· Enter a domain account to run the BI services. Do not specify a LocalSystem account.

· Select the start up type for the services—either manual or automatic.

4) Complete the additional configuration steps.

The installed BI Scheduler instances will be configured to participate in the BI Cluster.

Installing Oracle BI Presentation Services and Oracle BI Javahost

Install BI Presentation Services and BI Javahost on each of the machines that you have identified to

host these components following their installation.

In Oracle BI Presentation Services, select either Basic or Advanced installation type, depending

on your deployment.

· For Oracle BI Presentation Services, select the setup type Custom.

1) Select the feature Oracle Business Intelligence Presentation Services for installation.

2) If you are co-locating other BI components on this machine (for example, BI Presentation

Services Plug-in) select the other desired components for installation.

· On Windows machines, the Oracle BI Services screen is part of the installation.

1) Enter a domain account to run the BI services. Do not specify a Local System Account.

2) Select the start up type for the services—either manual or automatic.

The installed BI Presentation Services instances are configured to participate in the BI Cluster by setting parameters in the instanceconfig.xml file on each machine that hosts BI Presentation Services.

OBIEE Shared Files and Directories in Cluster Mode.

The BI components deployed in a clustered environment must share certain files and directories as described below. A shared storage device such as NAS or SAN may be used.

Presentation Catalog:

Create a network share for the Presentation Catalog. All instances of BI Presentation Services in

the cluster must have read and write access to this share.

· Place the Presentation Catalog on the network share.

· Reference the shared Presentation Catalog as \\FSHOST\OracleBIData\web\catalog\Catalog Name,

where “CatalogName” an example of the catalog name.

Repository Publishing Directory

· Create a shared directory for the Repository Publishing Directory. The Master BI Server must

have read and write access to this directory. All other BI Servers must have read access.

· Reference the shared network share for the Repository Publishing Directory

as \\FSHOST\OracleBIData\ClusterRpd.

Cluster-Aware Cache

· Create a shared directory for the global cache. All BI Servers must have read and write access

to this directory.

· Reference the shared global cache as \\FS-HOST\OracleBIData\ClusterCache.

Scheduler Scripts

Create network shares for the Scheduler scripts. The Scheduler servers must have read and write

access to this share.

· Reference the shared Scheduler scripts as \\FS_HOST\OracleBI\Server\Scripts\Common and

\\FS_HOST\OracleBI\Server\Scripts\Scheduler.

· Copy default and custom Scheduler scripts from to the corresponding network shares created for

the Scheduler scripts.

Configuration of OBIEE Components for Clustering, Load Balancing, and Failover

Use the procedures in this topic to configure the Oracle BI components for clustering, load balancing and failover.

Pre-Configuration Summary Tasks

Before configuring the Oracle BI components for clustering and load balancing, perform the following tasks:

· Identify the Cluster Controller to serve as the Primary Cluster Controller.

For example, BI-Server01.

· Identify the Cluster Controller instance to serve as the Secondary Cluster Controller. For

example, BI-Server02.

· Identify the BI Server instance to serve as the Master BI Server. For example, BI-SERVER-01.

· Copy the repository file (RPD) to the machines hosting the BI Servers.

1) The repository file must be copied to OracleBI_HOME\server\Repository.

2) On Linux or UNIX, the file must be copied to OracleBI_HOME/server/Repository.

· Before performing the configurations, shut down all BI services or processes. Restart the BI services or processes after configuration is complete.

Setting Parameters in the NQSConfig.INI File

This topic shows how to use the NQSConfig.INI file to configure your deployment. The NQSConfig.INI file is

located in following folders:

· Windows: OracleBI\server\Config

· Linux or UNIX: OracleBI/server/Config

Change parameters in the NQSConfig.INI file:

1) Open the NQSConfig.INI file for editing.

2) In the Repository section of the NQSConfig.INI, define your repository by setting the logical

Repository name and file name pair. For example: Star = < Custom rpd filename>, DEFAULT;

3) Set Cache parameters. Caching is enabled by default.

To use the cluster-aware caching capability, set the cluster-aware cache parameters for the BI

Sever. In the Query Result Cache section of the NQSConfig.INI file, uncomment and set the

following parameters:

· GLOBAL_CACHE_STORAGE_PATH. Set "<path to shared storage for cache>" and <Size>.

· MAX_GLOBAL_CACHE_ENTRIES. Set <Max number of entries>.

· CACHE_POLL_SECONDS. Set <Polling interval in seconds>.

4) In the Server Section, uncomment the parameter CLUSTER_PARTICIPANT and set it to YES.

5) When the BI Server is a cluster participant, comment out the parameter

SERVER_HOSTNAME_OR_IP_ADDRESSES = "ALLNICS";

6) Set the parameter RPC_SERVICE_OR_PORT to the desired port that the BI Server will listen on. The default port number is 9703.

7) In order for online modifications to be made to the repository, uncomment and set the following

Parameters:

· REPOSITORY_PUBLISHING_DIRECTORY. Set "<path to shared network location>".

· REQUIRE_PUBLISHING_DIRECTORY. Set to YES.

Here is the example:

[ REPOSITORY ]

Star = OBIEE_Custom.rpd, DEFAULT;

[ CACHE ]

ENABLE = YES:

// Cluster-aware cache

GLOBAL_CACHE_STORAGE_PATH = "\\FS-HOST\OracleBI\ClusterCache" 700 MB;

MAX_GLOBAL_CACHE_ENTRIES = 1000;

CACHE_POLL_SECONDS = 300;

CLUSTER_AWARE_CACHE_LOGGING = NO;

[ SERVER ]

# SERVER_HOSTNAME_OR_IP_ADDRESSES = "ALLNICS"

CLUSTER_PARTICIPANT = YES;

REPOSITORY_PUBLISHING_DIRECTORY = "\\FS-HOST\OracleBIData\ClusterRpd";

REQUIRE_PUBLISHING_DIRECTORY = YES;

Setting Parameters in the NQClusterConfig.INI File

This topic shows how to use the NQSClusterConfig.INI file to configure your deployment. The NQSClusterConfig.INI file in following folders:

Windows: OracleBI\server\Config

Linux or UNIX: OracleBI/server/Config

Change parameters in the NQClusterConfig.INI file:

· Open the NQClusterConfig.INI file for editing.

· Change the parameter ENABLE_CONTROLLER to YES to enable clustering.

· Identify the Primary and Secondary Cluster Controllers:

1) Change the parameter PRIMARY_CONTROLLER to the machine hosting the Primary Cluster

Controller.

2) Change the parameter SECONDARY_CONTROLLER to the machine hosting the Secondary Cluster

Controller.

· Set the parameter SERVERS by entering a comma-separated list of the BI Server hostnames.

· Set the parameter MASTER_SERVER by entering the hostname of the Master BI Server machine.

· Set the parameter SCHEDULERS for the Scheduler servers participating in the cluster.

SCHEDULERS = "sch_scheduler1:<rpc port>:<monitor port>", "sch_scheduler2:<rpc

port>:<monitor port>"; Default Port is 9705

Here is the sample nqclusterconfig.ini file:

[Cluster]

ENABLE_CONTROLLER = YES;

PRIMARY_CONTROLLER = "BI-CCS-01";

SECONDARY_CONTROLLER = "BI-CCS-02";

SERVERS = "BI-SERVER-01","BI-SERVER-02";

MASTER_SERVER = "BI-SERVER-01";

SERVER_POLL_SECONDS = 5;

CONTROLLER_POLL_SECONDS = 5;

CLIENT_SERVER_PORT = 9703;

CLIENT_CONTROLLER_PORT = 9706;

MONITOR_CONTROLLER_PORT = 9700;

MONITOR_SERVER_PORT = 9701;

SCHEDULERS = "BI-SCHEDULER-01:9705:9708","BI-SCHEDULER-02:9705:9708";

Configuring BI Presentation Services

Oracle BI Presentation Services is configured by setting parameters in the configuration file instanceconfig.xml. The instanceconfig.XML file is located in the following directory:

Windows: OracleBIData_HOME\web\config

Linux or UNIX: OracleBIData_HOME/web/config

Use the following procedure to configure BI Presentation Services on each machine that hosts BI Presentation Services:

· Open the configuration file instanceconfig.xml for editing.

· Locate the <Alerts> element. Configure for communication with the clustered Scheduler instances

in Section Alerts:

“<Alerts>

<ScheduleServer

ccsPrimary="BI-Server01" ccsPrimaryPort="9706" ccsSecondary="BI-Server02"

ccsSecondaryPort="9706"/>

</Alerts>”

· Under the ServerInstance tag, create the JavaHostProxy element.

· Set the JavaHostProxy element attributes and values to point to the Javahost cluster:

</ServerInstance>

<JavaHostProxy>

<Hosts>

<Host address="BI-Server01" port="9810" />

<Host address="BI-Server02" port="9810" />

</Hosts>

</JavaHostProxy>

</ServerInstance>

Where BI-Server01 and BI-Server02 are the machines that host the BI Javahost component. BI Javahost

was installed along with the installation of BI Presentation Services.

The Hosts element contains Host sub-elements that identify the Javahost and port pairs. (The default Javahost

port is 9810.)

1) Use the values in the Port element in the config.xml file on the machine where Javahost

is installed.

2) The config.xml file is located in OracleBI_HOME\web\javahost\config (Windows) and

OracleBI_HOME/web/javahost/config (Linux).

· Modify the <CatalogPath> element to point to the shared Presentation Catalog:

<CatalogPath>\\FS-HOST\OracleBIData\web\catalog\customCatalog</CatalogPath>

· Under the ServerInstance element, create the Catalog sub-element with the following attributes

and values:

<Catalog>

<AccountIndexRefreshSecs>120</AccountIndexRefreshSecs>

<AccountCacheTimeoutSecs>180</AccountCacheTimeoutSecs>

<CacheTimeoutSecs>120</CacheTimeoutSecs>

<CacheCleanupSecs>600</CacheCleanupSecs>

<PrivilegeCacheTimeoutSecs>180</PrivilegeCacheTimeoutSecs>

</Catalog>

These settings manage when BI Presentation Services cache is updated from disk in environments with multiple BI Presentation Services instances.

· Save changes to the file.

Configuring BI Scheduler on Windows

Use the BI Scheduler Job Manager to configure Scheduler for participation in a BI cluster.

Here are the steps:

· From the Windows Start menu select Programs > Oracle Business Intelligence > Job Manager.

· In Job Manager, select File > Configuration Options

· In the Scheduler > Advanced tab of the Scheduler Configuration window, check the "Participant

in Cluster" check box. The Cluster Monitor Port defaults to 9708. Change this port number as

needed.

· In the Scheduler > General tab, set the Scheduler Script Path and Default Script Path to network

shares.

Scheduler Script Path = \\FS-HOST\OracleBI\server\Scripts\Scheduler

Default Script Path = \\FS-HOST\OracleBI\server\Scripts\Common

· In the iBots tab of the Scheduler Configuration window, provide a comma-separated list of the

BI Presentation Services instances. OBI Presentation Server = BI-Server01:9710, BI-Server02:9710

where 9710 is the default port on which BI Presentation Services listens to RPC calls.

· In the Java Extension tab of the Scheduler Configuration window, provide the comma-separated

list of BI Javahost instances. Java Host Servers = BI-Server01:9810, BI-Server02:9810

Default Javahost port is ‘9810’.

Configuring BI Scheduler on Linux or UNIX

The Scheduler configuration options are set using schconfig, a console-based application. On the machines

where Scheduler instances are installed, in the directory OracleBI_HOME/setup, run the command schconfig:

· . sa-init.sh

· schconfig

Here are the steps:

· From the Delivers Configuration choices that appear, select 1 - Configure Scheduler.

· For each Scheduler Configuration Menu choice shown in the following table, select the listed

parameter and configure as shown.

· Select 0 to quit and save changes when prompted.

· Select 0 to quit the utility

Configuring BI Presentation Services Using J2EE

Follow the steps in this procedure to configure BI Presentation Services Plug-in to communicate with

the multiple BI Presentation Services instances. For the Java servlet, configure the Plug-in in the web.xml file,

located in following directory:

Windows: ORACLE_HOME\j2ee\bianalytics\applications\analytics\analytics\WEB-INF

Linux or UNIX: ORACLE_HOME/j2ee/bianalytics/applications/analytics/analytics/WEB-INF

Here are the steps:

· Open the web.xml file for editing.

· Locate the following param-name and param value pairs:

<init-param>

<param-name>oracle.bi.presentation.Sawservers</param-name>

<param-value>BI-Server01:9710;BI-Server02:9710</param-value>

</init-param>

· Save changes to the file.

· Copy the web.xml file to OracleBI_HOME\web\app\WEB-INF on Windows and to OracleBI_HOME/

web/app/WEB-INF on Linux.

· Restart your Java Servlet container.

Configuring BI Presentation Services Using IIS

Microsoft IIS, configure the Plug-in in the isapiconfig.xml file, located in the directory OracleBIData_HOME\web\config.

Here are the steps:

· Open the isapiconfig.xml file for editing.

· Locate the entry similar to the following:

<ServerConnectInfo address="localhost" port="9710"/>

· Replace this entry with the following lines:

<ServerConnectInfo>

<LoadBalancer autoRoute="true"/>

<Hosts>

<Host address="BI-Server01" port="9710"/>

<Host address="BI-Server02" port="9710"/>|

</Hosts>

</ServerConnectInfo>

· Save changes to the file.

· Restart IIS.

Modifying the BI ODBC Data Source under Windows

By default, the BI ODBC Data Source Name (DSN) is AnalyticsWeb. The DSN is modified using the

ODBC Data Source Administrator control panel, as shown in the following procedure. Default Port is 9706

Here are the steps:

· On the ODBC Data Source Administrator Systems DSN tab, select the AnalyticsWeb DSN.

· Click the Configure button to open the Oracle BI Server DSN Configuration window.

· Check the box "Is this a clustered DSN."

· In the Primary Controller text box, enter the name of the Primary Cluster Controller:

BI-Server01

· In the Secondary Controller text box, enter the name of the Secondary Cluster Controller:

BI-Server02

· Set the Controller Port field as appropriate.

Modifying the odbc.ini file under Linux/UNIX

On Linux and UNIX machines, the odbc.ini file is located in the OracleBI_HOME/setup directory. Default Port is 9706

Here are the steps:

· Open the odbc.ini file for editing.

· Make the following modifications:

IsClusteredDSN=Yes

PrimaryCCS=BI-Server01

PrimaryCCSPort=9706

SecondaryCCS=BI-Server02

SecondaryCCSPort=9706

Regional=No

· Save changes to the file.

UDML Techniques for OBI

UDML is a text based non-public API that may be used to manipulate RPD

This may be used for faster manipulation to metadata objects and create a new instance of RPD especially for creating instances of rpd during migration processes

There are two ways of using UDML

1. Command line utilities
2. Text editor

Using Command Line Utilities

There are two command line utilities that are available to use which are available as a part of installed OBI server component. These are located in Root:\OracleBI\server\Bin directory
1.nQUDMLGen.exe

This is used to generate UDML from an RPD

The following is a example of passing parameters to use this utility

D:\OracleBI\server\Bin>nQUDMLGen.exe -h
nQUDMLGen -U userid [-P [password]] -R repository_pathname -O output_script_pathname[-8] [-N] [-Q] [-S]
-h Display this usage information and exit.
-8 is for UTF-8
-N is for not generating upgrade id
-Q is for generating script without security objects
-S is for generating script for only security objects
Q and S override each other if both are present

2. nQUDMLExec.exe

This is used to execute UDML

The following is a example of passing parameters to use this utility

D:\OracleBI\server\Bin>nQUDMLExec.exe -h
nQUDMLExec [-U [userid]] [-P [password]] -I input_script_pathname[-B base_repository_pathname] -O output_repository_pathname [-8]
-8 is for UTF-8
Eg 1: nQUDMLExec -I sampletestudml.txt -O rp1.rpd create a new repository rp1
Eg 2: nQUDMLExec -U administrator -I sampletestudml.txt -B rp1.rpd -O rp2.rpd
modify rp1 and write to rp2

-h Display this usage information and exit

Copying UDML to a Text Editor

1. Right click on a selected RPD object, Copy and Paste to a notepad













2. Make the required changes and save as .udml in Root:\OracleBI\server\Scripts directory

















Let's see a simple UDML to change connection pool settings

The UDML file named test.udml has the following content # TEST.udml - Hashed PASSWORD derived by manually entering the # appropriate PASSWORD through Admin Tool and extracting UDML code DECLARE CONNECTION POOL "Oracle Database"."Connection Pool" AS "Connection Pool" UPGRADE ID 2150315609 DATA SOURCE {TEST_DSN} TIME OUT 300 MAX CONNECTIONS 10 TYPE 'Default' USER 'TEST_USER' PASSWORD 'D7EDED84BC624A917F5B462A4DCA05CDCE256EEEEEDC97D5D1CC182AFED63DC0C01716548C936275' SHARED LOGIN CONNECTIONS TO SAME URI 10 OUTPUT TYPE XML HEADER PATH {d:\\OracleBI\\server\\config\\NQSQueryHeader.xml} TRAILER PATH {d:\\OracleBI\\server\\config\\NQSQueryTrailer.xml} BULK INSERT BUFFER SIZE 32768 TRANSACTION BOUNDARY 10 TEMP TABLE PREFIX {TT} OWNER {} PRIVILEGES ( READ);

The following may be used to execute the UDML that updates Connection Pool from DEV.rpd and create TEST.rpd

nQUDMLExec.exe -U Administrator -P SADMIN -I D:\OracleBI\server\scripts\TEST.udml-B D:\OracleBI\server\Repository\DEV.rpd -O D:\OracleBI\server\Repository\TEST.rpd

FLASHBACK 10 YRS AGO: "nQuire Rolls Out Internet Software"

Minnesota-based nQuire Software began to offer a business intelligence suite of products in late 1999. Siebel aquired nQuire in 2001, and Oracle aquired Siebel in 2006. Here is an article published 10 years ago announcing the launch of nQuire eBusiness Analytics which became Siebel Analytics and is now known as Oracle Business Intelligence Enterprise Edition...

Fri. Aug. 27, 1999
From the August 27, 1999 issue of CRN

nQuire Software Inc., a start-up focused on enterprise data access, unveiled its first product, a search engine and related suite of software for accessing and analyzing structured data from disparate internal systems.

"The average person can go home and pull up on the Web all kinds of information, but they go to work and can't even get adequate sales data for a given period because it resides on different platforms," said Larry Barbetta, president and chief executive of nQuire.

The nQuire Suite addresses this issue and provides users with self-service access and analysis of the data stored across various operational systems, data warehouses, datamarts, relational databases and XML-based sources, Barbetta said.

In addition, the nQuire technology enables organizations to deliver better service both inside and outside their corporate walls, Barbetta said. The technology can be used to power enterprise information portals, corporate intranets, business-to-business commerce and Internet customer access, he said.

"The Internet has raised the bar of people's expectations," Barbetta said. However, "the Internet has affected consumers, but it really hasn't been hammered home in corporate space to the degree that it has impacted their computing structure," he said, adding that corporations still have trouble accessing and analyzing information that resides on their internal systems.

"The ability to hide the complexity of data analysis requires an enormous amount of technical savvy," said Bob Moran, vice president of decision support research at the Aberdeen Group Inc., Boston. "It is to nQuire Software's credit that it has been able to accomplish this feat in an Internet/portal context, without requiring enterprises to undergo a disruptive change," he said.

"Delivering accurate and comprehensive information from multiple applications and services has been a challenge for business intelligence," said Hadley Reynolds, research director at The Delphi Group, Boston. "nQuire's multi-repository search capability promises to break down the barriers associated with application and warehouse boundaries, and give portal users a substantially clearer discovery channel for knowledge management or E-business activities."

The nQuire server features data access techniques coupled with a scalable, high-performance engine capable of performing business calculations, joining, merging, filtering, aggregating and integrating data, Barbetta said.

The nQuire Suite also provides thin-client, browser-based access to the nQuire Server, as well as support for existing reporting, online application processing, query, and development tools and applications capable of querying a relational database management system, the company said.

The browser enables non-technical users to access buried corporate information. "We allow the user to get at the data in the way they think about it, not in the way it's stored," Barbetta said.

Building the company's channel organization is among the first major goals for Duane Cologne, nQuire's vice president of marketing and business development, Barbetta said.

"We plan to leverage our sales model with partnerships with key service providers," he said. "We're talking to both the new-age integrators as well as the 'big dogs,' " he added, referring to the large mainstream integrators.

Before being positioned for the Siebel aquisition, nQuire established several key customer wins at such companies as CSX Transportation, McDonald's, Simon Property Group, and Union Pacific Railroad.

Financial terms of the Siebel acquisition of nQuire were not disclosed. The Oracle acquisition of Siebel was valued at approximately $5.85 billion.

BI Publisher and OBIEE: Integration with Dashboards & Prompts

In this post I will demonstrate another way to "integrate" OBIEE with BI Publisher ("BIP"): adding a pre-built BIP report to a Dashboard and configuring it to interact with a Dashboard Prompt.

For this example I've already logged into BIP as "Administrator" and created a report named "BI Publisher Subject Area" including some simple dimensions and facts from the "Paint Exec" demo Subject Area. I've uploaded a simple table template aptly named "SubjectAreaTable" using Word to this report. The resulting report viewed in HTML looks like this:

Step 1: Add a BIP Report to Dashboard

From a blank Dashboard page, drag & drop "BI Publisher Report" object into the blank Column. Click "Properties" then "Browse" to the location of the existing BIP report "BI Publisher Request." (Note what happens when you "hover" over the report name - that's the value of the report's Description field.)



Click on the report name and then OK. Click OK again in the Properties window (accept the default parameters for now). Save your changes then view the new Dashboard. Note that the standard BIP controls are available - you can select a Template & format and even choose to deliver the output using the standard BIP options (which are a bit different from OBIEE's options).

Step 2: Configure the BIP Report to respond to a Parameter for "Region"

Now switch to BI Publisher (More Products > BI Publisher). Navigate to "My Folders," then click the "Edit" link under the "BI Publisher Request" report.

Click the "Parameters" option in the left-hand nav, then click "New" to create a new parameters.



Fill in the values as follows:

Identifier: "REGION_NAME"

This value is fairly arbitrary, but as you will see below we will be referencing it in Answers so it doesn't hurt to consider some sort of naming convention. I am also deliberately making the identifier different than the name of the Presentation Column in Answers to clarify the distinction between the two.

Data type: "String"

I've had spotty results using anything but "String" -- Date in particular gave me trouble.

Parameter type: "Text"

There are some interesting uses for other types, but we'll save that discussion for later.

Display Label: "Region Name"

This value can also be arbitrary

Text Field Size: [leave blank]
Options: [leave blank]

Both of these are also out of scope for this discussion.

Now we click into the Data Set (within the Data Model "folder") and add the line "WHERE Markets.Region = :REGION_NAME" to the SQL Query input box. Note we are using the "Identifier" of the Parameter we just created.



Save your changes (click the disk icon in the upper LH corner) and view the results - Note the results by default will be blank because we haven't entered a value. Enter "CENTRAL REGION" in the parameter box and click "View."



Step 3: Add a Prompt to your dashboard

Go back to Answers and create a new Dashboard Prompt based on the "Region" Presentation Column. Here's the important part: Configure the prompt to set a Presentation Variable named - you guessed it - "REGION_NAME" and save your new prompt.




OK last step: Still in Answers, go back to modify your Dashboard and add the new prompt. Might as well put it on top of the existing BI Publisher report.


Save your changes and view the Dashboard. Again, because the default value for Region is blank, you won't get any results unless you pick one from your new prompt.



But, uh oh, how can I select ALL regions? The "All Choices" selection doesn't seem to work the same way with BI Publisher as it does with native OBIEE reports...

Hmm. How about I give a banana to whomever can chime in with the solution to THAT problem?


Remember that reports in BI Publisher can be created against other data sources besides OBIEE... but the Dashboard integration is essentially the same. I'll save that topic for another post.

TTFN

Marketing Server Optimization on Teradata

The purpose of this blog is to help and understand the techniques that may be implemented for optimizing Marketing Server

Let us understand some marketing metadata terms before we get into steps for implementing the optimizing techniques

1. Target Level A Target Level is the entity that a marketer is interested in counting. Target Levels are usually customer types such as individuals, businesses, or households

2. Segmentation Catalog A segmentation catalog is a Oracle BI subject area (presentation catalog) that is enabled for segmentation. The segmentation catalog provides a set of dimensions and facts that can be used to create segment criteria

3. Sampling Factor Sampling is a subset table for a target level dimension table that contains the same set of columns, but only a percentage of the data

4. List Catalog A list catalog is a Oracle BI subject area (presentation catalog) that is enabled for List Format design (list generation)

5. Qualified List Item
A Qualified List Item is entity that is evaluated against segment criteria so that the information related to that entity can be exported in a list file

6. Caching
Segmentation criteria blocks that count target level identifiers may be used by a user frequently

7. Saved Result Sets
The resulting set of target level identifiers of complex segmentation criteria may be saved permanently

Lets us look at the steps to enable sampling for a Target Level

The purpose to enable sampling is to improve user response time of running Marketing queries for segments and segment trees. While creating and debugging segments and segment trees, running the queries would take a long time to complete if the entire data set is used. Use sampling for these purposes to decrease the query completion time because a smaller subset of the data set is used.

1. Create physical sampling tables
2. Setup session variable
3. Map sampling tables to Target Level

Create Physical Sampling Tables

Sampling must be enabled at a Target level

• First identify the desired target level and sampling factoro For this example the desired Target Level to be sampled is Contacts
• Generate DDLs for all physical dimension and fact tables to be sampled Example : CREATE TABLE AS ( SELECT * FROM SAMPLE RANDOMIZED ALLOCATION ) WITH DATA
• Rename the physical table and index names to include the target level and sampling factor. For example, if the original table name was W_PERSON_D. A sampled table name for a 10% sample of Contacts could be M_10C_PERSON_D
• Populate M_10C_PERSON_D with 10% of W_PERSON_D.
• Cascade the 10% Contacts join relationship to the remaining sampled tables. For example, to populate a campaign history sample table, you would include all fact records that join to the 10% sample of Contact records

Map the sampling tables into the physical layer of metadata

Setup Session Variables

Select Manage > Variables > Initialization Block > New Initialization Block

1. Only one initialization block needs to be setup to enable sampling eg: Marketing Sampling Tables with session variables associated. The session variables must be created with Default Initializer as the name of target level table







2. Click Manage--> Marketing and then Double Click on the Targeting Level “Customers”. Click on the “Sampling Tables” tab
3. Click on the “Add” button.Fill in the name Sampled Physical table in the “Sample Physical Table Name” field. In the “Repository Table Object” field, fill in the name of the Target Level Table. In the “Factor” field, fill in the number that represents the following calculation: (# of rows in the sample table *100)/# of rows in the Target Level Table.Repeat this step for every sampling table for this Target Level table








DDL for Marketing Cache tables in Teradata

Create the Marketing cache tables for each Target Level using the following DDL to improve poor array insert performance into Teradata and its ODBC driver. The schema scripts are in Root Drive:\OracleBI\server\Schema path
CREATE MULTISET TABLE M_C_CONTACT ( GUID varchar(50) NOT NULL, QUALIFIED_ID varchar(30) NOT NULL ) PRIMARY INDEX (GUID, QUALIFIED_ID);

Model Cache Tables in RPD

Import the created cache physical tables into the physical layer. This table would not have any physical joins







Create the logical model of the cache table as Fact - Marketing - Segmentation Person Cache with M_C_CONTACT source table renamed as MKTG_CACHE_PERSON and QUALIFIED_ID column renamed as Person ID







Create complex join for this table to Dim-Contact








Create a presentation catalog Marketing Segmentation Cache and Saved Results and expose these columns into - Contact Cache sub folder








Create Save Result Set Table DDL for Teradata

If the saved result set feature is to be used, create the Marketing saved result set tables for each Target Level using the following DDL for improved performance in Teradata. The script is under same path as specified above
CREATE MULTISET TABLE M_SR_CONTACT ( GUID varchar(50) NOT NULL, TARGET_LEVEL_ID varchar(30) NOT NULL ) PRIMARY INDEX (GUID, TARGET_LEVEL_ID);

Model Save Result Set Tables in RPD

Follow similar steps as above to model the M_SR_CONTACT table aliased as M_SR_HEADER(Contact)





Associate the above mapping to the target level in RPD

1. Click Manage--> Marketing

2. Double click on Contacts QLI







3. Click on Cache Information Tab













4. Click on Cache Catalog ellipsis button and map to the Marketing Segmentation Cache and Saved Results presentation catalog

5. Similarly map GUID, Qualified ID presentation columns

6. Click on Default SQL button to populate the Physical SQL query

7. When a criteria block is cached, the populate stored procedure is used to write the cache/saved result set to the database. All segmentation users/groups need to be given this privilege via Manage Security->Users/Groups->Permissions->Query Limits->Populate Privilege, set to ‘Allow’
8. Under Manage--> Marketing. Click on Target Levels in the left pane and double click on Contacts Target Level








9. Select Saved Results Tab and add the names for Saved Results catalog,GUID Column, Target ID Column , Physical table Name, Connection Pool Name as in below figure















10. Click on Default SQL’s button to populate SQLs

Enable Caching for a Target Level

The purpose of these steps is to correctly setup caching to improve poor array insert performance into Teradata and its ODBC driver.

Set Teradata Database Physical Repository Object Properties

- Double-click the Teradata database physical repository object
- Select the Features tab
- Add the following value for MAX_PARAMETERS_PER_BULK_OPERATION = 128














Set Connection Pool Object for Selects from Teradata

- Each database physical repository object has 1 connection pool object defined by default
- This default connection pool object will be used for selects
- Set the Isolation Level to Dirty Read
- Select the Write Back tab- Buffer Size (KB) = 64
- Transaction Boundary = 20 – 50

The Transaction Boundary range stated above provides a general estimate of the value that Will yield the best array insertion throughput. Setting the Transaction Boundary within the range should generate about 650 records per second. The best Transaction Boundary value will be a function of the Teradata system used and its configuration










Following the same steps above create Connection Pool Object used for Inserting into Cache Tables in Teradata

- Add a new connection pool object to the Teradata database physical repository object
- Set the new connection pool object’s data source to the same data source used in the select

Connection pool

- Set the Isolation Level to Default

Enable Marketing Server to use Temp Table Caching

Once the caching is setup for Target Levels according to the previous section, enable Marketing Server to use temp table caching to avoid potential Teradata deadlock errors while inserting into the cache tables.

1. Edit /Web/config/instanceconfig.xml

2. Add the following to the instanceconfig.xml within the ServerInstance tag:

CREATE MULTISET TABLE @{TempTableName} (GUID varchar(50) NOT NULL, QUALIFIED_ID varchar(30) NOT NULL) PRIMARY INDEX (GUID, QUALIFIED_ID) INSERT INTO @{PhysicalTableName} (GUID,QUALIFIED_ID) Select GUID, QUALIFIED_ID from @{TempTableName} DROP TABLE @{TempTableName}
Important Note:

• The SQL used must be valid SQL for the database the Marketing Server is using which is Teradata in this case
• Use @{TempTableName} in the MktgCacheTempCreateSQL tag as a generic temp cache table variable name for the temp cache table DDL
• Use @{TempTableName} in the MktgCacheTempDropSQL tag as a generic temp cache table variable name for the drop temp table SQL
• Use @{TempTableName} in the MktgCacheTempInsertSQL tag as a generic temp cache table variable name for the insert-into-select SQL• Use @{PhysicalTableName} in the MktgCacheTempInsertSQL tag as a generic physical cache table variable name for the insert-into-select SQL
• The DDL specified in the MktgCacheTempCreateSQL tag must match the Marketing cache table DDL
• The column names specified in the insert-into-select SQL in the MktgCacheTempInsertSQL tag must be consistent with the columns in the cache table and temp tables

3. Save instanceconfig.xml

4. Start OracleBI Presentation Services

Bloomington, MN Workshop Highlights

BI Consulting Group co-hosted a hands-on workshop with Oracle in Bloomington, MN on June 12. The hands-on workshop featured customers who have purchased OBIEE or are evaluating purchasing OBIEE.

Oracle presented an overview of OBIEE and BICG presesnted the hands-on portion of the day where students were able to get hands-on experience with Answers. Students learned a lot about how to build Answers queries and how to apply best practices to Answers and dashboards.

For info on workshops and ones that are coming to a city near you, please visit:

http://www.biconsultinggroup.com/aboutus.asp?CategoryID=373

Hidden Dashboard Tips

We tend to get comfortable building OBIEE applications in a certain way.

If you have some free time, you might want to try out some of the options on the dashboard builder.

Guided Navigation in a Section and Arrange Horizontally may come in handy for instance.

There have been times when I felt that the dashboard editor allowed for too much whitespace between sections.

This got me thinking so I started exploring some options.

I had never used arrange horizontally so I thought I would give it a try.

Here is a sample of a dashboard with some embedded content and an OBIEE answers compound view in another section and column.

So if you take a look at the first column where we have the embedded URLs, you might want to slide out the video section and place it between the report and the Google search.

Notice how it swings the video right beside the embedded browser with very little whitespace.

This will certainly come in handy when formatting your dashboards for the widescreen monitors.

Next is the Guided Navigation choice at the section level. You can have entire dashboard sections appear and disappear depending on the results of an answers request. It works just like a Guided Navigation Link, only at the section level.

Take a look.

The following setting will SHOW the entire dashboard SECTION only if the request comes up empty. This allows you to show a section only when something is missing in the data. This technique can be used as an alerting mechanism.

Sometimes it pays off to explore options within the OBIEE suite if you can find a little extra time or if you are just plain curious ! I know there are some other handy little morsels splashed about in this software. If you find one, please share it with us.