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.

Tuesday Jul 02, 2013

Oracle Database 12c: Oracle Multitenant Option by Gerlinde Frenzen

1. Why ?
2. What is it ?
3. How ?

1. Why ?

The main idea of the 'grid' is to share resources, to make better use of storage, CPU and memory. If a database administrator wishes to implement this idea, he or she must consolidate many databases to one database. One of the concerns of running many applications together in one database is: ‚what will happen, if one of the applications must be restored because of a human error?‘ Tablespace point in time recovery can be used for this purpose, but there are a few prerequisites. Most importantly the tablespaces are strictly separated for each application. Another reason for creating separated databases is security: each customer has his own database. Therefore, there is often a proliferation of smaller databases. Each of them must be maintained, upgraded, each allocates virtual memory and runs background processes thereby wasting resources. Oracle 12c offers another possibility for virtualization, providing isolation at the database level: the multitenant container database holding pluggable databases.

2. What ?

Pluggable databases are logical units inside a multitenant container database, which consists of one multitenant container database and up to 252 pluggable databases. The SGA is shared as are the background processes.

The multitenant container database holds metadata information common for pluggable databases inside the System and the Sysaux tablespace, and there is just one Undo tablespace. The pluggable databases have smaller System and Sysaux tablespaces, containing just their 'personal' metadata. New data dictionary views will make the information available either on pdb (dba_views) or container level (cdb_views).

There are local users, which are known in specific pluggable databases and common users known in all containers.
Pluggable databases can be easily plugged to another multitenant container database and converted from a non-CDB. They can undergo point in time recovery.

3. How ?

Creating a multitenant container database can be done using the database configuration assistant: There you find the new option: Create as Container Database. If you prefer ‚hand made‘ databases you can execute the command from a instance in nomount state:


And of course this can also be achieved through Enterprise Manager Cloud.

A freshly created multitenant container database consists of two containers: the root container as the 'rack' and a seed container, a template for future pluggable databases.

There are 4 ways to create other pluggable databases:

1. Create an empty pdb from seed

2. Plug in a non-CDB

3. Move a pdb from another pdb

4. Copy a pdb from another pdb

We will discuss option2: how to plug in a non_CDB into a multitenant container database. Three different methods are available :

1. Create an empty pdb and use Datapump in traditional export/import mode or with
Transportable Tablespace or Database mode. This method is suitable for pre 12c databases.

2. Create an empty pdb and use GoldenGate replication.
When the pdb catches up with the non-CDB, you fail over to the pdb.

3. Databases of Version 12c or higher can be plugged in with the help of the new dbms_pdb Package.

This is a demonstration for method 3:

Step1: Connect to the non-CDB to be plugged in and create an xml File with description of the database.
The xml file is written to $ORACLE_HOME/dbs per default and contains mainly information about the datafiles.

Step 2: Check if the non-CDB is pluggable in the multitenant container database:

Step 3: Create the pluggable database, connected to the Multitenant container database.

With nocopy option the files will be reused, but the tempfile is created anew:
A service is created and registered automatically with the listener:

Step 4: Delete unnecessary metadata from PDB SYSTEM tablespace:
To connect to newly created pdb, edit tnsnames.ora and add entry for new pdb.
Connect to plugged-in non_CDB and clean up Data Dictionary to remove entries now maintained in multitenant container database. As all kept objects have to be recompiled it will take a few minutes.

Step 5: The plugged-in database will be automatically synchronised by creating common users and roles when opened the first time in read write mode.

Step 6: Verify tablespaces and users:

There is only one local tablespace (users) and one local user (scott) in the plugged-in non_CDB pdb_orcl.
This method of creating plugged_in non_CDB from is fast and easy for 12c databases.

The method for deplugging a pluggable database from a CDB is to create a new non_CDB and use the the new full transportable feature of Datapump and drop the pluggable database.

About the Author:

Gerlinde Frenzen has been working for Oracle University Germany as one of our Principal Instructors for over 14 years. She started with Oracle 7 and became an Oracle Certified Master for Oracle 10g and 11c. She is a specialist in Database Core Technologies, with profound knowledge in Backup & Recovery, Performance Tuning for DBAs and Application Developers, Datawarehouse Administration, Data Guard and Real Application Clusters.


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


« July 2013 »