Just a quick update on this one, as I have got to the point where I can make the TM1 server fall over which whilst an achievement in itself is not exactly what I was looking for...
My originally code above was not connecting to the server since I the scope of the host and server names were not correct and hence coming through blank, something I had fixed but lost when Excel crashed on me...
Code: Select all
Private Sub Form_Load()
Dim sServerName As String
Dim sUserName As String
Dim sPassword As String
Dim hUserHandle As Long, vServerHandle As Long
Dim pPoolHandle As Long, vResult As Long
Dim vPassword As Long, vServerName As Long, vUserName As Long
Dim vStringLength As Long
Dim RetVal As String * 75
Dim pSpreadResult As Long
Dim MaxSize As Long
Dim voDim As Long
Dim sDimensionName As String, sElementName As String, sCubeName As String
Dim cubeArray() As Long, cubeArray2() As Long, pSpreadString As Long
Dim vArrayOfCells As Long, vCellReference As Long
Dim sErrorMsg As String * 75
'
' Each Visual Basic application begins by calling TM1APIInitialize,
' TM1SystemAdminServerHostSet, and TM1SystemServerConnect.
'
TM1APIInitialize
hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, "tm1host"
pPoolHandle = TM1ValPoolCreate(hUser)
'
' We have to take the strings containing the login information (such as the
' user name and password) and turn them into TM1 value capsules. First
' establish the maximum length of the string as 30 characters.
'
vStringLength = TM1ValIndex(pPoolHandle, 30)
'
' Next, use this string length to build value capsules for the
' user name, password, and TM1Server name. We can reuse the pool handle
' for these functions.
'
vUserName = TM1ValString(pPoolHandle, "zSpread", vStringLength)
vPassword = TM1ValString(pPoolHandle, "", vStringLength)
vServerName = TM1ValString(pPoolHandle, "tm1host development donotuse", vStringLength)
vServerHandle = TM1SystemServerConnect(pPoolHandle, vServerName, vUserName, vPassword)
'API is now connectedd so we can spread
'I think MaxSize will be the number of dimensions in the cube plus 1 for the cube name
MaxSize = 8
ReDim cubeArray(MaxSize)
ReDim cubeArray2(MaxSize)
'MaxSize = TM1ValIndex(pPoolHandle, MaxSize)
'create references to element names
sDimensionName = "Patronage Entity"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "BTL"
cubeArray(2) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(2) = cubeArray(2)
sDimensionName = "Pax Measure"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "Pax"
cubeArray(3) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(3) = cubeArray(3)
sDimensionName = "Patronage Version"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "Plan of 2009"
cubeArray(4) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(4) = cubeArray(4)
sDimensionName = "Fare Class Summary"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "All Fare Types"
cubeArray(5) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(5) = cubeArray(5)
sDimensionName = "Route"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "Rt 2b"
cubeArray(6) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(6) = cubeArray(6)
sDimensionName = "Patronage Model Measures"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "Absolute Adjustment - Pax"
cubeArray(7) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
sElementName = "Base + WD + Pax Growth + Fare Growth"
cubeArray2(7) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
sDimensionName = "Periods"
voDim = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerDimensions(), TM1ValString(pPoolHandle, sDimensionName, 100))
sElementName = "P 13 - 2009"
cubeArray(8) = TM1ObjectListHandleByNameGet(pPoolHandle, voDim, TM1DimensionElements(), TM1ValString(pPoolHandle, sElementName, 100))
cubeArray2(8) = cubeArray(8)
'create cube handle.
sCubeName = "Patronage Model"
cubeArray(1) = TM1ObjectListHandleByNameGet(pPoolHandle, vServerHandle, TM1ServerCubes(), TM1ValString(pPoolHandle, sCubeName, 100))
cubeArray2(1) = cubeArray(1)
'Declare arrays
vArrayOfCells = TM1ValArray(pPoolHandle, cubeArray(), MaxSize)
vCellReference = TM1ValArray(pPoolHandle, cubeArray2(), MaxSize)
''set the array values
Call TM1ValArraySet(vArrayOfCells, cubeArray(1), 1)
Call TM1ValArraySet(vArrayOfCells, cubeArray(2), 2)
Call TM1ValArraySet(vArrayOfCells, cubeArray(3), 3)
Call TM1ValArraySet(vArrayOfCells, cubeArray(4), 7)
Call TM1ValArraySet(vArrayOfCells, cubeArray(5), 5)
Call TM1ValArraySet(vArrayOfCells, cubeArray(6), 6)
Call TM1ValArraySet(vArrayOfCells, cubeArray(7), 7)
Call TM1ValArraySet(vArrayOfCells, cubeArray(8), 8)
Call TM1ValArraySet(vCellReference, cubeArray2(1), 1)
Call TM1ValArraySet(vCellReference, cubeArray2(2), 2)
Call TM1ValArraySet(vCellReference, cubeArray2(3), 3)
Call TM1ValArraySet(vCellReference, cubeArray2(4), 7)
Call TM1ValArraySet(vCellReference, cubeArray2(5), 5)
Call TM1ValArraySet(vCellReference, cubeArray2(6), 6)
Call TM1ValArraySet(vCellReference, cubeArray2(7), 7)
Call TM1ValArraySet(vCellReference, cubeArray2(8), 8)
pSpreadString = TM1ValString(pPoolHandle, "RP+5000", vStringLength)
'
'
pSpreadResult = TM1CubeCellSpread(pPoolHandle, vServerHandle, vArrayOfCells, vCellReference, pSpreadString)
'Call TM1ValErrorString_VB(hUser, pSpreadResult, sErrorMsg, 75)
'
' To log out and disconnect from the API, you must
' call TM1SystemServerDisconnect, TM1SystemClose, then TM1APIFinalize.
'
' In addition, best practice dictates that all TM1 Value Pools used
' in your program be destroyed by calling TM1ValPoolDestroy().
'
vResult = TM1SystemServerDisconnect(pPoolHandle, vServerName)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Add Code to delete all TM1 Value Pools here.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
TM1ValPoolDestroy (pPoolHandle)
TM1SystemClose hUser
TM1APIFinalize
End Sub
If I run the script without any of the spreading functionality then everything performs as expected (well nothing dies) and I can see zSpread log onto the server in TM1Top.
When I add in the spreading script then the server crashes, the strange thing is that the server falls over in a strange place.
The TM1CubeCellSpread function executes without issue but I see no indication of activity on the zSpread user on TM1Top. When I then execute the line TM1SystemServerDisconnect the status of TM1Top for zSpread changes to CubeCellSpread (from memory) and then server become unresponsive and falls over.
So the cell spread only happens when I execute the disconnect statement which is pretty strange!
Version 9.0.3 U9 64 bit used throughout.