Sunday, September 28, 2008

Oracle BI Applications Upgrade - Part 3

Welcome back, these are the final steps in an Oracle BI Applications upgrade scenario

Step 8: Merge the DAC repository

After merging the custom folders in the Informatica repository, we have to merge the second (of four) repositories, that is the DAC repository. The new DAC has the unique feature of allowing direct upgrades and merge processes of two existing repositories, even from Siebel Analytics Applications 7.8.4, which had a "non-partioned" repository without containers. Please note that the following steps can only be executed in DAC Previous versions do not support an automated upgrade.

Below is a short summary of steps, please see the documentation for more details:
  • Connect to the new standard DAC repository and export the containers you want to upgrade.
  • Connect to the old, customized DAC repository and start the upgrade wizard.
  • Select Refresh Base. This option is recommended when you upgrade from BI Applications 7.9.x
  • Inspect the difference report and resolve conflicts if necessary
  • Click Merge

The result is a DAC repository which inherits the modifications made to the standard containers between 7.9.x and 7.9.5 as well as the custom container. However, there might be some additional manual work necessary to complete the DAC upgrade.

Step 9: Run test Execution Plans

After verifying the Setup view of DAC (registering Informatica servers for 8.1.1 is different) you can rebuild your execution plans and execute test runs to ensure that the ETL works as expected.

Step 10: Upgrade and merge the Oracle BI rpd file

Number 3...

If you have customized the Oracle BI rpd file and you want to benefit from changes made by the Oracle developers, you have to use the equalizerpds utility and the Administration Tool in order to accomplish a merge of the three versions
  • Prior Customer Repository
  • Prior Standard Repository
  • New Standard Repository
into a single New Customer Repository

According to the Oracle BI Applications 7.9.5 Upgrade Guide you have to get the original 7.9.x rpd file from the Upgrade folder and copy it to a temporary folder along with your customized rpd and the new standard rpd

You then execute the following major steps
  • use equalizerpds.exe to resolve minor differences in spelling (such as "Core"."W_DAY_D" is the same as "Core"."W Day D"
  • Compare repositories to find which elements have been created, removed or modified
  • Merge the repositories using the Administration Tools 3-way merge functionality
  • Test the outcome of the merge
This concludes the upgrade of repository #3, one more to go...

Step 11: Upgrade the Oracle BI Presentation Catalog

If you need to combine the changes of Oracle engineering with your customizations (in order to benefit from the new standard list formats for Siebel Marketing, for example) you have to merge the different versions of BI Presentation Catalogs into one. This is supported by the Catalog Manager tool. You have the choice between a simple copy and paste process if you adhered to the best practice of creating your own folders and the merge functionality of Catalog Manager.


Adhering to best practices and staying "close to the standard" during the development phase is crucial for successful and timely upgrades. Oracle BI Applications get you in touch with 4 different architectures and repositories (Informatica, DAC, rpd and presentation catalog) all of which exist in an original standard, a customized and a new standard version.

These are the recommended ways to customize them:
  • Informatica: Only modify objects in custom folders
  • DAC: Only modify objects in the custom container
  • rpd: Trim the original repository and create new presentation catalogs if needed
  • Presentation Catalog: Only modify objects in custom folders
Have a nice day

Wednesday, September 24, 2008

Oracle BI Applications Upgrade - Part 2

as promised in part 1 of this series, here is part 2 of a summary of steps for an Oracle BI Applications upgrade:

Step 5: Import the DAC 7.9.5 repository

Use the DAC 7.9.5 client to create a second connection to a new schema and create an empty DAC repository.
Import the BI Apps 7.9.5 DAC repository into the new schema using the DAC client.

Note: you can not import the 7.9.5 DAC repository using DAC (see below).

Optional Step: Install DAC

The Data Warehouse Administration Console has been released some time ago.
This is an optional step. You can stick with the DAC that comes with Oracle BI Applications 7.9.5 but you might want to look at the new features of DAC However, if you decide to use the new DAC, there is no easy way to revert back (the DAC repository) to 7.9.5, so a good reason to start with taking a backup.

Use the new DAC to create connections to the old and new repository and allow DAC to upgrade the schema by logging in to both connections.

Quick delta for DAC 7.9.5 to
  • No hibernate libraries required, but make sure you copy the database connectivity .jar file into the lib directory
  • Must create authentication file on first login, then change the Administrator password. For subsequent logins, use the Administrator account.
  • Existing repository (7.9.x) is automatically upgraded to new DAC version upon first login, you can not use earlier DAC versions (this applies to both client and server) after the upgrade
Step 6: Upgrade and merge the Informatica Repository

Upgrade the old version of the Informatica Repository following the instructions in the Informatica PowerCenter 8.1.1 Installation and Configuration Guide. The process is executed in the web-based Administration Console.

Next you can establish connections to both repositories (the one you customized and the new one) in Informatica Repository Manager. You can now compare folders or objects and copy the custom folders to the new repository. If you adhered to the recommendation to do all modifications in custom folders, this is a very safe path. If you made any customizations to objects in standard folders, you most probably have to reapply these changes to the objects in the new repository.

Step 7: Upgrade the OBAW Schema

According to the OBI Apps 7.9.5 Upgrade Guide you have to
  • run prepared SQL scripts
  • use the ddlimp utility to change the schema
  • start some specialized workflows manually, if you upgraded the Siebel source application to 8.0 or 8.1
  • Reset the sequence generators of Informatica using a script
  • Import a specialized repository which contains upgrade workflows and run the respective workflows
Need a break? All right, see you in a few days for the final. In part 3 we will discuss merging the DAC repository, running test execution plans, upgrade and merge the .rpd file and the presentation catalog.

Saturday, September 20, 2008

Oracle BI Applications Upgrade - Part 1

donkey rides

Those among you who have undertaken the quest of a Siebel CRM upgrade know what it means to upgrade one (1) repository and merge new standards with old customizations.

How does it (look and) feel to upgrade the stunning number of four (4) different repositories, such as in Oracle BI Applications where we currently have

  • 1 Informatica repository
  • 1 DAC repository
  • 1 BI Server repository
  • 1 Presentation Catalog

I had the opportunity to do a POC for a custom training and this series of posts tries to point you to the basic steps and pitfalls.

The slightly customized BI Application we have to start with consists of the following

  • Oracle BI EE
  • Oracle BI Applications 7.9.2 including DAC
  • Informatica 7.1.4
The target versions are

  • Oracle BI EE
  • Oracle BI Applications 7.9.5
  • Informatica 8.1.1 SP4
  • DAC

In this first part of the series we cover upgrading the infrastructure basically by installing the new versions of the software packages.

However, whenever you undertake an upgrade make sure you read the fabulous manual. And read it before you lay your hands on the keyboard.

Step 1: Upgrade the Oracle BI EE Infrastructure

This is a straightforward step since you simply install the new version on top of the old version, which is a supported practice even for Siebel Analytics 7.8.4. Of course, you will do a backup of all valuable files and if you modified any standard xml files you better watch out and copy them to the custom folders next time ;-)

