Implement self-join in the repository

Community DW Integration

Hi,

OBIEE 11.1.1.9

We have a table which contains Work Orders and Project Numbers in the same table (like a manager and employee relationship).  A project is made up of 1 or many Work Orders.

Here is some sample data.

Whenever I want WO data with Project Number info with it, I use the below query.

select w.wo_id, w.wo_num, w.wo_desc, p.wo_num as proj_num, p.wo_desc as proj_desc, p.wo_id proj_idfrom work_orders w, work_orders pwhere w.proj_id = p.wo_idand w.proj_ind = 0;

While building my repository, I created a view for the above query and imported it.  I also imported the WORK_ORDERS table and then created a join between the view and WORK_ORDERS table on the WO_ID -> PROJ_ID key.

Is there a better way to implement these self join type data in OBI repository?

Thanks. 

Martin van Donselaar

Sure, you can use an alias in the physical layer and create a join between them, just like you do in the query.

Them you can put them together in a logical table source.

This is very basic OBIEE 101. Make sure you understand the basics of OBIEE modelling.

You can start here: http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11117/rpd/rpd.html

Robert Angel

Martins answer +1.

Also, try not to use views, I have seen too many OBIEE implementations where people used to databases quickly latch onto opaque view as the solution to everything. The same is true of people who have migrated from discoverer, where sql folders become prevalent.

The truth is opaque view will not be as performant as physical tables, so wherever you can model the physical table, always use aliases, and replicate the physical structure using the same joins you would in sql, then in the business layer modelling use the physical objects to model a star schema, flattening any snowflakes into the star structure.

Best of all is to have a fully formed data warehouse which is already in this structure, a prepopulated table will always out perform a self-join for obvious reasons.

Martin van Donselaar

Robert Angel wrote:Best of all is to have a fully formed data warehouse which is already in this structure, a prepopulated table will always out perform a self-join for obvious reasons.

Yes, Especially true when the source datamodel design is poor, which seems the case in the above example

[Deleted User]

Such good responses. Not much more to add than:

"Also, try not to use views, I have seen too many OBIEE implementations where people used to databases quickly latch onto opaque view as the solution to everything."

^-- THIS. Use the tool as it was designed to function. OBI does not work with "queries". It works with models.

Robert Angel

Thanks Christian and Martin for the words in support

I fear though that we may again be preaching to the converted as 'Skull' has not acknowledged our input...

I think I am now #3 of #3 for replying and no answer, mark, or signs of life from the asker...

Martin van Donselaar

Come on, give the him/her some time to react ;-) It's only been a few hours.

Community DW Integration

OBI Gurus - Thank you for your prompt answers and guidance _/\_ 

@Martin van Donselaar Thanks for the link.  That will be a great start for a newbie.  And thanks for sticking up for me :-)

@Robert Angel Agreed.  I will implement it without views.  Thanks again for the insight.  And we all are in different timezone, hence the delay.

Again, I wish I could mark multiple Correct Answers.  Thank you both and @Christian Berg too.

Community DW Integration

My apologizes @Robert Angel if I might have missed any of your earlier replies :-(  Nothing personal or disrespect to you or any of the folks in this community.  I truly appreciate everyone's contribution in OBI world.

Robert Angel

Apologies and points not needed but appreciated.

It can get you down trying to help people who don't have the courtesy to reply, but this was more a general observation at an ongoing trend with newbies rather than a specific complaint aimed at you.

All of that is a poor attempt at me saying 'sorry', hope no offence was taken, none was intended!

Hope your BI journey continues down the right road, there also used to be some good 'By Example' materials on the web site and it is also worth downloading the Oracle Virtual Box and BI Snapshot to have a friendly sandpit with a lot of good example material in.

Community DW Integration

No offense taken.  I understand when you all are helping the newbies (and sometimes even the experts) by answering their questions, there is nothing wrong in expecting a small thank you or an appreciation or an acknowledgement.  Like I said, I truly appreciate everyone's contribution to the online community, while maintaining your full time day jobs - it is no easy task.  Without sounding like a preacher, please continue with your good deeds - no good deed goes unnoticed!

Yes, I have started downloading the Virtual Box to learn more about this product.