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.
Articles Related
In the GUI of an OBIEE 10g answer, it was possible 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.
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
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
- In response of this thread: Querying against multiple subject areas