Exploring OLAP Cube with Visual Basic

Introduction

In an earlier tutorial the mechanics involved in accessing OLAP data from MS Excel were described. Pivot table service provided by the Analytical Services was used to access the data. In this tutorial, we will look at programmatically exploring the various structural details of the Cube, a basic unit of storage of data used in analyzing business related data. In a future article, creating the cube from scratch will be described, but you should also review the online tutorial that is bundled with Microsoft Analytical Services; it takes you through a tour that helps with understanding Analytical Services. This tutorial is divided into the following sections:

• Cube Basics
• Measures and Dimensions
  • Cube details under investigation
• Using VB to explore the OLAP Cube
  • Creating a VB project
  • Add library references
  • Storing global variables in the module
• Properties of the Cube
  • Properties of Dimensions
  • Properties of Hierarchies
  • Properties of Members
  • Properties of Levels

In Analytical Services, measures and dimensions describe a cube. The dimensions and measures are derived from the cube's data source. The set of tables with their relationships used to derive the dimensions and measures is called the cube's schema. The cube consists of a single fact table and one or more dimension tables. Measures are obtained from columns in the fact table, and dimensions are obtained from the columns of the dimension table.

The NewOlap database in the Analysis Server Nechost has a Cube, MyFirstCube. This is created from an OLTP database, the Northwind example database that is available in an out of the box installation of MS SQL Server 2000, and MS Access database products. This is shown in the next figure.

The various tables used in constructing NewOlap are shown in the Schema. The Schema of a cube is the various tables used in configuring the cube. This Schema is different from the OLTP schema.

There are two types of schemas common in OLAP data. One is called the Star schema, wherein the measures table relates directly to each of the existing dimension tables. The other type, called the Snowflake schema, which may include tables not directly accessible by the measures tables, but through another dimension. The MyFirstCube used in the previous tutorial must have been correctly labeled, snowflake, rather than star. The next figure shows the Schema of MyFirstCube in the Analysis Server's Editor window.

As data is added to the database, the cube data needs to be refreshed. Thus, it is necessary to process the data. The control that is used to process can be accessed by right clicking the Cube and clicking on the Process.. menu item from the pop-up contextual menu. For this to work correctly the backend SQL Server containing the OLTP data must be running. When the process is run successfully, the following process monitoring window shows up. Depending upon the amount of data to be processed, and the hardware resources available, it may take a certain amount of time.

Using VB to Explore the OLAP Cube: Creating a VB Project

Create a standard vb executable project and save it under a suitable name. Here it is called ExploreOlap as shown in this picture.

Add library References

In order to connect to the OLAP server, you need to add references to the Microsoft ActiveX Data Objects 2.6 (recent versions include up to 2.8) library, which should add the msado2.6.tlb (the same as msado15.dll). In order to access the OLAP objects, reference to the Microsoft ActiveX Data Objects (multi-dimensional) 2.6 library should be made, which adds the msadomd.dll to the project as shown below.

At this point it will be very instructive to look at VB's Object Browser as shown in the next picture. The structure of the cube is listed when we access the ADOMD library.

Storing Global Variables in the module

As several of the properties are going to be reviewed programmatically, it will be proper to declare the variables at the module level. Add a module and insert the following declarations using the key word NEW. These variables can be used in the forms to access the properties of the Catalog, the Dimensions, the Level, the Hierarchies, and the Members.

Option Explicit
Public oCn As New ADODB.Connection
Public oCat As New ADOMD.Catalog
Public ocube As ADOMD.CubeDef
Public ocdim As ADOMD.Dimension
Public oprop As ADODB.Properties
Public oLvl As ADOMD.Level
Public ohr As ADOMD.Hierarchy
Public oMem As ADOMD.Members

Properties of the Cube: Properties of Dimensions

A cube is constructed from dimensions and measures, as mentioned earlier. Cubes store data derived from relational data and contain categorized data useful in analysis. These categories are the dimensions, and should be distinct. If the data type in the dimension is of type time, it is called a Time dimension; otherwise, it is a Standard dimension.

