Sunday Feb 09, 2014

Oracle Enterprise Repository Reports and International Number Formats

(Author: Marcelo Parisi, Staff Consultant, Oracle Brazil)

Oracle Enterprise Repository (OER) provides several enterprises, across the globe, the ability to manage their service lifeycles, perform advanced registration workflows, publish their services catalogue, monitor metrics and analytics, implement security policies, while providing extended metadata management and IDE plugins

One of the most useful features of OER is its ability to generate reports which not only help you manage your runtime but also provide high level metrics which give you visibility into your performance around Service Level Agreements (SLA), Return on Investments (ROI) and more.

As an Oracle consultant, I have installed and configured OER in several customer environments in Portuguese (in Brazil). This article aims to document my experience in making OER reports more useful to non-English speaking environments in international environments.

Number Formats – A Comma Can Change Everything

Number formatting has a small but crucial difference across some major languages. For example, 1234.56 written in English, would be written as 1234,56 in Portuguese (and many Latin American and European languages). The comma and dot in a number can have totally opposite meanings in two languages. For example, a comma is treated as a unit separator in English whereas it signifies a decimal place in Portuguese, Spanish, Dutch, German and several other languages. It’s easy to see how this can be misleading.

Since the client side of OER typically runs in the local language (based on browser setting), numbers are stored in OER in their native format.

When you try to then run an out-of-the-box OER report, say, Consumer Reported Asset Value (by Asset), you might hit this:

This happens due to the difference in numbering format. Taking a closer look at the report you can see that it is running this:

After taking a look at that SQL we can find that the value that is giving us problems, in this case, is from here:

If you run that SQL directly in the database, the following result is shown:

This happens because the value is stored in a string format in the database, and there are some functions in the Reports that are used to convert these from string to numeric, in order to perform calculations on it, such as TO_NUMBER, CONVERT and CAST.

Making Numbers More Relevant

There are several ways to make this work – here I have chosen to use a replace function. However, the solution needs to be as per your specific requirement and case.

The solution applied here is to utilise replace() SQL function to replace the comma character with a point. We can do this either by editing the report through BI Publisher interface (OER comes with a restricted use licence for BI), or by editing the .xdo file directly. I prefer editing the .xdo file directly. Here’s a step-by-step description:

1. Open the .xdo file related to the report we want to fix, in this case Consumer Reported Asset Value (by Asset).xdo, and find the locations where it is converting the number. In this particular file, for e.g., that is on lines 46, 62, 65, 78, 79, 177, 190, 193, 290, 311 and 312:

to_number(NVL(axi.stringvalue, 0)) AS PNHS
CASE :p_order_by WHEN 'PNHS' then to_number(NVL(axi.stringvalue, 0)) end desc,
CASE :p_order_by WHEN 'PNHR' then to_number(NVL(axi.stringvalue, 0))  end ]]>
to_number(DECODE(nvl(survey2.value, 0), '-', 0, nvl(survey2.value, 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerGrossSavings,
to_number(DECODE(nvl(survey3.value, 0), '-', 0, nvl(survey3.value, 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerTimeToReuse,
CONVERT(numeric,ISNULL(axi.stringvalue,'0')) AS PNHS
CASE :p_order_by WHEN 'PNHS' then CONVERT(numeric,ISNULL(axi.stringvalue,'0')) end,
CASE :p_order_by WHEN 'PNHR' then CONVERT(numeric,ISNULL(axi.stringvalue,'0')) end DESC]]>
CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) AS PNHS,
CASE ORD5 WHEN 1000 then CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) end,
CASE ORD6 WHEN 2000 then CAST(COALESCE(axi.stringvalue, '0') as decimal(9,2)) end desc]]>

2. Now, add the replace() function as seen below, so that the commas are replaced with a dot:

to_number(NVL(replace(axi.stringvalue,',','.'), 0)) AS PNHS
CASE :p_order_by WHEN 'PNHS' then to_number(NVL(replace(axi.stringvalue,',','.'), 0)) end desc,
CASE :p_order_by WHEN 'PNHR' then to_number(NVL(replace(axi.stringvalue,',','.'), 0))  end ]]>
to_number(DECODE(nvl(replace(survey2.value,',','.'), 0), '-', 0, nvl(replace(survey2.value,',','.'), 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerGrossSavings,
to_number(DECODE(nvl(replace(survey3.value,',','.'), 0), '-', 0, nvl(replace(survey3.value,',','.'), 0)), '99999999D99', 'nls_numeric_characters = ''. ''') AS ConsumerTimeToReuse,
CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) AS PNHS
CASE :p_order_by WHEN 'PNHS' then CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) end,
CASE :p_order_by WHEN 'PNHR' then CONVERT(numeric,ISNULL(replace(axi.stringvalue,',','.'),'0')) end DESC]]>
CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) AS PNHS,
CASE ORD5 WHEN 1000 then CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) end,
CASE ORD6 WHEN 2000 then CAST(COALESCE(replace(axi.stringvalue,',','.'), '0') as decimal(9,2)) end desc]]>

3. Save the file, and run your report again. That’s it – success!

I have used the same approach for other out-of-the-box OER Reports such as Asset Portfolio Valuation, Asset Production Detail by Project, Asset Use (by Project) and Submission Status (by Producer) with success.

Hope this goes further in making your SOA Governance and OER initiatives even more productive and rewarding.

Friday Oct 04, 2013

Introducing Oracle Enterprise Repository Express Workflows - Simplified Lifecycle Management

As announced at Oracle Open World 2013 last week, we are delighted to introduce an exciting, new feature for Oracle Enterprise Repository 11g PS6 - Express Workflows.

OER provides a set of features and options for moving an asset from an initial to a final lifecycle state. Express Workflows are a mechanism for    automating these asset lifecycle approval processes in OER.

Express Workflows help automate this process for any given type of assets that are produced by your project(s). It allows you to easily assign approvers and define the flow of approvals from one approver to the next.

You can use Express Workflows to pre-define, automate and ensure that assets submitted or harvested into your OER projects go through as much or as less of an approval process as per your requirement - in a few, simple steps.

Download the OER 11g Express Workflows here - requirements and detailed instructions are available in the PDF bundled within the patch.

Tuesday Oct 01, 2013

Announcing Oracle Enterprise Manager 12c Cloud Control Integration with Oracle Enterprise Repository 11g for Runtime Metrics

Whether you are a service consumer, producer (developer), architect or manager, getting regular updates on runtime metrics of your assets is critical for many reasons. OER 11g PS6 can now gather runtime metrics for harvested assets from EM 12c Cloud Control. These metrics will then show under respective assets at regular, configured intervals.

To achieve this, you need to point the EM Integration Utility (already included in your OER), to your EM 12c and OER 11g instances. A new configuration file for EM 12c integration is now available. Get the patch and the new settings file from here. Instructions for applying the patch and configuring the utility are bundled in the patch as well.

Thursday Jul 25, 2013

SOA Governance Training, August 26th–30th 2013 - EMEA (Lisbon)

As a part of the Oracle Fusion Middleware Summer Camps III for our Partners, we are delighted to offer a SOA Governance Hands-on training session delivered by Oracle SOA Governance Product Management.

SOA Governance Hands-on Training

The Oracle SOA Governance Session focuses on introducing you to the crucial discipline of managing your SOA/Integration projects using the Oracle Enterprise Repository (OER), based on SOA Governance principles.

Whether you are a Developer, an Architect or a Business Manager, OER has a lot to offer to help you make the most of your SOA initiatives – a Service Catalogue, Automated Publishing, Lifecycle Management, Policies and Reporting, to name a few. OER is a powerful and flexible tool that you can leverage to manage all aspects of SOA Governance – People, Processes and Tools.

What’s more – we will also talk about our popular OER Rapid Start 30-day program delivered exclusively by Oracle Partners or Oracle Consulting. OER Rapid Start is a 30-day program, delivered to many of our customers, to kick start their  SOA Governance initiative right from engaging the right stakeholders, defining lifecycles, publishing their services to tracking ROI on their SOA investments.

The session will be lab-based giving you hands-on experience on the most popular customer use cases with OER.

Trainer:

Yogesh Sontakke, Principal Product Manager, Oracle

Additional trainings during the OFM Summer Camps III:

  • BPM: Adaptive Case Management & forms builder by Niall Commiskey
  • B2B & Adapters by Scott Haaland
  • Advanced SOA Suite by Geoffroy de Lamalle
All sessions will be given by our HQ Product Management and PTS  team. The sessions will take place in Lisbon, Portugal from August 26th to 30th 2013. Participation is limited to two people per company and training track. Registration will be handled on a first-come-first-serve basis. Please note the skill requirements, the pre-requisitions and follow up. For details, please visit the registration page.

Friday Apr 06, 2012

Oracle Open World 2012 Call For Papers

At Oracle OpenWorld, more than a thousand people demonstrate their mastery and expertise by leading sessions on a vast array of Oracle technologies and products.
Now’s the time to submit your SOA Governance success story presentation abstract for review by the selection panel.
Don’t delay—submit your abstract now as the Call for Papers is open through next Monday,  April 9th.
The competition is strong: roughly 18% of entries are accepted each year from more than 3,000 submissions. Review panels are made up of experts both internal and external to Oracle. Successful submissions often (but not exclusively) focus on customer successes, how-tos, or technical topics.
What’s in it for you? Recognition, for one thing. Accepted sessions are publicized in the content catalog, which goes live in mid-June, and sessions given by external speakers often prove the most popular.
Plus, accepted speakers get a complimentary pass to Oracle OpenWorld with access to all sessions and networking events─that could save you up to $2,595!
For more information, please look here.

Monday Jul 11, 2011

Demo: How to Harvest Run-time Metrics Into Oracle Enterprise Repository Assets

Want to know how to harvest run-time metrics on a SOA composite from Enterprise Manager Grid Control into the Oracle Enterprise Repository? Here is a demo that shows the OER asset before run-time metrics are harvested and a tour of the run-time metrics in Enterprise Manager Grid Control. Then you will see the EM Integration Utility being run to harvest the run-time metrics. Finally you will revisit the OER asset and view the run-time metrics that were harvested. Watch the demo here

Thursday Feb 12, 2009

Got Policy Federation?

[Read More]

Friday Oct 24, 2008

The Role of BPM in SOA Governance

[Read More]

Friday Oct 10, 2008

Everything You Need to Know About Oracle SOA Governance

[Read More]

Tuesday Jul 15, 2008

Welcome to SOA Governance@work

[Read More]

Monday Jul 14, 2008

5 Tips for Buying SOA Governance

[Read More]
About

Latest news, updates, best practices and thoughts on Oracle SOA Governance products


Oracle SOA Governance includes Oracle Enterprise Repository, Oracle SOA Management Pack and our API Management Solution

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today