Visualizing Database Management with SVG

Keywords: Data-Driven Graphics, XML Database, XSLT, SVG DOM

Jinyu Wang
Senior Product Manager
Oracle Corporation
Redwood Shores
CA94065
U.S.A

Biography

Jinyu Wang is a senior product manager for Oracle XML Product management. She is in charge of the Oracle XML Developer' Kit, which provides the XML infrastructure components used across Oracle product stack. She is also a major developer for multiple projects that successfully provide XML solutions to Oracle customers for their business applications. She is a frequent speaker at conferences and publishes papers in magazines. She holds a master degree of computer science from University of Southern California.


Abstract


As people build information supply chains over the Internet, a huge amount of business data is stored into databases. The high volume of data and the high speed of data ingestion challenges traditional database management. Database administrators (DBAs) frequently run SQL scripts to generate reports from database catalog views, data dictionary views, and tables. To transform this dizzing array of data into useful information, we need reports to be straightforward, and graphics are often easier to understand than pages full of text.

Scalable Vector Graphics (SVG), an XML-based graphics standard recommended by World Wide Web Consortium (W3C), can help us achieve this and more. In this paper, we present a detailed study of an approach leveraging native XML database functionality, XSLT and SVG to help DBAs visualize the data and its metadata for database management.

In the example application, we use the native XML functionality to deliver XML content, use XSLT to generate SVG and manage SVG code fragments by storing them in native XML datatypes, which can be directly accessed through the database's HTTP server. The application also uses the SVG DOM APIs to build an interactive interface to provide on demand visualization for better decision support.


Table of Contents


1. Introduction
2. Consolidate Data Sources in XML
3. Building Reusable XSL Stylesheets
4. Publishing SVG Report
5. Using SVG DOM vs. XSLT
6. Conclusion
Reference

1. Introduction

As people build information supply chains over the Internet, a huge amount of business data is stored into databases. When dealing with high volume of data and high speed of data ingestion, the key to success database management is the timely dissemination of relevant information to the database administrators (DBAs)

SQL scripts are widely used by the database administrators (DBAs) to generate various reports from database catalog views, data dictionary views and tables. However, to effectively handle a dizzying array of reports, we really need the reports to be much more straightforward, preferably to be a high quality graphic rather than a page full of text.

Scalable Vector Graphics (SVG)[SVG01] is an XML-based graphics standard recommended by World Wide Web Consortium (W3C), which helps us achieve this and more. First, instead of representing graphics using a matrix of color points like the raster graphics, the vector-based SVG graphics greatly improves the image quality of the graphics. Second, as a text-based graphics, SVG is much smaller and easier to create and maintain than the raster graphics in binary format. Third, because SVG is an XML-based standard, it can leverage a variety of XML standards-based functionality to generate and process the content. Moreover, with its animation features and SVG DOM interfaces, we can code intelligence into SVG graphical applications.

Using an XML database such as Oracle 9.2, we can leverage a set of high-performance native XML to easily combine data or metadata of database into XML documents and process them to produce SVG graphics. The process is simplified because all these operations can be handled within SQL or PL/SQL commands.

In the following sections, we will discuss how to use this native XML functionality to build an SVG report application for database management and how to make building a graphics SVG report as simple as authoring an XML page and publishing the SVG reports as simple as copying XML files:

Finally, we conclude by discussing some further development strategies for using SVG in database management.

2. Consolidate Data Sources in XML

In the past, it has proven to be difficult to relate information from the different sources in a meaningful way, especially when application logic needs to be incorporated into a meaningful dataset when generating graphics. Since XML is an excellent text-based format to represent comprehensive content, it is well suited to prepare the data source for SVG generation.

It is now possible to consolidate the data sources by generating XML documents from databases. We can also perform the XML processing on the content, including the XSLT transformation, the XML element concatenations and aggregations, with the SQL and PL/SQL statements. Let us look at the following example. While it is using an Oracle database, these techniques are relevant for others.

In database management, one of the major responsibilities for the administrators is to ensure that the database resources are available and optimized for use. To make sure proper resources are allocated for parsing the SQL statements, PL/SQL procedures and data dictionary information, the status of the Shared Pool , which belongs to the Oracle database System Global Area (SGA) is on DBAs' radar everyday. Here is the SQL script normally used by DBAs to query the status:


SQL> col value form 999,999,999,999 heading "Shared Pool Size"
SQL> col bytes form 999,999,999,999 heading "Free Bytes"
SQL> SELECT pool, to_number(v$parameter.value) value, v$sgastat.bytes
2 FROM v$sgastat, v$parameter
3 WHERE v$sgastat.name = 'free memory' AND pool='shared pool'
4 AND v$parameter.name = 'shared_pool_size';
POOL Shared    Pool Size Free            Bytes
------------ ---------------- ----------------
shared pool        50,331,648       36,892,064
 

