VBA code to Commit data in pax via Macro

[Mithun.Mistry1103]

Hello

Not sure which section I should put this question.

What I am trying to do is create a macro to commit data into Pax rather than the function in the addin. Has anyone ever done this?

Thank you

[WilliamSmith]

Hi there,

I've been able to prototype writing into TM1 using the REST API with VBA. Would love to discuss further.

Code: Select all

Sub Example()
    
        'Configure element list (Dimension~Hierarchy~Element)
        Dim elementList As New Collection
        Set elementList = Nothing
        elementList.Add "MONTH~MONTH~2212"
        elementList.Add "DAY~DAY~19"
        elementList.Add "MEASURE~MEASURE~MEASURE1"
    
        TM1_Write_to_Cube "IntRate", elementList, newMeasureValue

End Sub

Code: Select all

Sub TM1_Write_to_Cube(cubeName As String, elementList As Collection, newValue As String)

    Dim payload As String
    payload = "{""Cells"":[{""Tuple@odata.bind"": ["

    For Each element In elementList
    
        payload = payload & """Dimensions('" & Split(element, "~")(0) & "') / Hierarchies('" & Split(element, "~")(1) & "') / Elements('" & Split(element, "~")(2) & "')"","
    
    Next
    
    payload = payload & "]}],""Value"":""" & newValue & """}"

    response = TM1_Post("/api/v1/Cubes('" & cubeName & "')/tm1.Update", payload)

End Sub

Code: Select all

Function TM1_Post(endPoint As String, payload As String)

    Dim x As MSXML2.ServerXMLHTTP60
    Set x = New MSXML2.ServerXMLHTTP60
    
    x.Open "POST", "https://your_tm1_server_host:your_tm1_server_port" & endPoint, False, "your_username", "your_password"
    x.setRequestHeader "WWW-Authenticate: Basic Realm", "TM1"
    x.setRequestHeader "Content-Type", "application/json"
    x.SetOption 2, 13056
    
    x.Send payload
    
    TM1_Post = x.responseText
    
End Function

[ascheevel]

start here