How to call relative proportional spread function by Macro!

[kennyyeung]

I have a template It have several month data.
Instead of use relative proportional spread manually for each month.
Can i add a button to my template to make it more automate?
After press the button , it call the relative proportional spread function automatically to get the data from perviod verson for those month

[David Usherwood]

Applix/Cognos/IBM have not rushed to provide support for using spreading in code - shame I know.
All there is is the TI command CellPutProportionalSpread.
Writing a rule is probably your best bet but this can be a bit tough if you haven't done much before. Rewarding though (in at least 2 senses).
Good luck and feel free to come back with more questions.

[lotsaram]

It is possible if the interface is Excel and not web with VBA.
You can use TM1CubeCellSpread from the TM1 API. But be warned, VBA coding with the API is never easy or simple!

[Steve Rowe]

lotsaram is not wrong!

I thought that I would have a crack at this since I have always meant to learn a bit more about how to code the API and I need the functionality for a modeling tool I am building.
The code below is very hardcoded at the moment and needs to be genralised a lot more but.
1. It executes without killing Excel or erroring.
2. It does not appear to do anything.

So some questions
1. Can I re-use element handles? In the two arrays that I have to declare most of the element references are the same so I have reused them, is the permitted?
2. Same for cube handles.
3. The help for the spread command indicates that it can return error values, how to convert the long returned into an error code?
4. Any tips? (Other than get someone else to do it!) Any pointers gratefully recieved!

I've double checked all the element references etc.

Code: Select all

Private Sub Form_Load()
 
Dim sServerName As String
Dim sUsername As String
Dim sPassword As String
Dim hUserHandle As Long
Dim pPoolHandle As Long
Dim vPassword, vServerName, 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

Call CallTI.InitialiseVariables


'
' Each Visual Basic application begins by calling TM1APIInitialize,
' TM1SystemAdminServerHostSet, and TM1SystemServerConnect.
'
 
TM1APIInitialize
 
hUser = TM1SystemOpen()
TM1SystemAdminHostSet hUser, HostServer
 
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, TM1Server, 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), 4)
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), 4)
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)






'
' 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

[kennyyeung]

Thanks !
because i need to do some calculation on the value manually.
i found that function TM1CubeCellValueGet is useful .
However when i call TM1CubeCellValueGet ,it return a error code !
(code refer to http://forums.olapforums.com/viewtopic.php?f=3&t=1068)
Can i have some example of TM1CubeCellValueGet ?

[Steve Rowe]

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...

Anyway my revised code below.

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.
Anyone have some thoughts?
Cheers,

[shlclku]

Hello Steve,

Have you ever been able to solve this issue? I also need to do relative proportional spreading via code but it's not described anywhere so I'm lost. The only thing that I found back is your code but in it you state that the server crashes when executing the code so ...

Thanks a lot in advance for your feedback

Kind regards

Kurt

[Jan]

I have the same question: is it possible to automate relative proportional spreading through API calls?

[mykill]

There is a Excel 4 macro function called "DBPROPORTIONALSPREAD(Number;String;String;....)".

maybe you can try this one.