Don’t forget the Logical Layer

I often see OBIEE RPD’s which are developed via “Drag ‘n Drop”. If you know what you are doing this hasn’t necessarily have to be a problem. It becomes a problem if you do not know what you are doing. Especially dragging and dropping multiple tables together can give you additional ‘functionality’, which you do not need. Sometimes you are not even aware that you added additional links and keys, you don’t need. It becomes even worse when it’s ‘functionality’ you don’t want. I know from experience that it can cause incorrect results, depending on the columns you select in your analysis.

Normally I build my Logical Models manually from scratch instead of via “Drag ‘n Drop”. This way I know exactly what I am doing and I don’t get surprised by unintended functionality. This is by no means a ‘Best Practice’, but it works for me. Let’s consider it to be a ‘Right Practice‘.

There is another reason for avoiding a “Drag ‘n Drop and leave the Logical Layer like that”-Construction of the Logical Layer. The Logical Layer is an important part in the construction of the actual Physical Query(s). The Oracle BI Server does not have to be a Black Box. You just need to give the Oracle BI Server as much information as possible so you know / understand, what query(s) the Oracle BI Server will fire to the underlying database(s). Pay attention to the following recommendations:

  • Dimension Logical Tables must have a Logical Key assigned, if possible a business related key (order number, purchase number, employee number). Fact Logical Table do not have a Logical Key assigned
  • Fact Logical Tables contain Measure Columns with an Aggregation Rule (SUM, COUNT, MIN, MAX, etc.) applied to it.
  • Define a Dimension Hierarchy for every Dimension Logical Table
    • Each Level is unique
    • The Primary Key of the lowest Level matches the Primary Key of the Logical Table
    • Specify the number of element per Level
  • Specify the Content Level for each Logical Table (Column)
    • Each Content Level ‘belongs’ to a Level in the Dimension Hierarchie
  • Construction of the Logical Table
    • (Re-) Naming Conventions
    • Only those columns you really need

The above listing is by no means exhaustive. There are few interesting links you can check out, when it comes to modeling the Logical Layer;

One important note has been made by Christian Berg; “Think and Understand before you give ‘Advice’“. The same goes when you are trying to apply things yourself. Don’t just do it, because you can. Do it because you understand why you have to do it that way.

Feel free to comment.

Joins in Oracle BI EE

There a lot of questions on the Oracle Forums, recently also, about join in Oracle BI EE. It seems like there are a lot of misconceptions about the use of joins in Oracle BI EE. 

There are two types of joins;

  • Foreign Key Join
  • Complex Join

These joins could be applied to two different in layers;

  • Physical Layer
  • Logical Layer (Business Model)

 –> When would you use which join?

You can use both joins in both layers. There are some basic rules when it comes to using joins in Oracle BI EE.

In the Physical Layer, you should use Foreign Key joins, while in the Logical layer it’s common to use Complex Joins.

–> Why should you use these joins ?

Physical Layer – Foreign Key Join

The Oracle BI Server uses the Foreign Key Joins to construct the where clause when selecting form multiple tables.

There are two types of keys in the Physical Layer:

  • Primary Key – Unique identifier of a single record in a table
  • Foreign Key – Reference to the Primary Key of another table

So basically the Foreign Key Join is a ‘Primary Key / Foreign Key’- relationship between two tables.

 There are situations where you could use Complex Joins in the Physical Layer. Jeff McQuigg  has written a good blogpost about this subject.

Basically it comes down to the following three situations:

  • Range Joins
  • Data Type Conversion Joins
  • Cartesian Joins

Logical Layer – Complex Join

You use Complex Joins in the Logical Layer only to tell the Oracle BI Server that there is a link between the two tables. The Oracle BI Server should go to the Physical layer to see the actual link between the two tables.

A logical tabel consists of one or more Logical Table Sources. Multiple Logical Table Sources  lead to multiple join paths. Using a Foreign Key join in a situation where multiple join paths exists, restricts the Oracle BI Server only to use the specified join. Therefore it is common practice to use a Complex Join in the Logical Layer.

I hear you think; Why is the Foreign Key Join supported in the first place? It seems to only be there for backwards compatibility

In the Logical Layer you define Primary Keys as well. The purpose of a Primary Key in the Logical Layer is….

  • …to identify the Unique identifier of a single record in a Logical Table
  • … to identify the lowes level of detail of a Logical Table

