Letting the users pick joins in OBIEE

The users want to define the joins between tables in answers, when doing adhoc queries. These views are used for real-time reporting and were sitting on top of EBS tables. This requirement was driven by the fact that most of these views could be joined in more than 2 ways depending of the business scenario. In a typical design we would create alias tables to correspond to each of the joins. But in our case the users were not able to define the joins in advance and there is a possibility of the view definitions changing over a period of time. To sum it up the users wanted to simulate a SQL tool with answers, sort of.

clip_image002

In our case View 1 and View 2 could be joined up to 4 different ways and there were quite a few views like that, making the whole aliasing approach cumbersome. One of the developers in the team came up with an idea, though it’s not the perfect solution, but definitely simple and worth mentioning.

First up we extended all the views in the database by adding a dummy column and the values were always 1.

View 1

Col1 Col2 Col3 Col4 Dummy
1
1

View2

Col1 Col2 Col3 Col4 Dummy
1
1

The physical layer in the RPD would have the join between the views as View1.dummy = View2.Dummy. There is nothing special about the BMM layer, the usual and the same with the presentations layer.

Now comes the answers part. If I were to run a query between the 2 views without any filters in the query the results would not make any since the join is View1.dummy = View2.Dummy.

Let’s say for query 1 I want to join on View1.col1 = View2.col1. Here are the steps.

Put a filter on View1.Col1. (View1.Col1 = value1)

Convert that filter to SQL

Equate View1.Col1 to View2.Col2 in the filter SQL.

clip_image004

This will have your physical queries appended by 2 join conditions 1) which is defined in the RPD(View1.dummy = View2.Dummy) and 2) View1.col1 = View2.col1 .

You could define any joins in a similar fashion. One serious limitation is that the joins are always inner joins.

Advertisements

2 Responses

  1. Interesting approach, assuming your report authors are willing to buy in.

    Did you try non-ANSI outer joins (assuming an Oracle back end) using user-defined filters of the form:

    View1.col1 = View2.col1 (+)

    -Eric

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: