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

About

The GUI of OBIEE 10g answer is already able to report against multiple subject area with the “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.

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:

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

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

  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at del.icio.us
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Digg
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Ask
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Google
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at StumbleUpon
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Technorati
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Live Bookmarks
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Yahoo! Myweb
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Facebook
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Yahoo! Bookmarks
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at Twitter
  • Bookmark "OBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)" at myAOL
 
dat/obiee/multiple_subject_area.txt · Last modified: 2011/04/29 15:29 by gerardnico