OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)
Post date: 15-Mar-2011 13:50:01
Reference: http://gerardnico.com/wiki/dat/obiee/multiple_subject_area
The GUI of OBIEE 10g answer is already able to report against multiple subject area with the “Combine with similar request”.
This functionallity support only the following set sql clause:
UNION
UNION ALL
Intersect
Minus
but not all other standard set sql clause such as joins.
Even if the GUI of answer can do it, BI Server is able to read and to serve this kind of clause because the logical sql is ANSI SQL 92 compliant.
This article show you with a little bit of advanced logical sql how to report against Multiple Subject area with the most use join clause statement in this case: the FULL OUTER JOIN.
While writing an advanced logical sql, it's good to keep in mind that:
the subject area is a sort of BIG flat, completely denormalized table of a star schema
and that the presentation table are only map to sort/classify the column.
You can't perform a join condition between table of a subject area but between two subject areas.
To develop your Logical SQL, you can use an ODBC client such as the Issue SQL module of BI Presentation Service.
This step by step guide is made with the help of the SH sample schema.
Articles Related
Design of the repository
For the purpose of the demonstration, a second fact table will be:
created in the database
and added to the repository
Creation of the second Fact table
SQL> CREATE TABLE FactWithCustomerDim AS SELECT amount_sold - 1 "amount", cust_id FROM sales; TABLE created.
Repository Design
Simple picture to see the different repository steps:
Creation of the reports
Against the first Subject Area
Just create a simple report such as below:
You can then see in the advanced tab of answer the following logical Sql:
SELECT Customers."Cust Id" saw_0, Customers."Cust Last Name" saw_1, "Sales Facts"."Amount Sold" saw_2 FROM SH WHERE Customers."Cust Id" < 10 ORDER BY saw_0, saw_1
Against the second Subject Area
Just create a second simple report such as below:
You can then see in the advanced tab of answer the following logical Sql:
SELECT Customers."Cust Id" saw_0, Customers."Cust Last Name" saw_1, "FactWithCustomerDim"."Amount" saw_2 FROM SH2 WHERE Customers."Cust Id" < 9 ORDER BY saw_0, saw_1, saw_2
Against two Subject Areas (Multiple Subject Area)
In the 10g version, the GUI of Answer is not able to provide a wizard to create a SQL (answer) against multiple subject with the JOIN clause, you have to create it manually and past it in the advanced tab.
To develop your Logical SQL, you can use an ODBC client such as the Issue SQL module of BI Presentation Service.
Steps:
Go to the advanced tab of answer and copy/paste the following Logical Sql which is a OBIEE - Full outer Join of the two previous sql:
SELECT SubjectArea1.saw_0 saw_0, SubjectArea1.saw_1 saw_1, SubjectArea1.saw_2 saw_2, SubjectArea2.saw_2 saw_3 FROM ( SELECT Customers."Cust Id" saw_0, Customers."Cust Last Name" saw_1, "Sales Facts"."Amount Sold" saw_2 FROM SH WHERE Customers."Cust Id" < 10 ) SubjectArea1 FULL OUTER JOIN ( SELECT Customers."Cust Id" saw_0, Customers."Cust Last Name" saw_1, "FactWithCustomerDim"."Amount" saw_2 FROM SH2 WHERE Customers."Cust Id" < 9 ) SubjectArea2 ON SubjectArea1."Cust Id" = SubjectArea2.saw_0 ORDER BY saw_0, saw_1
Click op the set XML button and review the result report