Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Create your own 3d graphics interface for Oracle

Oracle Database Tips by Donald BurlesonMay 29,  2015

It's easy to use the new SQL pivot operator to create your own multidimensional interface. 

Let's start this discussion with an overview of multidimensional database analysis

Understanding Multidimensional online analytical processing (MOLAP)

While the idea of a multidimensional database sounds exotic and difficult, it's actually quite a simple concept.  Multidimensional database analysis is know by several names:

  • MOLAP - Multidimensional online analytical processing describes the general theory of visualizing data in three dimensions.  This has the primary goal of visualizing trends and signatures to enable the end-user to spot important changes.

  • Pivot - A spreadsheet-like method for displaying aggregated data in matrix form.  Examples include MS-Excel pivot tables and the Oracle 11g SQL pivot clause.

  • Oracle warehouse builder - OWB allows you to add multiple dimensions for detailed analysis

To understand Oracle multidimensional databases quickly, take a few minutes to watch the MS-Excel help file on pivot tables.  This explains the concepts behind Oracle MOLAP.

Visualizing Oracle problems

The ability to visualize Oracle data is critical for Oracle troubleshooting and tools like Ion for Oracle were created especially for this process.

With three dimensional analysis you can plot the relationship of two variables, and the third dimension (z axis) is usually the time dimension:


An example 3d chart from Excel

For example, to find the time when an I/O problem started, you could create a MOLAP query with these three variables:

  1. disk_reads

  2. file_name

  3. snapshot_date

Once plotted as a 3d chart, the time when the I/O bottleneck began will pop right up:

The SQL pivot operator

In Oracle 11g, the pivot operator can be used to run SQL for a 3d chart:

WITH
   T
AS
(
   SELECT
      DEPTNO,
      JOB,
      SAL
   FROM
      EMP
)
SELECT
   *
FROM
   T
PIVOT
(
   MIN(SAL) AS MINSAL,
   MAX(SAL) AS MAXSAL
FOR
   (JOB)
IN
   (
      'CLERK' AS CLERK,
      'SALESMAN' AS SALES
   )
)
ORDER BY
   DEPTNO;

    DEPTNO CLERK_MINSAL CLERK_MAXSAL SALES_MINSAL SALES_MAXSAL
---------- ------------ ------------ ------------ ------------
        10         1300         1300
        20          800         1100
        30          950          950         1250         1600

Bur we you don't HAVE TO use pivot to create the data for 3d charts.  For instance, in this pseudocode we select three salient dimensions:

select
   file_name,
   disk_reads_delta,
   buffer_gets_delta,
   snap_time
from
   datafiles,
   segstat, etc
where
   rownum < 5 and snap_id between 1 and 20;

We'd get the same sort of results...diskreads and buffer gets by datafile by time.  As long as we could support multiple bars per snap time, we've got the same chart. 

Pivot is not needed in this case (and in many cases) because Oracle already denormalizes the relevant columns.  In cases where the data is normalized with name/value pairs (like in v$sysstat).

So, how do we add a third dimension to an Oracle query and visualize it?  Let's start with this sample Ion screenshot of a two-dimensional plot of tablespace activity over time.  Note the three variables (tablespace_name, physical_writes, and time):


Ion screenshot for tablespace physical writes over time

As we see in this 2d chart, all we need to add is to put the tablespace_name on one axis and use the z axis for the time dimension.  Let's take a closer look at our graphing options for 3d Oracle analysis.

Steps to create your own multidimensional interface for Oracle

To create interactive 3d charts you need to start with an interactive screen that accepts the two dimensional parameters.  The third dimension will always be TIME.

The time dimension is already there, expressed in the X-Axis.  X-Axis = time dimension, Y-Axis = physical reads dimension, Series = Tablespace dimension

Without a z axis, you can simulate a 3d chart in several ways:

Methods for simulating a 3d display 

Instead of stacking the values we can plot them with a third dimension.  In this crude example, we use the MS Excel chart wizard, using data that is gathered from Oracle using the Excel-DB tool:

A true 3d plot of Oracle values over time

Next, let's see how an Oracle enabled spreadsheet can be used to quickly query and extract Oracle data for trend analysis.

Using Excel-DB for automatic 3d plotting

Excel-DB is a MS-Excel plug-in that allows super-fast extraction of Oracle data into a spreadsheet using a super-fast API.  The developer of Excel-DB explains how to do 3d charting with the Excel-DB tool:.

We can run a query where you retrieve a quantity of interest and some other columns which which you want to partition the data (YPE, NAME, DAY, HR):

    select abc as TYPE, def as NAME, ghi as DAY, HR, SECS from sometable
 
What Excel-DB allows you to do is to couple the outcome of the query (the resultset, dynaset) to an Excel pivot table (you can even refresh it later by rerunning the query). Execute the SQL through Excel-DB's SQL dialog. A new dialog appears that asks where the output should be send to. Select: pivot table.
 
Excel will initially show a blank pivot table and a small window with the column names. You now need to drag the fields to their positions. Drag SECS to the middle of the table, TYPE to the page field, NAME to the top, hr and secs to the left. This will make a pivot table of the results. Use Excel to convert it to a pivot graph or create a new pivot graph using the data from this pivot table.
 
The possibilities are endless. Actually the only thing Excel-DB does is getting the data and attaching it to the pivot table. You could e.g. save the spreadsheet (could get large!), email it to someone else and play with the data there. Obviously without database connection there is no possibility of refreshing the data then...
 
The only technical requirement is that Excel-DB needs to connect using ADO to the database (oh, any database type in fact, not just Oracle). So, do not use the OO4O driver to connect when trying to use pivot tables.
 
The nice thing of Excel's pivot tables are also that they are highly interactive. If you can get all interesting fields together it is just a matter of playing with the fields to show different 'slices' through the data.

MOLAP analysis with Excel-DB

With Excel-DB there is no need for expensive multidimensional and OLAP tools.  Simply extract your data with native SQL, and you are ready-to-go with robust OLAP analysis.

See talking video of Business Intelligence with Oracle Spreadsheets.  Make sure to turn-up your speaker to hear the narrative.

By download data directly from Oracle or SQL Server you can quickly use the Excel pivot table tool to create OLAP cubes for fast data analysis.

Any Oracle data can be downloaded and cubed to provide a robust OLAP (ROLAP) solution for decision support and data analysis applications.

This alleviates the need to buy expensive Oracle OLAP tools such as the Oracle Business Intelligence (BI) plug-in, Hyperion or Cognos software.

Get Oracle OLAP fast and easy with Excel-DB, the preferred method for Oracle spreadsheet advanced analytics.

Now that we see how to do visualization in a spreadsheet, let's look at how an application front-end can be used to visualize performance issues.



 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational