Dashboards, Reporting & Business Intelligence

At the current client, the customer is using Oracle CRM and the packaged OBIEE BI Apps has CRM content sourced from Siebel CRM. BI Apps can help us provide the content for Finance, Supply Chain, Order Management and HR (peoplesoft). For the CRM piece we have enabled Daily Business Intelligence dashboards for Customer Support, Depot Repair and Field Service.

The good thing is Oracle provides the repository for DBI content (which I think they have discontinued promoting it). Using this repository and the OBIEE BI Apps repository we can merge them two and provide a single point of entry for almost all of their Analytics requirements.

In DBI (Daily Business Intelligence), the cost center hierarchy is maintained in the following table “fii_cost_ctr_hierarchies”

So, using the above table, it is possible to get all children for any given node. (This is possible from the FND tables in Oracle apps too)

Here is the query

SELECT child_value
FROM
(
SELECT LEVEL,
parent_cc_id,
child_cc_id,
child_value,
lpad(' ',    3 *(LEVEL -1)) || child_desc cc_hrchy,
next_level_is_leaf_flag
FROM
(
select
h.parent_cc_id
,h.child_cc_id
,node_child.flex_value child_value
,node_child.description child_desc
,h.is_leaf_flag
,h.next_level_is_leaf_flag
from fii_cost_ctr_hierarchies h
, fnd_flex_values_vl node_child
where  h.child_cc_id = node_child.flex_value_id
and  node_child.flex_value_set_id = h.child_flex_value_set_id
and parent_level = child_level -1
)
START WITH parent_cc_id in ( select flex_value_id from fnd_flex_values_vl
where flex_value='
'
and flex_value_set_id = 1005351 )
CONNECT BY PRIOR child_cc_id = parent_cc_id
)
where  next_level_is_leaf_flag='Y'

If you need to see only sub-parent nodes then replace the last line in the query to next_level_is_leaf_flag=’N’

This query can be used in other reporting requirements such as using in any Discoverer reports.

For e.g. In your GL chart of accounts, lets say your Cost Center is segment2 and there is a need to find total balance for some particular Cost Center node “XYZ” then you could write a query similar to

select sum(balance column)

from gl_balances gb, gl_code_combinations gcc

where gcc.code_combination_id= gb.code_combination_id

and ...

and ...

and gcc.segment2 in ( result set from the above query...)

With the Oracle Forms, it is easy to see behind the forms which view/table is involved. You can also see how the columns are mapped to the fields on the forms.

This became an issue when the self service html forms started dominating the scene. One of the reason I hate the self service pages is there is no easy way to see the SQL code behind, atleast there was no mechanism provided by Oracle to do this in a easy way.

Recently, I came to know the trick to see the code behind Daily Business Intelligence Financial Pages. All you have to do is set the following profiles.

FND: Debug Log Enabled to Yes
FND: Debug Log Level to Error
FND: Debug Log Module to BIS%

This might work for other modules too but not sure yet. This does not mean I am going to change my opinion about self service applications. The UI is the worst if you compare it to the GUI forms – Ugly long URLs in the browser – Takes far more number of mouse clicks and page navigations to get things done compared to the GUI forms.

Daily Business Intelligence Family Pack G (7.3) is now available from Metalink. Please see the About Document, Metalink Note 391782.1 for a complete list of new features along with installation and setup instructions. New Feature Overview
This release launches Daily Business Intelligence for Receivables.

Daily Business Intelligence for Receivables empowers Receivables, Credit and Collections managers to stay on top of the critical information related to their department’s operational performance.

The increased visibility enables Receivables and Collections managers to reduce the time of operational problems discovery and resolution, and therefore, increase the effectiveness of their departments. It also provides valuable insight into the state of customers’ receivables payment patterns enabling Credit Managers to formulate Credit Policies that would prevent undesired impact on company’s bottom-line.

Daily Business Intelligence for Receivables consists of the Receivables Management and Receivables Status dashboards. Together they allow analysis of the key performance indicators (�KPIs�) such as Days Sales Outstanding, Average Days Delinquent, Open Receivables and Past Due Receivables Amount, and Unapplied Receipts Amount across multiple dimensions, such as time, organization, customers, and collectors. They empower managers to evaluate the performance of their organizations and discover systemic processing inefficiencies. In addition they offer valuable insight essential for evaluating the productivity of individual collectors.

This release also further enhances the Daily Business Intelligence for Financials.

Profit and Loss Analysis provides valuable insight necessary for evluating the information related to a company’s revenue, cost of goods sold, operating expenses, gross margin and operating margin.

The Profit and Loss Analysis Dashboard targets financial analysts and line managers who are responsible for supporting and analyzing revenue and expenditures for particular cost centers and company entities across different ledgers. The Dashboard features a company/cost center/account-oriented view of the company’s key profit and loss activity.

I know it is not a fair question, as a “Daddy” myself; I want all of my children to be intelligent.
Well, this is not any parenting website so of course the title must and should be related to Business Intelligence, right?
  OK, let me ask another question, if you were running your own organization or company don’t you wish
You had information (read intelligence) on all the aspects of your company?

For e.g. in most of the BI initiatives, the emphasis is laid on getting the Key metrics for Financial and Manufacturing Data.
To generalize, the emphasis is laid on the most critical component of your business; it could be CRM, SCM, and Manufacturing
Or Finance. This is perfect, as with the given time and budget constraint you want the most important information.
Time goes by and budget runs over, somehow you manage to assemble all your KPIs into nice looking dashboards and call them “executive dashboards”.

Now lets dig deeper.
How about intelligence on your Outsourcing activity?
Can you effectively tell, if your Outsourcing activity is really beneficial? Do you keep track of any metrics for the code quality, Number of hours worked, Number of Onsite and Offshore developers, Bandwidth Usage, Cost of staying in touch
Etc.

Similarly, How efficient is your infrastructure and the team who is maintaining it? Do you keep track of the number of instances? What are the cloning schedules?
Does your DBA have the right tools in order to efficiently manage his daily job? Can your DBA easily check important System and Database parameters just as your “Executive” can check important financial information?

Similarly, Can your programmer check his or her own performances? Do they have access to metrics that tell them about their code quality, their productivity etc. Is it good for them to have access to a dashboard that instantly tells them about the Greens and Reds? Wouldn’t it be nice if they can see where they are performing good and where they
need improvement rather than you as the manager or supervisor hesitantly telling them anyway!

So effectively you can pick all the activities that is happening and derive intelligence out of it.

So going back to the title question, I guess everybody in your organization is entitled to intelligent information
so that they constantly improve what they are doing and that is what will give them an edge.

If you have plans for “Executive Dashboards” then shouldn’t you plan for “Non-Executive Dashboards” ?

Enough of questions, now lets get Dashboards for all our children

Here is a brief overview of PMF and PMV. I had to ask for help from my colleagues to get me some good information on this one.

Performance Management Framework (PMF) is a foundation technology of the
Business Intelligence product family and is a key component of Oracle e-Business
Suite. PMF provides a set of pre-seeded performance measures, such as Revenue or
Bookings, that managers can use to track activity across their enterprise.

The performance measures are provided when you setup the products in the Business
Intelligence product family: Oracle Business Intelligence System, Oracle Embedded
Data Warehouse, or Oracle Daily Business Intelligence.
PMF enables you to set up a target value for each of the preseeded performance
measures. You can also define up to 3 tolerance ranges for each measure. Each
tolerance range can be associated with a different owner, either a responsibility or
user, as illustrated in the following table. Whenever a measure falls out of one or
more of the defined tolerance ranges, the owner associated with that tolerance
range automatically receives a notification. You can also set up workflows that
automatically correct the problem.
You can schedule or request alerts for each measure using one of two supported
alerting technologies: Oracle Alerts or E-Business Alerts.

Data Sources
E-Business Alerts measures can retrieve data from any source, as long as the
performance information is contained in a database view. Oracle Applications
contains pre-built measures and their associated database views. These views can
provide information from a data warehouse or transaction system. The E-Business
Alerts measures are only available with Oracle Embedded Data Warehouse and
Oracle Daily Business Intelligence.
Oracle Alerts measures can retrieve data from any source that can be coded in the
alerts. Pre-built measures retrieve data from the transactional system, usually from
a summary database table.

The Performance Management Viewer (PMV) is the underlying technology that
renders portlets and reports in Business Intelligence applications. PMV is
technology independent so it can be used to render reports in either OLTP or EDW
platforms.
PMV lets users access a set of pre-built reports that are specifically tailored to
intelligence users. These reports provide an intuitive and easy way to navigate
through the user interface. For a complete list of the available PMV reports, see the
Business Intelligence documentation for the intelligence product and platform you
are using (for example, Oracle Embedded Data Warehouse User Guide, “Reports”
chapter).
PMV enables you to use the generic Performance Management portlet to display
graphs or tables from any of the PMV reports that you have access to.

 

GL Balances in Oracle General Ledger is the summary of your entire ERP and hence is the first level of aggregation available directly within the applications. There is some really good intelligent information stored in this table. We will see how to extract some of the vital information as below.

· What is expense by Account?
· What is expense by Cost Center?
· What is revenue by Product? (Provided you have product as one of your account dimensions)
· What is revenue by Account and Country?
· What is expense and Revenue by Project? (Project needs to be an account dimension)
· If you set up a dashboard with a list of account dimensions and account types as shown below then you get the ability to dynamically change the Account dimensions and get ad-hoc analysis of Expenses, Revenue, Liability, Assets and other Account types.

Also, Financial module of DBI uses similar logic to determine Profit and loss metrics but in DBI the information is static and refreshed at a certain frequency. If you need real-time analysis then a dynamic dashboard using the below mentioned logic can serve the purpose.
In order to proceed, we need to first extract some information about the accounting flexfield structure and its segments.
1. First Select the Account FlexField meta data, i.e. find out which is the Balancing segment, Cost Center, and Account segment.

2.
What are my company wide expenses for period=Dec-03 ?

In the below query, we are using the company segment and querying for “Expenses�

Note : You can get the value set id by querying the fnd_id_flex_segments table.

Here are some visual examples of the above scenarios

i. Revenue by Product (product is one of the Account Dimensions)

ii. Expense by Department

iii. Expense by Account

iv. Revenue by Account

Wayne Eckerson at SearchCIO.com offers his insight into Embedded Analytics. Here is what he says

“

Embedded analytics are the next wave in business intelligence because they bring BI closer to the operations and processes that drive businesses on a daily basis. Embedded analytics won’t replace standalone BI tools. Rather, they will make the functionality offered by such tool sets more readily available. By embedding BI functionality within operational applications and processes that drive the business, embedded analytics will make BI more operational, easier to use and pervasive — key challenges facing the current generation of BI adopters.“
Oracle has an excellent embedded analytics offering that comes packaged and ready to use with Oracle Applications. Its called Daily Business Intelligence (DBI), that comes with pre-defined dashboards for Financial Intelligence, Payables Intelligence, HR and many other modules. There are few setups involved and need to run few concurrent requests on a periodic basis in order to refresh the data in the seeded dashboards. It also comes with DBI designer, so you can create custom reports and KPIs. DBI is an offering under the CPM umbrella.

What are Business Views that Oracle refers to within all the intelligence products like BIS, Discoverer for APPs and Daily Business Intelligence?

• A Business View is a database view on top of Oracle Apps Tables
• Logical view encapsulating key columns and extra information from Flex Fields (Key and Descriptive)
• Easy and Efficient mechanism to reflect any Flex Field changes
• Pre-built Security encoded within each Business views.
• Security is defined based on Operating Unit, Inventory Organization and Business Groups
• Pre-seeded EUL Folders use Business Views as their data source
  Business Views is comparable to Noetix views

Why do we need to Regenerate Business Views?

• Oracle just provides the base skeleton of the view.
• This skeleton view usually has a suffix of _FV (FV = Full View)
• This skeleton view has all the required columns plus instructions on how to extract the Flex Field Columns
• Sample instruction ‘_DF:PER:PER_GRADES:grd‘
• Business View Generator Program reads the skeleton view definition and creates a complete _FG view (Full Generated)
• EUL Folders point to the _FG views