Dashboards, Reporting & Business Intelligence

When I started my career with Oracle Apps, I wished so hard if there were any books that would get me jump started. But times have changed now, for new comers to Oracle Apps there is ample of information available all around the web. This is an advantage and might also lead to some confusion as to where to begin.

Recently, Roel of http://www.altasen.com/ send me these awesome beginner books. I quickly glanced through it and knew at the first sight that these are truly useful resources for any beginner in Oracle Apps. So I asked one of my colleagues Kamlesh, who just started his career in Oracle Apps.

Flexfields for Oracle EBS
——————————

—————–

This book, I wished I had it in my early days and it would not had taken
that much of time to understand the topics of Flexfields and good screen shots to
understand the topics.

The topics of KFF and DFF are very well explained enough to be an expert.

BI Publisher for Oracle EBS
——————————

At the very first this books will tempt you to open through all the pages because of its cool green shiny
cover with nice animated nerdy charater.

This book is a real good start for myself. Havent touched those big
fat books for I was scared to invest considerable amount of time to commit for knowing BI publisher.

This book gives a very ground up start . With the simple and easy languange with lots of
screen shots to catch up the text description.

The book starts with the history of versions and the advantage of
learning BI publisher is that BI publisher developer can develop reports not just for Oracle but
also for Seibel,JD edwards and People soft.

The book gives step by step guided practice lessons. In short I would recommend this will be a great selection to start with

———————

Apart from the above two books, there are others in the series like System Administration and Workflow. I am looking forward to more topics being covered in similar style.

Check this beautiful documentation on Enterprise Performance Foundation. This is the Business Area that is shipped along with EPF – EPB patches.

This is the main index for the document

http://www.appsbi.com/epf/business_areas_index.html

Here is the first page for the document http://www.appsbi.com/epf/business_areas0.html

and here is the menu http://www.appsbi.com/epf/business_areas_menu.html

Found a  post on  Mark Rittman’s blog mentioning the news about Oracle buying Hyperion

This news just made my head spin! What can I say?

One thing is for sure, Oracle is trying to duplicate the product line-up.

It seems for every category, Oracle now has duplicate products as a result of all the acquisitions.

The major impact could be on the CPM product line-up. What is the fate of Financial Consilodation Hub (FCH), Enterprise Planning and Budgeting and the likes?

”

NEW YORK (Reuters) — Oracle Corp., the world’s top database software maker, is near an agreement to buy Hyperion Solutions Corp., which makes software that lets companies analyze and track their performance, for about $3 billion, The New York Times said on Wednesday in its online edition.

The transaction is expected to be announced as soon as Thursday, and terms were not immediately available, the newspaper said citing unnamed people briefed on the transaction.

Under the direction of CEO Larry Ellison, Oracle has acquired several rival companies in the last few years.

Oracle and Hyperion did not immediately return calls seeking comment. Oracle is based in Redwood City, California, and Hyperion in Santa Clara, California.

Hyperion (Charts) shares closed on Wednesday at $42.84, giving the company a market value of about $2.53 billion based on reported shares outstanding. Oracle (Charts) shares closed at $16.43. Both companies’ shares trade on the Nasdaq.“

The client where I am working have 3 set of books in use. They have 2 calendars, primary currency is USD but one of the SOB uses some foreign currency ‘xyz’

So here is the equation

US SOB : Calendar : US Calendar, Currency: USD

EMEA SOB : Calendar : US Calendar, Currency : XYZ

APAC SOB : Calendar : APAC Calendar, currency : USD

APAC and US Calendars have the same period_names and have the same period date range definitions so far (**).

When it comes to bringing actuals data into EPF and EPB here is the typical process

• Bring calendar definitions for each calendar in GL
• Define Balance Rules for each SOB
• Run  Balanace Rules for the desired Periods
• Create a Business process for Actuals (load and solve)
• Create a report and you are done!

The problem with this approach is when you try to view the report in EPB, you cannot see the consolidated
balances in one shot. for e.g to view the APAC balances, you need to edit the report and include the APAC calendar
hierarchy and if you need to see the US balances, then you to include the US Calendar hierarchy.

The other problem was with EMEA balances, even though we were bringing translated balances ( from ‘XYZ’ to USD) into
Enterprise Performance Foundation (EPF), EPB requires to complete the multi currency setup and the Business Area
needs to be ‘Currency enabled’ to bring the translated actuals from EMEA SOB.

Needless to mention, the user was not happy

Knowing that the client did not have any consolidated SOB in GL, I asked the possibility of having a
consolidated SOB created in GL and then just bring this SOB data into EPB. But the user rejected it saying
its not feasible in near future. I understood this, so now I again command my idea genie for a better solution

Doing some tests and briefing up the API documents my idea genie presented me the following solution.

Bring all the actuals from different SOBs into one SOB e.g ‘US SOB’
Bring balances for ‘USD’ and only the translated ‘USD’ balances for EMEA
Treat the balances as if they are coming from one calendar i.e. ‘US Calendar’
All balances are brought in under one common ‘US Ledger’

The user liked the approach but I had to alert him of the potential drawbacks

You cannot disect and view balances by any specific SOB or ledger in EPB. This also meant that the APAC and US Calendars should have exactly the same naming conventions for periods.

For e.g you cannot have the October 2006 period in US Calendar to be named as ‘OCT-06′ and the APAC calendar to be named as ‘OCT-2006′
(if you have this situation where the names are different, you can always create a workaround for this, ask my idea genie, how? )

The user was fine with the drawbacks as their cost centers belong to single company segment and hence they have a mechanism to see balances for each set of books although indirectly.

So I began prototyping the idea.

• created a package procedure which would extract the balances from gl_balances
• Consolidated the balances and dumped them into the fem_bal_interface_t table
• Called the ‘External FEM Balance load’ program from within this procedure to automatcally load the balances in fem_balances table.
• Create an actuals business process and ran it
• Yahooooooo! My balances are showing in EPB.
• I do some validations at each point GL_Balances to FEM_BAL_interface_t, GL_balances to FEM_balances and finally EPB balances to GL_balances
• I ask the user to do his validations.
• So far the solution works great and is more user friendly then the seeded Balance rule programs

Benefits of this custom approach

• Consolidated Actuals data
• Ease of bringing monthly and historical data
• No need to run different rules for each ledger

Further enhancements that can be done is to call the Actuals business process from within this procedure so if you need to bring in new actuals data for the recently closed business process, all you have to do is just submit this one program. Things couldn’t be much simpler, isn’t it?

Let me know if you find any flaws with the approach or have any suggestions.

A new exciting patch was released recently.  ZPB.B Rollup Patch F1 provides some eagerly anticipated functionality and some critical bug fixes.  In this patch EPB development have integrated a version of the FCH Excel Add-in.  This Add-in allows customers who use the OGL link to bring GL data into EPB, to create or open dynamic reports in Excel and drill down to GL transactional data.

5493249 Oracle Enterprise Planning and Budgeting Rollup Patch F1

There is more information on the Excel Add-in here, and as always the EPB About Document contains all the information you need on patch levels, certification, documentation here

Here are few scripts that I use to validate actual balances. In the below script, you can add or remove dimensions based on the actuals report you have created in EPB.

Let’s say you have a actuals report that has line, cost center and period then you can include those dimensions in the below query (in between select and sum and also include them in the group by clause). Also restrict the below query by cal_period_id
select cctr,line_desc,
sum(xtd_balance_e),
sum(xtd_balance_f),
sum(qtd_balance_e),
sum(qtd_balance_f),
sum(ytd_balance_e),
sum(ytd_balance_f),
sum(ptd_debit_balance_e),
sum(ptd_credit_balance_e),
sum(ytd_debit_balance_e),
sum(ytd_credit_balance_e)
from
(
SELECT
b.ledger_name ledger,
c.cal_period_name cal_per,
d.company_cost_center_org_name cctr,
d.description cctr_desc,
e.financial_elem_name fin_elem,
g.line_item_name line,
g.description line_desc,
j.user_dim2_name,
j.description dim2_desc,
xtd_balance_e,
xtd_balance_f,
qtd_balance_e,
qtd_balance_f,
ytd_balance_e,
ytd_balance_f,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e
FROM fem_balances a,
fem_ledgers_tl b,
fem_cal_periods_tl c,
fem_cctr_orgs_tl d,
fem_fin_elems_tl e,
fem_ln_items_tl g,
fem_user_dim2_tl j
WHERE a.ledger_id = b.ledger_id
AND a.cal_period_id = c.cal_period_id
AND a.company_cost_center_org_id = d.company_cost_center_org_id
AND a.financial_elem_id = e.financial_elem_id
AND a.line_item_id = g.line_item_id
AND a.user_dim2_id = j.user_dim2_id
and a.ledger_id in and c.cal_period_id =

)
group by line_desc

Similarly you can construct a query against GL Balances as below

select
cctr_org, line_item
,sum(XTD_BALANCE_E)
,sum(XTD_BALANCE_E) XTD_BALANCE_F
,sum(YTD_BALANCE_E)
,sum(YTD_BALANCE_E) YTD_BALANCE_F
,sum(PTD_DEBIT_BALANCE_E)
,sum(PTD_CREDIT_BALANCE_E)
,sum(YTD_DEBIT_BALANCE_E)
,sum(YTD_CREDIT_BALANCE_E)
from
(
select
(
case when gcc.account_type in (’R',’E')
then bal.Period_Net_Dr – bal.Period_Net_Cr
else ((bal.period_net_dr + bal.begin_balance_dr) – (bal.period_net_cr + bal.begin_balance_cr))
end
)
XTD_BALANCE_E
, ((bal.period_net_dr + bal.begin_balance_dr) – (bal.period_net_cr + bal.begin_balance_cr)) YTD_BALANCE_E
,bal.period_net_dr PTD_DEBIT_BALANCE_E
,bal.period_net_cr PTD_CREDIT_BALANCE_E
,bal.period_net_dr + bal.begin_balance_dr YTD_DEBIT_BALANCE_E
,bal.period_net_cr + bal.begin_balance_cr YTD_CREDIT_BALANCE_E
, (
case when gcc.account_type = ‘R’ then ‘455′
when gcc.account_type = ‘E’ then ‘457′
else ‘100′
end
) FINANCIAL_ELEM_DISPLAY_CODE
,gcc.segment3 CCTR_ORG
,gcc.segment1 NATURAL_ACCOUNT
,gcc.segment5 CHANNEL
,gcc.segment1 LINE_ITEM
,gcc.segment7 PROJECT
,gcc.segment6 CUSTOMER
,gcc.segment2 region
,gcc.segment4 legal_entity
from gl_balances bal
, gl_sets_of_books sob
, gl_code_combinations gcc
, gl_periods gp
where bal.actual_flag=’A’
and bal.set_of_books_id in < list of ids ie. value of set_of_books_id from gl_sets_of_books>
and bal.period_name=gp.period_name
and bal.currency_code=’USD’
and bal.set_of_books_id = sob.set_of_books_id
and bal.code_combination_id = gcc.code_combination_id
and gp.period_set_name =
and gp.period_name=
)
where
(xtd_balance_e<>0
or ytd_balance_e<>0
or  ptd_debit_balance_e <> 0
or ptd_credit_balance_e <> 0
or ytd_debit_balance_e <> 0
or ytd_credit_balance_e <> 0
)
group by
cctr_org,line_item

The angular brackets <> need to be replaced with real values applicable for your situation.

Also, change the segment mapping, in the above query segment3 is cctr_org but in your case it could be totally different segment.

Query the below table

select * from fnd_id_flex_segments where id_flex_code=’GL#’ order by id_flex_num and it would give you the mapping that is used in your chart of accounts structure.

Happy Troubleshooting

Here is an excerpt from EPB development group about the feasibility of putting Siebel Analytics on top of EPB.

“ 

We have received a number of questions recently about the feasibility of putting SBA on top of EPB AWs. Currently, this is not an easy thing to do. SBA requires ’standard form’ AW metadata, which EPB does not support as it is insufficient for financial analysis, and SBA does not support value-based hierarchies.

Technically, one could manually create a shadow AW of the shared AW and manually create the standard form metadata that corresponds to the EPB structures. However, each time that the metadata in the EPB shared AW is updated, one would manually have to update the shadow AW’s standard form metadata. We have not tested this approach, and we would not recommend it because of the manual work involved whenever, for example, a hierarchy was updated or a new dimension member added.

It is feasible to put SBA on top of FEM, but obviously then the data is not processed by EPB.

“

Here is an excerpt from this survey

“…..
When asked to select the component(s) of business performance management they are focusing on, the majority chose budgeting and planning. This has been the case since the start of our survey several years ago. It�s not surprising for two main reasons. The first is pain. Many companies are still using Excel for budgeting and have long ago outgrown it. It creates a labor-intensive, error-prone budgeting process that fails to meet expectations. Until BPM came along, there were not too many alternatives. The other reason budgeting tops the list is that it is a key foundation element for BPM. How are you going to use a dashboard to see how you�ve performed if you don�t have a set of targets (the budget) to compare your actual results against? Operational analytics has been number two on the list for two years running and signals the beginning of a focus
on

…..”

Is your organization using Excel for Budgeting and Planning? You should seriously start considering applications like EPB : Enterprise Planning and Budgeting. I have talked about EPB and its benefits on various occasions on this site.
If you need my help in scoping for any EPB implementation effort then feel free to contact me at njethwa@gmail.com or use the contact form in the About page.

What is the main difference between running Dimension and Hierarchy Rule?

Let us consider the example hierarchy below

For instance, if you have the following hierarchy in GL
TOTAL
-1000
–1100
—1110 (L)
—1120 (L)
–1200
—1210 (L)
-2000
–2100
—2110 (L)
—2120 (L)

All the nodes with (L) are the lowest level leaf nodes.
When you run a Dimension Rule, it will bring over 1110, 1120, 1210, 2110, 2120.
When you run a Hierarchy Rule with TOTAL as the starting parent value, it will bring over the TOTAL, 1000, 1100, 1200, 2000, and 2100 parent values into EPF.

In GL you can have multiple hierarchies with different top nodes. In Oracle GL, you can have one child report to multiple parents but in EPF, this is not allowed. One child can report only to one parent and the hierarchy rule will fail if there is a violation of this rule.