Oracle Warehouse Builder (OWB) Weblog

OWB 11.2 uses one of the widely used logging API: the Java^TM Logging APIs to handle log messages. The Java^TM Logging APIs are introduced in package java.util.logging, whose central goal is to support maintaining and servicing software at customer sites. With this built-in logging system, user can define OWB client logging with a configuration file that will be read at startup, such as defining delivering plain text or XML-formatted log records to the console or to a file.

Where does the configuration file locate?

On Linux there is a text file called "logging.properties" under directory "<owb installation path>/owb/bin/" when OWB is installed. Copy "logging.properties" to "<owb installation path>/owb/bin/admin/", and change the file name to "owbclient.logging.properties". The same for Windows, copy "<owb installation path>\owb\bin\logging.properties" to "<owb installation path>\owb\bin\admin\", and change the file name to "owbclient.logging.properties".

This .properties file establishes the configuration of the logger as you desire. It is a text file in standard java.util.Properties format, with each line in format as "<key>=<value>". Any changes in this file like adding a new parameter will take effect next time you startup OWB.

How to configure the file?

In the .properties file, you can specify "handlers" which export the logs to different places (to the console or a file), "messageFormat" of each handler (to format the output) and "levels" at many different points (so you only see messages of certain priorities), and you can even specify these options on a per-package or per-class basis.

Here is an example file:

handlers=java.util.logging.FileHandler,java.util.logging.ConsoleHandler

console.messageFormat={2}

file.message Format={1,time}:{2}

.level = INFO

sun.level = OFF

oracle.level = ALL

java.util.logging.FileHandler.pattern = %h/java%u.log

java.util.logging.FileHandler.limit = 50000

java.util.logging.FileHandler.count = 1

java.util.logging.FileHandler.level = ALL

java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

java.util.logging.ConsoleHandler.level = CONFIG

java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

Let us consider what each line in the configuration file is doing.

handlers=java.util.logging.FileHandler,java.until.logging.ConsoleHandler

Messages are sent to a FileHandler that writes log messages to a file and to a ConsoleHandler where messages are exported to the console.

console.messageFormat={2}

Messages output to the console consist of just the message.

 file.messageFormat={1,time}:{2}

Messages sent to the log file include the time a message was logged along with the message.

 .level = INFO

Set the root logger's level to INFO. This is the default level for all loggers. By setting the level of the root logger we ensure that all other loggers inherit that level except for the ones we set otherwise.

 sun.level = OFF

Messages logged by sun.* loggers will be filtered.

 oracle.level = ALL

Messages logged by oracle.* loggers will not be filtered (all).

 java.util.logging.FileHandler.pattern = %h/java%u.log

Set output file name. In this case the log file is placed in user's home directory as "~/java0.log" on Linux, and something like "C:\Documents and Settings\<username>\java0.log" on Windows, depending on Windows version and configurations. Pattern can consist of: %h - user's home directory, %t - temporary directory, %u - a unique identifier, %g - file count.

 java.util.logging.FileHandler.limit = 50000

Limit size of output file in bytes to 50000.

java.util.logging.FileHandler.count = 1

This is the number of output files to cycle through, by appending an integer to the base file name.

 java.util.logging.FileHandler.level = ALL

Override of global logging level. Define level for FileHandler to ALL, which means all messages will be written into log file.

java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

Messages sent to log file are XML-formatted.

 java.util.logging.ConsoleHandler.level = CONFIG

Override of global logging level. Set level for ConsoleHandler to CONFIG, which means messages with level CONFIG or higher will be exported to the console.

 java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

Messages output to the console are in plain text format

Let's go into details of this configuration file.

• Handlers

The handler defines how the message is exported. For example, write them to the console or log them to a file. There are two standard handlers that are available, java.util.logging.FileHandler and java.util.logging.ConsoleHandler. In the following example a FileHandler is added which exports log messages to a file.

