Exploring OLAP Cube with Visual Basic - Properties of Hierarchies
(Page 5 of 6 )
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
Properties of Members
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)]
Next: Properties of Levels >>
More Database Articles
More By Jayaram Krishnaswamy
