OBIEE - Power to the people - joining the unjoinable

In normal life Administrator creates the model, including the joins between objects and the users use them. From time to time the users require things that are not covered by the model. I see 3 option in such case:

1. Update the model - the proper way.

2. Allow the user to access Direct database request (I've seen System admins that told me they would rather drink cyanide).

3. Use "create Analysis from simple Logical SQL" - this topic.

Lets see an example. In my model I deleted the join between Customers table and Sales one:

 What I intend to do is to create 2 separate queries and join them in a third one.

I created the first analysis:

Naturally I will have an error if I try to add a column from Sales table:

At the Advanced Tab we can press "New Analysis" to get the query:

I copied the SQL and did the process again with the other query:

Now I have 2 queries:

1. SELECT "CUSTOMERS"."CUST_ID" saw_0, "CUSTOMERS"."CUST_LAST_NAME" saw_1, "PROFITS"."AMOUNT_SOLD" saw_2 FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20

2. SELECT "SALES"."CUST_ID" saw_0, "SALES"."AMOUNT_SOLD" saw_1, "PRODUCTS"."PROD_CATEGORY" saw_2 FROM "SH" WHERE "SALES"."CUST_ID" < 20

I can create single query from both. For example:

Select Q1.saw_1 Cust, Q1.saw_0 Cust_id, Q1.saw_2 Cust_Amount, Q2.saw_1 Prod_cust_Amount, Q2.saw_2 Prod_cat 
FROM
(SELECT "CUSTOMERS"."CUST_ID" saw_0, "CUSTOMERS"."CUST_LAST_NAME" saw_1, "PROFITS"."AMOUNT_SOLD" saw_2 FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20) Q1,
(SELECT "SALES"."CUST_ID" saw_0, "SALES"."AMOUNT_SOLD" saw_1, "PRODUCTS"."PROD_CATEGORY" saw_2 FROM "SH" WHERE "SALES"."CUST_ID" < 20) Q2
WHERE
Q1.saw_0=Q2.saw_0

(Don't bother too much with the new names, OBIEE will convert them to saw_x if you do it this way. See Remarks at the end.)

Now I'll take the SQL and paste it to a new analysis (or I can overwrite the SQL of one of the original queries)

This is working. I joined the 2 results as an end user:

A remark:

If you prefer to change the names while working with the SQL, and not after the results are ready, you can do it. 

In the following example I changed all the saw_X column to natural names. Intentionally I had different names in the external and internal SQL statements.

For this code:

Select Q1.CUST_LAST_NAME Cust, Q1.CUST_ID Cust_id, Q1.AMOUNT_SOLD Cust_Amount, Q2.AMOUNT_SOLD Prod_cust_Amount, Q2.PROD_CATEGORY Prod_cat 
FROM
(SELECT "CUSTOMERS"."CUST_ID", "CUSTOMERS"."CUST_LAST_NAME", "PROFITS"."AMOUNT_SOLD" FROM "SH" WHERE "CUSTOMERS"."CUST_ID" < 20) Q1,
(SELECT "SALES"."CUST_ID", "SALES"."AMOUNT_SOLD", "PRODUCTS"."PROD_CATEGORY" FROM "SH" WHERE "SALES"."CUST_ID" < 20) Q2
WHERE
Q1.CUST_ID=Q2.CUST_IDthe result was:

So we learned  OBIEE uses the internal SQL names and disregards the external (for example the first column name is CUST_LAST_NAME and not Cust).