Adding New Measure to OLAP Cube

microsoft.public.sqlserver.programming

2 Sep 2005 5:48 AM

wilsonjust

To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters

Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>

Public Sub main()
    Dim dsoServer As New DSO.Server
    Dim dsoDB As DSO.MDStore
    Dim dsoCube As DSO.MDStore
    Dim dsoMea As DSO.Measure
    Dim dsoAssFactCube As DSO.Cube
    Dim dsoPortAnalyzerCube As DSO.Cube
    'for storing initial command line arguments as entered by user
    Dim strArgs() As String
    'for storing the parsed command line arguments
    Dim ParsedArgs As String
    'for storing the final array of command line arguments
    Dim finalArgs() As String

    'Splitting the command line arguments based on a space
    strArgs = Split(Command$, " ")
    'Parsing the command line arguments to generate the parsed string
    For i = 0 To UBound(strArgs)
        If Len(Trim(strArgs(i))) > 0 Then
            ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
        End If
    Next
    'Splitting the parsed string into final array of arguments
    finalArgs = Split(ParsedArgs, " ")
    'Check for correct number of arguments
    If UBound(finalArgs) < 2 Then
        MsgBox ("Wrong Syntax...." & "or wrong number of
arguments.....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
    Else
        'connect to the server (Analysis Server name)
        dsoServer.Connect (finalArgs(0))
        'Examine whether all necessary components are present (Cube
name)
        If dsoServer.MDStores.Find(finalArgs(1)) = False Then
            GoTo err_no_database
        End If
        'Connect with the data base (CUBE) (Cube name)
        Set dsoDB = dsoServer.MDStores(finalArgs(1))
        If dsoDB.DataSources.Count = 0 Then
            GoTo err_no_datasource
        ElseIf dsoDB.Dimensions.Count = 0 Then
            GoTo err_no_dimensions
        ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
            GoTo err_no_fact_cube
        ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
            GoTo err_no_analyzer
        End If
        'Set the cube table to use
        Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
        Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
        'Specify the name of the new measure
        Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
        'Specify the source column based on which the operation need to
be performed
        'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
        dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
        'The datatype for the column
        dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
        'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
        dsoMea.AggregateFunction = aggCount
        'update the cube
        dsoAssFactCube.Update
        dsoAssFactCube.Process
        'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
        'the characteristics of the measure of the material cubes
        Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
        'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
        'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
        dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
        dsoPortAnalyzerCube.Update
        dsoPortAnalyzerCube.Process
        dsoDB.Process
leave_now:
        UserOLAPUpdate = 0
    ' Exit Function
err_no_database:
    l_errnum = 1
    s_errdesc = "Datenbank konnte nicht gefunden werden!"
    UserOLAPUpdate = vbObjectError + 1
'   Exit Function
err_no_datasource:
    l_errnum = 1
    s_errdesc = "Datenquelle konnte nicht gefunden werden!"
    UserOLAPUpdate = vbObjectError + 2
'   Exit Function
err_no_dimensions:
    l_errnum = 1
    s_errdesc = "Dimensionen konnten nicht gefunden werden!"
    UserOLAPUpdate = vbObjectError + 3
'    Exit Function
err_no_fact_cube:
    l_errnum = 1
    s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
    UserOLAPUpdate = vbObjectError + 4
'    Exit Function
err_no_analyzer:
    l_errnum = 1
    s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
    UserOLAPUpdate = vbObjectError + 5
'    Exit Function
error_handler:
    l_errnum = Err.Number
    s_errdesc = Err.Description
    UserOLAPUpdate = 1      ' although it could be any non-zero value
                            ' to indicate an error
   End If
End Sub