io Programmo 32/IOPROG_32.ISO/SOFT/SqlEval7/MSOLAP/samples/Samples.exe/VbDSOExample/AdvancedSampleCode.bas

BASIC Source File | 1998-10-30 | 22.5 KB | 636 lines

Attribute VB_Name = "AdvancedSampleCode"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' AdvancedSampleCode.bas - Contains the advanced functions for maintaining a cube.
'
' (C)Copyright 1998, Microsoft Corporation. All rights reserved.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Option Explicit
Option Compare Text


'
'   CreateCalculatedMember - create a calculated measure called Average Sale
'                            Average Sale = Store Sales / Sales Count
'
Public Sub CreateCalculatedMember()
    ' lock the cube for writing
    ' so that no other application can modify it
    On Error GoTo Err_Lock
    m_dsoCube.LockObject olaplockWrite, _
                         "DSOSample is adding calculated member to the cube"

    ' create the new calculated member
    On Error GoTo Err_Create
    Dim dsoCalculatedMember As DSO.Command
    Set dsoCalculatedMember = m_dsoCube.Commands.AddNew("Average Sale")

    ' set the command type
    dsoCalculatedMember.CommandType = cmdCreateMember

    ' set the MDX statement that defines the calculated member
    dsoCalculatedMember.Statement = _
            "CREATE MEMBER Sales.Measures.[Average Sale] AS " & _
                "'[Measures].[Store Sales]/[Measures].[Sales Count]', " & _
                "FORMAT_STRING = 'Currency'"

    ' save the cube definition in the metadata repository
    ' the calculated member will be saved as a part of the cube
    On Error GoTo Err_Update
    m_dsoCube.Update

    ' unlock the cube
    m_dsoCube.UnlockObject

Exit Sub

Err_Lock:
    ' Failed to lock the cube
    ' Possible reasons:
    '   - the cube is already locked by another application
    '   - the machine on which OLAP server is running is not reachable

Err_Create:
    ' Failed to create the calculated member.
    ' Possible reasons:
    '   - the metadata repository is unreachable

Err_Update:
    ' Failed to persist the cube definition in the metadata repository
    ' Possible reasons:
    '   - the metadata repository is unreachable
    '       you can see where the metadata repository resides by looking
    '       up the following registry entry:
    '       HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server Connection Info
    '           Repository Connection String

    ' unlock the cube
    m_dsoCube.UnlockObject

    MsgBox "Create calculated member failed" & vbCrLf & Err.Description

End Sub


'
'   CreateVirtualDimension - create a virtual dimension based on
'                            Store dimension
'
Public Sub CreateVirtualDimension()
    ' get the reference to the Store dimension
    Dim dsoStoreDim As DSO.Dimension
    Set dsoStoreDim = m_dsoDatabase.Dimensions("Store")

    ' lock the Store dimension for reading
    ' so that no other application can modify it
    On Error GoTo Err_Lock
    dsoStoreDim.LockObject olaplockRead, _
                           "DSOSample is creating a virtual dimension based on Store"

    ' create the new virtual dimension in the
    ' database's Dimensions collection
    On Error GoTo Err_Create
    Dim dsoVirtualDimension As DSO.Dimension
    Set dsoVirtualDimension = m_dsoDatabase.Dimensions.AddNew("Store Type")

    ' lock the dimension so that no app can alter it
    On Error GoTo Err_Lock
    dsoVirtualDimension.LockObject olaplockWrite, _
                                   "DSOSample is setting properties of the virtual dimension"

    '
    ' create dimension levels
    '
    Dim dsoLevel As DSO.Level

    '
    ' create the All level
    '   the All level is the top-most level in the dimension hierarcy
    On Error GoTo Err_Create
    Set dsoLevel = dsoVirtualDimension.Levels.AddNew("All")

    ' set the level type
    dsoLevel.LevelType = levAll

    ' the All level has only one member
    ' set the MemberKeyColumn of the All level to a constant
    ' this constant is at the same time the name of that single member
    dsoLevel.MemberKeyColumn = "All Store Types"

    '
    ' create the virtual level
    '   the virtual level is based on a member property from the
    '   source dimension
    On Error GoTo Err_Create
    Set dsoLevel = dsoVirtualDimension.Levels.AddNew("Store Type", sbclsVirtual)

    ' members of the virtual level come from the source dimension
    ' MemberKeyColumn of a virtual level has the following format:
    '   [<dim name>].[<level name>].[<member property name>]
    dsoLevel.MemberKeyColumn = "[Store].[Store Name].[Store Type]"

    ' save the virtual dimension definition in the metadata repository
    On Error GoTo Err_Update
    dsoVirtualDimension.Update

    ' unlock the dimensions
    dsoStoreDim.UnlockObject
    dsoVirtualDimension.UnlockObject


    '
    '   add the virtual dimension to the cube
    '

    ' lock the cube for writing
    On Error GoTo Err_LockCube
    m_dsoCube.LockObject olaplockWrite, _
                         "DSO Sample is adding a dimension to the cube"

    m_dsoCube.Dimensions.AddNew "Store Type"

    ' save the cube definition in the metadata repository
    On Error GoTo Err_UpdateCube
    m_dsoCube.Update

    ' unlock cube
    m_dsoCube.UnlockObject

Exit Sub

Err_Lock:
    ' Failed to lock the dimension
    ' Possible reasons:
    '   - the object is already locked by another application
    '   - the machine on which OLAP server is running is not reachable

Err_Create:
    ' Failed to create the dimension.
    ' Possible reasons:
    '   - the metadata repository is unreachable

Err_Update:
    ' Failed to persist the cube definition in the metadata repository
    ' Possible reasons:
    '   - the metadata repository is unreachable
    '       you can see where the metadata repository resides by looking
    '       up the following registry entry:
    '       HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server Connection Info
    '           Repository Connection String

    ' unlock the dimensions
    dsoStoreDim.UnlockObject
    dsoVirtualDimension.UnlockObject

Err_UpdateCube:
    ' unlock cube
    m_dsoCube.UnlockObject

Err_LockCube:
    ' Failed to lock the cube

    MsgBox "Create virtual dimension failed" & vbCrLf & Err.Description

End Sub


'
'   CreateVirtualCube - create a virtual cube based on the Sales cube
'                       the virtual cube will be a subset of the Sales cube,
'                       i.e. it will only have one measure Store Profit
'
Public Sub CreateVirtualCube()

    ' lock the Sales cube for reading
    ' so that no other application can modify it
    On Error GoTo Err_Lock
    m_dsoCube.LockObject olaplockRead, _
                         "DSOSample is creating a virtual cube based on Sales"

    ' create the new virtual cube in the
    ' database's MDStores collection
    On Error GoTo Err_Create
    Dim dsoVirtualCube As DSO.MDStore
    Set dsoVirtualCube = m_dsoDatabase.MDStores.AddNew("Virtual Sales", sbclsVirtual)

    ' lock the virtual cube so that no app can alter it
    On Error GoTo Err_Lock
    dsoVirtualCube.LockObject olaplockWrite, _
                              "DSOSample is setting properties of the virtual cube"

    '
    ' add the measure to the virtual cube
    '
    Dim dsoMeasure As DSO.Measure
    Set dsoMeasure = dsoVirtualCube.Measures.AddNew("Store Profit")

    ' map this measure to the Sales cube Store Profit
    ' NOTE: the format of virtual cube measure is:
    '       [<cube name>].[<measure name>]
    dsoMeasure.SourceColumn = "[Sales].[Store Profit]"

    '
    ' add the dimensions to the virtual cube
    ' NOTE: with exception of virtual dimensions, all of the
    '       dimensions must be included in the cubes on which virtual
    '       cube measures are based
    dsoVirtualCube.Dimensions.AddNew "Product"
    dsoVirtualCube.Dimensions.AddNew "Time"
    dsoVirtualCube.Dimensions.AddNew "Store"
    dsoVirtualCube.Dimensions.AddNew "Store Type"

    ' save the virtual cube definition in the metadata repository
    On Error GoTo Err_Update
    dsoVirtualCube.Update

    ' unlock the cubes
    m_dsoCube.UnlockObject
    dsoVirtualCube.UnlockObject

Exit Sub

Err_Lock:
    ' Failed to lock the cube
    ' Possible reasons:
    '   - the object is already locked by another application
    '   - the machine on which OLAP server is running is not reachable

Err_Create:
    ' Failed to create the virtual cube.
    ' Possible reasons:
    '   - the metadata repository is unreachable

Err_Update:
    ' Failed to persist the cube definition in the metadata repository
    ' Possible reasons:
    '   - the metadata repository is unreachable
    '       you can see where the metadata repository resides by looking
    '       up the following registry entry:
    '       HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server Connection Info
    '           Repository Connection String

    ' unlock the cubes
    m_dsoCube.UnlockObject
    dsoVirtualCube.UnlockObject

    MsgBox "Create virtual cube failed" & vbCrLf & Err.Description

End Sub


'
'   CreatePrivateDimension - create the private Customer dimension
'
'   NOTE: private dimensions can be used in only one cube
'
Public Sub CreatePrivateDimension()
    ' create the Customer dimension in the database's
    ' Dimensions collection
    ' NOTE: private dimension name contains the name of the cube
    '       and the name of the dimension, separated by '^'
    On Error GoTo Err_Create
    Dim dsoDimension As DSO.Dimension
    Set dsoDimension = m_dsoDatabase.Dimensions.AddNew("Sales^Customer")

    ' lock the dimension so that no app can alter it
    On Error GoTo Err_Lock
    dsoDimension.LockObject olaplockWrite, _
                            "DSOSample is setting properties of the private dimension"

    ' set the dimension's datasource
    Set dsoDimension.DataSource = m_dsoDatabase.DataSources("Foodmart Sample Database")

    ' get the quoting characters from the datasource
    Dim sLQuote As String, sRQuote As String
    sLQuote = dsoDimension.DataSource.OpenQuoteChar
    sRQuote = dsoDimension.DataSource.CloseQuoteChar

    ' set the dimension type
    dsoDimension.DimensionType = dimRegular

    ' set the comma separated list of the dimension tables
    ' NOTE: the tables must be quoted
    ' the Customer dimension uses only the "customer" table
    dsoDimension.FromClause = sLQuote & "customer" & sRQuote

    '
    ' create dimension levels
    '
    Dim dsoLevel As DSO.Level

    '
    ' create the All level
    '   the All level is the top-most level in the dimension hierarcy
    On Error GoTo Err_Create
    Set dsoLevel = dsoDimension.Levels.AddNew("All")

    ' set the level type
    dsoLevel.LevelType = levAll

    ' the All level has only one member
    ' set the MemberKeyColumn of the All level to a constant
    ' this constant is at the same time the name of that single member
    dsoLevel.MemberKeyColumn = "All Customers"

    '
    ' create the Customer Country level
    '   the Customer Country level contains all of the countries
    On Error GoTo Err_Create
    Set dsoLevel = dsoDimension.Levels.AddNew("Customer Country")

    ' set the level type
    dsoLevel.LevelType = levRegular

    ' define which column contains the level member keys
    ' sLQuote and sRQuote are obtained from dsoDimension.Datasource
    ' NOTE: the tables and columns must be quoted using the quoting
    '       characters obtained from the dimension's datasource
    dsoLevel.MemberKeyColumn = sLQuote & "customer" & sRQuote & "." & _
                               sLQuote & "country" & sRQuote

    ' tell DSO how many members this level has
    ' this information will be used during the design of aggregations
    '   when determining the optimal set of aggregations
    ' there are 3 countries in the "customer" table: Mexico, Canada, and USA
    dsoLevel.EstimatedSize = 3

    ' this level contains unique members
    dsoLevel.IsUnique = True

    ' DSO needs to know what is the type and maximum size of the members keys
    ' NOTE: the ColumnSize property needs to be set only for
    '       levels that have string members
    dsoLevel.ColumnType = adChar
    dsoLevel.ColumnSize = 6         ' the longest members ("Mexico", "Canada")
                                    ' can fit in 6 characters

    ' specify how should the level members be ordered
    ' we want the countries ordered by Name
    dsoLevel.Ordering = orderName

    '
    ' create the Customer State level
    '   the Customer State level contains all of the states
    On Error GoTo Err_Create
    Set dsoLevel = dsoDimension.Levels.AddNew("Customer State")

    ' set the level type
    dsoLevel.LevelType = levRegular

    ' "customer_state" column contains members for this level
    dsoLevel.MemberKeyColumn = sLQuote & "customer" & sRQuote & "." & _
                               sLQuote & "state_province" & sRQuote

    ' there are 13 distinct states in the "customer" table
    dsoLevel.EstimatedSize = 13

    ' this level contains unique members,
    ' i.e. no state appears in more than one country
    dsoLevel.IsUnique = True

    ' DSO needs to know what is the type and maximum size of the members keys
    dsoLevel.ColumnType = adChar
    dsoLevel.ColumnSize = 9             ' the longest member ("Zacatecas")
                                        ' can fit in 9 characters

    ' we want the states ordered by Name
    dsoLevel.Ordering = orderName

    '
    ' create the Customer City level
    '   the Customer City level contains all of the cities
    On Error GoTo Err_Create
    Set dsoLevel = dsoDimension.Levels.AddNew("Customer City")

    ' set the level type
    dsoLevel.LevelType = levRegular

    ' "customer_city" column contains members for this level
    dsoLevel.MemberKeyColumn = sLQuote & "customer" & sRQuote & "." & _
                               sLQuote & "city" & sRQuote

    ' there are 109 distinct cities in the "customer" table
    dsoLevel.EstimatedSize = 109

    ' this level does not contains unique members
    dsoLevel.IsUnique = False

    ' DSO needs to know what is the type and maximum size of the members keys
    dsoLevel.ColumnType = adChar
    dsoLevel.ColumnSize = 20            ' the longest city in the table
                                        ' can fit in 20 characters

    ' we want the cities ordered by Name
    dsoLevel.Ordering = orderName

    ' save the dimension definition in the metadata repository
    On Error GoTo Err_Update
    dsoDimension.Update

    ' unlock the dimension
    dsoDimension.UnlockObject


    '
    '   add the private dimension to the cube
    '

    ' lock the cube for writing
    On Error GoTo Err_LockCube
    m_dsoCube.LockObject olaplockWrite, _
                         "DSO Sample is adding a dimension to the cube"

    ' add the private dimension to the cube
    m_dsoCube.Dimensions.AddNew "Sales^Customer"

    ' update the cube's FromClause and JoinClause
    m_dsoCube.FromClause = m_dsoCube.FromClause & ", " & sLQuote & "customer" & sRQuote
    m_dsoCube.JoinClause = m_dsoCube.JoinClause & " AND " & _
                "(" & _
                    sLQuote & "sales_fact_1998" & sRQuote & "." & sLQuote & "customer_id" & sRQuote & _
                    " = " & _
                    sLQuote & "customer" & sRQuote & "." & sLQuote & "customer_id" & sRQuote & _
                ")"

    ' save the cube definition in the metadata repository
    On Error GoTo Err_UpdateCube
    m_dsoCube.Update

    ' unlock cube
    m_dsoCube.UnlockObject