The following code will bring out all the properties of the dimensions. Notice that the connection is of the type ADODB. The connection string is simple, containing the provider, the SQL Server data, and the Initial Catalog. The ocube is the cube object described in the catalog. This code counts the number of dimensions (4); lists the various dimensions (Measures, MyOrders, MyProducts, MyCustomers, MyOrders); and lists the various properties (17) of the Measures.

Private Sub Command1_Click()
'Open a connection by providing a 
'connection string
oCn.Open "Provider=MSOLAP;Data Source=NECHOST; & _
Initial Catalog=NewOlap;"
'Catalog's active connection is established
Set oCat.ActiveConnection = oCn
'Cube is defined by catalog's CubeDef()method
Set ocube = oCat.CubeDefs(0)
'Cube's dimension is defined
Set ocdim = oCat.CubeDefs(0).Dimensions(0)
'number of dimensions in the cube counted
Debug.Print ocube.Dimensions.Count
Dim ccnt As Integer
ccnt = ocube.Dimensions.Count
Dim j
For j = 0 To ccnt - 1
Debug.Print "------"
'name of dimension printed
Debug.Print ocube.Dimensions(j).Name
Next j
'dimensions properties are counted
Debug.Print ocdim.Properties.Count
Dim cnt
cnt = ocdim.Properties.Count
Dim i
For i = 0 To cnt - 1
'Dimenion's Name, Type, and value are printed
Debug.Print ocdim.Properties(i).Name & "," & _
ocdim.Properties(i).Type & "," & _
ocdim.Properties(i).Value
Next
End Sub

The above code accesses the properties of the shared dimensions of the cube, as shown in this picture.

The result of running the above procedure produces the following the properties of the Measures. A similar result is obtained for the other dimensions as well.

 4 
------
Measures same as, MyOrderDetails in the above picture
------
MyCustomers
------
MyOrders
------
MyProducts
17 
CATALOG_NAME,202,NewOlap
SCHEMA_NAME,202,
CUBE_NAME,202,MyFirstCube
DIMENSION_NAME,202,Measures
DIMENSION_UNIQUE_NAME,202,[Measures]
DIMENSION_GUID,72,
DIMENSION_CAPTION,202,Measures
DIMENSION_ORDINAL,19,0
DIMENSION_TYPE,2,2
DIMENSION_CARDINALITY,19,3
DEFAULT_HIERARCHY,202,[Measures]
DESCRIPTION,202,
IS_VIRTUAL,11,False
IS_READWRITE,11,False
DIMENSION_UNIQUE_SETTINGS,3,0
DIMENSION_MASTER_UNIQUE_NAME,202,
DIMENSION_IS_VISIBLE,11,True

A hierarchy is the set of members in a dimension and their position relative to one another. The following code loops through the various properties of the hierarchies. The first dimension is the Measures and the properties of the first of its hierarchy are looped through.

Private Sub Command2_Click()
oCn.Open "Provider=MSOLAP;Data Source=NECHOST; & _
Initial Catalog=NewOlap;"
Set oCat.ActiveConnection = oCn
Set ocube = oCat.CubeDefs(0)
Set ocdim = oCat.CubeDefs(0).Dimensions(0)
Set ohr = ocube.Dimensions(0).Hierarchies(0)
Dim chrt As String
'the name of the first dimension
chrt = ocube.Dimensions(0).Name
Debug.Print chrt & vbCrLf & "-------"
Dim chrhr As String
chrhr = ohr.Name
Debug.Print chrhr
Debug.Print "-----"
Dim cnt
cnt = ocube.Dimensions(0).Hierarchies(0).Properties.Count
Debug.Print cnt
Dim i
For i = 0 To cnt - 1
Debug.Print ocube.Dimensions(0).Hierarchies(0). & _
; Properties(i).Name & "," _
; ocube.Dimensions(0).Hierarchies(0).Properties(i).Type & "," _
; ocube.Dimensions(0).Hierarchies(0).Properties(i).Value
Next i
End Sub

The result of running this code is shown next.

Measures
-------
-----
21 
CATALOG_NAME,202,NewOlap
SCHEMA_NAME,202,Null
CUBE_NAME,202,MyFirstCube
DIMENSION_UNIQUE_NAME,202,[Measures]
HIERARCHY_NAME,202,Null
HIERARCHY_UNIQUE_NAME,202,[Measures]
HIERARCHY_GUID,72,Null
HIERARCHY_CAPTION,202,Measures
DIMENSION_TYPE,2, 2 
HIERARCHY_CARDINALITY,19, 3 
DEFAULT_MEMBER,202,[Measures].[Unit Price]
ALL_MEMBER,202,Null
DESCRIPTION,202,Null
STRUCTURE,2, 0 
IS_VIRTUAL,11,False
IS_READWRITE,11,False
DIMENSION_UNIQUE_SETTINGS,3, 0 
DIMENSION_MASTER_UNIQUE_NAME,202,Null
DIMENSION_IS_VISIBLE,11,True
HIERARCHY_ORDINAL,19, 0 
DIMENSION_IS_SHARED,11,False

The following code accesses the member properties of the Measures[Dimensions(0)].

Private Sub Command3_Click()
oCat.ActiveConnection = "Provider=MSOLAP; " & _
"Data Source=NECHOST; Initial Catalog=NewOlap;"
Set oMem = oCat.CubeDefs(0).Dimensions(0).Hierarchies(0).Levels(0).Members
Debug.Print oCat.CubeDefs(0).Dimensions(0).Name
Dim cnt
cnt = oMem.Count
Debug.Print cnt
Dim j
For j = 0 To cnt - 1
Debug.Print oMem.Item(j).Properties(j).Name & "," _
; oMem.Item(j).Properties(j).Type & "," _
; oMem.Item(j).Properties(j).Value & "," _
; oMem.Item(j).Caption & "," _
; oMem.Item(j).LevelName
Next j
End Sub

The result of running this code is shown next. There are four items for which the New Calculated Member is not a data member (Is_DataMember is False). Please refer to the Schema shown earlier. The members of the Measures are shown in the next picture.

Measures
4 
EXPRESSION,202,,Unit Price,[Measures].[MeasuresLevel]
MEMBER_KEY,202,,Quantity,[Measures].[MeasuresLevel]
IS_PLACEHOLDERMEMBER,11,False,Discount,[Measures].[MeasuresLevel]
IS_DATAMEMBER,11,False,New Calculated Member,[Measures].[MeasuresLevel]

The same code, now run for Dimension(2) returns the following. All MyOrders is a system-generated member of the (All). Being system generated it is not in the MyOrders dimension table.

MyOrders
1
EXPRESSION,202,,All MyOrders, [MyOders].[(All)]

Properties of Levels

The following code accesses the levels in the dimensions of the cube.

oCn.Open "Provider=MSOLAP;Data Source=NECHOST; Initial Catalog=NewOlap;"
Set oCat.ActiveConnection = oCn
Dim strAll As String
strAll = ""
Text1.Text = "Name of Catalog: " & oCat.Name
strAll = oCat.Name
Text2.Text = "Name of Active Connection: " & vbCrLf & oCat.ActiveConnection
strAll = strAll & "," & oCat.ActiveConnection
Debug.Print strAll
Set ocube = oCat.CubeDefs("MyFirstCube")
Dim strDim As String
For Each ocdim In ocube.Dimensions
Debug.Print strDim & ocdim.Name & "," & 
ocdim.Hierarchies(0).Levels(0).Members(0).LevelName
'Debug.Print ocdim.Name
List1.AddItem ocdim.Name
List1.AddItem ocdim.Hierarchies(0).Levels(0).Members(0).LevelName
Next ocdim

The result of running the above code produces the following output:

NewOlap,Provider=MSOLAP.2;Data Source=NECHOST;
Initial Catalog=NewOlap;Client Cache Size=25;Auto Synch Period=10000
Measures,[Measures].[MeasuresLevel]
MyCustomers,[MyCustomers].[(All)]
MyOrders,[MyOrders].[(All)]
MyProducts,[MyProducts].[(All)]

The objects and collections investigated in this tutorial are shown in the next picture. This is a partial view of the ADOMD's API. The positional information of Members in a cell will be investigated in the next tutorial.