Using the OWB10gR2 Data Object Editor

One of the new features in Oracle Warehouse Builder 10gR2 is the Data Object Editor. The Data Object editor, like the Data Profiler, is a Warehouse Builder "module" that launches when you edit a database object, and works for both relational and OLAP objects. As this feature is going to be new to most users of Warehouse Builder 10gR2, I've been putting together an example of it's use for the seminar I'm working on, to show how you can use it to define a warehouse fact table, summarize it using a materialized view, index it and partition it.

To define a new table or other object, you right click on the required node in the Project Explorer and select "New...". The Data Object Editor then starts up, and you can define the name of the table, add columns and constraints. New in OWB10gR2 is the support for more exotic column datatypes such as TIMESTAMP and XMLTYPE.

The bottom right-hand part of the interface contains a set of tabs for defining the table's properties. New with version 10gR2 of OWB is a tab for Data Rules (logical constraints on a column that get translated into automatic error handling and physical check constraints on the table) and a Data Viewer, for viewing the contents of the table, if it's one you've reverse engineered or subsequently deployed to the database.

Another interesting new feature is the Attribute Sets tab. This allows you to define sets of attributes, a feature I've never used before, but taking a look through the online help, one use for this feature is to define some of the item attributes that get passed through to Discoverer - whether an item is hidden or not, what the default aggregation is for an item, the position of the item, item class and so on.

According to the online help, the way you set this up is to select BRIDGE_TYPE from a "Type" drop-down list next to the name of the attribute set, but the problem I've had is that there isn't such a drop-down list there, just a field for entering the attribute set description. The help makes reference to other features that aren't there - an "Attributes Set of the Entity" section, and properties for the attributes in the set that let you define the Discoverer-specific settings for the column. Either I've missed something in setting this up, or it's actually a feature that was removed during the beta but is still in the online documentation.

Just to wrap this section up, the bottom left-hand side of the screen contains a properties inspector that lets you define the storage parameters for the table, together with something called "Shadow Table Name" and "Tablespace", which allows you to define the name of the table that OWB will use to handle errors if you've placed a data rule on your table.

Whilst OWB doesn't use DML Error Logging to handle table errors in this current release, my understanding is that it's a priority feature that's coming in a future release.

If this is a big table, or one that we want to be constantly available during a data refresh, or perhaps one that will feature a lot of concurrent access, then we'll probably want to partition it. Partitioning is defined using the Partitioning tab in the Data Object Editor, and in this case, we are going to range partition on the DATE_ID column.

The tab also supports the more exotic partitioning schemes, including composite (range-hash, range-list), and also has partition types of "hash by quantity" and "range-hash by quantity" - these aren't new partitioning schemes as such, they're a shortcut provided by OWB to help you quickly define a hash partitioning scheme by just specifying the number of hash buckets you require, and the column to hash on. You can then configure the partitions to share the same tablespace list.

In the next couple of days, I'll be taking a look at how OWB supports Partition Exchange Loading (PEL) with a suitably partitioned table.

Indexing is set up using the Indexes tab in the Data Object Explorer. Setting up an index involves selecting the index type (unique, non-unique, bitmap or function-based), it's scope (global or local) and the index partitioning, which will either be automatically set to be the same as the "parent" column if it's local in scope, or you can select from none (the default), range, hash or hash by quantity if the scope is global. As it can come sometimes tricky to remember which index types are valid for global and local scope types, and in general what the rules are around indexes and partitioning, running a validation on the object is usually a good step as it usually offers some good advice on what's allowable and what's not.

Once you've defined the indexing for the table, the final step is usually to create any required materialized views. Unlike OLAP summaries which we'll come on to later, relational summaries, Materialized Views, are data objects in their own right and you need to go back to the Project Explorer to define them.

Unfortunately this is an area that isn't particularly improved by OWB10gR2; you still have to manually type in the SELECT statement to define the materialized view source, and you still have to define the column names, datatypes and so on.

The configuration options section of the page lets you specify materialized view-specific parameters such as the refresh type, when the refresh takes place, whether it's eligable for query rewrite and so on. This all seems pretty much the same as with earlier versions of OWB.

Where this area does get improved though is when you start to deal with OLAP objects. With a relational OLAP fact table, you define the associated summary using the DBMS_ODM package, which creates a materialized view using GROUP BY ... GROUPING SETS such that a single summary contains multiple levels of aggregation; with a multi-dimensionally stored cube, the summary is stored alongside the detail-level data in the same analytic workspace object, again potentially covering all possible aggregations in the same object. In both these cases, OWB10gR2 lets you define the summary at the same time as defining the detail-level object, just asks you to select the levels of aggregation, and then goes away and sets up the summary automatically. I'll cover this in the next couple of days, but for the time being, that's how you define relational objects using OWB10gR2.