Step 2: Upgrade Oracle BI Applications

Now it's time for another backup, this time you copy the entire DAC directory to a safe place. Next you backup any custom files (scripts, parameter files etc.).

We have to uninstall the existing version of Oracle BI Applications and then install Oracle BI Applications 7.9.5.

Oracle BI Applications 7.9.5 are "Fusion Edition"

Step 3: Upgrade Informatica 7.1.4 to 8.1.1 SP4

Oracle BI Applications 7.9.5 come with exclusive support for Informatica 8.1.1 SP4 which is a bit of a challenge as 8.1.1 has a completely different server architecture (see delta summary below).

Best thing is to carefully follow the pre-upgrade steps in the Informatica PowerCenter 8.1.1 Installation and Configuration Guide, especially creating another database (or schema) for the new repository

Backup time again. This time we take care of the existing repository and the Repository Agent configuration file (.cfg).

Then we uninstall previous versions of Informatica Servers and Clients and install Informatica Services and Clients 8.1.1 and apply SP4 immediately afterwards. The installer creates the new repository service, make sure you name it different than the previous repository.

Caveat: Note that the install.exe might not launch if a java-based process such as Oracle's Enterprise Manager Console is running.

Quick Delta for Informatica 7.1.4 to 8.1.1 SP 4 (Windows)

  • No separate windows services for Informatica server and Informatica Repository Server
  • New "wrapper" service: "Informatica Services 8.1.1"
  • Server administration now done completely in a web client
  • Repository service and Integration Service (formerly known as Informatica server) are administered from the central web-based administration console
  • New concept of domains, which contain nodes, which host processes such as repository and integration services.

The web-based Informatica administration console in version 8.1.1

Step 4: Configure the new Informatica 8.1.1 Services

Follow the steps in the Oracle BI Applications 7.9.5 Installation Guide to set up the basic infrastructure. Import the new version of the Informatica Repository provided by the BI Apps 7.9.5 installer into the new Informatica 8.1.1 database. Copy the source and lookup files to the correct directories. Set PowerCenter properties according to the guide.

That's it for today, stay tuned for Part 2 where we will install DAC, upgrade the Informatica repository and the BAW schema.

Tuesday, September 16, 2008

Marketing Segmentation, File Export and Dashboards

While delivering an Oracle BI EE course, I came across an interesting requirement. End users want to download csv files and modify them on their workstations. External systems should access these files, too. Of course, this can be accomplished using the standard download links in Answers and Dashboards. However the number of records that these end users want to handle is in the millions, so any system in between (like Presentation Service or the browser itself) would be a bottleneck. Furthermore there are reports of browser crashes when users try to load very large tables.

Given the fact that the Marketing Segmentation module of Oracle BI EE has a standard functionality of producing large export files (comma-separated, fixed width, XML) and a nice interface to control the schema, the content and the output format of the files, it is an ideal candidate for a proof of concept.

First, you need to modify the rpd and create a Target Level, a List Catalog and the accompanying Qualified List Item, which is typically a primary key and glues the former two together. If you are not familiar with the marketing metadata in the rpd file, here is some documentation and there is also a nice training you can book.

Second, open the BI Client and navigate to More Products > Marketing > Create a List Format. List formats define the columns you would like to export and the output format (CSV, XML or even direct inserts into a database table).

Here we have created a simple list format. Make sure that you check the “Re-qualify list results against original segment criteria” check box to make your list format generic and it works with any segment. You can set Options such as output format, headers and footers and use the Preview tab to see how it works.

Next, we create a simple segment from our target level and associate it with the list format (List Preview File Format in the Advanced Options tab).

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

The final task is to enable end users to access this dialog. A simple solution would be to permit access to the segment designer, but let’s try a more elegant way to access the list generation from a dashboard page.

We could use the Embedded object feature but we need the URL behind the popup. So let’s launch good old firebug and inspect the popup.

In the Script tab you can expand the current URL and see that the following URL is used to retrieve the Preview popup:


It is interesting that the URL has two path parameters, one for the list format, the other one for the segment. So this allows for some automation scenarios, where one could think of variables passing the path of a segment.

Now we can embed the URL into a dashboard

The final result: A dashboard page from which end users can generate and download export files.

Friday, September 12, 2008

Oracle BI EE Variables

really quick

Some time ago I compiled a document about the various variables in Oracle BI EE, how to set them, how to get them etc.

We have the following variables in Oracle BI EE:

1. Server-side variables
1.1. Repository variables (dynamic and static)
1.2. Session variables (system and non-system)

2. Presentation variables

I tried to condense the information of the a/m document and have created a single page doc which gives an overview of the variable types and how to

  • initialize the variables
  • set the variable values
  • access the variable values from within the rpd and Answers (all about the notorious @{variable} style syntax in Answers)

Monday, September 08, 2008

Populate W_DAY_D correctly

2011 is near...

Oracle BI Applications come with a predefined Informatica Repository and a pre-built DAC (Datawarehouse Administration Console) repository. When loading the BAW (Business Analysis Warehouse, the predefined schema) for the first time, this is called a full load.

One table is of particular interest: w_DAY_D, implementing the time dimension, storing one record for each day between 1st of January 1980 and 31st of December 2010.

The end day of this period is now raising concerns (and eyebrows) among admins and analysts over the globe as they see that date coming soon.

So how is W_DAY_D populated?

Using the DAC Client this question is easily answered. There are four tasks that take care of W_DAY_D.

click to enlarge

The main task SIL_DayDimension has two parameters which are passed to the Informatica workflow.

$$START_DATE has a default value of 1980-01-01 and $$END_DATE one of 2010-12-31.

Before you can update these parameters, you have to create a custom container using the functionality in the DAC File menu. Once the container is created, go to the SIL_DayDimension task, click the Parameters tab and update the parameters according to your needs.

When a referenced object in the DAC repository is changed, it will be cloned and you have to commit that in a dialog box.

click to enlarge

Now we have to enforce a full load on the W_DAY_D table, which means setting the table's refresh date to NULL. This is standard behavior of the DAC. It will start the task in full mode when the refresh date of the target table(s) are set to NULL.

To do this, navigate to Setup > Physical Data Sources > DataWarehouse and click the Refresh Dates tab. Here you query for the table and set the refresh date to NULL.

When the next execution plan (you will have to create a new one once you have created a custom container) is run, W_DAY_D will be loaded with the new range of dates.

Please keep in mind that you should only extend the date range and never narrow it, so if you have those dates of 1980 already in W_DAY_D, they should stay there for the sake of referential integrity.

Thursday, September 04, 2008

Client Side Logging

books at bedtime

If you read technical blogs, you get the impression that these guys don't have a life and if they sleep at all, they sleep with the Siebel Bookshelf under their pillow.

I can confirm it is not that bad. However, rtfm is always a good thing and it sometimes reveals features that are quite helpful.

This post tries to shed some light on the Client-Side Logging or High-Interactivity Siebel applications. You can read the full documentation in the System Monitoring and Diagnostics Guide.

Client-side logging for high-interactivity applications writes information to a local log file.

It is useful to close the gap in monitoring that existed in earlier Siebel versions, which means you can now

  • capture browser activity data for troubleshooting, such as when a Siebel Web Client stops responding or fails
  • Log individual user or global session information for a specific Siebel Server
  • Debug the source code using JavaScript
  • Trace the sequences of operations

You can enable Client-Side Logging either by setting the SEBLCL_TRACEMODE environment variable on the client machine or setting parameters for the object manager component on the Siebel server like in the following example:

change param
for compdef SCCObjMgr_enu

Once enabled, log files are created in the given local directory of each client. Let's look at a typical file's content.

click to enlarge

So each entry in the file shows which area it belongs to along with the log level, timestamp and - most notably - a SARM id, which allows you to correlate the client file content with SARM files from the server components.

So it is possible to trace a complete client session from the the click in the browser to the operation in the database connector layer of the object manager.