handlers=java.util.logging.FileHandler

• MessageFormat

The messageFormat defines how the message is formatted. Each logging handler can have a messageFormat associated with it. The format can contain the following:

{0} = level being logged

{1} = date/time of logged message

{2} = the log message

{3} = the 'class' that is logging the message

{4} = the 'method' that is logging the message

For example if you set file.messageFormat={1,date} {1,time}: {0} {2}, messages printed to log file will be in format as

Nov 17, 2009 6:54:59 AM: DEBUG_ALL Initializing the backend controllers.

Nov 17, 2009 6:54:59 AM: DEBUG_ALL Controller Initializer: oracle.wh.repos.pdl.APIController

Nov 17, 2009 6:54:59 AM: DEBUG_ALL JVM LIMIT DISABLED: 256M

Nov 17, 2009 6:55:00 AM: DEBUG_ALL Thread[Thread-2,5,main]CacheMediator.init:

• Formatters

Possible formatters are SimpleFormatter which means printing message in a human readable format, and XMLFormatter which means printing into a standard XML format. For example output messages to console in plain text format by setting

java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

• Levels

A level describes the lowest logging level of the message to process. Possible values in descending order are SEVERE, WARNING, INFO, DEBUG_CUSTOM, DEBUG_VERBOSE, DEBUG_ALL, CONFIG, FINE, FINER, FINEST. In addition there are two levels: OFF that can be used to turn off logging, and ALL to enable logging of all messages. You can set level SEVERE to ConsoleHandler with command

Java.util.logging.ConsoleHandler.level = SEVERE

Enabling logging at a given level also enables logging at all higher levels. For example, if you set level to INFO, then logged message of level INFO or higher will be output, while those lower will be filtered out.

Level can be applied at either handler or at the class name level, which means that you can control what the lowest level for output for a handler is, along with controlling the lowest level for output for a class name. For example you can set level of FileHandler to CONFIG, meanwhile set level of messages output from class oracle.owb.mapping.map to ALL by

java.util.logging.FileHandler.level = CONFIG

oracle.owb.mapping.map.level = ALL

For detailed settings and complete use of Java^TM Logging APIs see the JDK reference for java.util.logging.

Things to consider when setting the log parameters

Using logging in an application benefits in many ways, such as logging can generate detailed information about the operation, which helps support staff with troubleshooting. However, logging does not come without costs. For example, logging adds runtime overhead from generating log messages and from device I/O, also if logs are too verbose or badly formatted, extracting information from them can be difficult. Then how to log efficiently?

• Properly set logging level.

The lower the level is, the more messages will be logged. For example level SEVERE indicates just logging serious failures, while ALL indicates that all messages should be logged.

• Turn off logging output from the packages you are not interested in.

For example if you are only interested in logging output from the "oracle.owb.mapping" package, you could do the following:

.level=OFF

oracle.owb.mapping.level=ALL

• Turn off System.out.

The OWB Logger 'intercepts' the System.out stream and 're-routes' them as "DEBUG_CUSTOM" messages. So you can use the same mechanism to control how much System.out stuff you want to see. If you set the level on a logger to "CONFIG" or lower, you will see the System.out stuff, if you set it to INFO or higher, it will not be displayed.

You can completely disable this interception process by adding

System.out.level = OFF

More examples

Logging configuration can be modified for different target uses. For example if you only want to capture serious problems or common problems that can be fixed or tracked locally, such as running out of resources or simple configuration errors, you can set default level to SEVERE, or you need a log file sent to support for troubleshooting, then add a FileHandler and set its level to DEBUG_ALL to log more detailed information on the internal execution.

An example of configuration file with which only serious problems are captured:

Handlers = java.util.logging.FileHandler,java.util.logging.ConsoleHandler

.level = SEVERE

file.messageFormat = {1,date} {1,time}: {2}

java.util.logging.FileHandler.pattern = %h/java%u.log

java.util.logging.FileHandler.limit = 50000

java.util.logging.FileHandler.count = 1

java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

java.util.logging.ConsoleHandler.formatter = java.util.logging.SimpleFormatter

An example of configuration file with which detailed information will be logged for troubleshooting:

handlers = java.util.logging.FileHandler

.level = DEBUG_ALL

file.messageFormat = {1,date} {1,time}: {0} {2}

java.util.logging.FileHandler.pattern = %h/java%u.log

java.util.logging.FileHandler.limit = 50000

java.util.logging.FileHandler.count = 1

java.util.logging.FileHandler.formatter = java.util.logging.XMLFormatter

One of the new mapping operators introduced in 11gR2 is the much talked about (over the years) subquery filter operator. As well as the heterogeneous capabilities added, a number of enhancements to existing operators (such as joiner and lookup operator) and a few new ones were added. The subquery filter supports the SQL grammar for exists, in, no exists and not in.

This simple example illustrated here illustrates a scenario where we want to get all the addresses of customers where they have a high credit limit and are married for example. The filter on customers gets the customers who are married and have a credit limit > 10000;

The subquery filter operator is defined to use the Exists query type and a condition since we have a correlated subquery;

The generated SQL with the correlated subquery looks like this;

SELECT
  "ADDRESSES"."CUSTOMER_ID" "CUSTOMER_ID",
  "ADDRESSES"."PHONE_NUMBER" "PHONE_NUMBER",
  "ADDRESSES"."CUST_STREET_ADDRESS" "CUST_STREET_ADDRESS",
  "ADDRESSES"."CUST_POSTAL_CODE" "CUST_POSTAL_CODE",
  "ADDRESSES"."CITY_ID" "CITY_ID"
FROM
  "ADDRESSES"  "ADDRESSES"
  WHERE
  (EXISTS (SELECT
  1 
  FROM
  "CUSTOMERS"  "CUSTOMERS"
  WHERE
  ( "CUSTOMERS"."MARITAL_STATUS" = 'married' )  AND
  ( "CUSTOMERS"."CREDIT_LIMIT" > 10000)  AND
  ( "CUSTOMERS"."ID" = "ADDRESSES"."CUSTOMER_ID" ) ))

When the mapping is executed the result is a table with contact information;

The OWB mapping operators are all pluggable together, so we can take this example further to utilize a lookup operator for example, so the CITY_ID is also taken from addresses and we lookup the CITY_NAME and STATE_PROVINCE from the CITIES table, and the resultant table has this information.
So the mapping now looks like;

The data after execution in the resultant table has;

So the subquery filter lets you model more scenarios than previous releases, there are still some cases that are not supported such as using less than (<) for example rather than exists/in etc. So the following style where clause 'where quantity < (select max(quantity) from sales where book_key = s.book_key)' cannot be modeled.

So what to do in this case? There is another new mapping related feature which is interesting is the in-line view operator. The view operator has an inline property such that if this is checked the SQL defined within OWB for the view will be placed 'in-line' in the generated code rather than the view name.

Here we have seen the subquery filter mapping operator introduced in 11gR2. The subquery filter supports the SQL grammar for exists, in, no exists and not in.

As OWB adopts the Fusion Client Platform (FCP), it is now using the new FCP startup configuration file for specifying the startup parameters for OWB. Unlike previous versions of OWB, where startup parameters are directly set inside the file owbclient.sh (for Linux) or owbclient.bat (for Windows), we now have a specific file named "owb.conf" that stores those startup settings for both Windows and Linux. You can navigate to "<owb installation path>/owb/bin/", and there it is! Just go and take a look at the file.

1. Let's examine "owb.conf"

Below is an example of "owb.conf". "owb.conf" is a text file, and each startup setting is organized per line. Settings are sequence-insensitive, as each of them refers to different meaning. Looking into each line, the setting has such format as "<command> <parameter(s)>", where <command> is FCP specific and <parameter(s)> is specific to each command.

