Disclaimer

All of the topics discussed here in this blog comes from my real life encounters. They serve as references for future research. All of the data, contents and information presented in my entries have been altered and edited to protect the confidentiality and privacy of the clients.

Various scenarios of designing RPD and data modeling

Find the easiest and most straightforward way of designing RPD and data models that are dynamic and robust

Countless examples of dashboard and report design cases

Making the dashboard truly interactive

The concept of Business Intelligence

The most important concept ever need to understand to implement any successful OBIEE projects

Making it easy for beginners and business users

The perfect place for beginners to learn and get educated with Oracle Business Intelligence

Monday, January 26, 2015

Tableau visualization - How to create sparkline chart


Having had some time working with Tableau, i have found some of the charting features that Tableau provides is very impressive and easy to use once you get used to it. Sparkline chart is one of those charts. Here is how to create your basic sparkline chart for beginners of this tool.

We will start with sample data set (superstore subset) that comes with Tableau installation:



In the worksheet, we will bring 1 dimension and 2 measures to the 'Row' section. The report will look like the following as soon as I am done with dragging region and sales (drag twice) over:



Then bring the date over as well by dragging Order Date over to the column section. By default, it automatically adds year function to order date and the graph shows 2 identical lines per region because of the 2 sales measures



Then click on one of the measure and do 'Add table calculation'. Here we are setting it as 'Moving Calculation' in the calculation type and choose Average for aggregation. I selected previous 5 months and check the box 'Include current value'. This is to calculate the moving average of sales for the previous 6 months including current month.




Then drag each of the measures and drop then under the axis section of the graph as you can see it gets highlighted when you move the cursor over.



After that the graph automatically becomes the following. You can change the formatting, hide the axis labels if you wish or change the colors of the 2 lines as you wish. In my case, i changed the Date function from year to Month, because that's my intended moving calculation.

This graph will provide user with visuals that displays the sales per region per month as well as it's moving 6 month average on the same chart as sparklines. 



Thanks 

Until next time..

Saturday, November 22, 2014

OBIEE -- Writeback update while keep the update history


Hello

I implemented the writeback in OBIEE as mentioned in this post, however the writeback feature in OBIEE is very basic, it does simple overriding of the data and it doesn't keep the historic changes. I have tried playing around with the writeback.xml file with insert or update tags, but i had no luck with it. Therefore, I decided a different approach.

The original table that I was using for writeback and reporting will be kept as it is. Now I am going to create another table with the same table structure. Then I will create a trigger that will insert records to this table when the original table gets a writeback update. Then last but not least, I will create a view which unions these 2 tables and use it in OBIEE rpd. Let me illustrate here:

The original table being used in the older post is FA_Table, which is being used in rpd.

Create a new table called FA_Table_AUX:

CREATE TABLE FA_TABLE_AUX
  (
LEDGER_NAME                   VARCHAR2(30),  
PERIOD_NAME                   VARCHAR2(15),  
JE_HEADER_NAME                VARCHAR2(100),
JE_LINE_NUM                   NUMBER,      
.
OTHER COLUMNS
.
.
)

Then create the trigger called FA_WRITEBACK:

CREATE OR REPLACE TRIGGER FA_WRITEBACK
BEFORE UPDATE
   ON FA_TABLE
   FOR EACH ROW
 
BEGIN
INSERT
INTO FA_TABLE_AUX
  (
    LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
  )
  VALUES
  (
    :old.LEDGER_NAME,
    :old.PERIOD_NAME,
    :old.JE_HEADER_NAME,
    :old.JE_LINE_NUM,
    :old.CURRENCY_CODE,
    :old.CURRENCY_CONVERSION_RATE,
    :old.CURRENCY_CONVERSION_TYPE,
    :old.CURRENCY_CONVERSION_DATE,
    :old.POSTED_DATE,
    :old.JE_BATCH_ID,
    :old.JE_CATEGORY,
    :old.JE_SOURCE,
    :old.ENTERED_DR,
    :old.ENTERED_CR,
    :old.ACCOUNTED_DR,
    :old.ACCOUNTED_CR,
    :old.EXCHANGE_RATE_USD,
    :old.TRANSACTION_LOCAL_AMOUNT,
    :old.TRANSACTION_USD_AMOUNT,
    :old.JOURNAL_LINE_DESCRIPTION,
    :old.EFFECTIVE_DATE,
    :old.COMPANY,
    :old.COST_CENTER,
    :old.COST_CENTER_NAME,
    :old.ACCOUNT,
    :old.ACCOUNT_NAME,
    :old.PRODUCT,
    :old.PROJECT,
    :old.PROJECT_NAME,
    :old.INTERCOMPANY,
    :old.GEOGRAPHY,
    :old.GEOGRAPHY_NAME,
    :old.FUTURE2,
    :old.INVOICE_NUM,
    :old.INVOICE_AMOUNT,
    :old.INVOICE_LINE_DESCRIPTION,
    :old.VENDOR_NAME,
    :old.INVOICE_DATE,
    :old.INVOICE_GL_DATE,
    :old.INVOICE_PERIOD_NAME,
    :old.DIST_GL_PERIOD_NAME,
    :old.PO_NUM,
    :old.RECORD_TYPE,
    :old.PERIOD_NUM,
    :old.ASSET_NUMBER,
    :old.GLOBAL_TRANSFER,
    :old.REGION,
    :old.INTERIM_CATEGORY,
    :old.CAPEX_CATEGORY,
    :old.CAPEX_YES_NO,
    :old.RECORD_ID,
    :old.REVISION_NUMBER,
    'N',
    'Trigger',
    SYSDATE,
    :old.RAW_DATA_FLAG,
    'N');

:new.REVISION_NUMBER := :old.REVISION_NUMBER+1;
END;

--------------------------

Here as you can see, the old records from FA_Table gets inserted into FA_Table_AUX, and the last 5 fields will be hard-coded. It also updates the revision_number by 1 increment just to indicate the update versions.

Now create a view that union both of these tables. The BOLD columns will be included in the report and it will reflect the historical changes because the columns in FB_TABLE_AUX will be inserted based on trigger.

create or replace view FA_TABLE_AUD_TRAIL
    (LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG)
    as
    select
    LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
from FA_TABLE
union
select
LEDGER_NAME,
    PERIOD_NAME,
    JE_HEADER_NAME,
    JE_LINE_NUM,
    CURRENCY_CODE,
    CURRENCY_CONVERSION_RATE,
    CURRENCY_CONVERSION_TYPE,
    CURRENCY_CONVERSION_DATE,
    POSTED_DATE,
    JE_BATCH_ID,
    JE_CATEGORY,
    JE_SOURCE,
    ENTERED_DR,
    ENTERED_CR,
    ACCOUNTED_DR,
    ACCOUNTED_CR,
    EXCHANGE_RATE_USD,
    TRANSACTION_LOCAL_AMOUNT,
    TRANSACTION_USD_AMOUNT,
    JOURNAL_LINE_DESCRIPTION,
    EFFECTIVE_DATE,
    COMPANY,
    COST_CENTER,
    COST_CENTER_NAME,
    ACCOUNT,
    ACCOUNT_NAME,
    PRODUCT,
    PROJECT,
    PROJECT_NAME,
    INTERCOMPANY,
    GEOGRAPHY,
    GEOGRAPHY_NAME,
    FUTURE2,
    INVOICE_NUM,
    INVOICE_AMOUNT,
    INVOICE_LINE_DESCRIPTION,
    VENDOR_NAME,
    INVOICE_DATE,
    INVOICE_GL_DATE,
    INVOICE_PERIOD_NAME,
    DIST_GL_PERIOD_NAME,
    PO_NUM,
    RECORD_TYPE,
    PERIOD_NUM,
    ASSET_NUMBER,
    GLOBAL_TRANSFER,
    REGION,
    INTERIM_CATEGORY,
    CAPEX_CATEGORY,
    CAPEX_YES_NO,
    RECORD_ID,
    REVISION_NUMBER,
    CURRENT_REVISION,
    LAST_UPDATED_BY,
    LAST_UPDATED_DATE,
    RAW_DATA_FLAG,
    FINAL_DATA_FLAG
    from
    FA_TABLE_AUX

Then in the rpd file, simply replace the physical layer table with FA_TABLE_AUD_TRAIL. No need to change the writeback.xml, because the writeback.xml is still using update FA_TABLE as implemented from the older post, When it is trying to update FA_TABLE with user inputs, the trigger will be invoked and therefore the same row before user updates will be inserted into FA_TABLE_AUX table while FA_TABLE will have the updated data.. Since we are reporting against the view which unions both table, then from the OBIEE dashboard, you will get to see both new and older records.

If you are in doubt, you can always query against FB_TABLE_AUX from the DB, if it is blank, that means no insertion occurs, if it returns 1 row, that means user has trigger the update..

Thank you

Until next time..

Tuesday, August 19, 2014

OBIEE: Dynamically calculate quarterly difference without using time series function

Sometimes, you have a custom report built from custom schema, it could be a materialized view or view that were custom made, where the number is at a certain granularity without hierarchy defined. Like you have a quarterly report, but now the requirement is to display the numbers not only in the quarter that user pick, but also display the previous quarter of that user defined quarter along. Not only that, you also need to display the difference between the quarters as well as the monthly measures under these 2 quarters. Like the report below for instance:





As you can see from the report, the user just pick 2014 Q3 and the report comes back with Q3 as well as Q2 data with month and quarter column pivoted. Now the pivot view allows us to get the total per quarter, which as you can see, is done. Now the challenge is, how are we going calculate the difference between these 2 quarterly difference?


So, let me go back a little bit. Before I got to the report was you can see, I created a new column in the RPD which brings Year and Quarter concatenated based on what this table have. In other words, i take '2014' and '3', then concatenated it 'Q' in the middle to get you 2014 Q3. 'Trim' function is used here to get rid of any unwanted spaces that is trailing as a result of converting number into string.


The next step I decided to do is to create a simple report which takes the user input quarter value, then it should return that quarter value as well as the previous quarter value using the rpd column that I just created above.


In the above report, 'Current Year Quarter' is nothing but the RPD column, the Previous Year QTR has the following formula that returns the previous Quarter:

Since the time dimension does have Date (DATE_VALUE) column, by doing timestampadd at month level with '-3' will automatically give you the month that is 3 month prior to the month of that corresponding DATE_VALUE. It also needs to address the fact that the prior quarter of Q1 should be Q4 of the previous year, therefore a case statement is embedded to take care of this situation:

TRIM(TRAILING ' ' FROM CAST(case when "- XXFB_TIME_DIMENSION"."QUARTER" = 1 then "- XXFB_TIME_DIMENSION"."YEAR" - 1 else "- XXFB_TIME_DIMENSION"."YEAR" end AS CHAR))||' '||'Q'||Trim(trailing ' ' from cast(QUARTER_OF_YEAR(timestampadd(sql_tsi_month,-3,"- XXFB_TIME_DIMENSION"."DATE_VALUE")) as char))

After concatenating these components together, it finally gives me what I want:


This report will be accepting the presentation variable from the dashboard prompt quarter, so i call it 'Quarter'. Save this report as 'Previous Quarter'.

Now, go back to the main report that I have been creating as you can see below:



Here in the filter section, the Year&Quarter column (which is the new RPD column) is getting value from 'Current Year Quarter' OR from 'Previous Year Column' of the report 'Previous Quarter' created above. This means that when user select 2014 Q3 from the dashboard, it will return 2014 Q3 and 2014 Q2, both will be passed to year&quarter column of this report through this filter condition. Then by pivoting the quarter and month column, it will display the view that you saw.

Now the second thing is to come up with a way to calculate difference of these 2 quarters. Unfortunately, this will have to be done with a different calculation because time series function will make it very complicated and difficult to validate the logic.

So first thing is to find a way to calculate the measure for each of the quarter and it's previous quarter. This can be done using 'filter' function by making the Year&Quarter column equal 'current quarter' or 'previous quarter' from report 'Previous quarter'.


As it turns out, OBIEE does allow to use filter function with nested query, so this is what the expression looks like below:

FILTER(ifnull(FACT."Accounted Net USD",0) + ifnull(FACT."Accounted Net USD",0) USING ("- XXFB_TIME_DIMENSION"."Year&Quarter" IN (SELECT saw_0 FROM (SELECT "- XXFB_TIME_DIMENSION"."Year&Quarter" saw_0, TRIM(TRAILING ' ' FROM CAST(case when "- XXFB_TIME_DIMENSION"."QUARTER" = 1 then "- XXFB_TIME_DIMENSION"."YEAR" - 1 else "- XXFB_TIME_DIMENSION"."YEAR" end AS CHAR))||' '||'Q'||Trim(trailing ' ' from cast(QUARTER_OF_YEAR(timestampadd(sql_tsi_month,-3,"- XXFB_TIME_DIMENSION"."DATE_VALUE")) as char)) saw_1 FROM "FB - Custom Views for Oracle Financials" WHERE "- XXFB_TIME_DIMENSION"."Year&Quarter" IN ('@{Quarter}')) nqw_1 ))) -  FILTER(ifnull(FACT."Accounted Net USD",0) + ifnull(FACT."Accounted Net USD",0) USING ("- XXFB_TIME_DIMENSION"."Year&Quarter" IN (SELECT saw_1 FROM (SELECT "- XXFB_TIME_DIMENSION"."Year&Quarter" saw_0, TRIM(TRAILING ' ' FROM CAST(case when "- XXFB_TIME_DIMENSION"."QUARTER" = 1 then "- XXFB_TIME_DIMENSION"."YEAR" - 1 else "- XXFB_TIME_DIMENSION"."YEAR" end AS CHAR))||' '||'Q'||Trim(trailing ' ' from cast(QUARTER_OF_YEAR(timestampadd(sql_tsi_month,-3,"- XXFB_TIME_DIMENSION"."DATE_VALUE")) as char)) saw_1 FROM "FB - Custom Views for Oracle Financials" WHERE "- XXFB_TIME_DIMENSION"."Year&Quarter" IN ('@{Quarter}')) nqw_1 )))

Now the break down of this expression is below:

(Below will get you Current month measure)
FILTER(ifnull(FACT."Accounted Net USD",0) + ifnull(FACT."Accounted Net USD",0) USING
("- XXFB_TIME_DIMENSION"."Year&Quarter" IN
----- (The red part is the measure that we are dealing with)


(SELECT saw_0 FROM (SELECT "- XXFB_TIME_DIMENSION"."Year&Quarter" saw_0, TRIM(TRAILING ' ' FROM CAST(case when "- XXFB_TIME_DIMENSION"."QUARTER" = 1 then "- XXFB_TIME_DIMENSION"."YEAR" - 1 else "- XXFB_TIME_DIMENSION"."YEAR" end AS CHAR))||' '||'Q'||Trim(trailing ' ' from cast(QUARTER_OF_YEAR(timestampadd(sql_tsi_month,-3,"- XXFB_TIME_DIMENSION"."DATE_VALUE")) as char)) saw_1 FROM "FB - Custom Views for Oracle Financials" WHERE "- XXFB_TIME_DIMENSION"."Year&Quarter" IN ('@{Quarter}')) nqw_1 )))  
---- this is the other report that we created earlier, presentation variable needs to be entered here, saw_0 represent 'current year and quarter' from that report

 -
