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:
-
disk_reads
-
file_name
-
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.
and include the URL for the page.
