MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB

Multifaceted Hierarchy

The Data Shaping Service for OLE DB can be used to create a multifaceted hierarchical structure using the SHAPE command. In this example, a hierarchy is created within a grouped hierarchy, forming the parent with two child rowsets, a group detail, and a parameterized hierarchy.

Creating a Relation-based Hierarchy with Two Children

The first SELECT in the SHAPE command defines one of the child rowsets. This child rowset is named rsDetail. The COMPUTE clause indicates that the parent rowset is to be fabricated from the rsDetail rowset. RsDetail will be partitioned on the cust_id column, and the parent rowset will contain one row for each distinct value of cust_id in rsDetail.

In addition to the rsDetail and cust_id columns, the parent rowset will contain three other columns. The first is an aggregate column produced by the ANY (rsDetail.cust_name) function. The second is a chapter column named chpContacts. The chpContacts chapter column relates the parent rowset to the rsContacts rowset defined by the second SELECT statement in the SHAPE command. Because the RELATE clause relates the parent cust_id colulmn to parameter 0, the rsContacts rowset is parameterized.

The third additional column is the result of the Count (rsContacts.contact_id) aggregate.

Note   Because the Count function argument is rsContacts.contact_id (not just rsContacts), rows in the chapter where contact_id is NULL will not be included in the count.

Creating the Parameterized Hierarchy

The inner, or second, SELECT statement produces a parameterized hierarchy of contacts retrieved from the Contacts table in the Sales database. Contacts.* is the child of rsContacts. Once the parent rows have been retrieved, an index is built that establishes the relationship between parent and child. Each time action is requested on the parent row, a query based on the parameters is executed for the child or related rows. This hierarchy's only relationship to rsDetail is that of a common parent.

Shaping the Customer Group Hierarchy

After each of the parent hierarchies has been established, rsDetail and rsContacts are grouped under cust_id by the outer SHAPE … BY command. Again, the relation is made by creating the index or filter. Because the group rowset was created apart from the data store, it is not updatable.

SHAPE 
   {select customer.*, orders.* from Customers, Orders 
         where customer.cust_id = orders.cust_id} 
      AS rsDetail 
         COMPUTE rsDetail, 
         ANY(rsDetail.cust_name) 
      AS cust_name, 
   ({select * from Contacts where cust_id = ?}
          RELATE cust_id TO PARAMETER 0) 
      AS rsContacts,

         COUNT(rsContacts.contact_id) 
      AS [COUNT of Contacts]
   BY cust_id 
   

This results in the following aggregations within this multifaceted hierarchy: