Oracle BI EE 10.1.3.4.1 – Single Table Repository Design – Part 1

One of the best ways to understand the BI EE repository design is to start designing the Business Model Layer for a single Physical table. Any person who is new to BI EE will always start with testing the repository for a single physical table. This is where BI EE can be quite confusing for people who are crossing over from the realm of Business Objects, Cognos, Discoverer etc. Apart from providing a beginner’s perspective this type of design actually provides a very good understanding of the BI EE repository modeling. Advanced form of a single table BI EE design is used in reporting on Oracle OLAP 10g SQL views. I will be covering the latter in a follow up to this blog entry.

There are quite a few ways to approach the single table design. I will cover the most commonly used one (& its importance as i have seen some implementations where un-necessary joins are made). In the approach below, i shall be using a single table called as the ORDERS table from the OE schema.

BI EE mandates the presence of Dimensions and Measures in any Business Model. But a lot of people tend to argue that in a complete OLTP or a normalized data model, it is not always possible to identify measures & dimensions. A single column can act as a measure as well as a dimension metric. For example, Max Retail Price of a product can be a product attribute (dimension metric) while analyzed along with Products. But the same metric can be used as a measure while analyzing across all the other dimensions. So, in such cases, rather than grouping them as measures & dimensions, i always tend to group various columns into 3 broad classifications

1. Aggregating Attribute (any attribute which can be aggregated like Count, Sum etc)
2. Non-Aggregating Attribute
3. Both

In almost all the cases, you will for sure find attributes matching any of the 3 criteria above. In our case, the ORDERS table has the following columns.

ORDER_ID
ORDER_DATE
ORDER_MODE
ORDER_STATUS
ORDER_TOTAL
CUSTOMER_ID
PROMOTION_ID

In this table, all the attributes apart from ORDER_TOTAL are non-aggregating. To make it a bit interesting lets make ORDER_TOTAL to be considered also as an Order Attribute (non-aggregating). So, in effect we have 6 non-aggregating attributes and one attribute that is aggregating as well as non-aggregating depending on the report context.

So, we start with creating a Business Model layer containing 2 logical tables. Aggregating & Non-Aggregating (in BI EE terminology these are Facts & Dimensions).

Each logical table will contain the columns depending on the type of logical table. BI EE requires a minimum of 2 (both aggregating and non-aggregating attributes should be part of these 2) logical tables. So we start with including all the attributes (depending on their type) and in the corresponding logical table. The BMM layer offers the flexibility of having the same physical column to be treated as a dimension non-aggregating attribute and also as an aggregating measure/fact. In our case, ORDER_TOTAL physical column will be mapped in the dimension logical table as well as the fact logical table.

And every dimension logical table requires a business primary key so that the level of detail provided by each non-aggregating dimension table can be ascertained. The other important point to note is the logical join between the 2 logical tables. A logical join (complex as well as foreign key joins) denotes 2 important points

1. That the 2 logical tables are related to one another (possible to bring 2 columns from 2 tables in a single query)
2. That the 2 logical tables are related by either an inner join, outer join or a full outer join.

The first point above is straight forward. But the 2nd point will be the most confusing part since most tools out there provide a means of specifying outer joins in the physical relationship between the tables. BI EE is probably one unique tool that pushes this kind of join relationships to the Business Model layer (which is absolutely spot on though one quirk is we cannot control of order of outer joins in a physical query yet). For many this might look a bit odd but once you start understanding how the physical queries are structured, this will start making more & more sense.

For a single table model though, we just need to specify just the join between both the logical tables along with the cardinality. If we move on to the reports, we should be able to query both the ORDER_TOTAL’s in a single report

And if you look at the SQL, you will notice that one ORDER_TOTAL is pushed as part of the GROUP BY clause and the other will be pushed as a measure within the SUM clause

select T17214.ORDER_ID as c1,
     T17214.ORDER_TOTAL as c2,
     sum(T17214.ORDER_TOTAL) as c3
from
     ORDERS T17214
group by T17214.ORDER_ID, T17214.ORDER_TOTAL
order by c1, c2

This is pretty straight forward. There is another approach that i have seen people use in the past. For a single table, developers tend to create as many aliases as the number of dimensions & fact logical tables. Then a join is made on the primary key of the table (in our case ORDER_ID). All this is done in the physical layer as shown below

Then each alias is mapped on to the logical dimension & fact tables as shown below

This will work as well. But lets create the same report as shown above and look at the physical SQL

select T29596.ORDER_ID as c1,
     T29596.ORDER_TOTAL as c2,
     sum(T29605.ORDER_TOTAL) as c3
from

          ORDERS T29596 /* Alias - Dim Orders */  inner join
          ORDERS T29605 /* Alias - Fact Orders */  On T29596.ORDER_ID = T29605.ORDER_ID
group by T29596.ORDER_ID, T29596.ORDER_TOTAL
order by c1, c2

As you see, though the report output will be the same, the physical sql does a self-join to itself through the aliases that we created. So, this is not an approach that i will recommend for a single table design as it does not perform well(once you have more & more isolated fact tables) at-least in this context. Aliases can be very useful sometimes but when used inappropriately can result in pretty bad queries thereby causing bad performance.

Single table design can get quite complicated when dealing with monstrous views like Oracle OLAP 10g views. I will cover some of the concepts like Dimension Binning (will be useful in general for any other SQL exposed OLAP tool like Hyperoll etc) used in BI EE while going against OLAP 10g in the next blog entry.

i