Everything Oracle

Home

Everything Oracle

 

 

 

 

 

 

Introduction

 

OBIEE is a very large and a very complicated product suite – Oracle takes over 2,800 pages to describe ... how shall I put it ... all the “gory” details.  As I am not inclined to write, nor I suspect are you inclined to read, such a large volume of text, this overview must of necessity be rather selective in its scope.

 

At its core OBIEE works in much the same way as “Oracle BI Discoverer” or any of the other ad hoc query tools designed to be “exercised” by business users.  In this article we’ll look at what this core functionality is and how it appears from both a business user and a developer perspective. 

 

 

How ad hoc query tools work

 

Let’s start with a very simple example.  From your collection of relational database tables, stored in, say, an Oracle database, you select a subset that is of interest to a particular group of business users.  Let’s suppose you select the “Customers”, “Products”, and “Sales” tables:

 

To keep things simple let’s suppose that each table only contains the following rather modest number of columns:

 

Now your developers could join these tables together to create a single denormalized view of the entire set of tables, leaving out those columns that are not of interest, and giving those columns that are included business-friendly presentation aliases:

 

SELECT c.customer_name “Customer Name”,
       p.product_name “Product Name”,
       s.sales_quantity “Sales Quantity”,
       s.sales_value “Sales Value”
  FROM customers c,
       products p,
       sales s
 WHERE c.customer_id = s.customer_id
   AND p.product_id = s.product_id
 ORDER by 1, 2;

 

But your developers are not restricted to selecting column values.  They could also select SQL expressions that perform various calculations; for example:

 

SELECT c.customer_name “Customer Name”,
       p.product_name “Product Name”,
       sum( s.sales_quantity ) “Total Sales Quantity”,
       sum( s.sales_value ) “Total Sales Value”
  FROM customers c,
       products p,
       sales s
 WHERE c.customer_id = s.customer_id
   AND p.product_id = s.product_id
 GROUP by c.customer_name, p.product_name
 ORDER by 1, 2;

 

Clearly, if this example were more realistic and you were to include all column values and expressions likely to be of interest to your group of business users then you might be selecting hundreds of columns from a dozen tables.

 

Now let’s suppose that this denormalized master view of “interesting” data is held in some memory resident data store, ready for use.  Since business users regard SQL with much the same displeasure that vampires regard garlic, you'll need to hide the SQL and just present your “programmatically challenged” colleagues with a set of items corresponding to the presentation aliases:

 

Customer Name
Product Name
Total Sales Quantity
Total Sales Value

 

Again, in a more realistic example, you might have hundreds of these presentation items, and you’d make an effort to organize them into logical groups that formed the nodes on a tree that business users could navigate – in much the same way that they navigate directory structures within Windows Explorer.

 

Now to create a query all a business user has to do is to click on the presentation items that are of interest; for example:

 

Customer Name
Total Sales Value

 

Behind the scenes the software will retrieve the master query, and then prune it by eliminating those columns and expressions that have not been selected, and by removing any redundant table joins, “group by”, and “order by” expressions:

 

SELECT c.customer_name “Customer Name”,
       sum( s.sales_value ) “Total Sales Value”
  FROM customers c,
       sales s
 WHERE c.customer_id = s.customer_id
 GROUP by c.customer_name
 ORDER by 1;

 

Then the software will run the pruned query and display the results just as the business user requested.

 

So, in essence, ad hoc query tools are very simple.  Developers need to construct the master queries that are of interest to business users, and then business users just choose from the selection of presentation items that are on offer.

 

 

Simplified OBIEE Architecture

 

If we reduce the architecture of OBIEE to its bare essentials, we can say it consists of a “user facing” component – the “BI Presentation Services” – and a “data source facing” component – the “BI Server”:

 

The BI Presentation Services receive the selection of presentation items made by a business user, construct a very simple SQL query, and forwards it to the BI Server.  In the case of our example, the log file lists the SQL sent to the BI Server as:

 

From the perspective of the BI Presentation Services all the presentation item values correspond to column values in a single database table – called ‘“OBIEE Introduction”’ in the present case (see below for how this query corresponds to the Presentation layer within the repository).

 

The BI Server translates this simple request into optimized, native SQL sub-queries and sends each sub-query to the corresponding data source.  In the case of the example, we have only a single data source, and the log file lists the SQL being sent across the network to our Oracle database as:

 

As you can see this query corresponds exactly – apart from the formatting – to the pruned query that we derived in the previous section:

 

SELECT c.customer_name “Customer Name”,
       sum( s.sales_value ) “Total Sales Value”
  FROM customers c,
       sales s
 WHERE c.customer_id = s.customer_id
 GROUP by c.customer_name
 ORDER by 1;

 

The BI Server waits for the results to be returned, integrates them together, and performs any calculations that couldn’t be performed by the data sources.  The BI Server returns the results to the BI Presentation Services in the simplified format that the BI Presentation Services expects.  Then the BI Presentation Services “prettifies” the data – formatting it as a table, a pivot table, or a graphic chart – before sending it on its way back to the business user’s web browser.

 

 

The Business User Perspective

 

Now, let’s look at our simple query from the business user perspective.  The component of the BI Presentation Services that is used to formulate queries (in OBIEE speak they are called “requests” – it makes OBIEE sound more up market, and thereby merit an enhanced license fee) is called “BI Answers”.  If a business user had just created a “request” corresponding to the above query then his web browser would display the following screen:

 

In the left-hand pane you can see the four presentation items that we’ve made available for our user to select from: “Customer Name”, “Product Name”, “Total Sales Quantity”, and “Total Sales Value”.  Each has been organized within an appropriate folder, and these in turn have been allocated to a “Subject Area” called “OBIEE Introduction”.  While this level of organization is clearly unnecessary in this case of this simple example, you can see that it would prove very useful were there hundreds of items from which a user could choose.

 

In the right-hand pane you can see the two presentation items that the user has selected by clicking on them in the left-hand pane: “Customer Name” and “Total Sales Value”.  Now to run the query – sorry “request” – all the user has to do is to click on the “Display Results” button.  Then, after a brief, or not so brief, hiatus – during which the user is exposed to a clock ticking at a maddeningly fast pace – the results of the “request” are displayed:

 

Had our business user selected “Product Name” and “Total Sales Quantity” instead then the results would have been:

 

And if all four possible items had been selected then the results would have been:

 

Clearly, from the end user perspective using OBIEE to perform basic queries like these is extremely simple.  With one click per item and one click on the “Display Results” button to follow, OBIEE couldn’t be more “minimalist” in the effort that it requires.

 

If the queries are not “one-off”, but need to be executed on a regular basis, then the user can save them and add them to a “dashboard”.  On entering this dashboard at some future date the associated queries are automatically performed and the results displayed; for example, if our user had placed all three of the above requests on the same dashboard it would appear as follows:

 

You can see that even with the default formatting, this dashboard looks very “presentable”.  And if the user is so inclined, then a vast set of formatting options exist to change colours, fonts, and just about anything else that you could think of.  And if a simple table is not enough then various other representations of the same data, such as a pivot table:

 

or a chart:

 

are only a few clicks away.  If the user is so inclined, then all these representations of the same or different data sets can be added to a dashboard until it resembles the flight deck of a 747 – your CEO can imagine that he really is a captain of industry!

 

Whether all these tables, dials, gauges, and charts will lead to improved decision making is, of course, a moot point.  But if decision making does not improve then at least the blame clearly lies with the business user and not with the IT department.

 

 

The Developer Perspective

 

Now, of course, some developer is going to have to create the master query that provides business users with their simplified view of business data.  In OBIEE the equivalent of master queries are held in a data store called a “repository”, and they are created and maintained using a software product called the “BI Administration Tool”.  In the case of our example, the display within the Administration Tool looks like this:

 

Now you can see that the left-hand “Presentation” layer is identical to the pane that the user saw in BI Answers.  So the left-hand pane represents the output, the view that the BI Server presents to the BI Presentation Services.

 

You can also see that the right-hand pane, the “Physical” layer, is almost identical to the table descriptions we saw earlier in SQL*Plus (the “Connection Pool” element tells the BI Server how to navigate the network to the database or file system that contains the data in question).  In this diagram all the tables reside in a single database, but in a more complex situation multiple databases might be involved with a single “heterogeneous” or “federated” query spanning disparate data sources.  So the right-hand pane is the view that the BI Server sees when it looks out across the network to the sources from which it draws its data.

 

Now OBIEE doesn’t actually store the SQL statement that maps the Physical layer onto the Presentation layer – you can’t call up an editor and see it, more’s the pity.  However, you can imagine it as hiding behind the middle pane, the “Business Model and Mapping” layer.  The table joins and calculations that form our query are broken up into components and are distributed like “confetti” amongst the elements that appear in the middle layer (principally under the “Sources” nodes).

 

This distributed approach to master query specification increases the learning curve for developers.  However, once a developer is familiar with the architecture of the middle layer and knows which components of the master query are stored under the various nodes, the Administration Tool proves a reasonably productive environment.  The tree-based organization proves very useful when it comes to organizing large, complicated mappings.  And the good use made of the different types of icons helps to explain the purpose served by the elements at the various nodes: the white table icons prefixing “Customers” and “Products” denote logical dimension tables; the yellow table icon prefixing “Sales” denotes a logical measure table; and the “sigmas” prefixing the two sales totals denote aggregations (in addition to the icons appearing in this diagram, almost all practical instances of the Mapping layer would also contain “Dimension” nodes that are used to allocate logical columns to levels within a dimension).

 

The main weakness of the Administration Tool is that for a modest amount of effort Oracle could have produced a far more productive development environment.  Almost everything in the Presentation and Mapping layers could be derived automatically from the contents of the Physical layer.  And the Physical layer can be imported as metadata from the relevant data source, leaving very little to be “tweaked” by hand.  In general, once a developer has selected the physical tables to be used and has designated those that are to serve as measure tables, then nearly complete versions of the Middle and Presentation layers could be generated automatically.  All the developer would have to do to polish off the mapping would be to delete redundant elements, to add any non-standard calculations, and to allocate dimensional levels for some of the logical columns (where the relational tables are in third normal form the allocation of dimensional levels could be fully automated).  We must hope that in some future version of OBIEE Oracle will address this shortcoming, and will also provide us with a screen where developers can see the master query in all its glory in the familiar form of an SQL statement.

 

 

 

Everything Oracle

Home

Everything Oracle

 

Copyright © 2008 PWG Consulting, All Rights Reserved