Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

We are processing cubes Two methods using SSIS script Task. Which one is better. Expand / Collapse
Author
Message
Posted Wednesday, October 31, 2012 1:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 29, 2013 2:29 AM
Points: 16, Visits: 63
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
Post #1379172
Posted Wednesday, October 31, 2012 10:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 2:51 PM
Points: 285, Visits: 771
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.
Post #1379438
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse