Thursday Oct 30, 2014

Introduction to Oracle BI Applications with ODI

By Laura Garza, Principal Instructor for Oracle University

Often when teaching Oracle Business Intelligence Applications (OBIA) with Oracle Data Integrator (ODI), I get many questions concerning: 1) The different OBIA repositories, 2) Selecting an OBIA offering and Functional Area and 3) ODI terminology and how it relates to OBIA.

The Different OBIA Repositories:

- The Oracle BI Application Oracle Data Integrator repository contains the OBIA specific prebuilt ETL logic.

- The Oracle BI Application Components repository contains the repository for the Configuration Manager and Functional Setup Manager. It also contains the load plan definitions, setup objects and domain mapping to name a few.

How do we select the OBIA offering and Functional Area?

The Configuration Manager is a web application that allows us to configure the offering (Oracle Financial Analytics) that we have purchased. We will also enable the functional area (Account Payables, General Ledger) that we wish to implement via the Functional Setup Manager. Granted, I am only naming a few things that can be done within the Configuration Manager and Functional Setup Manger.

Here we see a screen shot on how to enable an offering and functional area to be implemented via the Configuration Manager.

Once you have finished the OBIA installation and identified which offering and functional area to implement, you will log into ODI Studio. You will then see the following Prebuilt OBIA ETL Logic.

ODI terminology and how it relates to OBIA.

If you have used OBIA with Informatica, you will see some familiar naming conventions within the prebuilt Oracle BI Applications ETL logic. What we need to be aware of is that ODI has different terminology and concepts than Informatica.

Within the Designer navigator tab of ODI Studio is a child tab called Projects. A Project is how we organize components such as interfaces, packages or procedures. When you install OBIA, a prebuilt project will be created called BI Apps Project. When you expand the BI Apps Project, additional folder nodes will be displayed. A folder called Mappings will now be visible. The Mapping folder is where you will begin to see the Prebuilt Adaptor folders. Notice that the Adaptor folders have the same naming convention that was used within OBIA with Informatica. Oracle did this to help us in the transition between the two products.

Many times students are wondering about the location of the SDE or SIL mapping definition. When we expand the Prebuilt Adaptor folder, SDE_ORA11510_Adapter, a child folder is displayed with the naming convention of the dimension or fact table we are trying to load, SDE_ORA_APAgingBucketsDimension. Right Click to expand the child folder and 3 additional objects will be displayed: Packages, Interfaces and Procedures.

An Interface consists of a set of rules that define the loading of a target datastore.

A Package is a workflow made up of a sequence of steps organized into an execution diagram. A Package references other components from a project such as an interface.

The procedure node contains scripts that can be used to customize database objects.

Also within the Designer navigator tab is a tab called Models. A Model is a description of a set of datastores. They correspond to a group of tabular data structures that are stored in a data server. A folder called Oracle BI Application will have a Model folder named Oracle BI Applications. The Oracle BI Application model folder will have child folders which are named based on the table type, ie: Dimension, Fact, Aggregate. The table structure is known as a Datastore.

Finally, we have the Load Plans and Scenarios. Load Plans and Scenarios are found within the Designer navigator tab of ODI studio. In order for source components (interface, package, procedures) to be placed into production, a scenario must be generated. Below we will see the Prebuilt OBIA Load Plans and Scenarios. Keep in mind that we generate and execute the OBIA Load Plans within the Configuration Manager.

In this blog, we discussed the OBIA Repositories along with an introduction to the Configuration Manager and Functional Setup Manager. We also covered some of the ODI terminology and how it relates to OBIA. I hope we were able to provide you an introduction of OBIA with ODI. I look forward to having you attend one of our Oracle BI classes.

Get Started with the new Oracle BI Applications 11g: Implementation using ODI training course. View all Oracle Business Intelligence training from Oracle University.

About the Author:

Laura Garza and been teaching for Oracle for over 15 years. She teaches both the Language and BI classes. For the past 8 years her primary focus has been Oracle BI.

Monday Nov 04, 2013

Some OBI EE Tricks and Tips in the Admin Tool
By Gerry Langton

How to set the log level from a Session variable Initialization block