Sorting the Month Name

I saw a post on the Oracle BI EE Forums today; Sorting month name. There were various solutions to the question. I think Kishore Guggilla came up with the best solution, so he deserves all the credits.

Although, it’s a very easy solution to a very simple ‘problem’, I thought I write a little post about the subject, because the solution can be used very often. Not only in a month sort.

Picture the following. I have created a dimension table with month names and month numbers.

Based on this dimension table I have created the following Business Model.

Now we can create a simple report with the month names.

As you can see the month names ar sorted alphabetically. We can verify that by the query fired.

If we want to sort the month names based on theire month number, we have various options. As said before I like the solution via the repository. In the repository you can sort a column, using another column. Just change the properties of the logical column. Now the BI Server knows that it hast to sort the month name via the month number.

Now we can create the same simple report with the month names.

As you can see now the month names ar sorted by month number. We can verify that by the query fired.

Easy but powerful in some cases.

Multiple Fact Reporting on (Non-)Conforming dimensions – Part II

Last week I have been blogging about Multiple Fact Reporting on (Non-)Conforming dimensions. Thanks to Nicolae I was triggered to do some further investigation on this topic. He had a question; What happens when you want to filter on a non-conforming dimension?

When you filter an a non-conforming dimension, you could get a null value for the fact which has all the dimensions as conforming.


Is this not exactly how the Oracle BI-server works? We have created one logical fact table and two different logical table sources (LTS). The Oracle BI-server creates seperate queries for each LTS in a Oracle BI Answers query.

Query I:

Query I connects fact table 1 to all the dimensions, including a filter on the non-conforming dimension.

Query II:

Query II connects fact table 2 one to the conforming dimensions only. Because there is no physical relationship between fact table 2 and the non-conforming dimension, it is not possible to filter query II on  dtnc1.value3 = ‘3CCC3’ as well.

The results of both queries:

Lucky enough for me Nicolas did some investigating himself to:

It looks like the most easy solution is to filter on the fact table which has all the dimensions as conforming. In this case, that would be; Fct1 Value1.

I will try to ask some other sources what their solution is to this ‘problem’.

More to come.

Multiple Fact Reporting on (Non-)Conforming dimensions

There are some questions, which are popping up at the Oracle BI EE Forums regularly. One of those questions is; 

*** How to model multiple facts against (non-) conforming dimensions?

I will try to work things out. Click on the images to see more detail.

Note: I am aware of the extra white space between the images. That’s not intended functionality, but lack of knowledge of  WordPress. 

Picture the following:

There are two fact tables and three dimension tables. FACT_TABLE_1 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2 and one non-conformed dimension table DIM_TABLE_NON_CONF_1.

FACT_TABLE_2 has two conformed dimension tables; DIM_TABLE_CONF_1 and DIM_TABLE_CONF_2. 

The Physical Model would have the following structure:

Physical Diagram

Based on the Physical Model we could construct the following Logical Model: 

Logical Diagram

I have created one fact table which contains Logical Table Sources (LTS) for FACT_TABLE_1 and FACT_TABLE_2

Logical Model

As you can see I have created Dimensions (Hierarchy’s) for each Dimension Table.   

FACT_TABLE_2 has no physical relationship with DIM_TABLE_NON_CONF_1. Therefore you should set the logical levels for FACT_TABLE_2 to the ‘Grand Total’-level of DIM_TABLE_NON_CONF_1. This way the Oracle BI Server won’t look for a join between DIM_TABLE_NON_CONF_1 and FACT_TABLE_2.

If you want to avoid nulls, set the detail levels for the facts. Set the ‘Grand Total’-levels for the metrics as well.

Logical Table Source - Fact I
Logical Table Source - Fact II
Logical Column - Fact II

If we take a look at Oracle BI Answers, we can create a report which contains data from the following tables;

Oracle BI Answers - Conformed Dimension

Now we can bring data from DIM_TABLE_NON_CONF_1 into this report. It is impossible to devide data from FACT_TABLE_2 over this dimension. Therefore the data will be the same for every value of this dimension.

Oracle BI Answers - (Non-) Conformed Dimension

*** Summary:

 It’s possible to report on facts and dimensions which not have a physical relationship to each other. Just make sure you create dimensions (hierarchy’s) for every dimension table. Next to that you should set the logical levels for your logical tables.