VBA Code for PAFE

[mboderoriv]

Hi,

Apologies if this has already been requested in the past, but I haven't been able to find anything about it. I have recently joined a company that uses IBM Analytics for excel (PAfE) and I am still trying to get my head around it. This is used mainly used in excel through an excel add in and the version is IBM_PAfE_x64_2.0.89.3.

I am trying to create a VBA code that automatically refreshes all TM1 tables including the DBRW formulas. However, I have been unsucessful finding the right code. I am trying to use PAX.RefreshAllData but it is not working because the object is not found. I have checked my references and I cannot find any reference that refers to "IBM Cognos Office Automation" or "IBM Planning Analytics for Microsoft Excel". From what I read, PAfE provides its own API that I can access through VBA using the PAX object. However, I am unable to use this object because as said I cannot see any reference available related to IBM in Project window.

What can I do? How can I use VBA with Pafe if I encounter this issues?

Thanks in advance

[gtonkin]

Start here - Should get you going with the prerequisites you need using the CognosOfficeAutomationExample.bas and CognosOfficeMessageSuppressor.cls files. Without these you will battle.

[mboderoriv]

Hi gtonkin,

As mentioned previously, I use IBM with an excel addin so I don't have access to Cognos Automation because this is provided through Microsoft I believe. So the link doesnt really help me.

Start here - Should get you going with the prerequisites you need using the CognosOfficeAutomationExample.bas and CognosOfficeMessageSuppressor.cls files. Without these you will battle.

[gtonkin]

HI, If you are using PAfE then the documentation I referred you to is valid. The names may say Cognos but what you need to automate PAfE is detailed in those references. This has nothing to do with Cognos Automation and it is purely confusing naming again by IBM.

[JohnO]

Hi gtonkin,

As mentioned previously, I use IBM with an excel addin so I don't have access to Cognos Automation because this is provided through Microsoft I believe. So the link doesnt really help me.

A bit of history might be useful.

Back in 2007 (I think), Cognos acquired Applix who owned TM1. TM1 had an Excel add-in called TM1 Perspectives which was a Windows 3 application. A year later, IBM acquired Cognos.

Cognos 8 (Pretty sire it was 8) came with 2 excel add-in's. One was a called CAFE (Cognos Analysis for Excel). This was a modern Excel add-in born in the age of the Internet and it was also built around then modern BI standards using MDX as the query language supporting concepts such as dimension hierarchies.

IBM have spent the last 17 years putting Perspectives functionality into CAFE - still not fully there and unlikely they will do more in that regard however they have built some new stuff in it. They renamed it PAFE (Planning Analytics for Excel) about 10 years ago.

The ideas of a cube viewer, explorations and quick reports are CAFE tools that have been adapted to interface with/ support TM1 as the primary data source. Universal Reports support (First release 1.5 years ago) some of the functionality that was expected 8 years ago when TM1 Server 11 came out. Universal Reports are still being enhanced so are a work in progress.

In terms of the API, I used it when it was CAFE back in 2012 to support custom burst reporting - something IBM have not done with PAFE though several of their partners have built this. I looked at the functions available in the API about a year ago and it seemed that Universal Reports had not been included, perhaps they have now. So I think it will depend on the functions you need to support as to whether these have been included in the API.