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.

Update to the FAQ-section of the OBIEE – Sample Application (V406)

For those of you who are using or are interested in the OBIEE – Sample Application (V406), the following might be of interest to you.

As you can see in this Comments Section, an issue was raised regarding the expiry of some Oracle Database Users. Development has updated the FAQ Section.



Searching for Doc Id 1428008.1 on MOS

If you are searching online for information about multiple Authentication Providers in Oracle BI 11g, you could find references (even on my own blog) to Doc Id; 1428008.1 on My Oracle Support (MOS). Via this note you were able to obtain following: TechNote_LDAP_Auth_DB_Groups_V3.pdf.

If you try to find this note now, you won’t be able to find it. It seems that the note has been closed. You should be able to find the information you are looking for here (Configuring LDAP as the Authentication Provider and Storing Groups In a Database)


Setup Oracle eBS 2 Oracle BI SSO

I made several blogposts in the past. That’s why it could be useful to collect these posts into one document. This document covers all the steps needed to integrate Oracle eBS (R12) and Oracle BI (11g) with each other. SSO might be a little bit confusing. In this case it’s meant that you can log into Oracle BI via Oracle eBS without having to log in again and while maintaining context.

Oracle Data Integrator 11g Cookbook – Review

Somewhere in the summer of 2010 the first 11g R1 release of Oracle Data Integrator (ODI) became GA. ODI is the leading Data Integration platform within the Oracle Product Family. It seemed like a logical step that a book on ODI 11g would be launched. The Oracle ODI Product Management team have shared their knowledge into a book called; Oracle Data Integrator 11g: Getting Started. This book presents the audience with a step-by-step tutorial. In the end the reader should be familiar, comfortable and successful with (the new features of) ODI 11g R1.

After the first ‘Getting Started’ book a more advanced book on ODI 11g seemed like a very logical next step.  The Oracle Data Integrator 11g Cookbook is a collection of recipes which cover the more advanced topics in ODI 11g.

Oracle Data Integrator 11g CookbookWhere the first book is more an introduction to ODI, the aim of the second book will uncover the more advanced features of ODI. Some basic knowledge of ODI is essential if you want to get the most out of the this Cookbook.

The Oracle Data Integrator 11g Cookbook is nicely structured. In a chronological order, the various components of ODI are covered. From Installation, Security, Variables, (inside) Knowledge Modules, Advanced Coding, Advanced ETL Processing to a Catch-all chapter which reveals the little know secrets and gives answers to several FAQ’s.

Each chapter consists of several (related) recipes. Each recipe is a step-by-step explanation of a specific task. Sometimes you have to perform some actions to get ready for the (next) recipe. Next to that it’s explained how things work. Last but not least, the recipes go beyond the steps necessary to complete a task in the; ‘There’s more’- and the ‘See also’-sections of the recipe. Within the tasks you will find notes which give some additional information to the recipe. Screenshots, code-(snippets) and links to external sites (Documentation, Oracle Support, Blogs, etc.) complete the recipes.

Consider the Oracle Data Integrator 11g Cookbook as a written MasterClass. Probably even for the more experienced ODI consultants, this book will reveal something they didn’t know before. I guess the authors succeeded in writing a book which differs from the first one and is still worth reading.

For me personally, I this book gives me more understanding of the capabilities of Oracle Data Integrator. I just recently (seriously) started exploring ODI. I would say that in this stage the Oracle Data Integrator 11g: Getting Started better fits my knowledge, but the  Oracle Data Integrator 11g Cookbook definitely is next on my reading list.

Enjoy reading this book.