Use of VBA in PA

[schlemiel29]

I have a report, I like to publish or to update.
The VBA macro recorder does not track any actions in the TM1 ribbon.
So how can I publish a report with TM1 menu operations using VBA?
Is there a list of commands available like using

Code: Select all

Application.Run("TABDIM", sCube, i)

?

[Steve Rowe]

Are you talking about publishing to TM1 Web / PASS from within PAFE

If so I don't think it is possible using VBA.

[WilliamSmith]

I think these are all the valid TM1 related VBA commands, using the legacy perspectives (Tm1p.xla) add-in. https://www.ibm.com/docs/en/planning-an ... -functions

If you are using PaFE, there is a VBA API: https://ibm.github.io/paxapi/#introduction

I'm not sure if either will give you what you want

[20 Ton Squirrel]

The documentation on VBA with PAfE is sparse but it does lay out a baseline to work from. It is not intended for users that rely on macro-recording to write their VBA. You'll need to know all those dirty details like procedures, object variables, scope, binding, etc… ¯\_(ツ)_/¯

That aside, you CAN write VBA to perform nearly all of the commands available from the menu. Things like refreshing, committing, or discarding are simple enough. You could even write code that modifies the MDX behind a report. I have a report where there are multiple QR on a single worksheet, they are co-dependent so they must be refreshed in a specific order to work properly.

THAT aside, keep your expectations with VBA/PAfE interactions low. VBA is practically undead at this point and is fairly limited in how it can interpret responses from REST/PAfE. PAfE itself is notoriously undocumented and a few of the core functions (like TM1ELLIST) don't behave as described.

[WilliamSmith]

I agree with 20 Ton Squirrel.

I will caveat that you can use Msxml2.ServerXMLHTTP.6.0 (or some variant that lets you GET / POST to http) in VBA to interact with the ODATA v4 TM1 API directly, and it's a "fairly" modern implementation. Although working in VBA means single thread / no async, so you're limited and CPU bound.

I've found it's better in practice to stage data from TM1 to a relational database using a modern programming language like Python / C#, then use Power Query to build semantic models into Excel. That also gives you a great platform for expanding into dashboarding in PowerBI / Tableau etc.

[Adam]

THAT aside, keep your expectations with VBA/PAfE interactions low. VBA is practically undead at this point and is fairly limited in how it can interpret responses from REST/PAfE. PAfE itself is notoriously undocumented and a few of the core functions (like TM1ELLIST) don't behave as described.

Respectfully disagree on the VBA point - lots of big business has VBA in their workflow, that's why Microsoft keeps it going.

I do agree if you're building some generic framework that can take any REST command, it's going to be annoying.

However, if you're building something specific we've found VBA/PAXAPI/REST to work quite well.

For the specific TM1ELLIST example, we actually use the following with REST API:

Code: Select all

Dim apiHeader As String: apiHeader = "/tm1/xxxxxxx/api/v1/ExecuteMDX?$expand=Axes($expand=Hierarchies($select=Name),Tuples($expand=Members($select=Name))),Cells($select=Ordinal,Value)"
Dim apiBody As String: apiBody = "{""MDX"":""SELECT { [}ElementAttributes_xxxxx].[}ElementAttributes_xxxxx].[Element Name] } DIMENSION PROPERTIES MEMBER_NAME ON 0, { [xxxxx].[xxxxx].[xxxxx].Children } ON 1 FROM [}ElementAttributes_xxxxx]""}"