Sharing structured data

XML Magazine

Subscribe to XML Magazine: eMailAlertsEmail Alerts newslettersWeekly Newsletters
Get XML Magazine: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


XML Authors: Peter Silva, Stackify Blog, Mamoon Yunus, Progress Blog, XebiaLabs Blog

Related Topics: SOA & WOA Magazine, Java Developer Magazine, Software Configuration Management, ANSI SQL Advanced Hierarchical Processing

Advanced Hierarchical Processing: Article

Naturally Increasing Data Value with Hierarchical Structures

Why hierarchical structures are useful

Hierarchical structures have an inherent ability for significant data value increases beyond the data collected.  This will be shown to exist in hierarchical structures and even more powerfully in their natural hierarchical processing capabilities. These will demonstrate flexible and efficient ways to increase data value automatically and will be discussed in this article. SQL will be used to perform a wide range of hierarchical processing operations that easily demonstrate these increasing data value capabilities.

Basic Hierarchical Data Modeling
The SQL view definition in Figure 1 below uses a sequence of standard SQL LEFT Outer Joins to model the shown hierarchical structure. This hierarchical view is modeled at the node level to define basic physical structures like IMS or XML. It defines logical hierarchical structures using flat data like relational. The LEFT Outer Join hierarchically preserves the left data argument over the right argument. This allows the left data argument to exist when there is no matching right data argument. These LEFT Outer Joins can be strung together with the ON clause to define and build full hierarchical multiple path structures. The ON clause was also introduced in the ANSI SQL-92 Outer Join to offer more local control over the WHERE clause for specifying join criteria. Notice in Figure 1 below that the ON clause is specified at each specific join point to control its specific join operation. All of these capabilities allow the LEFT Outer Join syntax to exactly model hierarchical structures unambiguously. This was not previously possible using a single WHERE clause for join control. The WHERE clause is still used to specify global data filtering described later.

Basic Hierarchical Data Modeling

CREATE VIEW RDB AS
SELECT * FROM R
LEFT JOIN D ON R.r=D.d    /* Extend path from node R to node D */
LEFT JOIN B ON R.r=B.b    /* Start new Path from node R to node B */

Basic Structure

   R

 /    \

D     B

Figure 1: SQL Hierarchical View

The corresponding hierarchical semantics to the LEFT Outer Join data modeling syntax shown in Figure 1 defines exactly how this data structure definition operates hierarchically. It performs hierarchical data preservation when processed directly by the ANSI SQL processor. As shown in Figure 1 above, this hierarchical data definition can be specified in an SQL view.  The R, D, and B items in the hierarchical view structure in Figure 1 above are relational table names which also represent nodes in the hierarchical structure.

The hierarchical structure defined in Figure 1 above follows basic hierarchical principles supplied by the SQL Outer Join semantics. Nodes D and B can not exist without the higher level R node. But node R can exist without nodes D and B. Node D can exist without node B and visa versa because hierarchical pathways are independent of each other.

Hierarchical Structures Contain More Data Value than Data Stored
As a hierarchical structure naturally grows top to bottom and left to right as in Figure 1 above, their data value increases nonlinearly as new nodes are added. This is because natural data reuse is used in a hierarchical pyramid fashion. Both linear single path data stacking and the more powerful nonlinear multiple path stacking significantly increases the value of the data.

With linear path data stacking, the multiple data occurrences of the lower level data node are shared across the current parent data occurrence. With nonlinear path data stacking, all of the current parent data node's data occurrence lower level pathways are included in the data sharing. All of these different natural combinations of related data enable hierarchical structures to contain more data value than was initially captured. This automatic data value accrual as new data is added is a valuable capability not being utilized today. This is a good reason to use hierarchical data structures when possible. SQL hierarchical processing can automatically utilize this goldmine of accrued information to perform more powerful queries described later.

Static Hierarchical Structure Combining
Basic structure views like the one in Figure 1 above can also be joined together hierarchically to create larger more powerful views that combine hierarchical structures. This is also performed by using the LEFT Outer Join and ON clause as shown in Figure 2 below. The only difference is that the view structures are being joined instead of isolated nodes. In this example, two additional basic views, IMS and XYZ, are used. They represent a physical IMS and XML data source modeled in the same way as the logical RDB view was defined in Figure 1 by using the LEFT Outer Join. In the IMS view, Segments I, M, and S represent nodes. In the XML view, Elements X, Y, and Z represent nodes. These common nodes allow seamless hierarchical data structure integration. Not being relational data, the non relational views contain additional specific physical meta information needed for their specific physical processing. They will also require an external process for retrieving their non relational data into rowsets when needed. This process will be covered further later.

Logical Data Structure

CREATE VIEW TestView AS
SELECT * FROM RDB
LEFT JOIN IMS ON B.b=I.i
LEFT JOIN XYZ ON B.b=X.x

Full Heterogeneous Structure

     R

   /    \

  D     B

        /    \

      I        X

    /   \      /   \

  M   S    Y   Z

Figure 2: Logical data view

The combined view named TestView in Figure 2 consists of views RDB, IMS and XYZ. This makes TestView a heterogeneous view consisting of multiple data structure types defined seamlessly to ANSI SQL by using the LEFT Outer Join data modeling operation described above. When joining physical view structures together, the result is a logical structure. Logical structures can be joined with other logical or physical structures. Logical structures can also be directly processed by the ANSI SQL processor. This is because the logical view defined by TestView in Figure 2  above with its corresponding relational rowset hierarchically maps to the combined and expanded LEFT Outer Join views RDB, IMS and XYZ.  This supplies the natural hierarchical data modeling syntax and semantics needed for SQL to automatically perform hierarchically on the now homogeneous flattened rowset.

While the hierarchical data has been flattened in the rowset, it still automatically represents hierarchically preserved multiple variable length pathways using null filled data. The null data is used to fill out the variable length paths keeping the data aligned properly in the rowset.   This allows hierarchical structures to be still operated on hierarchically by SQL.

Logical View Joining Further Increases Data Value
The joining of structures shown in Figure 2 above continues to significantly increase the value of data being processed as described previously. But now by joining larger more meaningful logical structures, the level of data value increase is raised to a new level. In addition, these views are free to be joined in multiple ways each creating new logical different views that contain more data value when processed. These views are temporarily materialized when processed, so they are not taking up space when not used and avoid the replicated data problems.

Increasing Data Value with Multipath Query Data Qualification
Specifying multiple paths on a data qualification query requires nonlinear processing logic. These multipath queries using the SQL WHERE operation are incredibly powerful and extremely complex to process.  They correlate references across pathways that interact with each other. This processing is hidden by SQL's automatic and inherent hierarchical structured data processing capability.

There are two different usages of a SQL multipath query data qualification. These are: 1) Selecting data from one pathway of the hierarchical data structure based on data from another pathway or: 2) WHERE clause searches comparing data on or across multiple pathways. These multipath queries use the naturally occurring structure semantics that exists between the concurrently processed pathways to solve this query. This dynamically increases the value of the data and significantly increases the number of different queries possible. This further increase the value of the data beyond the static hierarchical view data value increases described earlier. This enables a new level of powerful hierarchical processing available to uncover new more deeper meaning in the data without requiring additional query syntax or knowledge of the structure for the user. Global view capability magnifies the usefulness of these multipath processing capabilities further by making unlimited combinations of pathways more available and easier to specify because these large views have no overhead.

Dynamic Hierarchical Structure Combining
It is important to point out that SQL hierarchical processing can perform all of its hierarchical processing capabilities dynamically. This means that SQL can dynamically and hierarchically join hierarchical structures in an ad hoc or interactive way (instead of in a static view). This is shown in Figure 3 below where views RDB and IMS are dynamically joined at runtime to combine structures. This also increases data value with the joining of views, but in this example it is dynamically performed by interactively querying which enables discovery and drill down operations. This makes these interactive operations even more powerful with the dynamic data value increase.

SELECT B.b, M.m, S.s
FROM RDB
LEFT JOIN IMS ON B.b=I.i

Result Structure

     B

   /    \

 M     S

Figure 3: Dynamic Structure Joining

Conclusion
This article has described how hierarchical data structures can be very powerful and useful for storing data and in the processing of this data by dynamically increasing the value of the stored data. Statically the stored data value increases in use and information value as data is added and then dynamically as the data value is increased when processing the query. The query controls how the data value is increased in order to answer the query. This automatic multilevel data reuse and dynamic combining of the hierarchical data structures makes hierarchical structures a very useful data storage structure that automatically leverages its increasing data value.

More Stories By Michael M David

Michael M. David is founder and CTO of Advanced Data Access Technologies, Inc. He has been a staff scientist and lead XML architect for NCR/Teradata and their representative to the SQLX Group. He has researched, designed and developed commercial query languages for heterogeneous hierarchical and relational databases for over twenty years. He has authored the book "Advanced ANSI SQL Data Modeling and Structure Processing" Published by Artech House Publishers and many papers and articles on database topics. His research and findings have shown that Hierarchical Data Processing is a subset of Relational Processing and how to utilize this advanced inherent capability in ANSI SQL. Additionally, his research has shown that advanced multipath (LCA) processing is also naturally supported and performed automatically in ANSI SQL, and advanced hierarchical processing operations are also possible. These advanced capabilities can be performed and explained in the ANSI SQL Transparent XML Hierarchical Processor at his site at: www.adatinc.com/demo.html.