OBIEE 10G - Reporting on Multiple Subject Area (Advanced Logical SQL)

Obiee Ceim

About

In 11G, you can easily joins two subject areas. This article shows advanced logical SQL statement for the sake of knowledge and was written on a 10G because within an answer, it was possible through the GUI to join two subject areas.

Even if the GUI of answer can't 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.

In the GUI of an OBIEE 10g answer, it was possible to report against multiple subject area with the “Combine with similar request”.

Obiee Combine With Similar Request

This functionality support only the following Set Operations:

  • UNION
  • UNION ALL
  • Intersect
  • Minus

but not all other standard set sql clause such as joins.

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:

Obiee Multiple Subject Area Repository Design

Creation of the reports

Against the first Subject Area

Just create a simple report such as below:

Obiee Multiple Subject Area Report Sa 1

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:

Obiee Multiple Subject Area Report Sa 2

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:

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

Obiee Multiple Subject Area Report Sa 1 And 2

BI Server Log

In the log, you can see that because the two subject area are made on the top of the same data source, the query compiler create and send

only one Physical Sql

(part “Sending query to database named orcl SH”).

+++Administrator:330000:330009:----02/09/2010 12:18:46

##############################################
-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/users/administrator/MultipleSubjectArea/Report on Multiple
 Subject Area';SELECT SubjectArea1."Cust Id" saw_0, SubjectArea1.saw_1 saw_1, SubjectArea1.saw_2 saw_2, 
SubjectArea2.saw_2 saw_3 FROM ( SELECT Customers."Cust Id" "Cust Id", 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


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- General Query Info:
Repository: Star, Subject Area: SH, Presentation: SH


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- Logical Request (before navigation):

RqList 
    Customers.Cust Id as c1 GB,
    Customers.Cust Last Name as c2 GB,
    Amount Sold:[DAggr(Salesfacts.Amount Sold by [ Customers.Cust Id, Customers.Cust Last Name] )] as c3 GB
DetailFilter: Customers.Cust Id < 10


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- General Query Info:
Repository: Star, Subject Area: SH, Presentation: SH2


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- Logical Request (before navigation):

RqList 
    Customers.Cust Id as c1 GB,
    Customers.Cust Last Name as c2 GB,
    AMOUNT:[DAggr(FactWithCustomerDim.AMOUNT by [ Customers.Cust Id, Customers.Cust Last Name] )] as c3 GB
DetailFilter: Customers.Cust Id < 9


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- Execution plan:

RqList <<48903>> [for database 3023:156:orcl SH,46]
    D1.c1 as c1 [for database 3023:156,46],
    D1.c2 as c2 [for database 3023:156,46],
    D1.c3 as c3 [for database 3023:156,46],
    D2.c3 as c4 [for database 3023:156,46]
Child Nodes (RqJoinSpec): <<48917>> [for database 3023:156:orcl SH,46]
    (
        RqList <<49139>> [for database 3023:156:orcl SH,46]
            CUSTOMERS.CUST_ID as c1 [for database 3023:156,46],
            CUSTOMERS.CUST_LAST_NAME as c2 [for database 3023:156,46],
            sum(SALES.AMOUNT_SOLD by [ CUSTOMERS.CUST_ID] ) as c3 [for database 3023:156,46]
        Child Nodes (RqJoinSpec): <<49028>> [for database 3023:156:orcl SH,46]
            CUSTOMERS T186
            SALES T245
        DetailFilter: CUSTOMERS.CUST_ID = SALES.CUST_ID and CUSTOMERS.CUST_ID < 10 and SALES.CUST_ID < 10 
[for database 0:0]
        GroupBy: [ CUSTOMERS.CUST_ID, CUSTOMERS.CUST_LAST_NAME]  [for database 3023:156,46]
    ) as D1 FullOuterJoin <<48901>> On D1.c1 = D2.c1
    (
        RqList <<49155>> [for database 3023:156:orcl SH,46]
            CUSTOMERS.CUST_ID as c1 [for database 3023:156,46],
            CUSTOMERS.CUST_LAST_NAME as c2 [for database 3023:156,46],
            sum(FactWithCustomerDim.AMOUNT by [ CUSTOMERS.CUST_ID] ) as c3 [for database 3023:156,46]
        Child Nodes (RqJoinSpec): <<49098>> [for database 3023:156:orcl SH,46]
            CUSTOMERS T186
            FactWithCustomerDim T7425
        DetailFilter: CUSTOMERS.CUST_ID = FactWithCustomerDim.CUST_ID and CUSTOMERS.CUST_ID < 9 and 
FactWithCustomerDim.CUST_ID < 9 [for database 0:0]
        GroupBy: [ CUSTOMERS.CUST_ID, CUSTOMERS.CUST_LAST_NAME]  [for database 3023:156,46]
    ) as D2
OrderBy: c1 asc, c2 asc [for database 3023:156,46]


+++Administrator:330000:330009:----02/09/2010 12:18:46

-------------------- Sending query to database named orcl SH (id: <<48903>>):
WITH 
SAWITH0 AS (select T186.CUST_ID as c1,
     T186.CUST_LAST_NAME as c2,
     sum(T245.AMOUNT_SOLD) as c3
from 
     SH.CUSTOMERS T186,
     SH.SALES T245
where  ( T186.CUST_ID = T245.CUST_ID and T186.CUST_ID < 10 and T245.CUST_ID < 10 ) 
group by T186.CUST_ID, T186.CUST_LAST_NAME),
SAWITH1 AS (select T186.CUST_ID as c1,
     T186.CUST_LAST_NAME as c2,
     sum(T7425.AMOUNT) as c3
from 
     SH.CUSTOMERS T186,
     SH.FactWithCustomerDim T7425
where  ( T186.CUST_ID = T7425.CUST_ID and T186.CUST_ID < 9 and T7425.CUST_ID < 9 ) 
group by T186.CUST_ID, T186.CUST_LAST_NAME)
select SAWITH0.c1 as c1,
     SAWITH0.c2 as c2,
     SAWITH0.c3 as c3,
     SAWITH1.c3 as c4
from 
     SAWITH0 full outer join SAWITH1 On SAWITH0.c1 = SAWITH1.c1
order by c1, c2

Documentation





Discover More
Obiee Logical Join Bmm
OBIEE - Full outer Join

To perform a , you have two ways : in the repository by using the fact vertical partitioning capabilities or with the Obiee logical sql To know what sort of join is a full outer join, follow this...
Obiee Ceim
OBIEE - Logical Sql

The logical SQL is the SQL that understand BI Server. It's a full Ansi 92 SQL (including subqueries and derived tables), plus special functions (SQL extensions) like AGO, TODATE, EVALUATE, and others....
Obiee 11g Subject Area Bmm Create
OBIEE - Subject Area / Presentation catalog

A subject area in Presentation Service is equivalent to a presentation catalog in BI Server. In 10g, a difference was made between: a subject areas in the Presentation Service a presentation catalog...
Saw Object
OBIEE 10G/11G - Answer/Analytics

(Answers 10g|Analyses 11g) is the base component of BI Presentation Service to create Web report . It provides: ad-hoc query and analysis capabilities. The definition of an answer is principally...



Share this page:
Follow us:
Task Runner