OBIEE and BPEL

In the BI community we know that the pervasive adaptation of Business Intelligence technology can have a potentially transformative effect on a company's performance, especially when BI is applied with the philosophy of "enabling action from insight." One way OBIEE currently enables action is by providing links from reports directly to the transactional source system for that data so that the end user can take action on transactions related to that data. With OBIEE 11g, Oracle will be bringing actionable insight an evolutionary jump forward with the ability to integrate tightly with BPEL Process Manager.

In this post I'd like to introduce the basic concept behind BPEL and explain its importance in the context of OBIEE.

First it's important to highlight the distinction between transactions and processes. Transactions are important. They are the lifeblood of a busienss. But we all know that a business isn't just a collection of isolated transactions. Instead, we work with processes: For example, if the inventory level for Product X hits a certain threshold, someone has to make a decision about whether to reorder that item. The responsible party typically has one or more process she follows before she takes a business action and these processes are often comprised of both human and systems activities.

Historically the primary role of IT has simply been to support the "tech" side of a business in isolation from the processes that the tech supports. Consequently there has arisen a kind of division -- too often hostility -- between IT and the business it serves. One manifestation of this divide happens when attempting to keep any sort of process documentation: so often, as soon as the documentation is complete it is rendered obsolete. Any kind of documented understanding of the process has little relevance to the process itself, almost to the point that the process Once the documentation is complete.

But as tech becomes more tightly woven into the conduct of our activities, the complexity of the human/tech interactions in any given business process -- and the speed with which these interactions can change -- has likewise grown. Smartly managing and supporting these processes requires a healthy understanding of how the whole system interacts.

Enter BPEL - Business Process Execution Language - an XML-based language developed to describe in a systematic way the orchestration of steps within a business process. It combines the more-or-less "human readable" qualities of XML with the rigor of a real programming language. With BPEL, in a very real sense the processes themselves and the documented description of the processes are one and the same. BPEL is a very powerful way to bridge the business / IT divide: it challenges the business to unambiguously define exactly what they do and it requires IT to implement the tech components to support these activities exactly as described by the business.

Now, BPEL is a unique, "human readable" programming language, but in the end it's just that: a language. It needs a vehicle for executing the instructions defined in the BPEL program. The mechanism for executing these instructions is a BPEL Server, or "Engine." Several BPEL Engine products have been developed over the years, including Oracle's own BPEL Process Manager, which is one component of their SOA Suite.

What does all this have to do with OBIEE? Well imagine the reorder decision scenario above. Instead of simply linking to an inventory management system, OBIEE can let the user initiate the entire reorder evaluation BPEL process directly in BPEL Process Manager - which will in turn invoke other systems and human actions before the final click of the "Reorder" button. Or OBIEE can even invoke the process on its own based on user-defined thresholds, and let the process take its established course.

I have only begun to scratch the surface of exactly what BPEL is or how to use it. I encourage a deeper look into BPEL and the features of Oracle's BPEL Process Manager to truly appreciate the potential application of this technology in a BI implementation. Here are a few good places to start:

Wikipedia entry on BPEL: http://en.wikipedia.org/wiki/Business_Process_Execution_Language

Oracle's BPEL Process Manager http://www.oracle.com/appserver/bpel_home.html

Chapter on Oracle's BPEL Process Manager (from a larger work on BPEL in general)http://www.codeguru.com/cpp/sample_chapter/article.php/c10789

Enjoy...

Public Sector Workshop - Minneapolis, MN

BI Consulting Group co-hosted a hands-on workshop with Oracle in Minneapolis, MN on July 28. The hands-on workshop was geared towards a Public sector audience and featured customers who have purchased Oracle Business Intelligence Enterprise Edition Plus (OBIEE+) or are evaluating purchasing OBIEE+.

BI Consulting Group presented an overview of OBIEE+ as well as dove into the best practices when developing reports and dashboards as well as explained how to build a successful Public Sector BI Strategy. BICG also presented the workshop 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

Deploying OBI Presentation Services on Weblogic

The following are the steps that are required to deploy OBI Presentation Services

1. Create a new WebLogic Server domain using a JDK such as Sun JDK 1.5.x or
higher, in place of JRockit. By opening BEA Weblogic configuration wizard














2. Select Generate a Domain configured automatically

3. Configure the Administrator Username and Password








4. Configure the server start mode and JDK under JDK section













5. Select Yes for customize Environment and Server settings

6. In the Configure Administration Server give a valid name, Listen Address i.e the IP address where the Weblogic Console is hosted, Listen Port








7. Configure Managed Servers. In here specify the name of the OBI Presentation Services Server, Lisen Address which is the IP address, Listen Port typically this may be 7005






8. Skip configure clusters
9. In configure machines add the name of the machine that hosts weblogic server instance






10. Assign server to the machine,review domain configuration then create Weblogic Domain and check for successfull creation of Weblogic Domain









11. Create an exploded archive directory from the analytics.ear or analytics.war file
using the new domain, by following these steps:
a. Copy the analytics.ear or analytics.war file in the \OracleBI\web directory to
a destination directory, as shown in the following example.
-mkdir c:mydestination\BIEE\
-cp c:\OracleBI\web\analytics.war to c:mydestination\BIEE\
b. Manually unpack the analytics.ear or analytics.war file using a jar command,
as shown in the following example.
jar -xvf manual\generic\analytics.war
By running the jar command, you create a directory called "analytics." This is
the directory where the application will be deployed

If the BI Presentation Services Server runs on a different machine from the
WebLogic Server, then edit the web.xml file for the WebLogic Server to account for
this difference.










12. In a Web browser, start the WebLogic Administration Console, as shown in the
following example. http://hostname:7001/console
13. If you have not already done so, in the Change Center of the Administration
Console, click Lock & Edit.












14. In the left pane of the Administration Console, click Deployments.
15. Install the deployment as an application by completing the following steps:
a. In the right pane, click Install.
b. Select c:mydestination\BIEE and click Next.
c. Select Install this deployment as an application and click Next.
d. Select I will make the deployment accessible from the following location for example D:\OBIEE\Deployments\weblogic9\webapps\analytics from Source accessibility in the install application assistant window after selecting the option button for the name of the machine where OBI presentation services are deployed eg: OBIEE01










f. Then select option button for Yes take me to deployment screen, then view all screens clickin Next
g. Click Finish.

15. To activate these changes, in the Change Center of the Administration Console,
click Activate Changes.

16. Start the service by clicking Deployments, then analytics.

Putting Label of the Prompt in Front

If you want to put the Label of the prompts in front of the selection itself (intead of on the top)

1. To do this just for the current dashboard page, add a text frame to your dashboard page and add following content to it
< type="text/css">
td.GFPFilter br { display:none; }
< /style >
Set "Contains HTML Markup" chechkbox.

2. If you want to do this across all pages, edit views.css of the style you are using (S_ directory) and add
td.GFPFilter br { display:none; }

High Level OBIEE report design strategy

So often an organization will take on the task of installing OBIEE, establishing the infrastructure to support it, and even re-organizing their data appropriately. But when it comes to the actual report design, standards fail to be established, reports and dashboards become awkward and cumbersome, and user adoption suffers.

Since OBIEE is often seen as an Ad-Hoc reporting tool, this problem can often be the result of many developers developing with an Ad-Hoc mentality. The result is mismatched dashboards and confusing layouts. Other times, it’s the result of too many people involved in the development process. Once something gets approved by 15 people in a Power Point mock up, it’s difficult to get changes approved when something doesn’t pan out as expected. As a result, the small imperfections get set aside, but never fail to add up.

The following is a high level list of items to keep in mind when entering the report development stage of any OBIEE project. Keeping each of these items in mind will help you design attractive and useful reports and dashboards, which will ultimately lead to greater user adoption and pervasive use of the tool.


1. Trust your users - If your users don’t use your BI or other technology systems, it’s because they either get no value from it, or the system is too slow. These same users will spend their evenings shopping online and paying bills over the internet. It’s ignorant to believe they can’t use a well designed online reporting tool.

2. Think Insight, not Reports - Take the time to understand what the business user searches for, what they mark up with a yellow highlighter, and what they look at next after discovering an “exception”

3. Move Business Skills into your IT shop, not IT skills into your Business – Make an effort to understand the business needs, and then apply the technology accordingly. Don’t ask the business to “make due” with what IT provides.

4. Design your Dashboard in OBI EE itself using an iterative methodology – Don’t use Power Point to design your reports. Designing directly in OBIEE allows you to know immediately what will and will not work. Plan to make a series of adjustments throughout the design process, using feedback from the business users.

5. Establish a set of standards – If your users can’t derive insight in the first few seconds of looking at a Dashboard, it’s a bad design. This is no longer a world in which IT programmers lead the user’s impression of standards. Amazon, Google, Yahoo!, Apple iPhones, etc. all lead the pack when it comes to quality interfaces and standardized interfaces.

