Developing Effective Decision Support Objects (DSO) Solutions with Microsoft SQL Server 2000 Analysis Services
Summary: This paper describes how to effectively develop custom applications using Decision Support Objects (DSO) in Microsoft SQL Server 2000 Analysis Services by providing practical advice in real-world situations. (9 printed pages)
Table of Contents
Microsoft® SQL Server™ 2000 Analysis Services provides a rich set of tools that simplify administering and maintaining Analysis servers. By simplifying the execution of many complex tasks, wizards and other tools make using Analysis Services easier for administrators. For instance, with the Storage Design Wizard, aggregation design, a potentially complex task in other OLAP products, is as simple as a few mouse clicks.
As a tradeoff for this ease of use, however, the user is isolated from direct access to the more complex features of Analysis Services. For example, individual aggregations cannot be directly modified using the administrative tools supplied with Analysis Services.
The complex and powerful underlying features of Analysis Services can be accessed through the Decision Support Objects (DSO) library, which supports a robust COM object model providing complete control of all meta data in Analysis Services. This article provides a better understanding of the basics of using DSO within a custom application.
This article assumes the reader has a basic knowledge of SQL Server 2000 Analysis Services, an understanding of object-oriented programming concepts, and is familiar with DSO. Examples are presented in Microsoft Visual Basic® and Visual Basic Scripting Edition (VBScript).
DSO employs a strictly enforced object hierarchy which is managed by the Server object. This enforced hierarchy is represented by the collections used to store the various objects in the DSO object model; each collection serves as an object factory, creating and managing only objects that are allowed in that position in the hierarchy. The following diagram shows the general hierarchy of the DSO object model represented primarily by the collections that define the hierarchy.
Figure 1. General hierarchy of the DSO object model. (Click figure to see larger image.)
In the previous diagram, each collection is represented by the name of the collection (in bold), as well as by the class type (the value of the ContainedClassType property of the collection) of objects contained by the collection.
The diagram also represents major and minor objects in the DSO object model. Major objects are shaded; minor objects are non-shaded. A brief discussion of the importance of major and minor objects is provided later.
In the entire object model, only three objects are not referenced from a collection. The Server object is the topmost object in the hierarchy and is therefore never in a collection. The CubeAnalyzer and PartitionAnalyzer objects, represented respectively by objects with a ClassType of clsCubeAnalyzer and clsPartitionAnalyzer, have but one reference per cube or partition, and as such, it would be inappropriate to store them in a collection.
Understanding how the collections enforce the hierarchy is important for writing efficient DSO code. All objects below the Server object must be instantiated by or referenced from the collection that supports the object. For example, the following code cannot be used to create a new Database object.
Dim dsoServer As New DSO.Server Dim dsoDatabase As New DSO.MDStore dsoServer.Connect "LocalHost" With dsoDatabase .ClassType = clsDatabase .SubClassType = sbclsRegular .Name = "TestDB" End With dsoServer.MDStores.Add dsoDatabase, "Test"
To create a new Database object, the following code should be used. Note that the code instantiates a new Database object by using the AddNew method of the MDStores collection of the Server object. The Server object is created using the New keyword—the only object in the DSO object model that should be treated as a directly creatable object.
Dim dsoServer As New DSO.Server Dim dsoDatabase As DSO.MDStore dsoServer.Connect "LocalHost" Set dsoDatabase = dsoServer.MDStores.AddNew("Test", sbclsRegular)
Although each object in DSO implements its own interface, many objects also implement a common interface (and some implement more than one common interface). For example, all dimension objects, such as database dimensions and cube dimensions, implement the Dimension interface.
The most important interface implemented in the object hierarchy is the MDStore interface; it represents the "big four" in the object model—databases, cubes, partitions, and aggregations. Because the MDStore interface is used frequently in custom applications, it is recommended that you be familiar with its implementation for each of these objects.
It is highly recommended that you use only the interfaces explicitly documented in the Decision Support Objects Programmer's Reference in SQL Server Books Online. Other commonly implemented interfaces, such as ICommon, exist, but are for internal use only and should not be employed. If an object implements one of the interfaces detailed in the Interfaces section of the Decision Support Objects Programmer's Reference, use the implemented interface instead of the default interface of the object, with one notable exception. The Database object is the only object in the hierarchy that raises events; as such, the Database interface must be used if these events are to be received and handled.
Use the ClassType and SubClassType properties to become more familiar with the behavioral differences between similar objects. Objects that use commonly implemented interfaces often do not implement every property or method, or change the behavior of the property; using these two properties is the only way to confirm the behavior of the implemented interface for a given DSO object. The Decision Support Objects Programmer's Reference supplies an access cross-reference table for all of the properties of each interface, detailed in the Interfaces section. These tables should be on hand at all times during development; they can save you considerable head-scratching when you try to determine object behavior.
The Object Factory design pattern utilized by DSO is quite beneficial when you use the DSO object model in a scripting language. VBScript, for example, has only one data type, the Variant, and cannot directly use virtual function table (vtable) references to provide early binding. For example, the following statement, although valid in Visual Basic, will not work in VBScript.
Dim dsoDatabase As DSO.MDStore
When you use the collections to create the object references, the correct common interface is supplied for the object reference without the need for early binding. A few minor variations in code between Visual Basic and VBScript are present, but the basic coding technique remains the same when you use DSO for either language. The following example demonstrates the variations in code required for a scripting language such as VBScript.
Dim dsoServer Dim dsoDatabase Set dsoServer = CreateObject("DSO.Server") dsoServer.Connect "LocalHost" Set dsoDatabase = dsoServer.MDStores.AddNew("Test", sbclsRegular)
Other than the lack of variable typing and the required use of the CreateObject function, the code for creating a new Database object in the DSO object model varies little between Visual Basic and VBScript.
DSO supplies a very rigidly defined hierarchy, using collections to enforce the relationships in the DSO object model. The OlapCollection object, instantiated by all collections in the DSO object model, is much more robust than the standard Visual Basic for Applications (VBA) Collection object. To make it easier to work with such a rigidly defined hierarchy, the OlapCollection object includes several new features. For example, the Find method is invaluable when attempting to negotiate the DSO object model. Most collections do not allow you to determine the existence of an object within the collection before referencing; error handling is often used to trap the resulting error if an invalid object reference is requested from a collection. However, the OlapCollection object does not raise an error if you attempt to refer to an object not present in the collection. This behavior is by design to assist in using DSO with languages, such as VBScript, that do not have strong error handling features. If the object does not exist within the collection, the returned object contains Nothing and no error is raised. The Find method, provided with the key of an item potentially in the collection, returns a Boolean result that indicates whether an item with the specified key exists in the collection. The Find method should be used instead of the traditional error handling methods for dealing with validation of object references in a collection.
The following information addresses the practical use of DSO objects in a custom application, including ways to speed performance and reduce confusion when working with this complex and robust object model.
Because the behavior and capability of each DSO object can depend on other objects in the strict hierarchy of the DSO object model, DSO objects should be created in a specific order. Knowing the creation order of DSO objects can save you trouble later when you attempt to create a script for creating or maintaining meta data on an Analysis server. In some cases, fully creating a DSO object will be a two-step procedure. For example, when creating a DbLevel object, the OrderingMemberProperty property cannot be set to the name of a member property unless the corresponding MemberProperty object is created first.
The collections in DSO fall into three general categories, depending on the position of the collection within the DSO hierarchy: open, limited, and closed.
An open collection is a collection whose membership is unrestricted; it contains DSO objects that are directly creatable and that do not inherit subordinate objects and attributes from similar objects higher up in the DSO hierarchy. Objects can be added and removed as needed. For example, a DbDimension object does not inherit levels and member properties from another dimension. The Add and AddNew methods return a reference to a new DSO object, and do not depend upon the existence of another DSO object higher up the hierarchy. The Remove method destroys the DSO object.
A limited collection is similar to an open collection, but it contains DSO objects that can be created indirectly from other, similar DSO objects. The membership of a limited collection is based on the membership of another collection; only those objects that exist in this collection can be added or removed from a limited collection. Objects in limited collections inherit subordinate objects and attributes from similar objects higher up in the DSO hierarchy. The Dimensions collection of a cube, which contains CubeDimension objects, is an example of a limited collection: it requires a one to zero-to-one relationship with the members of the Dimensions collection of the corresponding database. Although both Add and AddNew methods work, as with an open collection, they depend on references to existing DSO objects; you will raise an error if you attempt to use AddNew with the Dimensions collection of a cube by supplying the name of a dimension that does not already exist in the Dimensions collection of the corresponding database. The Remove method destroys the DSO object in this collection, but does not disturb the corresponding DSO object higher up the hierarchy. For example, using the Remove method to remove a CubeDimension object from the Dimensions collection of a cube does not destroy the DbDimension object upon which it is based.
A closed collection has a fixed membership; its members are based completely on the membership of another collection. The members of a closed collection are inherited from the collection of a similar object higher up in the DSO hierarchy; no new objects can be added to or removed from the collection. The Dimensions collection of an aggregation is an example of a closed collection: all of its members are inherited directly from the Dimensions collection of the partition to which the aggregation belongs. The Add and AddNew methods raise an error when used, as does the Remove method.
In all three types of collections, the individual DSO objects can be read-only or read-write, depending on the collection. For example, although the Dimensions collection of an aggregation is a closed collection, certain properties of the individual AggregationDimension objects can be changed.
The following diagram indicates the order of precedence for DSO objects, and displays the open, limited, and closed collections within the DSO hierarchy.
Figure 2. The order of precedence for DSO objects. (Click figure to see larger image.)
Note that the CustomProperties collection is supported by all DSO objects, and is considered an open collection with read-write objects even for DSO objects that are read-only.
In DSO, the terms "major" and "minor" identify which objects can commit changes to the Analysis server. A minor object cannot commit its own changes; they must be saved to the Analysis server by the "owning" major object. This behavior is important to understand; many issues involving meta data management in Analysis Services are caused by not committing changes to minor objects through a major object. When working with the DSO hierarchy, always call the Update method of the appropriate major object when you make changes to subordinate minor objects.
One of the new features in DSO is the introduction of notification events in the Database object. Notification events are used to provide information during processing. As stated in SQL Server Books Online, these events are raised not only for the Database object itself, but also for all of its subordinate objects. Although the primary purpose of these events is to handle user interface interaction with custom applications using DSO, the ReportError event in particular can assist greatly in centralized error handling approaches. One precaution is recommended, however, when you use these events. For identification purposes, the obj parameter supplied by the Database object events contains a reference to the relevant DSO object itself; it is possible to alter the properties of this object. It is recommended that you do not change the properties of this object, because doing so may affect the processing of the object itself. Also, the Database interface must be implemented in order to receive these events; this is the only exception to the rule regarding the use of commonly implemented interfaces discussed earlier.
Together, the CubeAnalyzer and PartitionAnalyzer objects in DSO supply the functionality behind three different wizards in Analysis Services. The CubeAnalyzer is used by itself for the Usage Analysis Wizard, the PartitionAnalyzer is used by itself for the Storage Design Wizard, and both objects are used together for the Usage-Based Optimization Wizard. To understand how to employ these DSO objects in a custom application, a better understanding of how they are utilized in the Analysis Services user interface is required.
The CubeAnalyzer object can retrieve queries logged against a particular cube and can restrict the retrieval by a number of different elements, such as the start time or duration of the query, or the user who executed the query. This may sound familiar; the object supports both the Select Filter Criteria step of the Usage Analysis Wizard and the Select Queries step of the Usage-Based Optimization Wizard. The OpenQueryLogRecordset method, when called, returns a flat Microsoft ActiveX® Data Objects 2.6 (ADODB) Recordset object. The contents of this returned recordset are formatted and displayed in the Review Results step of both the Usage Analysis Wizard and the Usage-Based Optimization Wizard.
One of the most useful components of the recordset returned by this method is the Dataset column, which contains a simple representation of the levels and dimensions needed to fulfill a given query. The value in the Dataset column is concatenated from the ordinal positions of levels, referenced by the query, from every dimension in the cube. Combined with a count of the rows within the returned recordset that use this particular value in the Dataset column, this information can be used by the PartitionAnalyzer object to fine-tune aggregations, as discussed later.
The PartitionAnalyzer object can create aggregations for a particular partition in two basic modes. The first mode involves the use of goal queries: queries used by the PartitionAnalyzer to determine if designed aggregations can satisfy a specified query. This is where the information returned by the CubeAnalyzer object becomes valuable. The AddGoalQueries method of the PartitionAnalyzer can be fed with the value obtained from the Dataset column and the number of times that particular value occurs within the returned recordset to create goal queries. The resulting goal queries are then evaluated by the PartitionAnalyzer object by using the PrepareGoalQueries method. This establishes the goals against which the PartitionAnalyzer can test its designed aggregations to determine if they match the intended usage of the partition. This process is the basis of the Usage-Based Optimization Wizard.
The second mode does not involve goal queries; the PartitionAnalyzer object uses a mathematical simulation to create goals against which designed aggregations can be tested.
To keep existing aggregations, you must add them to the ExistingAggregations collection maintained by the PartitionAnalyzer object by using the AddExistingAggregations method. This method also returns the realized percentage of performance gain and disk storage that results from adding an aggregation, as well as the total count of designed aggregations maintained by the PartitionAnalyzer object. This method is used to support the Aggregations Already Exist step in both the Storage Design Wizard and Usage-Based Optimization Wizard.
Additional aggregations can be designed by using the NextAnalysisStep method of the PartitionAnalyzer object, and can be restricted either by the amount of disk storage used, the percentage of performance gain, or the total number of designed aggregations. This method can be called repeatedly to incrementally design aggregations; indeed, this is how the Set Aggregation Options step supports the Until I click Stop option on both the Storage Design Wizard and Usage-Based Optimization Wizard.
Aggregations can be designed individually, but using these two objects together can save considerable time and effort in fine-tuning partitions to meet your storage and performance requirements.
Remember that after aggregations are designed, the parent partition, as a major object, must be processed in order to generate and store the values for the designed aggregations.
Microsoft SQL Server 2000 Books Online contains more information about DSO. For additional information, see these resources:
- The Microsoft SQL Server Web site.
- The Microsoft SQL Server Developer Center.
- SQL Server Magazine.
- The microsoft.public.sqlserver.programming and microsoft.public.sqlserver.datawarehouse newsgroups at news://news.microsoft.com.
- The Microsoft Official Curriculum courses on SQL Server. For up-to-date course information, see the Microsoft Training & Services Web site.
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.
This technical article is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.
©2000 Microsoft Corporation. All rights reserved.
Microsoft, ActiveX, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.
The names of actual companies and products mentioned herein may be the trademarks of their respective owners.