Does OWB Create Logical, or Physical, Data Models (or a Bit of Both?)

July 10, 2006 Uncategorized

I’m just working through the Data Modelling part of my upcoming seminar, and
I’m at the point where I’m thinking about logical and physical modelling within
Warehouse Builder 10.2. Warehouse builder seems to mix up the concepts of
logical and physical modeling, and therefore I thought it worthwhile to work
through what I’m thinking, and perhaps get some input from other OWB users.

Most people’s understanding of logical and physical data warehouse data
modeling goes along these lines:

  • Your logical warehouse model consists of entities, relationships, keys,
    items (or columns) and so forth, is usually concerned with the normalized
    view of the warehouse (usually the staging or ODS layer), and is held in
    either spreadsheets or a tool such as Oracle Designer. You might also
    annotate your model with useful information ("metadata") and
    perhaps business rules, although this will usually go in the functional
  • You might also have a logical model of the dimensional part of your
    warehouse, which contains elements such as dimensions, hierarchies, levels,
    attributes, measures and so on. It is product-neutral and again might be
    stored in Excel or Designer.
  • The physical model, usually automatically derived from the logical model,
    contains the same items, perhaps denormalized, but with physical attributes
    – datatype, index details, partitioning details, storage clauses and so on –
    added on. This will certainly cover both the normalized and dimensional
    views of the warehouse, and it may in fact be the first point at which
    dimensional objects are mentioned.

Now with warehouse builder, the physical and logic models of the warehouse
appear to be, to my mind, mixed-up together. One way you could look at it would
be that the logical model is what’s in the OWB repository, whilst the physical
model is the result of the logical model’s deployment – but that doesn’t give
you a phyiscal model as such, just a logical model and the results of it’s

In fact the model that’s in the OWB repository contains both logical and
physical elements. It contains metadata and business rules (a.k.a. "data
rules") which could certainly be considered as part of the logical model,
and OLAP constructs that are held at a level of abstraction above the physical
implementation, to support the ability to deploy a dimension or cube to either
relational or multi-dimensional storage. And yet, what the OWB repository looks
most to me like is a model of the physical implementation of the warehouse with
elements of the logical model thrown in, but to work it through it’s probably
worth walking through some examples of how objects are represented in the
repository, to see if this idea holds water.

A first example is where the physical definition of objects in the OE schema
are imported into a data source model. In this case, the objects from the OE
schema appear in a data source module, and they’re clearly a straight
representation of the physical implementation of the objects, i.e. this is a
physical database model.

There’s no entities, relationships and so on. It’s a straight
physical model.

Now, say we do some data profiling on the OE schema, carry out
some corrections and define some data rules. A data rule is a logical construct
in Warehouse Builder that translates into a physical check constraint on the
physical table, and some instructions to OWB to wrap up the table, whenever it
is mapped to, in a construct that deals with rows that fail the data rule. So,
our model now contains logical information – the data rule – together with the
physical table design. It also contains functional information – a mapping to
implement a correction based on this data rule – and some additional tables that
the mapping will use when processing the correction.

Next, we define a dimension that takes it’s data from the
CUSTOMERS table. Some people would say that a dimension is a physical
implementation of the logical object, Customer, whilst others would argue that
the dimension object is a logical object in it’s own right. When we come to
create the customer dimension, it sits under the database node along with
tables, cubes, external tables and sequences, which would suggest that it’s a
physical object that’s held alongside the other physical objects in what must be
the physical model of the warehouse.

And yet – when you view the dimension in the Data Object Editor,
you can change the deployment type for the object from relational to
multi-dimensional, which suggest that the object we’re dealing with is a logical
representation of the dimension, with the physical implementation being hidden
away internally so as to present a unified approach to the developer. I can see
why this has been done – to keep away from the developer the complexities of an
OLAP deployment – but it does mean that OLAP objects in this model are logical
representations, not physical (although you can specify details of the physical
implementation, such as the AW name and tablespace within this logical model,
once you’ve chosen a storage method).

So my conclusion is, that OWB is a physical database modeler,
with certain details abstracted out (data rules, OLAP storage) to make life
"easier" for the developer. It’s not really a logical modeller,
although you could consider the representation of the warehouse in the OWB
repository as being the logical model, although I’m sure a user of Designer
would disagree.

Does anyone else have any views on this, perhaps a different
perspective? Add a comment if you have.