Using the OpenSchema Method

Using the OpenSchema Method

In addition to the CubeDef object, Microsoft® ActiveX® Data Objects (ADO) provides the OpenSchema method for the connection object. To use this method to get schema information about multidimensional and data mining meta data, use the following query types:

• AdSchemaCatalogs
  
  
• AdSchemaCubes
  
  
• adSchemaDimensions
  
  
• adSchemaHierarchies
  
  
• adSchemaLevels
  
  
• adSchemaMeasures
  
  
• adSchemaMembers
  
  
• adProviderSpecific

Using Restriction Columns

Restriction columns enable the returned recordset of an OpenSchema function call to be filtered by certain constraints. For any given schema rowset, a number of restrictions may be supported. For example, the MINING_MODELS schema rowset supports the following restriction columns:

• MODEL_CATALOG
  
  
• MODEL_SCHEMA
  
  
• MODEL_NAME
  
  
• MODEL_TYPE
  
  
• SERVICE_NAME
  
  
• SERVICE_TYPE_ID

To use a particular column (or set of columns), build an array of strings that corresponds to the list of restriction column in their order. For instance, to retrieve a list of all of the mining models in the FoodMart 2000 database that use the Microsoft Decision Trees algorithm, construct the following array in Microsoft Visual Basic®:

Array("FoodMart 2000", Empty, Empty, Empty, Empty, "0")

Each element in the array corresponds to an element in the restriction columns list. The first, "FoodMart 2000," specifies that all of the records returned should be members of the FoodMart 2000 database (that is, catalog). This is because the MODEL_CATALOG is the first element in the restriction columns. The next four elements are built as empty and specify that no restrictions should be placed on the returned records based upon their respective restriction columns. The last element of the array, "0", is in the position reserved for the SERVICE_TYPE_ID restriction column. The value "0" is determined by looking up the allowed list of values for this restrictions column in the OLE DB for Data Mining specification.

Use this array in the ADO OpenSchema method as the Criteria parameter.

Retrieving Rowsets Unsupported by ADO

To use a schema rowset that is not supported by the ADO SchemaEnum enumeration in the ADO OpenSchema method, use the enumeration value adSchemaProviderSpecific with any restriction columns that are appropriate to the schema rowset. The SchemaID parameter of the OpenSchema method will contain the schema's GUID in a string format. For more information, see Schema Rowsets.

Examples

A. Retrieving a List of Cubes

The following code shows how to use ADO to retrieve a list of cubes in the current database. For more information about the OpenSchema method, see the ADO documentation.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim szCubeName As String
Const CubeNamePosition =2

Set cn = New ADODB.Connection
cn.Open ("provider=msolap; Data Source=LocalHost; Initial Catalog=FoodMart 2000;")

Set rs = cn.OpenSchema(adSchemaCubes,Array("FoodMart 2000",Empty,Empty))

Do Until rs.EOF
   szCubeName = rs.Fields(CubeNamePosition).Value
   Debug.Print szCubeName
rs.MoveNext
Loop

Const DMSCHEMA_MINING_MODELS = "{3add8a77-d8b9-11d2-8d2a-003029144fde}"
'Open the MINING_SERVICES schema rowset. Assume the existence of an ADO 
'connection (cn) and an ADO recordset.
Set rst = cn.OpenSchema(adSchemaProviderSpecific,Array("FoodMart 2000",Empty,Empty), DMSCHEMA_MINING_MODELS)