Everything Oracle

The Problem

One of the great weaknesses of the Administration Tool is the poor quality of the diagrammatic support it provides for developers. We have a physical layer diagram, such as:

Physical Layer Diagram

and a logical layer diagram, such as:

Logical Layer Diagram

but what we don’t have is a diagram that shows the mapping between these two layers.

Indeed, the fact that the names in these two diagrams have been anonymized and shorn of all semantic context, illustrates the point that these diagrams offer no support in understanding the logical to physical layer mapping whatsoever.

Now, of course the logical to physical layer mapping is defined in the metadata, but to gain an understanding of the mapping it’s necessary to open up, in turn, all the logical table sources, all the complex join links within the physical layer diagram, and all the links within the business model diagram. And it’s necessary to build and remember a mental mapping based on this analysis. The lack of diagrammatic support leads to implementation errors, and makes maintenance more difficult and more costly than it needs to be. It makes it difficult for a senior developer to draw up a design and then pass it on to a junior developer for the purposes of implementation. And it greatly increases the learning curve for developers who are new to OBIEE.

So what would be really useful would be a “see it all at a glance” diagrammatic representation that would allow a developer to see the logical to physical layer mapping in its entirety – given the high level of diagrammatic support that is found in most other tools, and the fact that OBIEE was developed by nQuire in the late 1900s, it’s surprising that neither Siebel Systems, in its time, nor Oracle, since its acquisition of Siebel Systems, has addressed this issue to date.

A Solution

I say “a solution” since there are many different ways of graphically representing the mapping between the physical and logical layers, so what’s on offer here is a general outline, rather than a detailed prescription (bear in mind that this is not the only diagram that is missing from OBIEE: a presentation column to source column(s) mapping diagram is also required – but that’s another article).

The diagrammatic convention that I’ll adopt for a logical decomposition diagram is as follows:

Physical layer tables that map onto the same logical table will be grouped together within an oval grouping box;

By default all physical layer tables within a grouping box will be assumed to belong to a single logical table source;

When physical layer tables within a grouping box belong to two or more logical table sources, then the tables belonging to each logical table source will be enclosed within a dashed rectangle;

All unannotated joins, whether specified within a logical table source or in the business model diagram, will be inner joins;

Outer joins, whether specified in a logical table source or in the business model diagram, will be denoted by “FO” (Full Outer Join), “RO” (Right Outer Join), and “LO” (Left Outer Join) respectively;

The letter “C” will be appended to the outer join notation where the join is a Cartesian product, representing a “1=1” complex join condition within the physical layer;

Joins within grouping boxes will represent joins specified within a logical table source; and

Joins between grouping boxes will represent joins defined in the business model diagram.

Like all definitions, the above is as “clear as mud”, so let’s get stuck into some examples to see if this notation for logical decomposition has any merit in it.

Some Examples

Logical Table Source and Business Model Diagram Inner Joins

Let’s start with a simple example. The following diagram has the same layout as the physical layer diagram with which I started this article. The only structural difference between the diagram and the physical layer diagram is the presence of the four oval boxes that provide a table grouping mechanism and their associated names:

Logical Decomposition Diagram With Inner Joins

The diagram tells us that we have four logical tables in the business model diagram, each of which has a single logical table source. Going clockwise we have three logical dimension tables, “Customers”, “Calendar”, and “Products”, and one logical measure table, “Orders”.

The logical table source for logical dimension table “Customers” will contain inner joins between the three physical layer tables: “Addresses”, “Customer Addresses”, and “Customers”. The logical table source for logical measure table “Orders” will contain an inner join between the physical layer tables “Orders” and “Order Items”.

The joins in both the logical table sources and the business layer diagram are all inner joins.

Now imagine the diagram with the grouping boxes and the associated labels removed. Not quite so easy to immediately grasp the mapping, is it? Now imagine a more realistic diagram with many dozens of tables. Now imagine that the table names are more esoteric, referring to a business with which you are not familiar. Now imagine that you’re new to OBIEE and are still trying to grasp how the mapping process works. The merits of “boxing” the tables should now become apparent!

What’s nice about this approach is that it provides a systemic method for creating the logical layer:

For each grouping box create a logical table;

For each logical table source add joins for any tables found within the box;

Drag across all items needed in the logical layer from the corresponding physical tables; and finally

Create a business model diagram that reflects the joins that exist between the grouping boxes.

Logical Table Source and Business Model Diagram Outer Joins

Let’s take a business that has customers and products and makes sales. Couldn’t be simpler. But now let’s assume that not all customers have purchased a product and that we want our Answers report of total sales by product and customer to show all customers, and not just those who have purchased a product.

There are two solutions to this problem. Here’s the first:

Logical Decomposition Diagram With Outer Joins

This diagram tells us that we have two logical tables in the business model diagram, each of which has a single logical table source. Going from top to bottom, we have a logical dimension table, “Customer Products”, and a logical measure table, “Sales”.

The logical table source for logical dimension table “Customer Products” will contain a full outer join between table “Products” and table “Customers”. The join is a Cartesian join (a complex physical layer join with a “1=1” join condition).

The logical join in the business layer diagram is a left outer join.

The point here is not whether you immediately grasp that this mapping will give the required result: the point is that it is immediately clear – if you're familiar with the notation – what the mapping is and how it should be implemented in terms of setting the metadata within the logical layer:

Create a complex join in the physical layer between tables “Products” and “Customers” with a “1=1” join condition;

Create a logical dimension table “Customer Products” with a single logical table source that contains a full outer join between tables “Products” and “Customers”; and

In the business model diagram or the join manager, create a left outer join between dimension table “Customer Products” and measure table “Sales”.

Implicit Federated Outer Join

Now for the second solution:

Logical Decomposition Diagram With Implicit Federated Outer Join

This diagram tells us that we have three logical tables in the business model diagram. Going clockwise, we have two logical dimension tables, “Products” and “Customers”, and a logical measure table, “Sales”.

Dimension tables “Products” and “Customers” each have a single logical table source, while measure table “Sales” has two logical table sources – the rectangular boxes. One logical table source maps onto physical layer table “Sales” and the other onto physical layer table “Dummy” – an opaque view that is used to force an outer join. All of the logical table sources map onto a single physical layer table.

In the physical layer, table “Sales” has normal foreign key joins from both “Products” and “Customers”, while table “Dummy” has complex joins, each with a join condition of “1=1”.

Once again, the point is not whether you immediately grasp that this mapping will give the required result: the point is that it is immediately clear what the mapping is and how it should be implemented in terms of setting the metadata within the logical layer:

In addition to the normal foreign key joins, create two complex joins in the physical layer between tables “Products” and “Customers” and “Sales”, each with a “1=1” join condition;

Create two logical dimension tables each with a single logical table source for tables “Products” and “Customers”;

Create a logical measure table “Sales” with two logical table sources, one mapping to “Sales” and one mapping to “Dummy”;

In the business model diagram, create standard inner joins between the two dimension tables and the measure table.

These examples should give you a feeling for whether you would find this diagrammatic approach to logical to physical layer mapping useful. The notation can be easily extended to deal with other mapping scenarios, such as aggregation and fragmentation.

Recommendations

While a logical decomposition diagram as described above provides an excellent means of summarizing the logical to physical layer mapping, ideally the diagram would also be used as a means of implementing the mapping as well.

Suppose that a facility to create this diagram is added to the Administration Tool. When the business model diagrammer is opened up the physical layer diagram would be replicated in the logical layer. Then the developer would use a set of drawing tools to draw the grouping (oval) and logical table source (rectangular) boxes, as above. As each box is drawn an appropriate popup window would appear to allow the relevant join conditions to be specified. The result would be that all the logical table sources could be created automatically, and edited directly from the business model diagram. For a repository with a single business model all the work could be done in the physical layer using just one diagram – if you’re “listening in Bristol” how about slipping this functionality in with the forthcoming 11g?

Summary

My experience is that logical decomposition diagrams greatly improve the ease with which developers new to OBIEE gain an understanding of repository mapping. And for the experienced OBIEE developer a logical decomposition diagram offers a useful documentation aid that allows the essentials of a mapping to be understood at a glance, obviating the need to open up “all those pop-up windows”.

To assess the utility of this approach I performed a little experiment. I explained the notation to a number of OBIEE developers and then gave each of them a pair of repositories of comparable complexity, one with an accompanying logical decomposition diagram, and one without. Then the developers were asked to make a simple change to each repository. The sample size was small so the results are only anecdotal: when no logical decomposition diagram accompanied the repository the developer spent some time “hunting” through the metadata to gain an understanding of the mapping before the change could be made; but when a logical decomposition diagram accompanied the repository the developer when straight to the location where the change needed to be made.

Everything Oracle

Home

Everything Oracle