We are processing cubes Two methods using SSIS script Task. Which one is better.

  • Hi All,

    We are processing cubes two methods using script task.

    we are following below two methods.

    Method 1: Method 1 is cubes are processing All in One time only

    Method 2: Method 2 is cubes are processing one after one cube.

    Which one is better. and which one faster below two methods.

    Please let me know

    Method 1:

    sXMLA = sXMLA + "<Batch ProcessAffectedObjects=^true^ xmlns=^http://schemas.microsoft.com/analysisservices/2003/engine^>"

    sXMLA = sXMLA + " <Parallel>"

    sXMLA = sXMLA + " <Process xmlns:xsd=^http://www.w3.org/2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/XMLSchema-instance^ xmlns:ddl2=^http://schemas.microsoft.com/analysisservices/2003/engine/2^ xmlns:ddl2_2=^http://schemas.microsoft.com/analysisservices/2003/engine/2/2^ xmlns:ddl100_100=^http://schemas.microsoft.com/analysisservices/2008/engine/100/100^ xmlns:ddl200=^http://schemas.microsoft.com/analysisservices/2010/engine/200^ xmlns:ddl200_200=^http://schemas.microsoft.com/analysisservices/2010/engine/200/200^>" & vbCrLf

    sXMLA = sXMLA + " <Object>" & vbCrLf

    sXMLA = sXMLA + " <DatabaseID>" & sDatabaseID & "</DatabaseID>" & vbCrLf

    sXMLA = sXMLA + " </Object>" & vbCrLf

    sXMLA = sXMLA + " <Type>ProcessFull</Type>" & vbCrLf

    sXMLA = sXMLA + " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & vbCrLf

    sXMLA = sXMLA + " </Process>" & vbCrLf

    sXMLA = sXMLA + " </Parallel>" & vbCrLf

    sXMLA = sXMLA + "</Batch>" & vbCrLf

    sXMLA = Replace(sXMLA, "^", """")

    SaveTextToFile(sXMLA.ToString(), sXMLA.ToString())

    SaveTextToFile("################################## " & sDatabaseID & " Cubes Started ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    errMsg = strValCube & ". Processing Cubes Started at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    objServer.Execute(sXMLA)

    errMsg = strValCube & ". Processed Cubes End at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    SaveTextToFile("####

    Method 2:

    For Each oCube In oDB.Cubes

    If oCube.Name <> "EquipmentUtilization" Then

    'If oCube.Name = "AssetOrders" Then

    Dim intLoopIndex As Integer

    Dim intLoop As Integer

    intLoop = oCube.Dimensions.Count()

    For intLoopIndex = 0 To oCube.Dimensions.Count - 1

    'System.Console.WriteLine("Hello from Visual Basic")

    sDimID = oCube.Dimensions.Item(intLoopIndex).DimensionID

    ' MsgBox(sDimID)

    If oCube.Dimensions.Item(intLoopIndex).Dimension.State = Microsoft.AnalysisServices.AnalysisState.Processed Then

    sProcessType = "ProcessUpdate"

    Else

    sProcessType = "ProcessFull"

    End If

    sXMLA = ""

    sXMLA = sXMLA + "<Batch xmlns=^http://schemas.microsoft.com/analysisservices/2003/engine^>" & vbCrLf

    sXMLA = sXMLA + " <Parallel>" & vbCrLf

    sXMLA = sXMLA + " <Process xmlns:xsd=^http://www.w3.org/2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/XMLSchema-instance^>" & vbCrLf

    sXMLA = sXMLA + " <Object>" & vbCrLf

    sXMLA = sXMLA + " <DatabaseID>" & sDatabaseID & "</DatabaseID>" & vbCrLf

    sXMLA = sXMLA + " <DimensionID>" & sDimID & "</DimensionID>" & vbCrLf

    sXMLA = sXMLA + " </Object>" & vbCrLf

    sXMLA = sXMLA + " <Type>" & sProcessType & "</Type>" & vbCrLf

    sXMLA = sXMLA + " <WriteBackTableCreation>UseExisting</WriteBackTableCreation>" & vbCrLf

    sXMLA = sXMLA + " </Process>" & vbCrLf

    sXMLA = sXMLA + " </Parallel>" & vbCrLf

    sXMLA = sXMLA + "</Batch>" & vbCrLf

    sXMLA = Replace(sXMLA, "^", """")

    SaveTextToFile(sXMLA.ToString(), sXMLA.ToString())

    SaveTextToFile(strVal & ". " & sDimID & ":" & sProcessType & " -- Dimension Start Time:" & DateTime.Now.ToString() & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    objServer.Execute(sXMLA)

    SaveTextToFile(strVal & ". " & sDimID & ":" & sProcessType & " -- Dimension End Time:" & DateTime.Now.ToString() & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    strVal += 1

    Next intLoopIndex

    For Each oMeasureGroup In oCube.MeasureGroups

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile("################################## " & oMeasureGroup.Name & " MeasureGroups Started ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    errMsg = strValCube & ". MeasureGroups " & oMeasureGroup.Name & " Started at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    oMeasureGroup.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ' Process all measure groups

    errMsg = strValCube & ". MeasureGroups " & oMeasureGroup.Name & " End at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    SaveTextToFile("################################## " & oMeasureGroup.Name & " MeasureGroups Completed ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    For Each oPartition In oMeasureGroup.Partitions

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile("################################## " & oPartition.Name & " Partition Started ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    errMsg = strValCube & ". Partition " & oPartition.Name & " Started at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    oPartition.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull) ' Process all partitions

    errMsg = strValCube & ". Partition " & oPartition.Name & " End at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    SaveTextToFile("################################## " & oPartition.Name & " Partition Completed ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    Next

    Next

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile("################################## " & oCube.Name & " Cube Started ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    errMsg = strValCube & ". Processing Cube " & oCube.Name & " Started at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    oCube.Process(Microsoft.AnalysisServices.ProcessType.ProcessFull)

    errMsg = strValCube & ". Processed Cube " & oCube.Name & " End at " & DateTime.Now.ToString & vbCrLf

    SaveTextToFile(errMsg, strErrorFilePath, errMsg)

    SaveTextToFile("################################## " & oCube.Name & " Cube Completed ####################################" & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    SaveTextToFile(" " & vbCrLf, strErrorFilePath, "DIM" & sDimID & vbCrLf)

    strValCube += 1

    End If 'EquipmentUtilization

    oCube.Refresh()

    Next

    Regards,

    Karunkar

  • Wouldn't you already know which was faster?

    I do not know which is Better, as that could have much to do with your environment.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply