Announcing the 2019 ODTUG Innovation Award Nominees

If you are an ODTUG member (Associate or Full), you should have received an email with a members-only link to vote for your favorite innovation. If you are an ODTUG member but did not receive the email with the voting link, please email melissa@odtug.com. 

Thank you to all the individuals who nominated these exceptionally outstanding individuals and their innovative projects. The winner of the 2019 ODTUG Innovation Award will be announced at the ODTUG Kscope19 General Session on Monday, June 24, 2019.

 

2019 Nominees: 

 

Oracle Health Check Automation

Nominee Name: Simon Pane

Project Video

Project URL 

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

Oracle Support provides customers with a number of useful tools for no additional charge. One of these tools is ORAchk, which is invaluable for checking the health of Oracle (database, hardware, other Oracle software, etc.) from a configuration perspective.

While many customers use ORAchk as provided, very few know that it's more than just a tool and is actually an extensible framework. Oracle has designed the tool with an APEX repository, allowing for the trending of collected data and the addition of additional/custom checks.

Simon Pane and Jared Still used the underappreciated side of this powerful tool extensively, adding literally hundreds of custom checks. In addition to this, another rarely known facet of the tool is that the output can be obtained in JSON (and XML) format for programmatic ingestion into other tools, which they also used.

Throughout this comprehensive testing and development of these two lesser used features of the tool, a large number of bugs and issues were uncovered. Working directly with Oracle program managers and support, Simon and Jared helped harden and enhance the product through their diagnostics, feedback, suggestions, and troubleshooting.

The result is that executing ORAchk has been greatly simplified for the DBAs at Pythian. After editing a few parameters in a configuration file, a script is run which runs ORAchk for each of the following categories, generating JSON files for the later step.

- Standard ORAchk Checks
- Security
- Backup Review
- Health Checks

The DBA types './run.sh' and comes back in 30–60 minutes to collect the zip file.

The second step in the process is to unzip the files in custom VirtualMachine and use the included Docker image to generate several Microsoft Word documents with varying combinations of the Check Categories. This is also accomplished via a simple command of 'rptgen.sh -c "client name"'. This step takes about 30–60 seconds to complete.

The resulting MS Word files contain nicely formatted information from the requisite checks, which is a huge time savings for the DBAs and consultants who are preparing these reports.

Finally, to share these lessons and expand general knowledge of ORAchk (and its broad functionality) in the community and for the benefit of all, Simon has prepared presentations delivered at several major conferences around the world.

 

Global Business Intelligence 

Nominee name: Beau Reynolds

Project URL

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

Background:
* ERP re-implementation to OFC
* FDMee on prem
* ARCS for reconciliations and tie outs between OFC and HFM

Issue:
How do you automate pushing extracted, staged, and transformed data from OFC to ARCS for reconciliations and for the different tie outs that take place in ARCS?

Beau's scripts to the rescue! Beau coded Java and Jython scripts that make use of SOAP and XML to automatically load data in ARCS. Oh, did I mention that his script also references the mappings from our HFM location to map data for reconciliations on the fly, thus eliminating the need to maintain yet another set of mappings? Thank you, Beau!

Additional information about the project and/or team members.

Rian Beatty at  GBI.

 

 

ASH Viewer

Project URL

Nominee Name: Alexandr Kardapolov

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

ASH Viewer is a free open-source standalone application that gathers, aggregates, and provides a graphical view of Oracle active session history, even on Standard Edition. ASH Viewer provides graphical view of active session history data within the Oracle instance.

Active Session History (ASH) is a view in Oracle that maps a circular buffer in the SGA.
The name of the view is V$ACTIVE_SESSION_HISTORY. This view is populated every second
and will only contain data for 'active' sessions, which are defined as sessions
waiting on a non-idle event or on a CPU.

ASH Viewer provides graphical Top Activity, similar Top Activity analysis and Drilldown
of Oracle Enterprise Manager performance page. ASH Viewer store ASH data locally using
embedded database Oracle Berkeley DB Java Edition. The default capture rate is one snapshot
every 30 seconds. ASH Viewer support 10g, 11g version of Oracle DB. Use SYSTEM database user
to connect to Oracle DB. Please note that v$active_session_history is a part of the Oracle Diagnostic Pack
and requires a purchase of the ODP license.

For Oracle 9i(8i) DB, ASH Viewer emulate ASH, storing active session data on local storage.
The default capture rate is one snapshot every 1 second. For Oracle 9i(8i) DB,
user SYSTEM must have access rights to the views sys.x_$ksuse, sys.x_$ksusecst

create view x_$ksuse as select * from x$ksuse;
create view x_$ksusecst as select * from x$ksusecst;

grant select on sys.x_$ksuse to system;
grant select on sys.x_$ksusecst to system;

System Requirements
-------------------
JDK:
1.5u11 or above.
Memory:
Minimum 128 Mb. Recommended 192 Mb.
Disk:
It depends on Oracle workload.
Operating System:
No minimum requirement.

Building ASH Viewer
----------------
1) Download JDBC driver ojdbc6.jar from http://otn.oracle.com and put it to ashv-<<version>>-bin/lib directory.

2) Run ./gradlew assembleDist (gradlew.bat for Windows)

3) Binary archive will be created in build/distributions directory

Running ASH Viewer
----------------
1) Unpack the archive, eg:
unzip ashv-<<version>>-bin.zip

2) A directory called "ashv-<<version>>-bin" will be created.

3) Download JDBC driver ojdbc6.jar from http://otn.oracle.com and put it to ashv-<<version>>-bin/lib directory.

4) Make sure JAVA_HOME is set to the location of your JDK,
see run.cmd/run.sh (on Windows/Unix platform).

5) Create monitor user in Oracle DB and grant select and execute priviliges using user_privs.sql file.

6) Run bin/ASH-Viewer.bat or bin/ASH-Viewer (on Window/Unix).

Known issues
--------------
Problem: When running ASH Viewer on JRE6, dragging window slider on Top Activity is too slow
Workaround: use JDK5+ or JRE5 to run ASH Viewer

Problem: On Oracle 9i, select from v$sql_plan, cause extreme library cache latch contention
Workaround: collect statistics on fixed table x$kqlfxpl to resolve this issue:
SQL> exec dbms_stats.gather_table_stats('SYS','X$KQLFXPL');

Additional information about the project and/or team members.

Alexander is an Oracle engineer at Jet Infosystems.

 

Custom Oracle Data Visualizations Extensions: "Elbow" Dendrogram Viz, Vertical Waterfall Viz, Butterfly Viz

Project URL

Nominee Name: Gary Adashek 

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

Gary and Diego created three custom plugins available to download, import, and start using immediately with any of your Oracle Data Visualization projects.
1) “Elbow” Dendrogram Viz
2) Vertical Waterfall Viz
3) Butterfly Viz

In building the custom plugins, it started with a recent interest to learn more about visualization. Since being diagnosed with a rare eye condition, the subject matter and domain of data visualization became exponentially important to me. It became more than that—it became personal.

All of the plugins are developed with the intent that they would be hierarchy aware, especially for EPM (Hyperion) applications (i.e., Essbase).

The first plugin, the “Elbow” Dendrogram, was developed with the intent to be able to visualize hierarchies since there are no by-default visualizations in Oracle DV that do so naturally. In addition, this visualization looks similar to how many people recognize hierarchy structures, albeit in a Windows environment or in an Essbase outline. The magic that drive this visualization being so useful is the fact that a user is not required to have a measure in the canvas; this visualization works with and without a measure element!

The second plugin, the Vertical Waterfall, was developed as an adaptation of what many people know as the traditional waterfall diagram, which is a visualization that is a part of the defaults in Oracle DV. With the customizations, the bars and text are able to be read top-to-bottom and left-to-right much more easily—the way the majority of natural language is read around the world. Not to mention that a waterfall in nature flows this way as well.

The third plugin to date is the Butterfly and it was developed to take advantage of multidimensional data and the ability to set and compare side by side along a common visualization row (attribute spine) i.e., compare Actuals vs Budget data. There is simply not a visualization that is able to do this by default today. And this is a commonly known visualization that the Hyperion (EPM) community recognizes with the ability to create such a numerical representation of this via Oracle Smart View for Office today. The difference is that this plugin takes the numerical data from a pure row & column on a grid and transforms it to be represented as a side-by-side bar chart comparison.

All three of the custom plugins developed to date are available to anyone to download for free and import to use in a project.

Additional information about the project and/or team members.

Additional team member: Diego Flores

 

 

Dynamic Oracle Performance Analytics Using Normalized Metrics 

Project URL

Nominee Name: Roger Cornejo

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

