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.

Implementing Row-Level-Security in Oracle BI EE

Sometimes there is a need to restrict data access to certain groups of users. Oracle provides a mechanism called Row-Level-Security. You could achieve similar functionality from within Oracle BI EE.

Picture this; You have a table of Sales Managers which are responsible for a certain region. Each Sales Manger may only see the data for his / her region.

First you have to know which user has logged on and to which region this user belongs. Therefore you should use Session variables. To set this up properly you could refer to the documentation. You validate the logged on user to a table of Sales Managers. This way you could also select the region a Sales Manager is responsible for. The principle of this solution is that you have the Sales Manager and their regions in a table which you can select from. Let’s say we now have a ‘REGION’ session variable.

We can go on to the Security Groups. Create a new Security Group called; ‘Sales Managers’. Assign all the Sales Managers (Repository Users) to this newly created group.

The final step is to set Business Model Filters on this group. The concept of these filters is thatb you add all Logical Tables to this group, which you want to restrict on a Sales Managers’ region. You could achieve this by following the next steps;

  1. Open the ‘Sales Manager’-Security Group,
  2. Click on; ‘Permissions’,
  3. Click on the Tab; ‘Filters’,
  4. Click; ‘Add’,
  5. Select the table you want to restrict, eg.; “Sales”.”Dim Region”.”Region Name”,
  6. Use the Expression Builder to create the actual filter; “Sales”.”Dim Region”.”Region Name” = VALUEOF(NQ_SESSION.”REGION”).

Now when you use the “Sales”.”Dim Region”-table in an Oracle BI Answers query, the Business Model Filter will be applied. This filter only applies to this Security Group. User which do not belong to this group will see all the regions.

Similar functionality is used when implementing Oracle BI Apps Security.