All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Saturday, May 29, 2010

Understanding Complex Join and Physical Join in OBIEE

What is the difference between complex join and physical join? The easiest way to understand the basic is to remember that physical join is used in Physical layer and complex join is used in BMM layer. However, just knowing that isn't going to be enough to build solid skills on OBIEE development. In order to gain more insight on how it really works in OBIEE, we need to know more about these 2 types of joins.

First, let's look at complex join:

The diagram is a window of a typical complex join. In here, you notice that you can't change any of the table columns of neither logical tables in the join and the expression pane is grayed out. However, you are able to change the type of join from inner to outer joins. This type of behavior is telling us that complex join is a logical join that OBIEE server looks at to determine the relationship between logical tables, in other words, it is just a placeholder. Complex join will not be able to tell the server what physical columns are used in joining, but it will be able to tell the server what type of join this is going to be..

In order to know how exactly the join is, we will need to look at physical join in the physical layer:

Notice that in this window of physical join, we are able to change the columns under both tables, we are also able to define our own expressions. However, are can't change the joining method unlike complex join. This behavior is to help us to know that this is where we tell OBIEE how to join the 2 actual tables by specifying the columns. Hence this is what physical join does.

Knowing the basic, let's take a step further. Can I use complex join in physical layer or can I use physical join in BMM layer? Yes we can, and by doing it the application will not flag errors. However, we need to know when to use them and what to expect after using them..

Let's look at complex join in physical layer. Although it doesn't happen frequently, it is sometimes needed. Let's say we have 2 tables, promotion fact and contract date dimension. I want to join these 2 tables in such way so that only the dates that are still in contract should return. Therefore, I can't just use a simple join on the date columns from both tables, conditions need to be applied.. In this case, let's use complex join in physical layer:

In the below diagram, I enter 'PTS_DATES.COMPANYDATEID >= PTS_STAR_FACTS.CONTRACTSTARTDATEID AND PTS_DATES.COMPANYDATEID <= PTS_STAR_FACTS.CONTRACTENDDATEID' to satisfy the joining condition. At the front end, when you run a report using these tables, this expression will be included in the where clause of the SQL Statement:

Having physical join in BMM layer is also acceptable, however it is very rare to see that happen. The purpose of having physical join in BMM layer is to override the physical join in physical layer. It allows users to define more complex joining logic there than they could using physical join in physical layer, in other words, it works similar to complex join in physical layer. Therefore, if we are already using complex join in physical layer for applying more join conditions, there is no need to follow this set up with physical join in BMM layer again.

Remember, the best data modeling design in OBIEE is not the most complex and overly convoluted design, it should be as straightforward as possible. Therefore, use physical join in physical layer and complex join in BMM layer as much as you can. Only when situation calls for a different join, then go for it.

Til next time


Bh.Ram Naidu said...

very nice ,good effort with nice explanation .Thanks Bhai

Sam said...

Very Good Post.. informative; Thanks alot.

Kashif M said...

In obiee 11g after new join came, I forgot about those complex joins

Kashif M

Anonymous said...

How can we implement physical join in BMM

Anonymous said...

Very nice... Keep up the good work of sharing the knowledge...!

Anonymous said...

wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again,
Regards, obiee training in hyderabad

Karen Baker said...

You really make it seem so easy together with your presentation however I in finding this topic to be really one thing that I feel I might never understand. It kind of feels too complex and extremely vast for me. I'm looking forward in your next post, I will attempt to get the grasp of it!
admission essay writing service

custom dissertation writing service said...

you have shared such a great information about the complex join and physical join. you have explained it well for us, thank you

Albert Barkley said...

Thank you very much for providing a better understanding of complex and physical layers join. Dissertation writing services

Anonymous said...

I guess I am the odd one who is still confused.

The first statement this is unclear to me is this:

"This type of behavior is telling us that complex join is a logical join that OBIEE server looks at to determine the relationship between logical tables, in other words, it is just a placeholder. "

This a placeholder for what?

Perhaps I am still at a beginners' understanding of business intelligence. I am not entirely sure I know what a logical table is vs. a physical table. Where can I find information on this? I am assuming a physical table is what one finds in a database (correct me if I am wrong). But what is a logical table?

I need help!

Neil Jakson said...

I am very glad to read your information about better understanding of complex and physical layers joining . thanks for sharing .Cheap dissertation writing services

Related Posts Plugin for WordPress, Blogger...