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.

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.

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.

Friday Jun 22, 2012

SOA Management in 3 minutes - Video explainer

Today’s CIOs and IT executives face challenges that take valuable time away from more strategic business objectives. They have to keep their systems running 24/7, manage increasingly complex applications, and more as part of their SOA environment.

Watch this quick 3 minute video explainer to learn how Oracle EM Management Pack Plus for SOA is engineered to deliver value right out of the box with a fully centralized management console - with a rich set of service and system level dashboards, administrators can view service levels for key business processes and SOA infrastructure components from a central location.

Watch the 3 minute video explainer

Monday Sep 12, 2011

Monitoring SOA Composites Using Enterprise Manager Grid Control

The final part of the 3-part series showcasing Oracle Enterprise Manager SOA Management capabilities.


This demonstration gives you an overview of monitoring SOA composites using Enterprise Manager Grid Control. You can monitor SOA composites with Enterprise Manager Grid Control when you purchase the SOA Management Pack Enterprise Edition.



View Demo


Thursday Sep 01, 2011

Monitoring SOA Infrastructure Components Using Enterprise Manager Grid Control - Demo

This demonstration gives you an overview of monitoring SOA infrastructure components using Enterprise Manager Grid Control. You can monitor SOA components with Enterprise Manager Grid Control when you purchase the SOA Management Pack Enterprise Edition.



View the demo


Watch this space for more such demos.


Monday Aug 29, 2011

ORACLE ENTERPRISE GATEWAY Business and Technical Seminar, 20-21 Sept 2011

 


Oracle PartnerNetwork would like to invite you to a FREE 2 day ORACLE ENTERPRISE GATEWAY Business and Technical Seminar which is taking place from 20th & 21st September 2011 in Oracle Reading, UK.

Description

This event is targeted at Partner organisations working with, or evaluating the possibility of using/deploying Oracle Enterprise Gateway. The event will consist of Business and Technical tracks for sales and technical personnel. It will show how business benefits can be delivered to your customers, increase revenue for your organisation and understand key product features, installation and deployment.

Oracle Enterprise Gateway (OEG), a purpose-built gateway product offering, simplifies and secures SOA deployments on-premise or in the cloud. It secures and expedites XML, Web Services and other types of data in a simple, easy-to-use manner addressing common SOA bottlenecks. Oracle Enterprise Gateway is a standards-based, policy-driven, standalone software security solution that provides first line of defense in Service-Oriented Architecture (SOA) environments.

Oracle Enterprise Gateway provides following key functionality:

• Provide DMZ-Class Security

• Provide Ultra-Fast XML Processing

• Integrate and Extend to 3rd party systems

• Enhance Service Governance

Topics covered

• What are the challanges of SOA deployments in enterprises?

• How does Oracle Enterprise Gateway add value and benefit to the end customer

• How do you sell Oracle Enterprise Gateway to Oracle and non-Oracle customers

• Oracle Enterprise Gateway High Level Architecture

• Oracle Enterprise Gateway Product Features

• Hands on product installation

• Configuration and customization

Registration

To register, please click on the link below. This will direct you to our online registration system. You will be prompted to log on to OPN with your usual partner login and password.


PLEASE NOTE: In order to complete the registration you will need your OPN agreement name. If you do not have this information contact the Oracle University Partner Education Hotline at 0118 92 40700 or if you are calling from outside the UK +44 118 92 40700.

If you have any queries regarding this event please call us on 0118 92 40700 or email parted_uk@oracle.com.


REGISTER HERE - 20th & 21st September 2011 - Reading 

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