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;

variable_name	variable_value
VAR_REGEXP	orderitems.*gz
VAR_TXNDATE	5-MAY-2009

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.

• 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.

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.

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.

For the convenience of attendees, here’s a floorplan.  Overlook 2 is right across from room 3018.

Mark Rittman will be presenting on OBI-EE integration and his experiences with OWB 11.2, I’ll be presenting on OWB Code Template Mappings that bring ODI Knowledge Module technology to OWB, and David Allan will be showing off some of the more advanced applications of code templates.

Hope to see you there.

Events at Oracle Open World 2009
The following events require an Open World pass for attendance:
 

OWB @ Oracle Open World DEMOgrounds
Mon, Tues, & Wed Oct 12,13,& 14 2009
Moscone West, Pods W-093 & W-094 See map
Come chat with OWB developers and product management about our latest release.

An ETL Framework Using Oracle Warehouse Builder
Monday October 12 2009
13:00 -14:00 in Moscone South Room 270
Speaker: Ian Abramson from Thoughtcorp, Inc
Session: S307863

Case Study: Building a Data Warehouse Cost-Effectively
Tuesday October 13 2009
11:30 - 12:30 in Moscone South Room 301
Speaker: Dr. Holger Friedrich from SumIT AG
Session: S307710

Unconference on OWB
Wednesday October 14 2009
14:00 -15:00 at Moscone West, Third Floor, Overlook II
Panel discussion
Register here

Other Events
The following events are open to the public unless otherwise noted:

Warehouse Builder Goes Hybrid
Thursday October 15 2009
14:00 -16:00 at The W Hotel
Panel discussion
Register here

OWB 11.2: Enterprise-grade Data Integration for the Oracle Data Warehouse
Wednesday October 21 2009
12:00 -13:00 Eastern Time, virtual event hosted by Business Intelligence, Warehousing and Analytics SIG
Speaker: Antonio Romero, Product Manager from Oracle
Register for this webcast here

Come to this 2-hr interactive session to talk with a panel of OWB gurus about the latest release, OWB 11.2.

The spotlight will be on what many herald as the most significant enhancement to OWB in years– hybrid mappings which combine:

• OWB’s traditional ETL capabilities (dimensional modeling, data cleansing, etc.)
• plus a new, more flexible method for accessing heterogeneous sources
• plus the ability to control where transformations are performed (source vs. staging area or target)
• all in a single tool with one repository, one set of operators, one set of table definitions

For those familiar with Oracle Data Integrator (ODI), you’ll recognize hybrid mappings as a combination of OWB technology plus ODI’s knowledge module technology.
For those familiar with OWB, come to this session to learn how you can take your existing OWB mappings and leverage the new technology for heterogeneous access.

Oracle Warehouse Builder is built on top of a metadata repository that captures your design. Once you have created your design in the repository, Warehouse Builder generates the code, and you deploy it somewhere. Prior to the 11gR2 release, for data integration routines, the tool generated a combination of SQL and PL/SQL (plus ABAP and SQL*Loader), allowing you to perform complex transformations on the data you are moving. It was primarily focused on Oracle, files and SAP sources with heterogeneous connectivity to all other systems using the Database Gateways. It was never possible to roll your sleeves up and reconfigure the physical execution of the
mapping to make better use of the assets at hand. It was never possible to change the built-in templates to support features that were not supported out of the box. As we know, technology and the world around us is forever changing! There is a need to be able to integrate new types of data in new and different ways.

With Warehouse Builder 11gR2 it all changes.

The 11gR2 release supports both the existing code generation capabilities and an open extensible code template and connectivity framework based on the Oracle Data Integrator Knowledge Module framework. The benefits of open, extensible code template support is that you can:

• Have much greater architectural flexibility; you can apply technology at the right place and at the right time (push code to execute wherever it can be optimally executed).
• Use alternative data movement strategies (bulk extract, ftp, scp, piped etc).
• Integrate with additional systems through the Open Connectivity framework (platforms and code templates)
• Provide implementations for standard design patterns for these systems (CDC, control, slowly changing dimensions etc).
• Integrate database features that were not incorporated in the core product (for example, data pump external tables, pre-process files for external table) out of product release cycles.
• Construct templates in a modular manner to share across common patterns.

Code Templates

There are different categories of code templates which serve different purposes; there are templates for moving data over the wire (Load), templates for capturing changes (CDC), templates for some data quality tasks (Control) and for data integration (Integrate and Oracle Target). The illustration below gives an overview of how they fit together.

With the OWB 11gR2 repository a number of code templates are already pre-seeded. There are a number of seeded platforms for integration such as Oracle, File, DB2 UDB etc and this set can be extended, allowing the reach of data to grow dynamically and the supporting integration capabilities extensible - a truly pluggable framework.

Code Code Templates (CTs) are components of Oracle Warehouse Builder’s open connectivity framework. CTs contain the knowledge required by Oracle Warehouse Builder to perform a specific set of tasks against a specific system or set of systems. Combined with a connectivity layer such as JDBC, CTs define an open connector that performs tasks against a system, such as connecting to this system, extracting data from it, transforming the data, checking it, integrating it, etc.

Open connectors contain a combination of:

• Connection strategy (JDBC, database utilities for instance).
• Correct syntax (SQL etc.) for the platforms involved.
• Control over the creation and deletion of all temporary/work tables, views, triggers, etc.
• Data processing and transformation strategies.
• Data movement options (create target table, insert/delete, update etc.).

With these concepts the template builder is in complete control of fulfilling the implementation. When extending the reach of the data to be integrated, a new platform can be added, defining a new platform allows system specific code templates to be built that leverage specific features of that system. The platform defines the characteristics of a system such as how to connect to it, SQL characteristics (such as column alias separators, null keyword and so on) and the data types the system supports.

Code Template Mappings

The mapping design is mostly as before, a single logical design with a major shift that you can now configure the physical design and assign customizable code templates or assign the existing non-customizable Oracle Target template. The mapping can include a mixture of both, so you can leverage the flexibility offered for staging data via the open connectivity framework and the transformation and data warehouse operators (for example) provided by the traditional Oracle Target template - for example can leverage the CDC code templates for capturing changes and the data warehouse operators to build the warehouse.

The illustration below shows how a logical map design is physically configured into execution units which are assigned code templates, and the physical design is per configuration.

When we were building this we drew an analogy to islands and bridges, some code templates are bridges that move data from one island to another, and transformations or integration tasks can be performed on an island.

The code template based mappings reside in a different module, since they are executed in a different engine (the Control Center Agent), this module (just like other modules in OWB) is assigned a location, an Agent. The agent is where the mapping will be deployed and when executed this agent will orchestrate its execution.

Control Center Agent

The code template based mappings and web service support is facilitated through the Control Center Agent, a program that can be run on a machine to orchestrate the tasks in the mappings.

A mapping executes on a single Control Center Agent. If you want to remotely execute parts of the map on different systems you will have to use remote API calls including JDBC invocations, remote execute, database jobs using agent and so on.

The agent itself could be placed on a source or transformation system for example if you wanted to unload data using a native un-loader. The code templates are based on the Oracle Data Integrator 10g Substitution Reference interface, this interface (its only an interface, you can’t deploy OWB maps to an ODI agent) has been implemented based on the metadata OWB deploys for locations, connectors, mappings, web services and OWB Control Center Agent components.

The figure below illustrates the mechanics and dependencies of code template based mappings within the Control Center Agent.

The mappings are deployed to this OWB agent in a completely headless manner. They are deployed as J2EE applications but are not heavyweight java applications - the application is basically a very small driver script and a some metadata about the map. The connectors (from agent to source and target system) are deployed as J2EE data sources.

The objects following the same pattern as previous design items, just you can get much more control of what capabilities of a system you would like to leverage. Can envisage some interesting templates leveraging the different loading capabilities of DBFS into database machine in the future.

Anyway that’s a quick update which was easier to do that laying bamboo flooring and building closets (how many times did I hammer my hand, I know get a nail gun next time).

While the formal sessions at Oracle Open World provide a lot of valuable information, sometimes the choicest nuggets turn up at the Unconference sessions, where Oracle employees, customers and partners can delve into the topics that interest them most in an interactive forum.

For OWB customers the best chance to see OWB 11.2 at OpenWorld will be an Unconference session conducted jointly by Oracle DW and BI guru Mark Rittman and data integration product manager Antonio Romero.

The real treat, though, will be the special guest appearance by OWB architect David Allan! Through this blog, David has become one of the public faces of OWB and his knowledge of how to get the most out of the tool has been invaluable to internal and external users.

Planned topics include:

• ODI-based code template mapping capabilities
• Integration of Warehouse Builder with OBI-EE
• Right-time Data Warehousing: CDC, Trickle-feed mappings

…but since we’re in Unconference mode, we can do a little off-roading, time permitting, as the audience demands it.

Schedule details: Wednesday, October 14, Moscone West, Third Floor, Overlook II.

While this is a bit unstructured, it helps to have a preliminary headcount. To register, you’ll need to take a second to join our LinkedIn Group if you haven’t already. Click here to register!

http://www.linkedin.com/groups?about=&gid=140609&trk=anet_ug_grppro

-Antonio