How to report on 2 datasets across different database in OBIEE

Hello

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: [[

select sum(T2060476.BEGIN_BAL) as c1,
     T2060476.SEGMENT1 as c2
from
     (select 
gcc.concatenated_segments, 
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7,
gcc.segment8,
gcc.enabled_flag ,
gcc.end_date_active, 
gb.period_name,
gb.currency_code,
gb.translated_flag,
fv.flex_value_set_id
--,gcc.segment3||'-'||gcc.segment1||'-'||gcc.segment6 combo,
,(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 */
from 
gl_balances gb, 
gl_code_combinations gcc, 
fnd_flex_values fv
where gcc.code_combination_id = gb.code_combination_id
and gb.actual_flag='A'
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
) T2060476
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: [[

select sum(T2060495.BALANCE_ACCT_AMT) as c1,
     T2060495.COMPANY as c2
from
     (Select
C.GL_ACCOUNT_NAME,
C.ACCOUNT_SEG1_CODE COMPANY,
C.ACCOUNT_SEG2_CODE COST_CENTER,
C.ACCOUNT_SEG3_CODE ACCOUNT,
C.ACCOUNT_SEG4_CODE PRODUCT,
C.ACCOUNT_SEG5_CODE PROJECT,
C.ACCOUNT_SEG6_CODE INTERCOMPANY,
C.ACCOUNT_SEG7_CODE GEOGRAPHY,
C.ACCOUNT_SEG1_CODE FUTURE2,
A.MCAL_PERIOD_NAME,
B.BALANCE_ACCT_AMT,
B.BALANCE_LOC_AMT
from
W_MCAL_PERIOD_D A,
W_GL_BALANCE_F B,
W_GL_ACCOUNT_D C
WHERE A.ROW_WID = B.BALANCE_DT_WID
AND B.GL_ACCOUNT_WID = C.ROW_WID
) T2060495
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..

Thanks

Until next time

Read More