Oracle Communities

Oracle Analytics and AI

Child Item

Implement self-join in the repository

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.

Find more posts tagged with

Accepted answers

All comments

Martin van Donselaar

@skull Happy to help, And good to see you are serious about learning to master OBIEE. Do not hesitate to ask any questions you might have.

Community DW Integration

Do not hesitate to ask any questions you might have.

Definitely :-) Thanks for offering your help.

So I was able to create an alias (so now 2 aliases for 1 table). I also had to add a filter for proj_ind = 0, which I was able to add it in the BMM layer, under the Logical Table -> Sources -> Properties -> Content tab -> WHERE filter to limit rows. Is that the right place?

Robert Angel

Yes, that is the right place.

Community DW Integration

Thanks @Robert Angel for confirming.

Robert Angel

You are most welcome!

Martin van Donselaar

Yes, BMM is where (business) rules typically will be enforced.

Be aware though in the BMM you should model a star model (Facts and Dimensions).

Although there seem to be no real facts (measures) in the example above, you should plan ahead what measures you are planning to create analyses for, like maybe hours spent per work order, Total Hours spent per project etc in your example. Does what you are doing now fit into that bigger picture?

Depending on the physical data model the data is in there can be different approaches on how you would set up your BMM Layer. (Like: are there aggregates, is the data hierarchical etc)

The real power of OBIEE can be tapped when you invest in building a good model and it will bite you if you don't

[Deleted User]

Amen. Disregard how a tool works and is built on pain of problems, performance issues and generally unacceptable results for the end client.