ADO Multidimensional
Now that you've taken a whirlwind tour of MDX, let's shift gears and look at another enabling technology: ADOMD. ADOMD allows you to query both the cube schema and data from a simple programming interface. Figure 5 outlines the main components of the ADOMD object model. The cube schema is queried through the Catalog and the cube data through the Cellset object.
When accessing a cube, the first object that's constructed is a standard ADO connection. The provider in this case will be MSOLAP. Once a connection has been made to the OLAP data source, a catalog can be constructed. This catalog will contain the multidimensional schema information (that is, cubes and underlying dimensions, hierarchies, levels, and members):
Dim conFoodMart, catFoodMart
' Create a connection to the FoodMart service
Set conFoodMart = _
Server.CreateObject("ADODB.Connection")
conFoodMart.Open _
"Data Source=MyServer;Provider=MSOLAP;"
conFoodMart.DefaultDatabase="FoodMart"
' Access the FoodMart cube catalog
Set catFoodMart = Server.CreateObject("ADOMD.Catalog")
Set catFoodMart.ActiveConnection = conFoodMart
Next, access to the underlying cube schema is obtained via the CubeDefs collection. This collection has an item for each cube within the OLAP data source. Each item is represented by a CubeDef object, which can be accessed using the cube name or an ordinal number:
Dim cubFoodMart
' Obtain a reference to the Sales Cube
Set cubFoodMart = catFoodMart.CubeDefs("Sales")
In navigating the schema of a cube, the relevant items are a set of related dimensions, hierarchies, levels, and members. Each of these are represented by their corresponding ADOMD objects: Dimension, Hierarchy, Level, and Member.
From the cube schema, you can then navigate down through the hierarchy of dimensions, hierarchies, levels, and members. The concept of dimensions should already be familiar. To simply list the dimensions the code would read:
Dim dimCube
For Each dimCube In cubFoodMart.Dimensions
Response.Write dimCube.Name
Next
The next object type is the hierarchy, which is the path of aggregation of a dimension. A dimension can have more than one hierarchy, but for my purposes, I will only consider a cube consisting of a single hierarchy. The levels collection provides the means for obtaining the items (levels) within the dimension. Finally, the members collection represents the data items within the dimension.
Most dimensions have a special member, usually prefixed with the word All, whose children are the members of the first level in the hierarchy. The actual value of this member can be obtained via the ALL_MEMBER property of the hierarchy. The code in Figure 6 puts this into practice. Figure 7 contains the output.
When reviewing the Measures dimension in Figure 6, the dimension MEMBERS would be required to allow an MDX statement to be constructed dynamically. This is achieved by iterating through the members of the first and only level. For the other dimensions, the easiest way to retrieve all the members of the first level is to take the CHILDREN of the All level. If an All level is not available, then you only need the members of the first level. The significance of this output will become apparent when outlining the ASP application.
This is a very basic introduction on how to obtain schema information about a cube. The relationship to MDX statements has also been emphasized, as the cube structure will often be examined to enable the construction of MDX queries.
Now that you've taken a whirlwind tour of MDX, let's shift gears and look at another enabling technology: ADOMD. ADOMD allows you to query both the cube schema and data from a simple programming interface. Figure 5 outlines the main components of the ADOMD object model. The cube schema is queried through the Catalog and the cube data through the Cellset object.
When accessing a cube, the first object that's constructed is a standard ADO connection. The provider in this case will be MSOLAP. Once a connection has been made to the OLAP data source, a catalog can be constructed. This catalog will contain the multidimensional schema information (that is, cubes and underlying dimensions, hierarchies, levels, and members):
Dim conFoodMart, catFoodMart
' Create a connection to the FoodMart service
Set conFoodMart = _
Server.CreateObject("ADODB.Connection")
conFoodMart.Open _
"Data Source=MyServer;Provider=MSOLAP;"
conFoodMart.DefaultDatabase="FoodMart"
' Access the FoodMart cube catalog
Set catFoodMart = Server.CreateObject("ADOMD.Catalog")
Set catFoodMart.ActiveConnection = conFoodMart
Next, access to the underlying cube schema is obtained via the CubeDefs collection. This collection has an item for each cube within the OLAP data source. Each item is represented by a CubeDef object, which can be accessed using the cube name or an ordinal number:
Dim cubFoodMart
' Obtain a reference to the Sales Cube
Set cubFoodMart = catFoodMart.CubeDefs("Sales")
In navigating the schema of a cube, the relevant items are a set of related dimensions, hierarchies, levels, and members. Each of these are represented by their corresponding ADOMD objects: Dimension, Hierarchy, Level, and Member.
From the cube schema, you can then navigate down through the hierarchy of dimensions, hierarchies, levels, and members. The concept of dimensions should already be familiar. To simply list the dimensions the code would read:
Dim dimCube
For Each dimCube In cubFoodMart.Dimensions
Response.Write dimCube.Name
Next
The next object type is the hierarchy, which is the path of aggregation of a dimension. A dimension can have more than one hierarchy, but for my purposes, I will only consider a cube consisting of a single hierarchy. The levels collection provides the means for obtaining the items (levels) within the dimension. Finally, the members collection represents the data items within the dimension.
Most dimensions have a special member, usually prefixed with the word All, whose children are the members of the first level in the hierarchy. The actual value of this member can be obtained via the ALL_MEMBER property of the hierarchy. The code in Figure 6 puts this into practice. Figure 7 contains the output.
When reviewing the Measures dimension in Figure 6, the dimension MEMBERS would be required to allow an MDX statement to be constructed dynamically. This is achieved by iterating through the members of the first and only level. For the other dimensions, the easiest way to retrieve all the members of the first level is to take the CHILDREN of the All level. If an All level is not available, then you only need the members of the first level. The significance of this output will become apparent when outlining the ASP application.
This is a very basic introduction on how to obtain schema information about a cube. The relationship to MDX statements has also been emphasized, as the cube structure will often be examined to enable the construction of MDX queries.