Oracle Essbase - Moving Beyond the Finance Department

People sometimes think of Oracle Essbase as only suited for use by the Finance department. While it’s true that Essbase “excels” at this (pun intended), the tool can help almost any business function that uses numeric data. Its ability to analyze data in multidimensional ways allows users to “slice and dice” numbers and gain new insight into what’s happening in their business. Once you see how to do this in one area, why not do the same thing somewhere else? Indeed, the goal should be to increase the value of Essbase in your organization by using it in ways you might not have previously considered. Move it out of the Finance department and make yourself an analytics superstar!

Here are a few areas where savvy users of Oracle Essbase can extend it horizontally across multiple business functions:

• Operations: inventory control, forecast accuracy analysis

• Sales and Marketing: product profitability, marketing spend planning

• HR: compensation trend analysis, timesheet tracking and analysis

• Manufacturing: creating build plans, bill of material (BOM) analysis

• Finance: days sales outstanding (DSO) analysis

Put on your thinking cap, think about how many people outside of Finance use data to accomplish their tasks and come up with Oracle Essbase models to satisfy their needs.

Leveraging Marketing Analytics to Export Large Files

As a part of a typical OBI implementation some of the business requirements may be handle exporting or downloading large files with size of many million records in different file formats like XLS, CSV. This in general may be planned to use the OOTB Download feature in OBIEE. Well if this is the case then there may be huge performance bottlenecks just leveraging on OBI services withing a browser.

Marketing Analtics may be explored to handle this requirements. Marketing Analytics has the feature to manage export of large files with several formats

Lets look at the configuration steps

1. we need to configure some marketing objects within OBI Repository
2. Lets use the OOTB Marketing Objects for Example Contacts
3. Click Manage --> Marketing within an opened OBI Repository to view existing marketing objects

4. Double click on Contacts Target Level, See the primary qualified item added as Contacts

5. In the Segmentation Catalogs Tab. The Customet Profile Segmentation is set as primary segmentaion catalog. In here this customer profile segmentation catalog is actuallya presentation catalog created in OBI Rpd

6. Click on Marketing Contact List List Catalog object which is used to define the list formats for export.








7. Double click on Marketing Contanct List list catalog to see Contacts Qualified List Item is attached with ROW_WID of Contacts













8. Loginto OBI Answers and Click on More Products-->Marketing

9. Create a list format with the required columns as shown below which are required to be exported and specify the file output format as xls in Options Tab, also check the “Re-qualify list results against original segment criteria” check box to make your list format generic and it works with any segment.













10. Create a simple segment using Contacts target level and attach it with the list format created above (List Preview File Format in the Advanced Options tab).






11. Click the Generate Lists button. In the popup dialog you can issue commands to run the list generation or to preview the list.

12. Find the URL of the Preview List Eg: http://bicg026/analytics/saw.dll
?MktgEditExportFormatReport&Action=previewFromSegment&Path=%2Fusers%2Fadministrator%2FCustomer%20List%20for%20Export&SegmentPath=%2Fusers%2Fadministrator%2FWest%20Contacts

13. In the Dashboad designer window add Link Object to embed this URL








This allows to download the file and save to disk

The Q2 2009 VendorRate results are in - Check out what Vendors landed the top rankings!

VendorRate provides ratings given by IT professionals with the intent of helping organizations make better decisions prior to selecting an IT vendor for products or services. Rating takes about three minutes, is fully confidential, rating totals are aggregated, and a vendor's total score and individual scores are always available online.

Highlights of The VendorRate Q2 2009 Report

• IBM Informix received the highest overall ranking with a score of 96

• GovConnection was the top rated reseller with a score of 98

• GovConnection received the highest score ever posted for a VendorRate quarterly report

• BI Consulting Group was the top rated consulting organization with a score of 92

• Microsoft customer satisfaction scores tumbled between Q1 and Q2 of 2009. Microsoft Applications dropped 17.9% to a
score of 64; Microsoft Server/Infrastructure fell 16.7% to 55; and Operating Systems slipped 9.5% to 67.

• Telecom and data service industries continued to lag behind hardware and software industries in customer satisfaction


To view the complete VendorRate Q2 2009 results, please follow this link: http://www.biconsultinggroup.com/Images/VideoAlbum/2009-07-15-Q209Report.pdf

The ten benchmarks that are rated are; 1) Integrity, 2) Communication, 3) Timeliness, 4) Usability, 5) Reliability, 6) Customer Service, 7) Budget, 8) Expertise, 9) overall Effectiveness, and 10) the rater's likelihood of recommending the vendor.

For additional information on VendorRate and to view the company(s) you may be researching, click: http://www.vendorrate.com

How to Hide the Saved Filter View Link

A new Saved Filter "View" Link now automatically appears when filters are displayed on reports (The new link appears in the more recent versions of OBI; I know it is in 10.1.3.4, but am not sure exactly in which version it first appears. ). When clicking on the view link, it will give details of the contents of the saved filter. While this new functionality may be useful in many circumstances, there are reasons you may not want the link to appear either. Unfortunately, there currently is no security surrounding this new saved filter view functionality. However, if you want to hide this view link, a simple css file change can do this for you.

Here you can see the new saved filter view link appearing on the report below ...


When you click on the link, the contents of the saved filter are displayed ...

To remove this "View" link so that it does not display, add the following css entry to the views.css file located in the \OracleBIData\web\res\s_oracle10\b_mozilla_4 ...

You will need to bounce your Web Server service and OBI Services.

That's it!

How to “Fake” a calendar dimension

Often, developers are asked to use less than ideal data structures to create reports and dashboards in OBIEE. These reports usually involve recreating some kind of list report found in MS Excel or another reporting tool. Recently, I was asked to do just this…

The requirements were that the request needed to filter on a number of specific dates, only use a single dashboard prompt, all without the luxury of a calendar dimension.

My solution was to use a calendar dimension from another subject area, then pass the date value selected to the request filter using a presentation variable. Below is a recap of steps I took to execute the solution:

1. Create a prompt using an “alien” calendar dimension. “Alien” meaning that this calendar dimension can exists in any other subject area, since any table relationships will have no bearing on this interaction.

2. In this case we’re using a Month End Date, and we’ve set the presentation variable name to be “Date_Variable”.

3. Within the request, we’ve set each date field we want to filter to recognize the presentation variable set in the prompt.

4. Add the Prompt and the Request to a dashboard page.

Once the Prompt and the Request have both been placed on a dashboard page, they should interact together, and the end user will be none the wiser. Notice how the value selected in the Prompt is reflected in the Filter View.

OBIEE Answers on Essbase

I recently presented a session titled "OBIEE Answers on Essbase: The Future of Ad-hoc Analysis" at the Kaleidoscope conference in Monterey, CA (June, 2009). My focus was the interaction between Essbase and OBIEE Answers - - where it is now and where it's going. Topics discussed included:

• OBIEE Answers vs OBIEE Answers Plus
• Hyperion Web Analysis vs OBIEE Answers
• Why This Is Important
• OBIEE Answers On Essbase (Demonstration)
  

Here is a link to the presentation:
http://www.biconsultinggroup.com/images/videoalbum/bicg_kaleidoscope_v031.pdf

Essbase administrators should become familiar with the OBIEE (Oracle Business Intelligence Enterprise Edition) file type "rpd". This "report definition file" contains the object definitions that allow Essbase outlines to be accessed using Oracle Answers and Oracle Dashboards. The presentation shows how to make this connection and begin using these tools together.

Any comments would be appreciated as we all explore this new BI functionality.

Essbase Substitution Variables

Substitution variables act as global placeholders for information that changes regularly. This functionality is frequently used with reports where the time values change over time. You simply store the substitution variable values within Essbase Application Server (EAS) for use at any time. Set once - - retrieve many!

For instance, suppose you create a sales report for June and want to rerun the same report 30 days later when you reach July. Without a substitution variable, you must manually update the Time dimension member to "July" instead of "June". With a substitution variable, the Time dimension member automatically retrieves the value stored within EAS (ie "July").

This is particularly helpful when you have complex reports with multiple time members. No more searching for every instance of where you need to change "June" to "July". The Essbase retrieval handles it for you automatically. This really helps improve overall data integrity.

Create the variable(s) and a corresponding string value within EAS. For example, now any time you retrieve the value "MnthMinus1", you will get the value for "FY06 May". Note that when you retrieve in Excel the syntax requires the ampersand "&" character as a variable prefix (ie: to retrieve data for "MnthMinus1", place "&MnthMinus1" in the cell).

A "before and after" example of an Excel retrieval using substitution variables is shown below.

Please contact me or leave comments if you have any questions.

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