All,

I have a large (275,000 row) changing dimension which I'm incrementally
updating overnight once a week, and the update works fine, and all lazy
aggregation is complete when I arrive on Monday.

But I would like to test to see how long these flexible aggregations
actually take to update in production, so I can manage the update better and
maybe change to a Biweekly update.

I've tried this script, but it always tells me that the cube is complete,
even when I see MSMDSRV.EXE still pulling 60% CPU usage.

Thanks,

Jim

-----------------------------------------------------------------------------------------------

'File: LazyProcessing.vbs
Option Explicit
'/************************************************** *******************
' File: LazyProcessing.vbs
'Desc: This sample script displays the lazy aggregator's
'progress for a specified cube (all partitions)
'
' Parameters: None
'************************************************* ********************
Call GetLazyProcessing
'************************************************* ********************
' Helper functions
Function ConvertState(dsoState)
Const olapStateNeverProcessed = 0
Const olapStateStructureChanged = 1
Const olapStateMemberPropertiesChanged = 2
Const olapStateSourceMappingChanged = 3
Const olapStateCurrent = 4
Select Case dsoState
Case olapStateCurrent
ConvertState = "Current"
Case olapStateMemberPropertiesChanged
ConvertState = "Properties changed"
Case olapStateNeverProcessed
ConvertState = "Never processed"
Case olapStateSourceMappingChanged
ConvertState = "Source mapping changed"
Case olapStateStructureChanged
ConvertState = "Structure changed"
Case Else
ConvertState = "Unknown state"
End Select
End Function
Sub GetLazyProcessing()
Dim bResult
Dim strMsg
If Wscript.Arguments.Count <> 3 Then
Msgbox "Invalid number of arguments. This script must be called with three
arguments." & VbCRLF & VbCRLF & "Usage is: (DOS prompt) CScript
LazyProcessing.vbs 'Server' 'Db' 'Cube'" & VbCRLF & "e.g. CScript
LazyProcessing.vbs ""Localhost"" ""Foodmart 2000"" ""HR"" ", , "Invalid
LazyProcessing calling arguments"
Exit Sub
End If
Dim sServer : sServer = Wscript.Arguments(0)
Dim sDb : sDb = Wscript.Arguments(1)
Dim sCube : sCube = Wscript.Arguments(2)
bResult = LazyProcessing(sServer,sDb, sCube, strMsg)
If bResult Then
Msgbox strMsg, , "Get Lazy Processing information"
Else
Msgbox "Error-" & strMsg, , "Error - Get Lazy Processing information"
End If
End Sub
'************************************************* ********************
' The real work . . .
Function LazyProcessing(strAnalysisServer, strOlapDb, strCube, strMsg)
Dim dsoServer : Set dsoServer = CreateObject("DSO.Server")
Dim dsoDB, dsoCube, dsoPartition
LazyProcessing = False ' assume we fail (strMsg will contain the error
text)
strMsg = ""
' VBScript does not support direct use of enumerated constants.
' However, constants can be defined to supplant enumerations.
Const olapStateCurrent = 4
' Connect to the Analysis server.
On Error Resume Next
dsoServer.Connect strAnalysisServer
' If connection failed, then end the script.
If Err.Number <> 0 Then
strMsg = Err.Description
Err.Clear
Exit Function
End if
On Error Goto 0
' Find the database on the server.
If (dsoServer.mdStores.Find(strOlapDB)) = 0 Then
strMsg = "Database '" & strOlapDB & "' not found on '" &
strAnalysisServer & "'."
Err.Clear
Exit Function
End If
Set dsoDB = dsoServer.mdStores(strOlapDB)
' Find the cube.
If (dsoDB.mdStores.Find(strCube)) = 0 then
strMsg = "Cube '" & strCube & "' not found in database '" &
strOlapDB & "'."
Err.Clear
Exit Function
End If
Set dsoCube = dsoDB.MDStores(strCube)
' Validate the state of the cube
if dsoCube.State <> olapStateCurrent Then
strMsg = " Cube '" & strCube & "' state is: " &
ConvertState(dsoCube.State) & VbCRLF
strMsg = strMsg & " Which cannot be checked." & VbCRLF
Err.Clear
Exit Function
End If
' Loop through each partition in the cube
strMsg = ""
For Each dsoPartition in dsoCube.Partitions
' Only check if the partition's state is current
' and, then, if lazy processing is ongoing.
' Normally, since the lazy aggregator is single threaded,
' only one partition is being processed at a time,
' but we won't assume that at this point.
If dsoPartition.State = olapStateCurrent Then
If dsoPartition.LazyOptimizationProgress <> 100 Then
' We are in-progress -- output the % complete
strMsg = strMsg & " Partition: " & dsoPartition.Name & " is " _
& CStr(dsoPartition.LazyOptimizationProgress) & "% complete." & vbCRLF
End If
End If
Next
If Len(strMsg) = 0 Then
strMsg = "Cube: " & dsoCube.Name & " is complete." & vbCRLF
Else
strMsg = "Cube: " & dsoCube.Name & vbCRLF & strMsg
End If
LazyProcessing = True ' we succeeded !
Set dsoCube = Nothing
Set dsoDB = Nothing
dsoServer.CloseServer
Set dsoServer = Nothing
End Function

Good question. I wrote the script and it has worked in several situations.
I have no explanation. Sorry.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Jim_OLAP" <JimOLAP (AT) discussions (DOT) microsoft.com> wrote