Chapter 25 - Getting Data to the Client
Microsoft® SQL Server™ 2000 Analysis Services supplies a robust server engine for online analytical processing (OLAP) and data mining. The server components, however, are useful only if the data managed by the server can be retrieved by and displayed to the user.
In this chapter, both data and meta data retrieval are discussed in relation to the data access technologies available for use with Analysis Services. Basic overviews and usage guidelines for each data access technology are provided, as well as samples illustrating the use of each technology.
Developing Analysis Services Client Applications
Analysis Services provides access to data and meta data through its implementation of the OLE DB for OLAP specification, Microsoft PivotTable® Service. Other OLE DB-compatible data access services, such as Microsoft ActiveX® Data Objects (ADO), can be used to supply a common, standard data access technology to client applications.
In conjunction with Microsoft Internet Information Services (IIS), PivotTable Service can also send and receive data and meta data across the Internet. For more information about using the Internet, see "Using The Internet With Analysis Services" in this chapter.
In addition to the above technologies, meta data on Analysis servers can also be accessed through Decision Support Objects (DSO), a powerful object model specifically designed to support Analysis Services. For more information about meta data access with DSO, see "Meta Data and Decision Support Objects" in this chapter.
Local Data and Meta Data
PivotTable Service also provides the ability to create offline, or local, cubes and data mining models. Client applications can connect to offline cubes and data mining models transparently, allowing client applications to copy and cache commonly-used OLAP and data mining objects from the server to the client and reduce network and resource usage. PivotTable Service supports a basic data definition language (DDL) useable only for offline OLAP and data mining objects.
Using offline OLAP and data mining objects allows for a third type of client architecture, in which the client application connects periodically and unpredictably to a server to synchronize its offline information. This architecture is prevalent in client applications designed for portable workstation and personal digital assistant clients.
Working with Data
One of the challenges with online analytical processing (OLAP) is the basic issue of multidimensional retrieval. The OLE DB for OLAP specification resolves this issue, detailing a complete solution, based on OLE DB, but capable of supporting the intricacies of multidimensional data access.
PivotTable Service is the Microsoft implementation of the OLE DB for OLAP specification, an OLE DB provider that can be used either with OLE DB directly, or with OLE DB in conjunction with other standardized data access technologies, such as ADO.
Data and PivotTable Service
PivotTable Service is an OLE DB provider, designed for Analysis Services, which implements the OLE DB for OLAP specification. It is used by other OLE DB compliant data access technologies, such as ADO and ADO MD to work with Analysis Services and relational database data.
However, PivotTable Service can be a complicated provider to use. The PivotTable Service provider is configured through the use of properties, usually set either through the connection string used to connect the PivotTable Service OLE DB provider to a data source, or through the collection of properties exposed, after a connection is made to an Analysis server.
PivotTable Service properties are accessible by other data access technologies, typically by the connection string used to connect the provider to the server or by registry settings stored on the client. The following properties are of particular interest when you develop client applications:
Auto Synch Period
The interval specified in this property determines how often the client cache synchronizes with the Analysis server during the course of a session. This property should be set depending on the current usage context of the session; for example, if the client application is accessing only historical data, the Auto Synch Period parameter can be set to zero, because nothing is likely to change between queries. Other applications may require automatic synchronization, and a useful value for this setting may change from usage context to usage context within a single session. One effective method of performance tuning is to allow this value to be set by the user or administrator as needed, as a tunable property available through the client application.
If the parameter is set to zero, automatic synchronization is disabled. The client cache synchronizes with the server cache only when a query is executed against the server. While this setting is useful in eertain situations, this value is not recommended for most client applications.
For more information about managing the client-side cache, see Chapter 26, "Performance Tuning Analysis Services."
Client Cache Size
The default value for this parameter is 25, or 25 percent of available virtual memory, but it can be changed during a session. Setting this parameter to zero can be problematic, because the client cache can quickly use most of the resources available to a client. Setting this value to a percentage of virtual memory can be imprecise if a client application is installed on a wide variety of machines; setting this value to a fixed amount of kilobytes can also be problematic for the same reason.
As with the Auto Synch Period property, supplying access to this property as a tunable property available through the client application can improve performance and reduce resource overload.
For more information about managing the client-side cache, see Chapter 26, "Performance Tuning Analysis Services."
CompareCaseNonSensitiveStringFlags and CompareCaseSensitiveStringFlags
Both of these settings can be set by a client application either through the connection string or through the use of registry keys. Both keys are kept in \HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft OLAP Server\CurrentVersion, and are stored as bitmapped long integers. The settings for both properties affect all sessions made by a specific client, and the values for subsequent connections are determined by the values used for the first connection in a given process thread. After they are set, they cannot be changed for a given session.
Typically, these keys do not require changes. The default value for the CompareCaseSensitiveStringFlags property is set to NORM_IGNOREWIDTH, NORM_IGNOREKANATYPE, and NORM_IGNORECASE, while the default value for CompareCaseNonSensitiveStringFlags property is set to zero (in other words, no settings are applied.)
This property maps directly to the DBPROP_INIT_TIMEOUT property supported by OLE DB, and the default setting for this property is 15 seconds. If the connection timeout period elapses before a connection can be completed to an Analysis server, the error &H80004005, "OLAP server error: The operation requested failed due to network problems", will be raised.
This value may need to be changed depending on network latency issues and how busy the OLAP Server is. We recommend not setting this value lower than the default value, but if the client application generates timeout errors, you may want to raise it to 30 seconds or higher.
PivotTable Service can connect to Analysis servers in two ways, using either TCP/IP or HTTP. If you are connecting to the Analysis server in the same domain, the data source is the computer name for the Analysis server. If you are connecting using HTTP or HTTPS, you can specify either an IP address or URL in order to establish a connection to the Analysis server.
Default GUID Dialect
Depending on the client application, queries can be processed slightly faster if the appropriate default dialect is selected. The default setting assumes that the SQL parser will be first used to parse submitted queries, which means that Multidimensional Expressions (MDX) queries will always be parsed twice—once by the SQL parser, which fails, and then by the MDX parser. A similar behavior exists for data mining queries. This can also prevent analysis of errors from the MDX or data mining parser, because the error message from the default parser is returned to the client application.
If your client application is used primarily for MDX, set this property to MDGUID_MDX. Otherwise, the default setting should be sufficient.
Execution Location and Mining Execution Location
The default value allows PivotTable Service to determine whether a query will be executed on the client computer or on the server. However, in certain situations, setting this property so that queries are executed on the server can improve performance. Two separate properties are used to separate OLAP and data mining queries.
This property determines the database context used by the connection. You must use this property to correctly establish this context for a specific database. If you do not specify this, the first database stored in Analysis Services is used as the database context. This property is especially necessary for handling meta data.
This property is extremely useful for debugging client applications; it functions in much the same way as an ODBC trace log, storing the process ID, date and time, query type, and query text for each query issued through the PivotTable Service OLE DB provider. However, using the log file affects performance because of increased file activity on the client, and should be used only in debugging situations.
This property affects how ragged and unbalanced hierarchies are viewed. If your client application can handle the display of ragged hierarchies as managed in Analysis Services, this property should be set to 2. Note that this property affects only placeholder members with data or with subordinate nonplaceholder members within the hierarchy. If the placeholder has no data and has no subordinate nonplaceholder members, it is always hidden regardless of the value of this property.
This property must be set in order to create permanent local data mining models on the client. Not setting this property is one of the common reasons that local data mining models are not properly persisted to the client.
Mining Persistence Format
Although the default setting for this property causes PivotTable Service to store data mining models in binary format, you can change this property to store data mining models as Extensible Markup Language (XML) to make the information portable with only a slight decrease in performance as a tradeoff.
This setting should be changed cautiously. The setting can be relaxed to allow external function libraries with potentially unsafe functions to be loaded for a given session, but this should never be done for function libraries with unknown contents. Corruption or data loss, or worse, can potentially result from using unsafe function libraries.
Secured Cell Value
A valuable property when used properly, Secured Cell Value formats the contents of a secured cell (in other words, a cell whose contents the client application cannot view due to lack of security rights) into a variety of settings. This can be very effective when using numeric calculations of cell data in client applications, as the default value sets secured cells to a string. This can interfere with most client-side calculations; if so, set this property to either three, which will return a NULL, or four, which will return a zero for secured cells.
This property is important if the Analysis server is on a domain that supports multiple security packages, such as Kerberos. Typically, this property needs to be changed from its default only if security measures for a given domain require such a change.
This property can be tuned if a client application experiences timeouts due to network latency issues or if the client application hangs due to server-side difficulties with writebacks. This property must be set in the connection string, because it cannot be changed for a session once set. If not set, the default setting for this property is Null; writebacks never time out by default.
Data and ActiveX Data Objects
ActiveX Data Objects (ADO) is an OLE DB-compliant data access technology, designed to handle relational and, to a limited extent, multidimensional data. Although best used in conjunction with ADO MD, this data access technology can support access to Analysis Services data and meta data directly.
Because ADO encapsulates OLE DB, which in turn uses PivotTable Service when connected to an Analysis server, most of the functionality available to OLE DB is available to the ADO library and, consequently, to the client application. Most client applications can use ADO and ADO MD without having to directly call or reference the OLE DB library. This is the preferred route for applications that support COM interfaces, such as Microsoft Visual Basic®.
ADO can be used to retrieve the data from the Analysis server in a tabular form (a flattened view of multidimensional data). You can use this approach for accessing multidimensional cubes if your client application is designed to retrieve data from relational sources, and you want to start querying relational and multidimensional sources without changing your code. The ADO Connection object has an open schema method that enables you to retrieve all meta data about cubes, dimensions, and other objects on the Analysis server in the form of schema rowsets. Later in this chapter we show that using ADO MD provides a much richer object model for easier exploration when accessing data and meta data.
The MDX Sample Application, shipped with SQL Server 2000 Analysis Services as a sample, provides an excellent example of using both ADO and ADO MD to view multidimensional data.
The following basic guidelines will allow you to make better use of ADO in Analysis Services client applications:
Use the ConnectionString property to your advantage.
The ConnectionString property, used by the ADO Connection object when connecting to a database, is used to pass information to the PivotTable Service OLE DB provider. The connection string information, however, can do much more than simply provide the data source and initial catalog used by ADO. The connection string can also configure the PivotTable Service OLE DB provider and, to a limited extent, perform offline operations. Each property used by PivotTable Service serves as a connection string parameter when using ADO or ADO MD.
For more information on individual connection string parameters, see "Data and PivotTable Service" in this chapter.
Take advantage of actions in Analysis Services.
In Analysis Services, actions are context-sensitive commands, stored on the Analysis server and defined as part of the structure of a cube. Actions are available to all client applications that access the cell, subject to the security defined for the action. Client applications can review the action type, which defines the general intended behavior of an action, and perform an application step based on the statement returned with the action. Because actions are typically provided as part of the retrieval of cube data, user interfaces tend to support actions within a display of cube data. For example, Cube Browser in Analysis Services supports actions as part of the pop-up menu available from the grid used to display cell data.
Actions can be retrieved from the SCHEMA_ROWSET_ACTIONS schema rowset through the OpenSchema method of an ADO Connection object or through the Commands collection of a cube in the DSO library.
Actions allow you to provide shared business functionality across multiple client applications without the maintenance issues typically associated with such shared functionality.
Data and ActiveX Data Objects (Multidimensional)
The ActiveX Data Objects (Multidimensional) library (ADO MD) is an extension library for ADO. ADO MD provides access to multidimensional data through an object model designed to access online analytical processing (OLAP) structures, and serves as an automation layer above PivotTable Service, the OLE DB provider that implements the OLE DB for OLAP specification. ADO MD can be used from any programming language that supports COM interfaces, such as Visual Basic, Microsoft Visual C++®, Microsoft Visual Basic Scripting Edition (VBScript), and Microsoft JScript®.
ADO MD takes advantage of the common features provided by the ADO library when connecting to an Analysis server. ADO MD uses an ADO Connection object, and supports the ability to pass a variety of commands through the ConnectionString property for PivotTable Service property support.
Using MDX, the ADO MD Cellset object provides a multidimensional representation of dimension and cell information retrieved from Analysis Services cubes. The cell data is provided on demand; the Cellset does not retrieve cell information until the cells are referenced.
One of the benefits of using the ADO MD Cellset object is the ability to handle speculative, or what if, analysis on retrieved multidimensional data. Analysis Services supports writing information back to a cube for a single cell or a set of cells, as a distribution, and ADO MD can take advantage of this feature. ADO MD supports either temporary writeback, which lasts only for the lifetime of a connection and can be used to perform speculative analysis without harm to underlying data, or permanent writeback, which allows the results of such analysis to be committed to a special table maintained by the Analysis server.
You should use ADO MD in conjunction with ADO to provide the best possible support for interactive client applications. For example, the MDX DrillThrough command allows you to drill through to underlying relational data, and retrieves the data in an ADO Recordset object. Drilling through becomes more complex in cubes with multiple partitions, as multiple resultsets are potentially returned (one resultset per partition). ADO and ADO MD complement each other in this manner, and can use the full range of functionality provided by Analysis Services when implemented together in a client application.
The MDX Sample Application, shipped with SQL Server 2000 Analysis Services as a sample, provides an excellent example of using both ADO and ADO MD to view multidimensional data.
The following recommendations should help in employing ADO MD to access data from Analysis Services:
Take advantage of actions in Analysis Services.
In Analysis Services, actions are context-sensitive commands, stored on the Analysis server and defined as part of the structure of a cube. Actions are available to all client applications that access the cell, subject to the security defined for the action. Actions allow you to provide shared business functionality across multiple client applications without the maintenance issues typically associated with such shared functionality.
For more information about using actions, see "Data and ActiveX Data Objects" in this chapter.
Use the Axes and Positions collections for determining the hierarchy of a member.
A common issue when displaying multidimensional data in a two-dimensional format is the need to flatten the data retrieved by an ADO MD Cellset object. One method commonly employed is the parsing of the unique member name to determine the ancestry of a particular cell or member—this is not a recommended technique. Instead, you should take advantage of the LevelDepth property, as well as the Axes and Positions collections, supplied by the ADO MD Cellset to determine the ancestry of a specific cell. The unique member name should instead be viewed as an arbitrarily generated unique key, lacking information for the purposes of client application development.
Take advantage of MDX functionality for user interface support.
MDX provides a number of functions designed for user interface support, such as drilling up and down on members or formatting individual cells based on member properties. For more information about using MDX to provide user interface support, see Chapter 23, "Business Case Solutions Using MDX."
Take care with resource usage in user interfaces.
Multidimensional data, in general, is overwhelmingly large. ADO MD, by definition, supports an exponential growth of referenced data; each dimension supported in an ADO MD Cellset object increases the potential number of retrieved cells by an order of magnitude. When designing user interfaces to support ADO MD data, you should be conscious of the resources required to display such data. For example, an ADO MD Cellset object accessing a cube with five dimensions, each with just 50 members per dimension, can potentially return 312,500,000 (50 x 50 x 50 x 50 x 50) cells. ADO MD refers to such data on demand, because of this potential for very large multidimensional cell sets. The client application, as well, should take advantage of an on demand methodology for showing large cell sets, such as using hierarchical grids and other limited-view techniques for displaying large amounts of information in a controlled manner.
Working with Meta Data
Meta data is a term used to describe the information about the schema and structure of data, such as the schema for a database. Meta data is simply data about the structure and organization of data—tables, columns, relationships, indexes, and so on.
For most client applications, meta data is used for informative purposes. Filling list boxes with available cubes, selecting a data mining model from a dropdown list, and so on, are common uses for meta data in client applications. Administrative client applications, however, make extensive use of meta data to create, delete, change, and manage all aspects of Analysis Services. Analysis Manager, for example, is an administrative client application, designed as a Microsoft Management Console (MMC) add-in with supplemental ActiveX DLL libraries.
In Analysis Services, access to meta data can be almost as important as access to data for client applications, due to the inherent complexity of multidimensional storage. Several technologies, listed below, support meta data access:
OLE DB with PivotTable Service
ADO with PivotTable Service
ADO MD with PivotTable Service
DSO does not require PivotTable Service to function, because it works directly with proprietary interfaces supported by Analysis Services.
Meta Data and Decision Support Objects
The Decision Support Objects (DSO) library consists of an object model that mirrors the object hierarchy of Analysis Services exactly. Unlike other data access methods for working with meta data, DSO works directly with the Analysis server, bypassing PivotTable Service as a provider. Using DSO for working with Analysis Services meta data is preferred over other methods discussed in this chapter for the following reasons:
The DSO library gives you power and flexibility without sacrificing the safety and stability of the Analysis server; the hierarchical nature of meta data in Analysis Services is directly represented and enforced by the DSO object model.
Ease of Use
The strictly enforced hierarchy of the DSO library makes it easier to work with potentially complex meta data in the right place at the right time.
DSO can be used in any programming language that supports COM interfaces, including scripting languages and languages that can handle only late-bound COM objects.
The DSO library is designed specifically to handle the meta data needs of Analysis Services, including access to features like calculated cells, actions, and data mining models.
The following diagram illustrates the object model of the DSO library.
The DSO library handles only meta data. To access the data stored in OLAP and data mining objects, you need to use another library, such as ADO MD. DSO is ideally suited for administrative applications that specialize in handling meta data, such as installation or remote maintenance applications, or for Analysis Services add-ins.
DSO cannot be used to handle offline cubes or data mining models, because the DSO object model works directly with the Analysis server.
DSO is highly recommended for administrative client applications and for client applications that need to work with Analysis Services meta data on a read-write basis. For client applications that need meta data for informative purposes only, another data access technology is recommended, such as ADO MD.
The following basic recommendations are useful if you plan to use DSO in an Analysis Services client application. For more information on the effective use of DSO in client applications, see the white paper "Developing Effective Decision Support Objects (DSO) Solutions with Microsoft SQL Server 2000 Analysis Services" at http://msdn2.microsoft.com/sqlserver/default.aspx.
Use only documented interfaces.
DSO objects implement a variety of interfaces, and many similar objects in DSO use one of several common interfaces. Use only the interfaces documented in SQL Server Books Online. Other interfaces, such as ICommon, are intended for internal use only, and can have unpredictable results on your meta data. The only exception to this rule is the Database interface, which must be used instead of the MDStore interface if you intend to trap database events in your client application.
Know the difference between major and minor objects.
In DSO, minor objects cannot commit their own changes. All minor objects belong to a major object; the major object commits changes not just for itself, but for its minor objects as well. If you do not use the major object to commit changes to the minor object, unpredictable results can occur.
Know the order of precedence.
Because DSO enforces the hierarchy of objects in Analysis Services, some objects must be created before other objects. The technical paper, "Developing Effective Decision Support Objects Solutions with Microsoft SQL Server 2000 Analysis Services," provides detailed information about the order of precedence for DSO objects. For more information about this white paper, see http://msdn2.microsoft.com/sqlserver/default.aspx.
Lock DSO objects when changing meta data.
DSO allows object locking while working with meta data, to prevent other users from reading, writing, or processing locked objects in Analysis Services. Use locks when changing meta data to prevent user confusion and lost changes.
Sample Application—Meta Data Scripter
The Meta Data Scripter sample provides an excellent way to exploit the features of DSO. The Meta Data Scripter (MetaDataScripter) is available on the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\MetaDataScripter.
The Meta Data Scripter sample consists of a Visual Basic® 6.0 ActiveX DLL project used to construct an Analysis Services add-in, as well as two VBScript template files. The add-in enables you to script the meta data of any object, including dependent objects that can be selected in Analysis Manager. The add-in generates a VBScript file that uses DSO to recreate the scripted objects.
The routines that use DSO to recreate the scripted objects are contained in the Footer.vbs template file included with the sample. The global variables used by the generated VBScript file are contained in the Header.vbs template file, also supplied with the sample. The Analysis Services add-in copies both files into the generated VBScript file. The generated VBScript script file can be executed either from the command line on any Analysis server with the Microsoft Windows® Script Host or in any environment that can support the VBScript scripting language.
The Meta Data Scripter is supplied as Visual Basic 6.0 source code and supporting files. Because this sample is an Analysis Services add-in, additional steps are required to register the add-in with Analysis Services.
Copy the MetaDataScripter files from the ToolsAndSamples folder of the SQL Server Resource Kit CD-ROM to a folder on your local hard drive.
Open the Metadatascripter.vbp project file using Visual Basic 6.0.
To register the Analysis Services add-in, execute the RegisterAddIn subroutine from the Immediate window. This routine writes the registry keys needed by Analysis Services to recognize the add-in.
Either compile the add-in into an ActiveX DLL or execute the Meta Data Scripter project from Visual Basic 6.0. If Analysis Manager is already running, close it first.
The Meta Data Scripter sample can script any object in the DSO hierarchy (including objects not directly viewable through the Analysis Manager). Follow these instructions to script an object in Analysis Manager.
Start Analysis Manager.
In the tree pane of Analysis Manager, expand the Analysis Servers folder.
Expand the server containing the database you want to use.
If the server does not appear, right-click the Analysis Servers folder and click Register Server.
Right-click the server or any object under it for which you want to create a meta data script.
Point to All Tasks, and then click Create meta data script.
Meta Data and PivotTable Service
As with any other OLE DB provider, PivotTable Service is not meant to be used directly, but in concert with OLE DB or an OLE DB compliant data access technology, such as ADO and ADO MD, to retrieve meta data.
Although more information about using other data access technologies with PivotTable Service can be found in other sections of this chapter, there are a few things to know about using the PivotTable Service provider with other data access technologies:
Know your schema rowsets.
The PivotTable Service provider does not support many of the expected OLE DB schema rowsets. ADO and ADO MD do not support the GetSchemas method provided by the IDBSchemaRowset interface in OLE DB; this can make discovering which schemas are supported difficult.
Know your properties.
The PivotTable Service provider supports over 40 different properties, most of which are accessible by connection string parameters and the Properties collection in ADO and ADO MD. For more information about using specific PivotTable Service properties, see "Data and PivotTable Service" earlier in this chapter.
Meta Data and OLE DB
OLE DB serves as the underpinning to ADO and ADO MD, and works in concert with PivotTable Service to retrieve meta data from Analysis servers.
PivotTable Service is the Microsoft implementation of the OLE DB for OLAP specification. Other data access technologies, such as ADO and ADO MD, also serve as a layer above both OLE DB and PivotTable Service.
OLE DB is usually not directly employed in client applications. Instead, other data access technologies such as ADO and ADO MD are used to access meta data. As with ADO, the easiest way to retrieve meta data in OLE DB is through schema rowsets. The IDBSchemaRowset interface provides access to schema rowsets, with the GetSchemas method allowing enumeration of supported schema rowsets and the GetRowset method allowing retrieval of a specific schema rowset.
Schema rowsets can be retrieved in OLE DB only by using the GUID to identify the desired schema rowset. Not all schema rowsets are required to be supported by OLE DB providers.
The following general guidelines are essential for successful meta data retrieval from Analysis Services:
Use both GetSchemas and GetRowset methods in IDBSchemaRowset.
The GetRowset method does not perform type checking of restriction values in the array supplied to the rgRestrictions parameter and does not validate the number of restrictions supplied to the cRestrictions parameter; you can inadvertently supply too few or too many restrictions in the array, or supply the wrong data type for restriction values.
If you are unsure of the number and data type of restrictions supported by a schema rowset, or even if the OLE DB provider supports the schema rowset, use the GetSchemas method first to retrieve all supported schema rowset GUIDs and their restrictions. Use this information to correctly construct a VARIANT array of restrictions to supply to the GetRowset method.
Meta Data and ActiveX Data Objects
Using ADO with ADO MD gives you the ability to manipulate both data and meta data for any multidimensional data provider (MDP), including Analysis Services.
Because ADO encapsulates OLE DB, which in turn uses PivotTable Service when connected to an Analysis server, most of the functionality available to OLE DB is available to the ADO library and, consequently, to the client application. Most client applications can use ADO and ADO MD without having to directly call or reference the OLE DB library. This is the preferred route for applications that support COM interfaces, such as Visual Basic.
One way to retrieve all forms of meta data is to use ADO to retrieve the schema rowsets for Analysis Services objects.
The OpenSchema method of the ADO Connection object makes the meta data of Analysis Services available as schema rowsets, returning an ADO Recordset object for a specified schema rowset. There are two techniques for using the OpenSchema method: either use an enumeration constant for the desired schema rowset or directly use the GUID for a desired schema rowset. The SchemaEnum enumeration included in the ADO DB library supplies the constants used for the first technique. If you intend to work with both OLAP and data mining meta data, the second technique is preferred; no constants are supplied with ADO for retrieving data mining schema rowsets. If you intend to work only with OLAP data, the first technique is preferable, because you do not need to make the GUID information for OLAP schema rowsets available for the client application.
ADO is an excellent tool for retrieving meta data from Analysis Services. The following guidelines will help you do so efficiently:
Know your restrictions.
The OpenSchema method of the ADO Connection object effectively encapsulates the IDBSchemaRowset interface of OLE DB, specifically the GetRowset method of the interface. The cRestrictions and rgRestrictions parameters for the IDBSchemaRowset::GetRowset method in OLE DB are based on the array supplied to the Restrictions parameter of the OpenSchema method in ADO. There is no type checking of individual restriction values or of the appropriate count of restrictions for a specified schema rowset; it is very easy to supply an incorrect number of restrictions or the wrong data type for a specific restriction value.
However, unlike in OLE DB, there is no corresponding method in ADO to retrieve a list of supported schema rowsets.
Sample Application—Schema Rowset Explorer
The Schema Rowset Explorer (SchemaRowsetExplorer) is available on the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\SchemaRowsetExplorer.
This sample illustrates the direct use of the OLAP and data mining schema rowset GUIDs to retrieve schema rowsets. Written in Visual Basic 6.0, the sample also illustrates the use of restriction columns, which allow you to retrieve only the desired meta data from the schema rowset. The information provided in the Restrictions parameter of the OpenSchema method is used to construct a SQL WHERE clause when querying the schema rowset.
The sample application uses a resource file to store the GUID and restrictions information for each schema rowset. When a schema rowset is selected, the restrictions information is retrieved and used to configure the application form, allowing you to enter restrictions for the selected schema rowset.
When the Load Schema Rowset button is clicked, the GUID for the selected schema rowset is retrieved. The values supplied for the restrictions, if any, are stored in a Variant array. Both the array and the GUID are supplied to the ADO OpenSchema method, returning an ADO Recordset object. The column layout and data from the ADO Recordset object is then loaded into the MSFlexGrid control on the application form for display.
The Schema Rowset Explorer is supplied as Visual Basic 6.0 source code and supporting files.
Copy the SchemaRowsetExplorer files from the ToolsAndSamples folder of the SQL Server Resource Kit CD-ROM to a folder on your local hard drive.
The Schema Rowset Explorer sample can view any schema rowset supported by the PivotTable Service OLE DB provider. Follow these instructions to start the application and view a schema rowset.
Start the Schema Rowset Explorer to display the Schema Rowset Explorer dialog box.
Enter a valid Analysis server and schema rowset into the Server Name and Schema Rowset fields, respectively.
Optionally, enter valid restriction values in the text boxes provided for the restriction columns in the Restrictions frame.
Click Load Schema Rowset to load the schema rowset.
Meta Data and ActiveX Data Objects (Multidimensional)
The ActiveX Data Objects (Multidimensional) library (ADO MD) is an extension library for ADO. ADO MD provides access to multidimensional meta data through an object model designed to access OLAP data. If you intend to use data mining meta data in your client application, you should use either DSO for read-write access or ADO for read-only access, because ADO MD does not support data mining models. ADO MD is best used in client applications that need to support multiple heterogeneous multidimensional data sources, or with ADO in client applications that access both relational and OLAP meta data. As with ADO, ADO MD encapsulates OLE DB, so most of the functionality available through OLE DB and PivotTable Service is available to client applications that use ADO MD.
ADO MD is specifically designed to handle multidimensional data access. ADO uses flattened recordsets when retrieving multidimensional information from Analysis Services cubes, but ADO MD preserves the multidimensional nature of such data by returning ADO MD Cellset objects from queries performed against Analysis Services cubes. While this is useful when working with data, it is not necessary when dealing with meta data.
One of the benefits of using ADO MD is the ability to consistently use the same data access technology for both data and meta data retrieval. The object model used by ADO MD compares roughly to that of DSO, although the ADO MD object model is designed to work with meta data from any MDP supporting the OLE DB for OLAP specification, while DSO is specifically designed to handle the intricacies of Analysis Services meta data. As such, ADO MD does not support access to some of the more specialized features of Analysis Services, such as calculated cells and actions, as meta data. Combined with ADO, complete support of relational, OLAP, and data mining data can be easily handled within a client application.
One drawback, however, is that the topmost object in the ADO MD hierarchy, the Catalog object, represents a database in Analysis Services. ADO MD, as with ADO, supports the concept of a default database; you must specify a database when you connect to the Analysis server, using the Initial Catalog parameter in the connection string to set the initially selected database. In other words, you cannot connect to an Analysis server and enumerate the available databases using the ADO MD object model. For non-administrative client applications, this is not much of a problem; most client applications of this nature use a specific database for their data access requirements.
Know your catalog.
This guideline is very important for ADO MD because of the way the initial catalog is established through the connection string used to connect to the Analysis server. There is no direct way, at the time of this writing, to retrieve a schema rowset in ADO MD containing the databases on a specific Analysis server; another technology, such as ADO or DSO, must instead be used to retrieve a list of databases on an Analysis server.
Sample Application—Analysis Server Catalog Explorer
The Analysis Server Catalog Explorer (CatalogExplorer) sample application is available on the SQL Server 2000 Resource Kit CD-ROM in the folder, \ToolsAndSamples\CatalogExplorer. This sample application illustrates using ADO and ADO MD to retrieve the meta data relating to OLAP objects, such as cubes and dimensions, from an Analysis server. Given a valid server name and catalog name, an ADODB Connection object is connected to the Analysis server, and then used for the ActiveConnection property of an ADO MD Catalog object.
The Catalog object, in turn, retrieves the entire available object model when the Retrieve catalog from Analysis server button is clicked. The subordinate objects of the Catalog object are then loaded into a TreeView control for perusal.
The Analysis Server Catalog Explorer is supplied as Visual Basic 6.0 source code and supporting files.
Copy the CatalogExplorer files from the CatalogExplorer folder of the SQL Server Resource Kit CD-ROM to a folder on your local hard drive.
The Analysis Server Catalog Explorer sample can view any database that can be selected from the Analysis Manager tree view. Follow these instructions to start the application and view the meta data for a database using ADO MD.
Start Catalog Explorer to view the Catalog Explorer dialog box.
For Server Name, type the computer name for a valid Analysis server.
For the local computer, use "LocalHost".
For Catalog Name, type the name of a database on the server.
Click Retrieve catalog from Analysis Server to load the database meta data into the tree view.
Using the Internet with Analysis Services
Analysis Services offers the ability to access multidimensional data across the Internet or intranet from servers running IIS, using HTTP or HTTPS to transfer data. A special Active Server Page (ASP), msolap.asp, and an ActiveX DLL, msmdpump.dll, are used on the server to support data transfer.
To use this functionality, the Uniform Resource Locator (URL) of the IIS server should be specified for the Data Source parameter used in the connection string sent to PivotTable Service on the client. PivotTable Service automatically and transparently handles the transfer of data across the Internet; most thick client applications can support Internet access directly, with no coding changes.
Most of the guidelines presented below refer more to administration issues, rather than development issues, with Analysis Services.
Use HTTPS whenever possible.
The nature of OLAP information is typically sensitive and proprietary; information-rich aggregated business data. You should take advantage of security features in IIS, such as the use of secure data transfer with HTTPS, to protect your OLAP data. Ideally, the virtual web site used to support msolap.asp should be separate from other web sites managed by the IIS server, to further isolate any additional required security.
Be aware of IIS security issues.
For example, one of the most common security issues encountered when accessing Analysis Services from IIS is that many Analysis Services objects, such as cubes, are not available to Internet or intranet users.
IIS uses a default user account, typically named IUSER_< Server Name > , when accessing external applications. This user account is not included as part of the All Users database role in Analysis Services; most Analysis Services administrators forget about this user account when planning access. If you plan on using Analysis Services across the Internet, this user account should be included in database, cube, and mining model roles where appropriate.