Exit Sub

Err_Create:
    ' Failed to create the object.
    ' Possible reasons:
    '   - the DSO database object is being locked by another DSO application
    '       a DSO object is locked while AddNew method is executing on
    '       one of its collections (Datasources, MDCustomers, Dimensions...)
    '   - the metadata repository is unreachable

Err_Lock:
    ' Failed to lock the dimension
    ' Possible reasons:
    '   - the object is already locked by another application
    '   - the machine on which OLAP server is running is not reachable

Err_Update:
    ' Failed to persist the dimension definition in the metadata repository
    ' Possible reasons:
    '   - the metadata repository is unreachable
    '       you can see where the metadata repository resides by looking
    '       up the following registry entry:
    '       HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server Connection Info
    '           Repository Connection String
    '   - the DSO dimension object is being locked by another DSO application
    '       it is not possible for two DSO apps to persist the same object
    '       at the same time
    '       it is not possible to persist a DSO object, it another DSO app
    '       has explicitly locked it

Err_UpdateCube:
    ' unlock cube
    m_dsoCube.UnlockObject

Err_LockCube:
    ' Failed to lock the cube

    MsgBox "Create private dimension Customer failed" & vbCrLf & Err.Description
End Sub


'
'   CreatePartition - create new partition in the Sales cube
'                     use "sales_fact_1997" as the partition's fact table
'
Public Sub CreatePartition()
    ' lock the cube for writing
    ' so that no other application can modify it

    On Error GoTo Err_Lock
    m_dsoCube.LockObject olaplockWrite, _
                         "DSOSample is adding new partition to the cube"

    ' NOTE: we do not have to lock the partition after adding it to the cube
    '       by adding it to the locked cube, the partition is also locked

    ' create the new partition
    On Error GoTo Err_Create
    Dim dsoPartition As DSO.MDStore
    Set dsoPartition = m_dsoCube.MDStores.AddNew("Sales 1997")

    ' NOTE: since the partition will be using the same datasource as the cube,
    '       we don't have to set the partition's datasource, it is by default
    '       the same as the cube's

    ' get the quoting characters from the datasource
    Dim sLQuote As String, sRQuote As String
    sLQuote = dsoPartition.DataSources(1).OpenQuoteChar
    sRQuote = dsoPartition.DataSources(1).CloseQuoteChar

    ' set the partition fact table
    dsoPartition.SourceTable = sLQuote & "sales_fact_1997" & sRQuote

    ' set the number of records in the fact table that will be loaded into
    ' the cube
    dsoPartition.EstimatedRows = 86837

    ' set the storage mode
    ' this partition will be ROLAP 0 aggregations
    dsoPartition.OlapMode = olapmodeRolap

    ' set the prefix for the aggregation tables
    dsoPartition.AggregationPrefix = "Sales1997_"

    ' set the partition's FromClause and JoinClause
    dsoPartition.FromClause = StringReplace(m_dsoCube.FromClause, _
                                            m_dsoCube.SourceTable, _
                                            dsoPartition.SourceTable)

    dsoPartition.JoinClause = StringReplace(m_dsoCube.JoinClause, _
                                            m_dsoCube.SourceTable, _
                                            dsoPartition.SourceTable)

    ' if you want to limit the records from the partition's fact table
    ' that are to be loaded into the partition, you can use the
    ' SourceTableFilter property.
    ' Ex: dsoPartition.SourceTableFilter = "sales_fact_1997"."time_id" = <value>"

    ' set the partition data slice
    ' the data slice is a hint for the server
    ' the server will not bother sending a query to this partition
    '   if it notices that based on the data slice, the query does
    '   not require any data from this partition
    ' set the slice to year 199
    ' the slice value is the member key, not member name
    Dim dsoPartitionDimension As DSO.Dimension
    Dim dsoPartitionLevel As DSO.Level
    Set dsoPartitionDimension = dsoPartition.Dimensions("Time")
    Set dsoPartitionLevel = dsoPartitionDimension.Levels("All")
    dsoPartitionLevel.SliceValue = "All Years"
    Set dsoPartitionLevel = dsoPartitionDimension.Levels("Year")
    dsoPartitionLevel.SliceValue = "1997"

    ' save the partition definition in the metadata repository
    On Error GoTo Err_Update
    dsoPartition.Update

    ' unlock the cube
    m_dsoCube.UnlockObject

Exit Sub

Err_Lock:
    ' Failed to lock the cube
    ' Possible reasons:
    '   - the cube is already locked by another application
    '   - the machine on which OLAP server is running is not reachable

Err_Create:
    ' Failed to create the partition.
    ' Possible reasons:
    '   - the metadata repository is unreachable

Err_Update:
    ' Failed to persist the partition definition in the metadata repository
    ' Possible reasons:
    '   - the metadata repository is unreachable
    '       you can see where the metadata repository resides by looking
    '       up the following registry entry:
    '       HKEY_LOCAL_MACHINE\Software\Microsoft\OLAP Server\Server Connection Info
    '           Repository Connection String
    '   - the DSO datasource object is being locked by another DSO application
    '       it is not possible for two DSO apps to persist the same object
    '       at the same time
    '       it is not possible to persist a DSO object, it another DSO app
    '       has explicitly locked it


    ' unlock the cube
    m_dsoCube.UnlockObject

    MsgBox "Create partition failed" & vbCrLf & Err.Description

End Sub


'
'   StringReplace - helper function for updating partition properties
'
Public Function StringReplace(Source As String, search As String, replace As String) As String
    Dim start As Integer
    Dim pos As String
    Source = " " & Source & " "
    start = 1
    While InStr(start, UCase(Source), UCase(search)) > 0
        pos = InStr(start, UCase(Source), UCase(search))
        Source = Left(Source, pos - 1) & replace & Mid(Source, pos + Len(search))
        start = pos + Len(replace)
    Wend
    StringReplace = Trim(Source)
End Function