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.

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.

Tuesday Apr 02, 2013

Oracle Enterprise Repository 11gR1 (11.1.1.7.0) - Released

Part of Oracle Fusion Middleware11g R1 (11.1.1.7.0), Oracle Enterprise Repository (OER) 11g R1 (11.1.1.7.0) was made generally available on April 1, 2013.

For more information, please look at the Oracle Enterprise Repository page on Oracle Technology Network (OTN):

This Oracle Enterprise Repository 11g R1 (11.1.1.7.0) release introduces the following new features:

  • Code Compliance Inspector
    • Built-in, design-time rule validation, e.g. WS-I BP compliance & Oracle Foundation Pack  Best Practices (optional)
    • Centralized reporting published within OER
  • Contracts Solution Pack
    • Formal capture of consumer-provider relationship
  • Harvesting enhancements
  • BI Publisher 11g support
  • And more…
For more information on those OER new features, please click 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
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