VDM Public

Public information; No registration required

VDMGEN Framework

VDMGEN is a simple, flexible and expandable framework for building and maintaining SQL/DDL/DML or PHP code libraries. It is perfectly suited for automating the maintenance of MQTs and other derived data in the database, or for application data access and other layered components. Because of its simplicity and openness it can be expanded to other uses as well.

VDM Access: 
VDM Public

Flow Problems and Aging

Whether it is accounts with aging balances, or inventory aging, the patterns are the same. This example illustrates how by using cumulative sums of sales/receipts, we can compute a baseline result set from which aging queries can be answered.

VDM Access: 
VDM Public

Last Activation Example (LAST_VALUE)

Assume a series of account transactions. We want for every transaction of each account to display the latest activation for the transaction. We use the LAST_VALUE function. The DDL file defines the tables and loads some data used by the SQL script.

VDM Access: 
VDM Public

OLAP Function comparison scripts

These scripts compare the outcome of OLAP LAG, LAST_VALUE and FIRST_VALUE functions depending on the parameters used and the existence of nulls. Modify the common table expression values to test your own variants.

VDM Access: 
VDM Public

OLAP Function Examples

Various examples of OLAP functions are discussed below.

VDM Access: 
VDM Public

Simple Hierarchies - Example Scripts

The examples in the script below show the simple ancestor-descendent traversal queries such as finding the ancestors or descendents of a node. It also shows the rollup of transactions by the account hierarchy. The last query does the same thing but instead of explicitly doing the between-join in the query, it uses a view that hides this complexity.

Notice that the posting nodes of the account hierarchy have a PostSign attribute that determines whether to negate the transaction amount in order to make expenses always negative and revenue always positive.

VDM Access: 
VDM Public

Hierarchies - Topological Ordering Topics (Nested Sets) - Examples

These example scripts describe the technique of topological ordering of hierarchies. They start by setting up a working set of tables and continue with a working example of the enumeration algorithm followed by use cases of ancestor-descendent queries and computational hierarchies.

This material is referenced by presentation E12 of the IDUG EMEA 2010.

Take a look at the The Relational Taboo page for the original article circa 1992. Also refer to Joe Celko's books for the basics of the technique.

VDM Access: 
VDM Public

Computational Hierarchies - example scripts

The following two scripts illustrate the use of topologically ordered hierarchies to rollup account details. At each level, there is an indicator that determines the sign of the rollup to the next level. A view is defined that simple links any ancestor-descendent pair in the account hiearchy and determines if the descendent needs to be negated or not.

The first file is the DDL and it also loads some data.

The second file progressively demonstrates how the method works.

VDM Access: 
VDM Public

Hierarchies - Topological Ordering - Table Setup Script

The enumeration method consists of a depth first left-to-right traversal of the hierarchy, assigning the left number when first visiting a node when last visiting it after all descendent nodes have been enumerated. To achieve this, imagine a thread created starting from the left of the top node, progressing through the left side of the first descendent, wrapping around the first leaf through its right side, up the right of the parent and so on...

Now imagine this thread streatched out. Each node appears twice. Once attached on the left, and once on the right. The objective is to order the nodes of the hierarchy in exactly the order visited by the thread, then enumerate the list, and pivot the left and right numbers against each node.

VDM Access: 
VDM Public

DW Assessment Deliverables

The assessment has three major deliverables:

VDM Access: 
VDM Public

Pages