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

BASIC Source File | 1998-10-30 | 8.0 KB | 223 lines

Attribute VB_Name = "WriteBack"
Option Explicit
Option Compare Text


'
'   WriteEnableCube - enables the Sales cube for write back
'
'   Enabling a cube for writeback entails creation of a writeback partition.  Writeback
'   partition is a ROLAP partition without any aggregations.  The source table of the
'   writeback partition is called writeback table.  The writeback information is stored
'   in this table.
'
'   We will create the writeback table in the Foodmart sample database
'
Public Sub WriteEnableCube()
    ' get the cube's datasource
    Dim dsoDatasource As DSO.DataSource
    Set dsoDatasource = m_dsoCube.DataSources(1)

    ' make sure that we are still connected
    If dsoDatasource.IsConnected = False Then
        MsgBox "Cannot create write enable cube. Connection to the datasource cannot be established."
        Exit Sub
    End If

    ' what will be the writeback table name
    Dim sWriteBackTableName As String
    sWriteBackTableName = "Sample_Writeback_Sales"

    ' get the connection to the datasource
    Dim adoConnection As ADODB.Connection
    Set adoConnection = dsoDatasource.Connection

    ' create the writeback partition
    Dim dsoWBPartition As DSO.MDStore
    Set dsoWBPartition = m_dsoCube.MDStores.AddNew("Writeback")

    ' set the writeback flag
    dsoWBPartition.IsReadWrite = True

    ' set the partition's source table
    dsoWBPartition.SourceTable = dsoDatasource.OpenQuoteChar & sWriteBackTableName & dsoDatasource.CloseQuoteChar

    ' set the partition's storage mode
    ' NOTE: the writeback partition should not have any aggregations
    dsoWBPartition.OlapMode = olapmodeRolap

    ' begin transaction on the datasource connection
    adoConnection.BeginTrans

    ' create the writeback table
    ' and map the partition levels and measures to this table
    On Error GoTo Err_CreateWriteBackTable
    CreateWriteBackTable dsoWBPartition, dsoDatasource, adoConnection, sWriteBackTableName

    ' commit the transaction on the datasource
    ' NOTE: the OLAP server uses a different connection to the
    '       relational database, so we need to commit the
    '       changes here
    adoConnection.CommitTrans

    ' save the writeback partition
    dsoWBPartition.Update

    ' process the write back partition
    On Error GoTo Err_Process
    dsoWBPartition.Process processFull

Exit Sub

Err_CreateWriteBackTable:
    ' rollback transaction on the datasource
    adoConnection.RollbackTrans

    ' remove the writeback partition from the cube
    m_dsoCube.MDStores.Remove dsoWBPartition.Name

    MsgBox "Cannot create writeback table. " & Err.Description
    Exit Sub

Err_Process:
    ' remove the writeback table
    adoConnection.Execute "DROP TABLE " & sWriteBackTableName

    ' remove the writeback partition from the cube
    m_dsoCube.MDStores.Remove dsoWBPartition.Name

    MsgBox "Cannot process the writeback partition. " & Err.Description
    Exit Sub
End Sub