Here are some useful commands for your reference. All commands are CASE SENSITIVE.

1) AddVMOption

Include command-line option when invoking the JVM. The standard options recognized by Java VM are described on the Java Application Launcher reference pages for Windows and Linux.

2) AddJavaLibFile

Add jar/zip file or directory to classpath for OWB.

3) SetJavaHome

Set the path where JDK/JRE are installed.

4) SetSkipJ2SDKCheck

This parameter is used to specify whether checking JRE installation under JAVA_HOME. If "SetSkipJ2SDKCheck" is set, JRE installation checking is skipped during OWB startup. For OWB, we always set "SetSkipJ2SDKCheck" true.

5) IncludeConfFile

Include another .conf file so that you can allow common or specific configuration to be defined into separate .conf files. For example the preceding "owb.conf" includes "owb-nodebug.conf" which specifies JVM logging setting.

2. JAVA: Out of Memory Error

The default settings in the "owb.conf" that comes with our OWB 11.2 release shall satisfy most of our customers' needs. Occasionally, you may find a need to modify this configuration file.

For example, if you have a huge MDL file that you want to import, you may run into this JAVA error:

Exception in thread "xxxx" java.lang.OutOfMemoryError: Java heap space

To get rid of the issue, you can simply increase the maximum Java heap size based on your physical memory. Go ahead and add following line into "owb.conf":

AddVMOption -Xmx768M

There we go! Restart OWB and try import that MDL file again, the new VM setting should be applied and you should be able to import the same MDL file successfully.

Just a quick tip: The new My Oracle Support launched a couple of days ago, and information in the Certification section has been rearranged. OWB is now listed under:

• Product Line Middleware
• Product Family Business Intelligence
• Product Area Oracle Warehouse Builder

A screenshot from the new support UI is attached. This covers even older OWB versions that were shipped by or with the database division.

Took me a while to find it; figured I’d save some of you some time. :)

Starting from 11gR2, OWB has adopted the Fusion Client Platform (FCP), which is the same IDE platform as Jdeveloper and SQL Developer. FCP provides an efficient and flexible way to manage navigators, panels and editors. When we login to OWB Design Client the first time, we have a interface as shown in fig 1. Projects Navigator, Locations Navigator, Globals Navigator, Graphical Navigator and Structure Explorer are bundled on the left. The main window is a Start Page, which links to many useful documentations as well as online resources, and is a good place to get started.

 Fig. 1

With 11gR2, multiple editors can be opened for editing at the same time. Take a look at fig 2, we have two mapping editors and one table editor. Projects navigator is minimized for better utilization of screen space, while Property Inspectors and Component Palette are docked for easier manipulation of mappings and access to component properties. All panels and editors can be dragged, resized, or docked as preferred, and the layout is fully customizable. If you would like to change the overall window layout, you can go to menu Tools->Preference->Environment->Dockable Windows.

 Fig. 2

Interested in OWB 11gR2 user interface? Take a User Interface Tour.

However, in rare cases, the layouts just get messed up. For instance, Property Inspector is gone missing. Fortunately, we can restore the factory default layout by removing the layout setting files. There was a post talking about this, but as time goes by, the content is outdated and may not be applied to 11gR2. Let's look into how to fix this in 11gR2.

1. Locate the settings directory.

On Linux, it is "~/.owb" (aka $HOME/.owb). This is a hidden folder under your home directory. Suppose your login handler is "tom", and you started OWB Design Client before, the setting directory for you sould be "/home/tom/.owb".

On Windows, it is "%APPDATA%/OWB". Depending on Windows version and configurations, it should be something like "C:\Documents and Settings\<username>\Application Data\OWB" on Windows XP.

(Note: For earlier releases, "$OWB_HOME/owb/bin/admin" is used.)

2. Change current working directory to the settings direcotry and delete setting files.

