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.

[WilliamSmith]

If you have Planning Analytics for Excel installed, and you are logged into PAW etc. you can trigger branching recalculation by calling the .Dirty() method on a range of cells.

Here is the VBA documentation: https://learn.microsoft.com/en-us/offic ... ange.dirty

This will not work if the application is set to manual calculation mode, so in VBA, below is the workarounds I use...

This has been very helpful to me in automating reporting using the new PAW Excel add-in. This allows a script to, for example, target a specific element, like a year / month or a project code, and iteratively refresh the DBRW formulas as the value changes.

I also hook up these subs to hot keys (CTRL+R) = recalc selection, (CTRL+SHIFT+R) = recalc active sheet, for myself and our end-users to perform adhoc analysis by re-querying from the TM1 server (also works on other TM1 formulas like DBSS).

Another note, with PAW + VBA, you can convert DBRW formulas to static values, without triggering the write-back to TM1, but you have to set Application.EnableEvents = false first, then Range.Value = Range.Value will work as expected.

To recalculation a specific range:

Code: Select all

Sub RecalcSelection()
    
    calc = Application.Calculation
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    Selection.Dirty
    
    Application.Calculation = calc

End Sub

Code: Select all

Sub ReCalcRng(rng As Range)

    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    rng.Dirty
    
    Application.Calculation = calc

End Sub

To recalculate a sheet:

Code: Select all

Sub RecalcSheet()
    
    calc = Application.Calculation
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    
    Application.Calculation = calc
    
End Sub

Code: Select all

Sub ReCalcWS(Optional ws)
    
    If IsMissing(ws) Then
    
        Set ws = ActiveSheet
    
    End If
    
    calc = Application.Calculation
        
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    
    Application.Calculation = calc
    
End Sub