Today I want to talk about this feature in OBIEE that allows you to do cross-db reporting, which I think is pretty cool.
Say you have 2 different result sets, they could be 2 tables or could be 2 queries. Now I want to see an OBIEE report that joins these 2 result sets based on some common dimensions, so how do we do it?
First of all, these 2 objects will be residing on different physical folders with different connection pools, this makes it complicated if you want to do physical joins.
In my example below, I have created 2 oblique views that contents 2 SQL queries, but they are located in 2 different physical folders:
Table1 is GL_Balance, which is from 1 DB and table 2 is A_GL_Balance, which is from another DB..
Both tables are actually SQL queries which already contents dimensions and measures from multiple DB tables.
Now in order to create a report that queries both table across the DB, I will leave these 2 tables as it is in the physical layer, but in the logic layer, I will create a logical confirm dimension that separates the dimensional attributes from the measures. So in the BMM layer, it will become 3 tables, F1, F2 and D. F1 will only have begin_bal and close_bal from Table1 and F2 will have only Balance_Acct_Amt and Balance_Loc_Amt from Table2. Then D will have all of the attributes mapped to both physical tables, you can both this in any BMM folder.
Now in the dimension table, I will map the logical columns to both physical table as sources, which means this table will have 2 LTRs and each columns will have 2 sources mapped to:
You can see that this table has 2 LTRs and each column (company for instances) is mapped to 2 sources..
The rest are pretty standard process, I will create a logical hierarchical dimension out of this table, set the hierarchy level at each fact and move everything into the presentation layer.
The reason I am putting all of the attributes in this table is because I want OBIEE to join these 2 datasets based on all of them, i think they are all common to one another. You can identify which columns are common between the 2 in your case and choose the right list.
What OBIEE does at run time is that, it creates 2 queries that execute in each DB to get the results seperately, it then merge them together in one report.
So I am creating a simple report by picking company and 1 measure from each table
The resulting data:
Now the query log shows the following:
The blue parts are just the SQL that each table was generated by.
[2014-07-25T19:19:03.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-18]  [ecid: 1710f6cbdf3264b0:6b7606d0:1476ec8e12a:-8000-00000000000061e2,0:1:9:5] [tid: eb631940] [requestid: 54700010] [sessionid: 54700000] [username: ] -------------------- Sending query to database named Omega Custom (id: <<1390911>>), connection pool named Omega Custom CP, logical request hash , physical request hash 8d6cb66e: [[1390911>
select sum(T2060476.BEGIN_BAL) as c1,
T2060476.SEGMENT1 as c2
,(NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)) begin_bal
, (NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0)) period_net
, ((NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)) + (NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0))) close_bal
/*, (NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0))) begin_bal
, SUM((NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0))) period_net
, sum(((NVL(gb.begin_balance_dr, 0) - NVL(gb.begin_balance_cr, 0)) + (NVL(gb.period_net_dr, 0) - NVL(gb.period_net_cr, 0)))) close_bal */
where gcc.code_combination_id = gb.code_combination_id
and gb.currency_code !='STAT'
and gcc.segment2 = fv.flex_value
--and gcc.segment3 like '213530'
--and gb.translated_flag IS NULL
--and gcc.segment1 like '304'
--group by gcc.segment3||'-'||gcc.segment1||'-'||gcc.segment6
order by 4
group by T2060476.SEGMENT1
order by c2
[2014-07-25T19:19:03.000-07:00] [OracleBIServerComponent] [TRACE:2] [USER-18]  [ecid: 1710f6cbdf3264b0:6b7606d0:1476ec8e12a:-8000-00000000000061e2,0:1:9:5] [tid: eb631940] [requestid: 54700010] [sessionid: 54700000] [username: ] -------------------- Sending query to database named Oracle Data Warehouse (id: <<1390933>>), connection pool named Oracle Data Warehouse Connection Pool, logical request hash 5f0261e4, physical request hash 597bac0c: [[1390933>
select sum(T2060495.BALANCE_ACCT_AMT) as c1,
T2060495.COMPANY as c2
WHERE A.ROW_WID = B.BALANCE_DT_WID
AND B.GL_ACCOUNT_WID = C.ROW_WID
group by T2060495.COMPANY
order by c2
So what OBIEE does is that it gets the result from each query individually, then at the server level, it merges the 2 dataset into one report..
The caveat here is that, if each query brings back huge data set, then this technique should be used with caution as it may kill performance during the merging..
Until next time