Monday, 30 July 2012

Oracle Business Intelligence 11.1.1.6.0 – How to include sum of all “Others” in Top N Report

This blog is an attempt to share a solution which answers a frequently asked question about Top N type of report. The requirement is if you select Top N (where is N is variable ) to drive any report , the report should also show the sum of all the “Others” which are not included in Top N criteria.

The solution is the outcome of the discussion on oracle forum and a contribution from Oracle Consultant working in BI solution area. This blog is an attempt to share the knowledge. There is no contribution from my side apart from inclusion of the solution as a blog entry.

Desired Report Output
The report should be driven by Top N (where N is user enter variable) selection and should show the sum of all the “Others” as below.  I have added conditional formatting on column for better clarity for demonstration.


Steps to Achieve the Top N and Others Report
The board steps which need to follow to achieve the ‘Top N and Others’ report are as follows

·         Specify the Analysis Criteria
·         Format the Analysis Results
·         Create a Dashboard Prompt
·         Create a Dashboard


Specify the Analysis Criteria
1.    Create a new Analysis, choose subject area - A - Sample Sales, add the column ‘Product Type’ which you are grouping by and then add same column second time

2.    Click the context menu button next to this second column and pick Edit formula from the menu.

3.    Set the Column Formula to your requirements by following this template:

CASE WHEN RANK("Base Facts"."Revenue") <= @{TopN}{9} THEN "Products"."Product Type" ELSE 'Others' END
Where
·         "Base Facts"."Revenue" should be replaced with the table.column you want to rank your results by.
·         TopN is the name of the Presentation Variable that you want to use to specify how many of the Top results you want to show before grouping the remaining results into the “Others” result.
·         3 is the default value to use if the Presentation Variable isn’t set.
·         "Products"."Product Type" should be replaced with the table.column you are grouping by.
·         'Others' is the name you want to give to the group that all results outside the Top N should be aggregated under.

4.    Check the Custom Headings checkbox.

5.    Set the Column Heading to TopN Product Type.

6.    Check the Treat as an attribute column checkbox. At this point the Edit Column Formula dialog should look something like this:



7.    Add the column you are ranking by (e.g. Revenue).

8.    Click the context menu button next to this third column and pick Edit formula from the menu.

9.    Set the Column Formula to your requirements by following this template:
CASE WHEN RANK("Base Facts"."Revenue") <= @{TopN}{9} THEN 1 ELSE 2 END
10.    Check the Custom Headings checkbox.

11.    Set the Column Heading to Rank Group.

12.    Check the Treat as an attribute column checkbox.

13.    Add the column you are ranking by (e.g. Revenue).

14.   Click the context menu button next to the Rank Group column and pick Sort | Sort Ascending from the menu.

15.    Click the context menu button next to the Rank Group column and pick Sort | Add Descending Sort from the menu. At this point your Selected Columns should look something like this:


Format the Analysis Results
1.    Click the Results tab.
2.    Click the New View button and pick Pivot Table from the drop-down menu.
3.    Click the Remove View button next to the original Table view.
4.    Click the Edit View button next to the Pivot Table view.
5.    Drag Product Type from the Rows section to the Excluded section.
6.    Drag Rank Group from the Measures section to the Rows section.
7.    Drag TopN Product Type from the Measures section to the Rows section.
8.    Click the More Options button next to Rank Group and pick Hidden from the drop-down menu.
9.    Click the Totals button in the Rows section and pick After from the drop-down menu.
At this point the Layout section should look something like this:


10.    Click Done and Save Analysis. I have done some conditional formatting on columns for clarity and demonstration.
Create a Dashboard Prompt
This section describes how to create a Dashboard Prompt that specifies how many of the Top results should be shown before remaining results are grouped under the Others result.
1.    Create a new Dashboard Prompt.
2.    Click the New button and pick Variable Prompt from the drop-down menu.
3.    Set the Presentation Variable name in the Prompt For field to Top.
(Where TopN is the name of the Presentation Variable you specified in step 5 of “Specify the Analysis Criteria” above.)
4.    Set the Label to Show Top N.
5.    Expand the Options section.
6.    Change the Variable Data Type to Number.
7.    Change Default Selection to Specific Value.
8.    Specify 3 as the Specific Value. At this point the Edit Prompt dialog should look something like this:

9.    Click OK.
10.    Save the Prompt.
Create a Dashboard  
All that remains to be done is to create a Dashboard Page that brings the Prompt and the Analysis together.
1.    Create a new Dashboard page.
2.    Add the Dashboard Prompt you created earlier.
3.    Add the Analysis you created earlier.
4.    Save the Dashboard Page.
5.    Run the Dashboard Page.
At this point the Dashboard should allow user to choose the Top N variable. The output reports for Top 6 and Top 3 are shown below

Thursday, 19 July 2012

BI in Fusion Application – OTBI, BIAPPS & Advanced Analytics

This blog is an attempt to introduce Oracle BI offerings in Fusion Application and discuss with minimal details on associated technologies and architecture supporting the various BI components.
   
What is BI in Fusion Application?
The Business Intelligence for Fusion Applications has following three bi components
Oracle Transactional Business Intelligence (OTBI)
This is a new BI component built on the OBIEE platform. It reports directly from the Fusion Application transactional tables and so it reflects changes in the transactional tables just as they occur. This is fusions real time BI component.
[OTBI offers a real time, self-service reporting and analysis facilities within Fusion Application


Oracle Business Intelligence Applications (OBIA)
This BI component known as BI Apps is build around a pre packaged data-warehouse, ETL, metrics and dashboards and it also uses the OBIEE platform. The BI Applications support the existing Oracle Applications ERP and CRM application, this component has been extended to support the Fusion applications suite.
[OBIA offers more historical and complex type of analyses. The underline data warehouse solution offers reporting areas like historical trending, year-over-year comparisons etc]
Advanced Analytics
The specialized analytics which includes tools like Real Time Decision, Data Mining and Hyperion Essbase are built into the Fusion Applications to support particular use cases, e.g. Essbase is used in Financials to deliver financial statements for the GL Ledger module and RTD is used for sales predictive capabilities.
[The advanced analytics provide domain specific analyses by using business intelligence foundation]
BI in Fusion Application- Advantages
In my view though all bi components are different products, the fusion application uses same business language, the same user experience, same report templates and graph types which drives the entire user experience.
This fusion application allows managing all business metadata from a single place. 
There is tight integration between business intelligence and Fusion Applications which is very important in terms of functional perspective. For example BI and Fusion applications use same flex fields and security definitions.
The adoption of tools and technologies in Fusion can reduce the installation and managing overheads of number of BI components
OTBI Vs OBIA
OTBI which is transactional business intelligence focused more on the ability to better manage your operations – What’s Happening now!
While OBIA is more focused more making strategic decisions or providing strategic insight to the business. This is questions like what happened? What will happen? Or better, what should happen.
The target users are also different for OTBI and OBIA. Users with more operational responsibilities are envisioned as the target for OTBI while more managerial and executive users are targeted for OBIA.
In short OTBI and OBIA are complementary to each other rather than competing with each other. Still I believe there is still overlap in some areas.
BI Fusion Pre-Built Contents  
The BI Fusion offers pre-built analytical contents for following oracle application modules
• Oracle Financials
• Oracle Supply Chain Management
• Oracle Procurement and Spend
• Oracle Project Portfolio Management
• Oracle Human Capital Management
• Oracle Customer Relationship Management
I believe the combination of OTBI, OBIA and Advanced Analytics can answers to all business questions from operational to analytical arena.

Sunday, 15 July 2012

Oracle Business Intelligence 11.1.1.6.0 – ADF Data Sources –A way forward for Real Time Transactional (Operational) Reporting

ADF components as a data source to the Oracle BI Server facilities the integration of any application build on ADF framework for operational reporting. For example, as Oracle CRM applications (Fusion Apps) are developed using the ADF Framework, Oracle Business Intelligence users can report on CRM data using an ADF data source that implements the required ADF Application Programming Interface (API).

This blog is an attempt to explain the Oracle ADF Architecture, ADF Business Components, and ADF VO concepts. 

Oracle ADF Architecture

Oracle Application Development Framework (Oracle ADF) is an object-relational framework that can be used to create J2EE business services and expose underlying database objects. This framework provides an abstraction layer that enables application developers to build applications quickly and efficiently.
Oracle ADF is based on the Model-View-Controller (MVC) design pattern. An MVC application is separated into:
  • a model layer that handles interaction with data-sources and runs the business logic,
  • a view layer that handles the application user interface, and
  • a controller that manages the application flow and acts as the interface between the Model and the View layers  
Separating applications into these three layers simplifies maintenance and reuse of components across applications. The independence of each layer from the others results in a loosely coupled, Service Oriented Architecture (SOA).
Oracle ADF implements MVC and further separates the model layer from the business services to enable service-oriented development of applications.


The Oracle ADF architecture is based on four layers:

  • The Business Services layer - provides access to data from various sources and handles business logic
  • The Model layer - provides an abstraction layer on top of the Business Services layer, enabling the View and Controller layers to work with different implementations of Business Services in a consistent way
  • The Controller layer - provides a mechanism to control the flow of the Web application
  •  The View layer - provides the user interface of the application
ADF Business Components
Any service-oriented Java EE applications, core business logic are defined by one or more business services. These back-end services provide clients with a way to query, insert, update and delete business data as required, which can also enforce appropriate business rule. ADF Business Components are prebuilt application objects that provide a ready-to-use implementation of Java EE design patterns and best practices.

ADF EO and VO?
The ADF model is represented through the ADF Business Component constructs called Entity Objects (EO) and View Objects (VO), usually constructed and defined during design time.

Entity Objects
Are components that represent a row in a database table and simplify modifying its data. It enables developer to encapsulate domain business logic for database rows to ensue business policies and rules validations.
View Objects
Are components that encapsulate a SQL query and simplify working with it results. In addition to read-only view objects, there are entity-based view objects that support updatable rows. Applications built using ADF obtain their data by querying the defined View Objects using the ADF APIs.
The ADF components can be used as a data source to OBI Server which allows builds the data model on top of the ADF Framework.
There are lots of enhancements has been done for OBIEE 11g/Web Logic so one can build the reporting data model on the top of the ADF Framework, such as
·         ADF Integration
·         SQL Bypass
·         Flex Support through BI Extender
·         Conformance/Federation Model
·         Action Framework and Embedding of BI content in Transaction pages
·         Reuse Apps Security
·         Date Effectivity Support
·         Enhanced Essbase Support
These enhancements are the foundation of Oracle Transactional Business Intelligence (OTBI) which is pre-built packaged operational reporting solution for Fusion Applications such as Oracle Financials, Oracle Supply Chain Management, Oracle Procurement and Spend, Oracle Project Portfolio Management, Oracle Human Capital Management and Oracle Customer Relationship Management.
The introduction of OTBI raise lots of questions such as What is difference between BI Apps and OTBI?  Are BI Apps and OTBI complement or compete with each other?  Would OTBI cannibalize BI Apps in recent future?
There is lot to explore and write about. Stay tune.

Thursday, 12 July 2012

Oracle Business Intelligence 11.1.1.6.0 BI Publisher - Report Bursting with SQL Based Delivery Criteria

Bursting is a process of splitting data into blocks, generating documents/output for each block and delivering the documents/output to one or more destinations. The data for the report is generated by executing a query once and then splitting the data based on a key value (e.g. buyer, customer) value. A separate output/document is generated and delivered for each block of the data.

Most of the BI products offer the report bursting option which can split a single report based on specific key value and deliver reports driven by delivery element. Most of tools support different template, output format and delivery method and local to each split segment of report. Some of the practical uses of report bursting are as follows

·         Invoice generation and delivery based on customer-specific layouts and delivery preference
·         Financial reporting to generate a master report of all cost centers, splitting out individual cost center reports to the appropriate manager
·         Generation of pay slips to all employees based on one extract and delivered via e-mail

The purpose of this blog is to discuss report bursting option provided by BI Publisher in general and BI Publisher 11.1.1.6.0 in particular. There a significant difference between how BI Publisher report bursting mechanism worked in earlier version of BI Publisher and BI Publisher 11.1.1.6.0.

Oracle BI Publisher Enterprise Edition
The BI Publisher data to destinations architecture is shown below.  The key points of BI Publisher reporting architecture are as
·         Supports multiple data sources
·         Separate layout and translation from data logic
·         Supports multiple report layouts and output formats and destinations



Bursting Overview
In short report bursting is the process of generating multiple documents from the same report and delivering each document to a different destination.



Key Concepts for Report Bursting

Split-by key element is an element from the data that will govern how the data is split. For example, to split a batch of invoices by each invoice, CUSTOMER_ID can be used as element. The data set must be sorted or grouped by this element. In short it determines how data is split.
Deliver-by key element is the element from the data that will govern how formatting and delivery options are applied. In the invoice example, it is likely that each invoice will have delivery criteria determined by customer; therefore the Deliver By element may also be CUSTOMER_ID. In short it determines delivery channel, document layout and output format etc.
Bursting Query (BI Publisher Enterprise) is a SQL query that you define for BI Publisher to construct the delivery XML data file. The query must return the formatting and delivery details. In short it is used to retrieve bursting information from database via a SQL query.
Bursting Control File (EBS) is a control file which has been created manually; it is used to store bursting information. It is not supported in BI Publisher Enterprise.
Report Bursting BI Publisher 10g and Earlier Versions
Report bursting option is available with EBS integrated BI Publisher and standalone BI Publisher Enterprise Edition. In EBS integrated mode uses control files as a delivery definition and then linked it with report. It uses concurrent manager to schedule and burst reports. The EBS bursting is not in the scope of this blog.
In BI Publisher Enterprise Edition the Bursting (or delivery) query is defined in the report definition. The option to burst option is selected while scheduling the report. A sample example is shown below which has employee query which has split by department name is used as source while the delivery query is set of parameters where Email has been specified as delivery channel.

The delivery query supports various channels such as Email, Printer, and Fax etc. The delivery channels and delivery query parameters supported by BI Publisher are as follows.

The bursting option is selected while scheduling the report as shown.


Major Limitations
The BI Publisher Bursting has following major limitations
·         Inability to assign name to the output file delivered to file system or email as attachment
·         Does not support event based scheduling for report bursting
·         The bursting query is defined at report level rather than data model level
·         Multiple report bursting query is not supported
·         The delivery status may not truly indicate the status of the document.
·         Report execution and delivery performance is a major problem for the substantial volumes of outbound documents
BI Publisher 11g Report Bursting
BI Publisher Enterprise bursting query is the only bursting mode supported in 11g. The control file based report bursting is not supported with Fusion Apps. The other major difference from the previous version is that bursting definitions(s) are now supported at data model level rather than report level. Multiple report bursting definitions are possible in the same data model. The bursting is enabled at the report level and it’s used while scheduling the report.
Bursting Definition
The bursting definition includes
Split-by key - Data in data model need to be sorted or grouped by split by key
Deliver-by key
Delivery query – SQL query that specifies template, template type, output format, locale, output name, delivery channel, and delivery parameters (e.g. email address, subject, email body, etc.)
Delivery XML
A delivery XML for Oracle BI Publisher 11.1.1.6.0 is as follows
<ROWSET>
<ROW>
<KEY> </KEY>
<TEMPLATE> </TEMPLATE>
<TEMPLATE_FORMAT> </TEMPLATE_FORMAT>
<LOCALE> </LOCALE>
<OUTPUT_FORMAT> </OUTPUT_FORMAT>
<OUTPUT_NAME> </OUTPUT_NAME>
<TIMEZONE> </TIMEZONE>
<CALENDAR> </CALENDAR>
<SAVE_OUTPUT> </SAVE_OUTPUT>
<DEL_CHANNEL> </DEL_CHANNEL>
<PARAMETER1> </PARAMETER1>
<PARAMETER2> </PARAMETER2>
<PARAMETER3> </PARAMETER3>
<PARAMETER4> </PARAMETER4>
<PARAMETER5> </PARAMETER5>
<PARAMETER6> </PARAMETER6>
<PARAMETER7> </PARAMETER7>
<PARAMETER8> /PARAMETER8>
<PARAMETER9> </PARAMETER9>
<PARAMETER10> </PARAMETER10>
</ROW>
</ROWSET>

Four new tags and 3 more parameters have been added. The four new tags and descriptions are as follows
·         OUTPUT_NAME – Name that will be assigned to the output file in the report job history. This also becomes the name of the attachment when emailed.
·         TIMEZONE–The time zone to use for the report
·         CALENDAR–The calendar type to use for the report
·         SAVE_OUTPUT–True or false, whether or not to save the burst output
There are three more parameters have been added, the value of the parameter depends on chosen DEL_CHANNEL. The DEL_CHANNEL and parameters definitions are
Delivery Query Parameter

Working Example of Report Bursting
Setting up bursting is a two step process.
1.    Define the bursting criteria on the data model, multiple definitions can be defined in a data model

2.    Associate bursting definition with report, a report can only be assigned to one of them
To explain how to achieve the report bursting step by step, an example of customer order detail report bursting from sample schema is depicted below.
Define Bursting Criteria on Data Model
The Customer Order Detail Data model has two SQL source data sets, customer and orders. The master detail relationship is set by making links between the customer data and the orders data by using data model editor. The relationship between two data sets is done based on customer name field. The list of values is defined as customer name.
Customer Data Set Definition is as follows 
select "SAMP_CUSTOMERS_D"."NAME" as "NAME",
        "SAMP_ADDRESSES_D"."ESTAB_NAME" as "ESTAB_NAME",
        "SAMP_ADDRESSES_D"."ADDRESS1" as "ADDRESS1",
        "SAMP_ADDRESSES_D"."ADDRESS2" as "ADDRESS2",
        "SAMP_ADDRESSES_D"."CITY" as "CITY",
        "SAMP_ADDRESSES_D"."STATE_PROVINCE_ABBRV" as "STATE_PROVINCE_ABBRV",
        "SAMP_ADDRESSES_D"."POSTAL_CODE" as "POSTAL_CODE",
        "SAMP_ADDRESSES_D"."COUNTRY_NAME" as "COUNTRY_NAME",
        "SAMP_CUSTOMERS_D"."CUST_KEY" as "CUST_KEY",
        "SAMP_ADDRESSES_D"."STATE_PROVINCE" as "STATE_PROVINCE",
        "SAMP_ADDRESSES_D"."REGION" as "REGION"
 from  "BISAMPLE"."SAMP_ADDRESSES_D" "SAMP_ADDRESSES_D",
       "BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"
 where   "SAMP_CUSTOMERS_D"."ADDRESS_KEY"="SAMP_ADDRESSES_D"."ADDRESS_KEY"
  and  "SAMP_CUSTOMERS_D"."NAME" IN (:P_NAME)
order by "SAMP_CUSTOMERS_D"."NAME" ASC

Order Data Set Definition is as follows 
select "SAMP_REVENUE_F"."CUST_KEY" as "CUST_KEY",
        "SAMP_REVENUE_F"."ORDER_NUMBER" as "ORDER_NUMBER",
        "SAMP_PRODUCTS_D"."BRAND" as "BRAND",
        "SAMP_PRODUCTS_D"."PROD_DSC" as "PROD_DSC",
        "SAMP_REVENUE_F"."UNITS" as "UNITS",
        "SAMP_REVENUE_F"."REVENUE" as "REVENUE",
        "SAMP_REVENUE_F"."ORDER_DAY_DT" as "ORDER_DAY_DT",
        "SAMP_REVENUE_F"."BILL_DAY_DT" as "BILL_DAY_DT",
        "SAMP_REVENUE_F"."PAID_DAY_DT" as "PAID_DAY_DT",
        "SAMP_REVENUE_F"."ORDER_STATUS" as "ORDER_STATUS",
        "SAMP_REVENUE_F"."CURRENCY" as "CURRENCY"
 from  "BISAMPLE"."SAMP_PRODUCTS_D" "SAMP_PRODUCTS_D",
       "BISAMPLE"."SAMP_REVENUE_F" "SAMP_REVENUE_F"
 where   "SAMP_PRODUCTS_D"."PROD_KEY"="SAMP_REVENUE_F"."PROD_KEY"

The report bursting definition for file and email deliveries are created for the Customer Order Detail Data Model, the bursting definition and associated SQL’s are below.

Burst to Email
select "SAMP_CUSTOMERS_D"."NAME" as "KEY",
'Orders Letter' TEMPLATE,
'XPT' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
"SAMP_CUSTOMERS_D"."NAME"||'_'||'Orders List' OUTPUT_NAME,
'EMAIL' DEL_CHANNEL,  ( Delivery Channel – Email)
'to@nowhere.com' PARAMETER1,
'cc@nowhere.com' PARAMETER2,
'from@nowhere.com' PARAMETER3,
'SUBJECT:BI Publisher Enterprise Bursting Report' PARAMETER4,
'BODY: Email Body' PARAMETER5,
'true' PARAMETER6,
'reply-to@nowhere.com' PARAMETER7
from   "BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"

Burst to File
select
"SAMP_CUSTOMERS_D"."NAME" as "KEY",
'Orders Letter' TEMPLATE,
'XPT' TEMPLATE_FORMAT,
'en-US' LOCALE,
'PDF' OUTPUT_FORMAT,
"SAMP_CUSTOMERS_D"."NAME"||'_'||'Orders List' OUTPUT_NAME,
'FILE' DEL_CHANNEL,
'/home/oracle/rb' PARAMETER1 ( Folder Location)
from
"BISAMPLE"."SAMP_CUSTOMERS_D" "SAMP_CUSTOMERS_D"


Associate Bursting Definition with Report
Now that the bursting definition has been setup, it must be associated with a report in order to be used. Edit Customer Letter and view the Properties.  By default, the bursting definition associated with the data model for report will be selected in the Advanced section of the report properties. The drop down beside Enable Bursting allows you to select which bursting definition to use for the report. Remember, you can only use 1 bursting definition in a single report.

Scheduling a Report to be Burst
To schedule a report to be burst, create a new Report Job and schedule as normal, except on the Output tab check the checkbox labeled „Use Bursting Definition to Determine Output & Delivery Destination‟. When this box is checked, the output and destination input fields are hidden as all delivery information is expected to come from the bursting query.

The result of bursting should be individual customer letter generated and stored in folder specified or delivered to customer email address.



Bursting is an extremely powerful feature of BIP, there is no doubt. The next blog I would like to compare the report bursting feature of BI Publisher with Oracle BI Delivers, which I have used for report bursting for number of times.