Under the settings directory, there is a folder named system11.2.0.X.X, where X.X is version numbers. Usually on a fresh install, you only have one folder, but if you upgraded from previous versions, there might be more than one. By removing these folders, the layout will be restored to factory defaults.

Following is an example on how to remove the setting directory from command line. If you prefer, you can also use a GUI explorer instead, but make sure hidden files are shown.

On Linux:

$ cd ~/.owb
$ ls 
system11.2.0.1.85
$ rm -rf system11.2.0.*
$
  

On Windows:

>cd %APPDATA%/OWB
>dir
08/28/2009  01:54 PM    <DIR>          system11.2.0.1.85
>del /s /f system11.2.0.*
>
  

Now, if you start the Design Client, you should see the factory default layout as shown in fig 1 again. And don't worry. Your data stored in database repository are kept safe and untouched.

Extended Reading:

OWB User Interface Tour

What's New in OWB 11gR2

David Allan's posts have been a major part of making the OWB Blog a must-read for our users. Soon, though, he will be joined by more members of our development staff, who will be bringing you more how-tos and insights into OWB 11.2 features that help you get the most value out of the tool.

The first of these should be up shortly-- keep an eye out for it.

Here we'll see how you can add runtime variables and reference them from code template mappings in OWB 11gR2; variables can be used in many places including expressions, filters, joiners and execution unit code template assignments. This lets you control how some aspects of the mapping will behave at execution time. Traditional database resident mappings support mapping input parameters, in 11gR2 the template mappings can be extended in many ways and this is one illustration.

This is essentially a small extension where a table holding the variable names and values is defined, a data source in the agent to identify where the variable table is defined and there is a JAR file with the support for setting and getting variable values.

Setup

There are a few steps to configure OWB to support this (the zip is here) using a small custom extension I have created. Firstly copy the owbutils.jar file to the owb/lib/ext directory of the CCA host, then stop and start the agent. The table OWB_CT_VARIABLES should be defined in a schema of the user's choice, there is a SQL script supplied for Oracle named variables_oracle.sql in the zip, execute this in a schema of your choice.

The table OWB_CT_VARIABLES is defined with the following columns to represent the variable name and the variable value:

• variable_name
• variable_value

So to define a new variable insert a name and value into this table, for example the variable VAR_REGEXP is added below with the vale orderitems.*gz;

insert into owb_ct_variables values ('VAR_REGEXP', 'orderitems.*gz');

commit;

You can refer to variables inside expressions in OWB, for example within filter, join and expression operators, they can also refer to variables from within code template options in a mapping.

Configuring the Agent

Finally create a data source named jdbc/OWBCTVariablesDS in the Control Center Agent (CCA). The data source points to the schema containing the table used for storing the variables. Below is an example of a data-sources.xml file (in owb/jrt/config) that has a data sources added.

The data source named jdbc/OWBCTVariablesDS is used by the variable API that provides operations on variables to get and set variable values (the API is available when the jar file is copied to owb/lib/ext) - there are also increment/decrement operations for integers.

Illustrations

To illustrate lets look at an example that loads sets of files into a target table. The file list is dynamic and needs to be a regular expression that can be supplied to load all order items files with the gz (compression) suffix in one run. Another example will load all records from files with a transaction date greater than some date we define at runtime. The table OWB_CT_VARIABLES has the variable names and values;

Taking the mapping from the post on bulk loading files, if we look at code template options we can also use variables here too, so we can solve our challenge by getting the variable value for the filename regular expression to use, the example will use the VAR_REGEXP variable name to obtain the expression for file names at runtime.

 
This lets us deploy the mapping once and execute the mapping with whatever values we desire for the regular expression since the value is pulled at runtime.

Another illustration using a filter

In this example we define a filter to get all records after the date defined in the variable, we define the condition below, using the API (ctvariable.get) passing the variable name;