'
'   CreateWriteBackTable - create the writeback table
'
'   The writeback table must have one column for each measure
'   and level in the cube.  It also has two columns used by
'   the OLAP server for storing auditing information
'
'   This function creates the table and maps the writeback
'   partition levels and measures to this table
'
Private Sub CreateWriteBackTable(dsoWBPartition As DSO.MDStore, _
                                 dsoDatasource As DSO.DataSource, _
                                 adoConnection As ADODB.Connection, _
                                 TableName As String)

    ' drop the table if it already exists
    On Error Resume Next
    adoConnection.Execute "DROP TABLE " & TableName
    Err.Clear
    On Error GoTo 0

    ' construct the create table statement
    Dim sCreateTable As String
    sCreateTable = "CREATE TABLE " & TableName & " "
    sCreateTable = sCreateTable & " ( "

    ' create two columns required by the OLAP server
    ' for storing auditing information
    sCreateTable = sCreateTable & _
                    "MS_AUDIT_USER varchar(32), " & _
                    "MS_AUDIT_TIME  DateTime"

    ' create a column for each level
    ' since the column names have to be unique
    '   add the level OrdinalPosition to the level name
    Dim dsoPartDimension As DSO.Dimension
    Dim dsoPartLevel As DSO.Level
    Dim sColumnName As String
    For Each dsoPartDimension In dsoWBPartition.Dimensions
        For Each dsoPartLevel In dsoPartDimension.Levels
            If Not dsoPartLevel.LevelType = levAll And _
               Not dsoPartLevel.IsDisabled And _
               Not dsoPartLevel.SubClassType = sbclsVirtual Then
                ' get the column name
                sColumnName = GetColumnName(dsoPartLevel.Name & "_L" & CStr(dsoPartLevel.OrdinalPosition))
                ' update the create table statement
                sCreateTable = sCreateTable & ", " & _
                               sColumnName & " " & GetColumnTypeDefinition(dsoPartLevel.ColumnType, dsoPartLevel.ColumnSize)

                ' map the partition level to this column
                dsoPartLevel.MemberKeyColumn = dsoWBPartition.SourceTable & _
                                               "." & _
                                               dsoDatasource.OpenQuoteChar & sColumnName & dsoDatasource.CloseQuoteChar
            End If
        Next
    Next

    ' create a column for each measure
    ' and map the partition measure to this column
    Dim dsoPartitionMeasure As DSO.Measure
    For Each dsoPartitionMeasure In dsoWBPartition.Measures
        ' get the column name
        sColumnName = GetColumnName(dsoPartitionMeasure.Name)
        ' update the create table statement
        sCreateTable = sCreateTable & ", " & _
                       sColumnName & " " & GetColumnTypeDefinition(dsoPartitionMeasure.SourceColumnType)

        ' map the partition measure to this column
        dsoPartitionMeasure.SourceColumn = dsoWBPartition.SourceTable & _
                                           "." & _
                                           dsoDatasource.OpenQuoteChar & sColumnName & dsoDatasource.CloseQuoteChar
    Next

    ' complete the create table statement
    sCreateTable = sCreateTable & ")"

    ' execute the create table statement
    adoConnection.Execute sCreateTable

End Sub


'
'   GetColumnTypeDefinition - return a string describing the data type
'                             that can be used in create table statement
'
'   NOTE: this function works with Jet provider, other providers have different data type names
'
Private Function GetColumnTypeDefinition(ColumnType As ADODB.DataTypeEnum, Optional ColumnSize As Integer = 50) As String
    Select Case ColumnType
        Case adChar, adVarChar
            GetColumnTypeDefinition = " VARCHAR (" & CStr(ColumnSize) & ") "
        Case adCurrency
            GetColumnTypeDefinition = " CURRENCY "
        Case adInteger
            GetColumnTypeDefinition = " INTEGER "
        Case adSmallInt
            GetColumnTypeDefinition = " SMALLINT "
        Case adSingle
            GetColumnTypeDefinition = " REAL "
        Case adDouble
            GetColumnTypeDefinition = " DOUBLE "
        Case adDBTimeStamp, adDate
            GetColumnTypeDefinition = " DATETIME "
        Case Else
            Debug.Assert False ' there is a new data type
                               ' we need to update this function
    End Select
End Function

'
'   GetColumnName   - create a column name based on the given string
'                     get rid of spaces
'
Private Function GetColumnName(ObjectName As String) As String
    Dim s As String
    Dim c As String
    Dim i As Integer

    For i = Len(ObjectName) To 1 Step -1
        c = Mid(ObjectName, i, 1)
        If c <> " " Then
            ' legal char, use it
            s = c + s
        End If
    Next

    GetColumnName = s
End Function