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

• • OBIEE - Full outer Join

  • OBIEE - Logical Sql

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