Roger developed a novel approach to performance analytics, which he calls the DOPA process for short [Dynamic Oracle Performance Analytics]. The DOPA process relies on big data and advanced analytical techniques to analyze and improve Oracle Database performance (basically, an anomaly detection mechanism). This innovative and novel approach represents a step-change paradigm shift away from traditional methods for performance analysis. Instead of relying on a few hand-picked, favorite metrics, or wading through multiple specialized tables of information such as those found in an automatic workload repository (AWR) report, you can draw on all available data, applying big data methods and analytical techniques to help you draw impactful, focused performance improvement conclusions. The “feature selection” of the DOPA process targets the most relevant metrics for the problem interval which results in event-focused and actionable intelligence on the performance issue and insight into possible solutions. Thus, the DOPA process improves on the typical/traditional approaches which may cause you to miss important system behavior because they typically monitor a standard set of hard coded metrics. Moreover, the DOPA process is easily accessible (there is nothing to install) and can be used by anyone since it is expressed as a single SQL statement.

 

 

RELAY Workflow

Nominee Name: Jason Aughenbaugh

Project Video 1

Project Video 2

Project Video 3

Project Video 4

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

RELAY Workflow was inspired by Oracle Application Express (APEX). It was built on the desire to change the way APEX developers build their applications.  

Often times in an APEX business application the business process is managed through page or transition methods that rely on the user following the application's lead. It's not a bad method when there is really no other option, but it has a few key flaws. First, a change to the business process is likely to require a change to the application's behavior. This is usually the case, but when the process is built-in, it often requires larger, more impactful changes that can break an application and leave a world of issues in its wake. Secondly, the application-based flow requires the user to interact. Not a bad thing, but humans are living creatures that need to eat, rest, or go on vacation.  

A separate workflow engine offers a number of benefits. It can automate the business process and allow changes without always requiring a change to the application. It can allow multiple processes to be applied to the same data with overlapping execution. It can even automate data-based decisions and conditions, further improving the efficacy of the process by eliminating human interaction to only those times when it becomes essential.

Is RELAY innovative? Maybe. There have been other workflow processing projects. Most are defunct and had limited capabilities. Workflow is not a new concept, but to date it has not had good focus from the Oracle and Application Express communities.

RELAY is a node-based business process automation system that is built entirely in the Oracle Database and allows interface using RESTful Web Services via Oracle REST Data Services (ORDS). Using an APEX application as its designer, RELAY allows a process to be built visually then configured and implemented.  

For the developer, RELAY:
• Separates business process logic from application logic
• Provides a singular platform for multiple applications
• Provides for multiple process flows for a singular set of data
• Reduces the probability that process change will impact application design
• Allows the developer to hand off the process design to a power user
• Makes assignments for tasking and decisions
• Routes data-based conditions and decisions automatically to reduce stop time
• Provides email communication when user interaction is required
• Can modify data points to be returned to the application
• Can receive a "limited" object of data to process workflow so sensitive, non-process data is not exposed

The original incarnation of RELAY was an engine that lived inside the same Oracle Database where the applications that would use it resided. In the current setup, RELAY can be triggered and used by multiple databases or applications through web services. This means that RELAY is able to provide Workflow as a Service to a whole world of applications, including those that are not based on Oracle technologies.

Additional information about the project and/or team members.

Adrian Png and Monty Latiolais contributed to the designer application in the first INSUM hackathon project called "MapIT".

 

 

WeWork - Oracle Analytics Cloud Project

Nominee Name: Alex Ladd

Project URL

How is this solution innovative? List the ways in which this project is innovative and how it's helping to lead and define Oracle technologies.

Improved Budgeting and Forecasting with OAC

This project streamlined and automated metadata and data from their Workday ERP system along with key statistical information from other sources. Essbase provided a user-friendly way for users to access all company information from a central location. Before OAC, users had to visit multiple source systems just to gather the information needed.

We provided WeWork with the ability to perform constant currency analysis on actuals, prior year actuals, budget, and forecasts.
We also provided the ability to quickly view different scenario variances at a high level using dynamically calculated members. Scenario variances included: Current year actuals vs Prior year actuals, Actuals vs Budget, and Actuals vs Forecast.

The project provides a central location for users to easily submit budgets and for management to quickly and cleanly see aggregated results. Before OAC, budget managers would need to individually collect Excel spreadsheets from all end users, then consolidate all templates into one master template. Their old process would take weeks to provide final results. With OAC budget, managers could now view consolidated results instantly and provide the feedback necessary.

Additional information about the project and/or team members.

Additional team members include Cuong Nguyen Tien and Ryan Rogowski.

Recent Stories
Join the 2020 ODTUG Board of Directors - Call for Nominations Now Open

ODTUG August 2019 Newsletter

Jim Czuprynski Named Database Community Lead