(Below will get you previous month measure)
FILTER(ifnull(FACT."Accounted Net USD",0) + ifnull(FACT."Accounted Net USD",0)

USING ("- XXFB_TIME_DIMENSION"."Year&Quarter" IN

(SELECT saw_1 FROM (SELECT "- XXFB_TIME_DIMENSION"."Year&Quarter" saw_0, TRIM(TRAILING ' ' FROM CAST(case when "- XXFB_TIME_DIMENSION"."QUARTER" = 1 then "- XXFB_TIME_DIMENSION"."YEAR" - 1 else "- XXFB_TIME_DIMENSION"."YEAR" end AS CHAR))||' '||'Q'||Trim(trailing ' ' from cast(QUARTER_OF_YEAR(timestampadd(sql_tsi_month,-3,"- XXFB_TIME_DIMENSION"."DATE_VALUE")) as char)) saw_1 FROM "FB - Custom Views for Oracle Financials" WHERE "- XXFB_TIME_DIMENSION"."Year&Quarter" IN ('@{Quarter}')) nqw_1 )))
 --- saw_1 represents the 'previous year and quarter' from that report 

Now, test the new report:



It's working nicely..

Thanks

Friday, August 1, 2014

OBIEE vs Tableau Report

Having used both technologies quite extensively, I can't help but to make some comparisons between the 2 tools.

For those that are not quite familiar with Tableau, it is a pretty cool technology that also provides very good look and feels of the reports. It is a pretty popular technology in a lot of companies.

Here are some of the cool things that tableau has to offer:
1.
Tableau allows developers to bypass the metadata design and go directly into the report building phases. In other words, there is no equivalence of Admin Tool in Tableau. You develop your SQL or PL/SQL, you then create reports and views based on this SQL. This allows the development to be very flexible because there is no needs for dimensional modeling. You can join as many tables, create as many self-joins, making as many snowflake or normalizations of dimensions as you want, as long as your query is giving you the right data, you can put it in tableau and it becomes the report you want. The same thing in OBIEE would probably have to be done through custom views or materialized views, which will still need to be migrated across the 3 layers in Admin tool.

2.
Tableau makes mapview much easier to build. All you need is spatial data in your table: latitude and Longitude and city. Once these fields are in your query, it will show up in tableau with different Icons letting you know they are available. You can then putting in the mapview and the map will be generated with every city in your table on the map. This process is much simpler than doing the same in OBIEE.

3.
The latest versions of Tableau provides your calender for any date filters (I am surprised it took this long for such important feature to come up ). The date filter has some pretty cool features that it allows users to not only filter based on dates, but also provides other filtering options such as 'last 2 weeks', 'last 2 months', 'between each Monday' with all just a click away. This can also be done in OBIEE but will require the use of variables, which will table some engineering.

4.
Tableau report allows users to pick their own filters while viewing the report. In OBIEE, the user will normally have to contact the report developers if they want to change filters and prompts.

5. The common features that OBIEE has, such as agents and navigation among reports and views can also be found in tableau.

6 Tableau is less complicated in its server architecture compared with OBIEE and weblogic.

On the other hand, there are some downside of tableau as well compared with OBIEE:

1.One of the main thing about Tableau is that it doesn't seem to have a query engine. This means it will try to query the entire DB first before applying any filters. This will cause a lot of overheads and oftenly at a enterprise level of reporting, the Tableau server is less stable than OBIEE because of the size of the Data they are dealing with.

2.For enterprise reporting, there might be reports that has to go through auditing to meet certain compliance based on the formatting and other things. OBIEE reporting has been tested for this purpose while Tableau will still have to be trialed and tested. What's the use of having all of the fancy reports that don't pass the auditing?

3.Another disadvantage of Tableau, which can be major for some companies, is that it doesn't allow writebacks. We all know that OBIEE has a writeback feature, which allows report users to update records on the dashboard without having to go to DB. This can be important in a lot of the financial reporting that the users typically would want to update certain records during their month-end closing. This feature is absent in Tableau.

Overall, Tableau is more of a point solution rather than enterprise-wise solution like OBIEE. But Tableau is way less costly than OBIEE and much more flexible in it's design, which also means that the level of SQL Programming for Tableau is much higher than in OBIEE.

I hope this helps

 

Friday, July 25, 2014

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
Related Posts Plugin for WordPress, Blogger...