Note the \u0022 character which will give us the required quotes through the code generation. Depending on where the expression is defined will change how the code is written, inside a mapping expression the Unicode character is required, in the mapping template options use the quotation mark.

Summary

Here we have seen how you can add runtime variables and reference them from code template mappings in OWB 11gR2; the variables can be used in many places including expressions, filters, joiners and execution unit code template assignments letting you control aspects of the execution at runtime.

Here we will look at building cube organized materialized views for a relational fact table. The illustration uses the Oracle OLAP 11g Sample Schema and rather than the cube organized materialized views being created via the script in the demo, they will be designed and generated from OWB. The cubes and dimensions designed in OWB can serve multiple purposes;

• physical data warehouse tables may be generated
• dimensionally aware ETL operators for maintaining data
• cube organized materialized views generated for summary management and performance
• derive the OBIEE layers - a great metadata integration scenario.

OWB 11gR2 supports the design of 11g form Analytic Workspaces (as well as 10g form AWs), multidimensional designs can be deployed as 11g cubes PLUS the relational fact table can be summarized using cube organized materialized views.

Firstly let's download the OLAP training example here. Complete the 'Installing the base OLAPTRAIN schema' step, do not install the analytic workspace (I did this on Linux). We must then import the base table metadata into OWB, we will build dimensions and cubes on top of these tables next. There are further performance illustrations based on these examples that we will illustrate leverage on, the Oracle database viewlet can be found on OTN at Improving Query Performance with Oracle OLAP Cube MVs.

Now execute the OWB OMB script (download here) to create the cubes and dimensions which are bound to these tables. The script creates the dimensional model.

This will define the dimensions and cubes and setup the storage type which will inform OWB to generate cube organized materialized views.

For example the SALES_CUBE object is bound to the underlying fact table SALES_FACT below. The dimensional model defined is skeletal, so the dimensions primarily just have their hierarchies defined for demo purposes (ie. not all dimension attributes are defined).

We can see the cube and dimensions are all defined to be stored as relational using cube organized materialized views, they have the 'ROLAP: with Cube MVs' option enabled. This lets the user select information from both the relational storage options (such as create bitmap indexes) and from the multidimensional storage options such as the name for the analytic workspace.

As it stands if we deployed the summary in the AW would have the same grain as the base fact table, which is much more than we need for summary management. To illustrate, if the code is generated for the cube we can see the join from the SALES_FACT table to the TIMES table is based on the DAY_KEY and also the expression loaded into the cube is DAY_KEY (see the Expression and JoinCondition properties below). Remember the DAY level is our lowest level for the time dimension in our cube.

So what about storing summaries at the non-leaf level...? Read on...

Summary levels in cube organized materialized view

If we want to summarize at a non-leaf level in the cube, then we must change the dependent dimensions' summary load level. For example we can see just now in the TIMES_DIM dimension the summary load level is not set (so the leaf is used).

We can set the MONTH level for the STANDARD hierarchy as the summary level, the base facts are still stored in the relational fact table at DAY, but the summary is loaded into the cube organized materialized view for MONTH.

Now if we generate the cube we will see the join condition is still the same but the expression for TIMES is now MONTH_ID (see the Expression="TIMES.MONTH_ID" name value pair below).

Generating the dimensions generates multiple scripts; one script is the relational DDL which goes to the DAY level, and the other script is to build the dimension definitions in the AW which have the summary level in the cube organized view as MONTH.

What's the point of storing the entire fact table in the MV again? Silly right? Just as well we can define the summary level that is loaded in the cube organized materialized view. We can for example change the PRODUCT_DIM dimension to have a summary level of SUBTYPE rather than the base of ITEM. Note this is done on the dimension, not on the cube.

So why will this generate a materialized view....?

There are configuration properties that tell the OWB code generator to enable the MV creation, see the figure below initially the script created the Enable Query Rewrite with value DISABLE, we must change this to ENABLE.

    ==>Enable==>    

Once this has been changed then when we generate and deploy all the dimensions and cube an AW is created with a cube setup for rewrite using cube organized materialized views.

Show me the MVs....

After the dimensions and cubes have been deployed, you can inspect the Oracle dictionary to see the materialized views created, and even open up AWM and see the cubes and dimensions created by OWB to represent the cube organized materialized view.

Here we see the materialized views created;

SQL> select mview_name from user_mviews;

MVIEW_NAME
------------------------------
CB$SALES_CUBE
CB$CHANNELS_DIM_STANDARD
CB$CUSTOMERS_DIM_STANDARD
CB$PRODUCTS_DIM_STANDARD
CB$TIMES_DIM_STANDARD

In AWM we can see the TIMES_DIM dimension for example does not have the DAY level loaded, the summary in the AW starts at the MONTH level, base fact data is stored in the relational fact table, and is not replicated in the AW.

The cube is defined with all of the desired materialized view options enabled.

Refreshing the materialized views...

Above we configured the materialized views to refresh on demand, with this options we have to manually execute the refresh of the materialized views. The views can also be refreshed on commit (when the data is inserted into the fact or dimension table, the summary is automatically refreshed) or periodically using a simple schedule (using start date and next date expressions).

To manually refresh, detach from the AW in AWM. Now let's manually refresh the materialized views from SQLPlus (just like regular database materialized views you can refresh on demand, on commit, on a scheduler etc.).

• execute DBMS_SNAPSHOT.REFRESH('CB$CHANNELS_DIM_STANDARD','C')
• execute DBMS_SNAPSHOT.REFRESH('CB$CUSTOMERS_DIM_STANDARD','C')
• execute DBMS_SNAPSHOT.REFRESH('CB$PRODUCTS_DIM_STANDARD','C')
• execute DBMS_SNAPSHOT.REFRESH('CB$TIMES_DIM_STANDARD','C')
• execute DBMS_SNAPSHOT.REFRESH('CB$SALES_CUBE','C')

These steps could be included in a process flow for example or other OWB objects (transformations, pre mapping transformation and so on) and executed.

Summary

In summary we have see how cube organized materialized views can be generated from a relational data warehouse design in OWB 11gR2. The rich metadata definitions captured in the OWB designer provide many benefits from data warehouse design to a rich set of operators built specifically for loading warehouses, to summary management capabilities and business reporting integration. In subsequent postings we'll see the query plans and integration with OBIEE.

So yesterday’s Unconference event at OOW was a great success, The Overlook was mostly full, the technology mostly cooperated, and attendees mostly chose to stick with us till the end rather than running for the keynote :)

Mark Rittman presented his experiences with the OBI EE integration in OWB 11.2 and the rest of the product, and David Allan described some of the more advanced possibilities of OWB 11.2 Hybrid Mappings.  The audience was mostly current OWB users and partners, some of whom had also worked with 11.2 and had their own positive feedback.

This afternoon’s customer event (OWB @ the W Hotel: Warehouse Builder Goes Hybrid, 2-4PM, W Hotel, Workroom 2) should be an even bigger draw, with more speakers, more interactivity, and more time to delve into detail. Talk with a panel of OWB gurus (Oracle staff and independent experts) about the latest release, OWB 11.2.

A common problem in warehouses is to load a number of similar files into the database, often the filenames are dynamic so the names cannot be hard-wired, if they are known its easy enough to configure the external table or SQL*Loader mapping with those names. What to do in the dynamic case? Well, here we will see one approach using code templates, we can create a design for what we want and apply it in a code template. We can load more files, faster and easier. This is a great example of customizations of the power of customizations where new templates can be created to solve real world problems.

There are 2 requirements we will specify for the file loading mechanics - the first is to allow a regular expression to be supplied in order to load all files that match a pattern and the second is to support compressed file loading.

The Starting Point

Where to start? The File to External Table code template is a fair starting point, this creates an external table for staging a single file from the file-system via an external table in the Oracle database. Currently the template stages a single file with some small changes we can enhance this to stage many files and also incorporate the preprocessor to handle compressed data.

Essentially all we have to do is define some options on a code template so that a regular expression could be supplied for the desired file names, we will change the template so that it retrieves all filenames that matched the regular expression. An additional change will be to support the preprocessor directive to load directly from compressed files (for example). This will provide better performance of moving the file over the network - often very large files are compressed, this significantly reduces the time to transfer. Rather than having to uncompress this compressed file it can be streamed directly into the database. The Oracle database's external table has a PREPROCESSOR directive to fulfill this exact requirement. Including this as an option in the code template will add significant capabilities.

The Template

Rather than editing the File to Oracle external table seeded template we will use this as a basis. Using the OWB OMB scripting language, we will copy-paste the file to external table code template and apply the changes. Using this approach we can take updates to this base and apply the changes using the script (download here). Below we will look at excerpts of it. Firstly using OMB we can copy paste existing templates to customize.

OMBCOPY CT '/PUBLIC_PROJECT/BUILT_IN_CT/LCT_FILE_TO_ORACLE_EXTER_TABLE' TO 'LCT_FILES_TO_ORACLE_EXT_TABLE'

Then add new options to the code template to allow the user to define which preprocessor to use and an regular expression for the file (below we add FILE_REGEXP, EXT_EXE_DIR and EXT_EXE options to the template).

FILE_REGEXP - Process all files in the directory matching the regular expression.
EXT_EXE_DIR - SQL directory where the preprocessor program resides. This must exist prior to executing the mapping.
EXT_EXE - Name of the preprocessor script. If this value is set the PREPROCESSOR directive will be used in the external table.

When the template is complete there are changes to 2 parts of the create external table statement, the first change is to use the PREPROCESSOR directive if the EXT_EXE options is defined, this variable defines the name of the preprocessor;

The second change above checks for the existence of the regular expression, if no regular expression is defined it uses the file defined in the mapping, if a regular expression is defined then some code is executed to get all the file names that match the regular expression and include those in the external table definition.

The Mapping

The sample mapping below stages the ORDERS and ORDERITEMS data files using the Files to External Table code template, the stage tables are joined with some additional tables and then loaded into the cube/fact, this example illustrates using code templates for staging data in different ways and data warehouse operators (such as cube) integrated using the hybrid mapping design.

The mapping uses a regular expression orderit.*gz to get all COMPRESSED (gzipped) files in the directory, these will be included in the external table. The file names in our example are:

• orderitems_uk.csv.gz
• orderitems_us.csv.gz

Should we add new data files into this directory that match this pattern we could just rerun the mapping to pick them up! We would not have to change anything, simply copy the compressed file into the directory and we can dynamically pick up the data.

The preprocessor program gzunzipdb is a script defined in the SQL directory referenced by EXEC_DIR, and the script calls

• /bin/gunzip -c -d $*

The Oracle external table preprocessor feature takes the standard output of the preprocessor and pumps the data right through the external table. For details of the preprocessor directive see the article 'Using the Preprocessor Feature with External Tables in Oracle Database 11g' on OTN.

When the mapping is executed we can see the how these properties are applied in the template;

If we then look at the actual table definition in SQLDeveloper for example we can see the files are defined in the LOCATION clause and the preprocessor is also included.

What we have seen here is a way in which a new code template can be designed, and where the logical mapping design has remained the same. When the template is applied to the mapping we can define various characteristics that let the same map design move more data, faster and easier than before;

• there is no custom coding,
• file data can be transferred compressed over the network to the data warehouse,
• it is then uncompressed without landing the data on the file system and piped directly through the external table

and best of all, the mapping design remains the same. In subsequent postings I'll show how you add yet another level of dynamic capabilities by providing the regular expression at runtime.