If there is shortage of resources for the Shared Pool, immediate action is needed for a new resource allocation. Too much free memory is also a waste of the resources, therefore, DBAs should modify the allocation and use the resources for something else.

To collect the data for SVG creation, we modify the previous SQL query by adding necessary information for graphic generation using SQL/XML[SQLX02] functions:

SQL> column status format a80
SQL> SELECT
 2 XMLElement("result",
 3XMLAttributes('SGA Shared Pool Status Report' AS "title"), 
  4 XMLElement("group",
 5 XMLAttributes(to_number(b.value)/1000000 AS "total",
 6 'M' AS "unit"),
 7 XMLForest(
 8 XMLElement("item",
 9 XMLAttributes(a.bytes/1000000 AS "value",
 10 'Free Space' AS "name")) AS "Free_Space",
 11   XMLElement("item",
 12     XMLAttributes((to_number(b.value)- a.bytes)/1000000 AS "value",
 13         'Used Space ' AS "name")) AS "Used_Space"))) as Status
 14 FROM v$sgastat a, v$parameter b
 15 WHERE a.name = 'free memory' AND pool='shared pool'
 16 AND b.name = 'shared_pool_size';
STATUS
-----------------------------------------------------------------------
<result title="SGA Shared Pool Status Report"><group total="50.331648" unit="M">
<item value="13.760752" name="Free Space"></item>
<item value="36.570896" name="Used Space "></item></group></result>

The SQL/XML functions are defined in the ISO SQL/XML standard. The syntax of these functions is self-explanatory. Using these functions, you can create XML elements ( XMLElement ), XML Attributes ( XMLAttributes ), XML Fragments ( XMLForest ), aggregate the XML elements ( XMLAGG ), and so on.

In the Oracle database, both the XML Elements and XML Fragments created by the SQL/XML functions are returned as XMLType, which is the native XML object type in Oracle9i database. Just like other database types, such as the DATE datatype, the XMLType contains a set of member functions for XML storage and content processing.

For example, to make sure the XML data sources can be used by the XSLT transformations, you can use the XMLType. isSchemaValid() function to validate them against the XML Schemas:

SQL> 
  SELECT
  2 XMLElement("result",
  3 XMLAttributes('SGA Shared Pool Status Report' AS "title1"),
  4 XMLElement("group",
  5 XMLAttributes(to_number(b.value)/1000000 AS "total",
  6 'M' AS "unit"),
  7 XMLForest(
  8 XMLElement("item",
  9 XMLAttributes(a.bytes/1000000 AS "value",
10'Free Space' AS "name")) AS "Free_Space",
11  XMLElement("item",
12 XMLAttributes((to_number(b.value)- a.bytes)/1000000 AS "value",
13 
  'Used Space ' AS "name"))
14  AS "Used_Space"))
15  ).isSchemaValid('/public/SVG/xsd/pie.xsd','result') as "validation result"
16 FROM v$sgastat a, v$parameter b
17 WHERE a.name = 'free memory' AND pool='shared pool'
18 AND b.name = 'shared_pool_size';
validation  result
----------------- 
  1
  

In the above example, we specify the XML Schema file(" /public/SVG/xsd/pie.xsd ") and the root element(" result ") of the XML document. The pie.xsd is the XML Schema associated with XSL Stylesheets to produce pie charts. If the validation returns "1", the XML is ready for the XSLT transformation. Otherwise, we need to go back can check if there is anything missing in the XML document.

You may be curious about the URL used for the XML Schema document. XML databases provide the capability to link to the content both internally and externally. In case of an Oracle XML database, this is expressed as a DBUri pointing to the resources in Oracle XML database repository (XML DB Repository). We use this repository to store the resources for the application, including the XML Schema files, the XSL Stylesheets and the generated SVG reports. Further discussion will be given in the later sections when storing XSL Stylesheets and publishing SVG graphics. Now, you just need to know that we can refer to the XML Schema files stored in the repository using a DBURi when validating the XML data sources in the database.

To create pie charts from the XML data source, we apply the XSLT transformation using an XSLT PL/SQL function in the SQL statement:

SQL> set autoprint on
SQL> var result CLOB;
SQL> DECLARE
   2 res BOOLEAN;
   3 BEGIN
   4 dbms_lob.createtemporary(:result, true, dbms_lob.session);
   5 SELECT
   6 XSLTransform(
   7 XMLElement("result",
   8 XMLAttributes('SGA Shared Pool Status Report' AS "title1"),
   9 XMLElement("group",
  10 XMLAttributes(to_number(b.value)/1000000 AS "total",
  11 'M' AS "unit"),
  12 XMLForest(
  13 XMLElement("item",
  14 XMLAttributes(a.bytes/1000000 AS "value",
  15 'Free Space' AS "name")) AS "Free_Space",
  16 XMLElement("item",
  17 XMLAttributes((to_number(b.value)- a.bytes)/1000000 AS "value",
  18 'Used Space ' AS "name"))
  19 AS "Used_Space"))
  20 ).getClobVal(),'/public/SVG/xsl/pie_3d.xsl') into :result
  21 FROM v$sgastat a, v$parameter b
  22 WHERE a.name = 'free memory' AND pool='shared pool'
  23 AND b.name = 'shared_pool_size';
  24 END;
  25/
  

The XML output of the transformation is a Pie Chart in 3D as shown in Figure-1:

fig1.svg

Figure 1: Shared Pool Status report in 3D Pie

In summary, by augmenting standard SQL with XML functions, one can consolidate, validate and transform data sources to create SVG files.

3. Building Reusable XSL Stylesheets

As there is a set of standard graphic formats for business reports, it would be useful to build reusable XSL Stylesheets to create corresponding SVG graphics.

Take the above 3D Pie chart as an example, we can build a XSL Stylesheet (in Listing-1 (pie_3d.xsl) ), which contains the following main sections: the Java extension declarations, the <xsl:output/> definitions, the XSL template calls and a set of templates for graphics drawings.

First, the Java extension functions define the Java packages needed by the mathematical calculations for generating graphics. We use three Java packages: the java.awt.Color , the java.lang.Integer and the java.lang.Math package defined as follows:

<xsl:stylesheet 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" 
xmlns:Color="http://www.oracle.com/XSL/Transform/java/java.awt.Color"
  xmlns:Integer="http://www.oracle.com/XSL/Transform/java/java.lang.Integer"
 xmlns:JavaMath="http://www.oracle.com/XSL/Transform/java/java.lang.Math" 
 extension-element-prefixes="JavaMath" 
 exclude-result-prefixes="Color Integer JavaMath">

Second, we have to specify the SVG DTD definitions and set the proper MIME media type to be "image/svg+xml" for the XSL output document:

<xsl:output standalone="yes"
doctype-public="-//W3C//DTD SVG 20001102//EN"
 doctype-system="http://www.w3.org/TR/2000/CR-SVG-20001102/DTD/svg-20001102.dtd" 
    media-type="image/svg+xml"/>

Third, most of the XSL templates for SVG graphics creation have a set of parameters. Before calling these templates, the parameter calculations based on the XML data source are needed. The templates calls are also needed to be in the correct order as this affects the SVG graphics rendering process:

<xsl:template match="/">
   <svg xml:space="preserve" width="350"  height="400">
 <desc>
     <xsl:value-of select="result/@title"/>
 </desc>
 <xsl:variable name="total">
 <xsl:value-of select="result/group/@total"/>
 </xsl:variable>
 <xsl:variable name="total_num">
 <xsl:value-of select="count(result/group/*)"/>
 </xsl:variable>
 <g style="stroke:#000000;stroke-width:1;font-family:Arial;font-size:16">
 <xsl:choose>
 <xsl:when test="number($total_num)!=0">
 <xsl:for-each select="result/group/item">
 <xsl:call-template name="Draw3DPie">
 <xsl:with-param name="index" select="position()"/>
 <xsl:with-param name="sec_num" select="./@value"/>
 <xsl:with-param name="total_num"  select="$total"/>
 <xsl:with-param name="runningTotal"
 select="sum(preceding-sibling::*/@value)"/>
 <xsl:with-param name="total_sec"  select="$total_num"/>
 </xsl:call-template>
 <xsl:apply-templates select="." mode="legend">
 <xsl:with-param name="index" select="position()"/>
 <xsl:with-param name="total_num" select="$total"/>
 <xsl:with-param name="sec_num" select="./@value"/>
 <xsl:with-param name="y-legend-offset" select="50 + (position() * 20)"/>
 <xsl:with-param name="total_sec" select="$total_num"/>
 </xsl:apply-templates>
 </xsl:for-each>
 <text x="-50" y="250">
 <xsl:value-of select="result/@title"/>
 </text>
 </xsl:when>
 <xsl:otherwise>
 <text x="10" y="120">Data is not available or in correct format.</text>
 </xsl:otherwise>
 </xsl:choose>
 </g>
 </svg>
 </xsl:template>

Finally, within the XSL templates, there are a set of calculations and SVG elements created with rendering and styling options. Using different graphics techniques, you can always create various graphics.

fig2.svg

Figure 2: 2D Pie Chart-1

fig3.svg

Figure 3: 2D Pie Chart-2