As we know it is normal to set the log level non-zero for a particular user when we wish to debug problems. However sometimes it is inconvenient to go into each user’s properties in the Admin tool and update the log level. So I am showing a method which allows the log level to be set for all users via a session initialization block. This is particularly useful for anyone wanting an alternative way to set the log level.

The screen shots shown are using the OBIEE 11g SampleApp demo but are applicable to any environment.

Open the appropriate rpd in on-line mode and navigate to Manage > Variables.

Select Session > Initialization Blocks, right click in the white space and create a New Initialization Block.

I called the Initialization block Set_Loglevel .
Now click on ‘Edit Data Source’ to enter the SQL.

Chose the ‘Use OBI EE Server’ option for the SQL. This means that the SQL provided must use tables which have been defined in the Physical layer of the RPD, and whilst there is no need to provide a connection pool you must work in On-Line mode.

The SQL can access any of the RPD tables and is purely used to return a value of 2. The ‘Test’ button confirms that the SQL is valid.
Next, click on the ‘Edit Data Target’ button to add the LOGLEVEL variable to the initialization block.

Check the ‘Enable any user to set the value’ option so that this will work for any user.
Click OK and the following message will display as LOGLEVEL is a system session variable:

Click ‘Yes’.
Click ‘OK’ to save the Initialization block. Then check in the On-LIne changes.
To test that LOGLEVEL has been set, log in to OBIEE using an administrative login (e.g. weblogic) and reload server metadata, either from the Analysis editor or from Administration > Reload Files and Metadata link. Run a query then navigate to Administration > Manage Sessions and click ‘View Log’ for the query just issued (which should be approximately the last in the list). A log file should exist and with LOGLEVEL set to 2 should include both logical and physical sql. If more diagnostic information is required then set LOGLEVEL to a higher value.

If logging is required only for a particular analysis then an alternative method can be used directly from the Analysis editor.
Edit the analysis for which debugging is required and click on the Advanced tab. Scroll down to the Advanced SQL clauses section and enter the following in the Prefix box:
Click the ‘Apply SQL’ button.

The SET VARIABLE statement will now prefix the Analysis’s logical SQL. So that any time this analysis is run it will produce a log.

You can find information about training for Oracle BI EE products here or in the Oracle University Learning Paths.
Please send me an email at if you have any further questions.

About the Author:

Gerry Langton started at Siebel Systems in 1999 working as a technical instructor teaching both Siebel application development and also Siebel Analytics (which subsequently became Oracle BI EE). From 2006 Gerry has worked as Senior Principal Instructor within Oracle University specialising in Oracle BI EE, Oracle BI Publisher and Oracle Data Warehouse development for BI.

Monday Jul 08, 2013

Some OBI EE Tricks and Tips in the Analysis Editor by Gerry Langton

The following examples use the SUPPLIER2 database schema which is used in the Oracle University OBI EE training courses.

How to handle NULLs in a Pivot Table column

Sometimes you want to be able to display a value in a pivot table column even though there is no value in a column.

In this example we see that only one of our Sales Reps has actually sold any Frozen goods. As a consequence the Frozen column is blank for all the other reps. Putting a case statement in the Dollars column formula, e.g. CASE WHEN "Fact-Sales"."Dollars" IS NULL THEN 0 ELSE "Fact-Sales"."Dollars" END looks like a good bet. But unfortunately it doesn’t work as we don’t have a null value in the Dollars column. We just don’t have a row for this particular combination of Sales Rep and Product Type.

However we can use a little known feature of the column properties Data tab: you can enter a custom format that uses formatting commands similar to those used in Excel. The syntax used in this case would be:

positive-value-mask (semi colon) negative-value-mask (semi colon) null-mask.

For example #,###;-#,###;0

or #,###;-#,###;’No value’

So with that Custom Numeric format in place our Pivot Table now looks like this:

How to convert a string to proper case

Looking at the pivot table in the previous example we find the Sales Rep column in the Customers table is in upper case but we would like to see it reported with the first letter of each word in capitals and the remainder of the word in lower case. We review available functions in the Analysis Editor expression builder and don’t find anything suitable; however we know that Oracle provides the INITCAP database function which does exactly what we require.

The Analysis editor provides an EVALUATE expression which allow you to use database functions in the formula of a column.

The syntax for EVALUATE is:

EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])

db_function is any valid database function understood by the underlying database.
data_type is an optional parameter that specifies the data type of the return result.
column1 through columnN is an optional, comma-delimited list of columns. Where column1 replaces %1 in the database function, column2 replaces %2 etc.

So we would type the following in the Sales Rep column formula:

EVALUATE('INITCAP(%1),Customers."Sales Rep")

Use of the EVALUATE function is disabled by default so you need to enable the feature in the NQSCONFIG.INI file and restart the OBI Server (via Enterprise Manager).

In the Server Section of NQSCONFIG.INI set the parameter EVALUATE_SUPPORT_LEVEL to either 1 or 2 as in the extract from the file below:

# 1: evaluate is supported for users with manageRepositories permssion
# 2: evaluate is supported for any user.
# other: evaluate is not supported if the value is anything else.


How to do Time Series calculations in the Analysis Editor

Whilst time series calculations can be created in the OBI Repository logical layer using Ago, To Date and Rolling Period functions, it is also possible to create new calculations ‘on the fly’ in the Analysis editor. When you look at the available functions for a column formula you will see ‘Ago’, ‘To Date’ and ‘Rolling Period’ etc. under the ‘Time Series’ heading. However ,the use of these functions is not particularly clear from the Online Help.

Let’s assume that the repository has been set up with a Time dimension which is called Dim-Time and a Time hierarchy called Time, and the Time hierarchy has levels ‘Year’, ‘Quarter’, ‘Month’ and ‘Day’.

According to the online Help, to use the To Date function we use the syntax:
TODATE(expr, time_level) . But what does time_level actually mean? Well we express the time level using the syntax: ‘time dimension’.’hierarchy’.’level’.

Assuming we wish to have year to date totals this would equate to “Dim-Time”.”Time”.”Year”.

So the formula for Year To Date Dollars would be:

TODATE(“Fact Sales”.”Dollars”, “Dim-Time”.”Time”.”Year”)

For Month Ago Dollars the formula would be: AGO(“Fact Sales”.”Dollars”, “Dim-Time”.”Time”.”Month”, 1), where 1 is the period offset, i.e. current month -1.

How to put an HTML link in the column formula

I was interested to discover that the Customer names used in our Oracle University OBI EE course database are in fact genuine American restaurants. I discovered this when I was playing around with the requirement to place HTML links into an Analysis, in this case to pass a column value into a Google search.

We can type the following into the formula of the Customer column:

'<a target="_blank" href="' || Customer.Customer || '&btnG=Google+Search">' || Customer.Customer || '</a>'

The first instance of the customer name (Customer.Customer) is passed to Google as the search string. The second instance is used to display in the Analysis.

The Google search string parameters used in this example are hl=en which sets interface host language to ‘en’, i.e. English and as_epq which specifies the whole phrase must be found, which is important in situations like this where there may be spaces in the column value.

Also check Custom Headings and put in a new heading for the column (otherwise it will display the html!)

Also check ‘Override Default Data Format’ and select HTML from the drop down in the column properties.

The Table view should look something like this and each customer name will be a link to Google Search, passing the customer name as a parameter.

So when you click on Alley-Cats for example you will find the restaurant in Arlington Texas.

One obvious problem you will encounter is that some of the Customer names include an ampersand (&) character, and this is a terminator in the Google search string. So when you click on ‘2nd & Goal Sports Cafe’, only ‘2nd’ is passed to Google. To get round this you would have to do a string replace, replacing the ampersand with ‘and’.

So the formula for the column would become:

'<a target="_blank" href="' || REPLACE("Customer"."Customer",'&', 'and') || '&btnG=Google+Search">' || Customer.Customer || '</a>'

You can find information about training for Oracle BI EE products here or in the OU Learning Paths. Please send me an email at if you have any further questions.

About the Author:

Gerry Langton started at Siebel Systems in 1999 working as a technical instructor teaching both Siebel application development and also Siebel Analytics (which subsequently became Oracle BI EE). From 2006 Gerry has worked as Senior Principal Instructor within Oracle University specialising in Oracle BI EE, Oracle BI Publisher and Oracle Data Warehouse development for BI.


Expert trainers from Oracle University share tips and tricks and answer questions that come up in a classroom.


« March 2015