Figure-2 and Figure-3 show the results of two 2-dimensional pie charts using different XSL Stylesheets.

Figure-4 shows that the XSL files are stored as files in the XML DB repository and you can access these files through the WebDAV folder of the XML DB repository.

fig4.gif

Figure 4: WebDAV Folders for Oracle XML Database Repository

With the WebDAV folders, you can create/copy/delete the XSL files just as working on another folder on your file system. You can also open and edit the files with any application supporting WebDAV standard.

4. Publishing SVG Report

As a result of the XSLT transformation, the SVG documents are created. To publish them, we can create an XML database resource using the resource management PL/SQL packages. In Oracle database, this package is called the DBMS_XDB PL/SQL package. For the previous example, we can use the following PL/SQL code to create an new " /public/SVG/shared_pool_pie3d.svg " resource file:

DECLARE
  res BOOLEAN;	
BEGIN 
res := DBMS_XDB.CreateResource('/public/ shared_pool_pie3d.svg',
 '&&svg_result');
END;

After the transcaton is commited, we see the generated SVG files in the WebDAV folder of XML DB repository as shown in Figure-5:

fig5.gif

Figure 5: WebDAV folder for publishing SVG Reports

Other XML database may support different or additional protocols for publishing. In Oracle's case, we could have also used the HTTP or FTP protocol interfaces to access the files and the source control service. With these protocol interfaces, the publishing process is just as simple as creating and copying files.

5. Using SVG DOM vs. XSLT

In many cases, the discussed XSLT-SVG approach is enough for building SVG report applications. However, if we need to provide an interactive interface for graphics customization or on-demand visualization, we found it useful to use the SVG DOM APIs.

It is not a good decision to build SVG from scratch using the SVG DOM APIs. The reason is that the operations are complicated due to the fine-granularity of the DOM. For example, when building the bar chart graphics to show the status of database tablespaces, we use the following XSL template calls to draw the coordinate axis:


<xsl:template name="drawYAxis">
   <xsl:param name="Ylength"/>
   <xsl:param name="Xlength"/>
   <xsl:param name="x"/>
   <xsl:param name="y"/>
   <xsl:param name="depth"/>
   <xsl:choose>
     <xsl:when test="number($depth) &gt; 1">
       <xsl:call-template name="drawYAxis">
         <xsl:with-param name="Ylength" select="number($Ylength) div 2"/>
         <xsl:with-param name="y" select="$y"/>
         <xsl:with-param name="x" select="$x"/>
         <xsl:with-param name="depth" select="number($depth)-1"/>
         <xsl:with-param name="Xlength" select="$Xlength"/>
       </xsl:call-template>
       <xsl:call-template name="drawYAxis">
         <xsl:with-param name="Ylength" select="number($Ylength) div 2"/>
          <xsl:with-param name="y" select="$y+number($Ylength) div 2"/>
          <xsl:with-param name="x" select="$x"/>
          <xsl:with-param name="Xlength" select="$Xlength"/>
          <xsl:with-param name="depth" select="number($depth)-1"/>
       </xsl:call-template>
     </xsl:when>
    <xsl:otherwise>
     <line x1="{$x}" y1="{$y}" x2="{number($x)+number($Xlength)}" y2="{$y}">
      </line>
    </xsl:otherwise>
   </xsl:choose>
  </xsl:template>

It is a recursive calls to the XSL template, which control the refinement of the axis drawing.

fig7.svg

Figure 6: Recursive Depth=3 for the "drawYAxis" Template

fig8.svg

Figure 7: Recursive Depth=5 for the "drawYAxis" Template

Figure-6 and Figure-7 shows the bar chart graphics with different recursive depth. So, by simply updating the recursion parameter, the details of the coordinate axis is updated. If using the DOM APIs, the process will be much more complex.

While XSLT is a powerful programming language to build SVG efficiently, if you need intelligent graphics customization, such as even-based animation, the XSLT-SVG approach is expensive. This is because the process requires all the content to be re-generated. In such cases, you can use the SVG DOM APIs to make direct changes to the SVG documents.

fig9.svg

Figure 8: Bar Chart with Live Help and Time Display

Figure-8 shows that in the graphics with the current time information. If the user moves the mouse over the names of group categories, such as the "Tablespace size" category, the help notes will be shown. These features help DBAs analyze the reports while not overloading the server by re-rendering the graphics.

6. Conclusion

In order to keep pace with today's demand for database management, we discussed how to use native XML database functionality to create high quality business graphics in SVG. The approach is useful because:

With all of these features, it is easy to see how they can help DBAs and also the database developers quickly get the information they need to keep their business running optimally.

Reference

XHTML rendition created by gcapaper Web Publisher v2.0, © 2001-3 Schema Software Inc.