Hello -

Long time TM1 user (since 1989) and I need some advice! 

We are moving to PAW and PAX slowly from TM1 10.2.  Our goal is to use PAX as the user front-end with Architect as the Admin tool.  Here is my issue: I have an application built in 10.2 that tracks vacation earned, approved, used and other indirect time metrics.  One of the reports is a simple calendar report.  The report can be run from a company top level, a specific cost center or group manager.  The report itself contains no TM1 formulas, just the format of a monthly calendar - 5 workdays across (15 columns, 3 per day) and 4 or 5 weeks (rows).  

TM1 contains a vacation cube with the following dims: month, employee and metrics.  

The user selects a month, metric and a reporting point (top level, cost center or group manager) from a VBA menu and based on the selection, VBA code loops through each day of the selected month (month dimension) and then loops through the employee (employee dimension) and if the selected metric contains a value in the vacation cube, the employee name is written out to Excel as text in the appropriate date section. 

With Perspectives 10.2, I can run the top level company level (250 employees) for a given month, and the report completes in under 15 seconds using Excel 2010.  For comparisons, Perspectives (64 bit) that comes TM1 2.0.91 - also under Excel 2010 and under Excel 2016 - runs roughly the same time.  Note - this is with PAX add-ins disabled.

However, running just PAX with the Perspectives add-in disabled, the same report completes in 12 - 15 minutes!   The VBA code calls just a few TM1 worksheet functions - I.E. 

sEmployee = Run("subnm", sDimEmp, sSubset, dDateNo, "firstlast")
   
I've submitted a ticket to IBM support and was told that the VBA calls to TM1 Worksheet functions using PAX and the IBM Cognos Office Reporting TM1 Add-in has not been optimized for TM1 Worksheet functions yet.

Hopefully, someone from IBM can tell me when these functions will be optimized or if there is another approach I can take?

Thanks

------------------------------
Mark Valenti
------------------------------

#PlanningAnalyticswithWatson

Mark,

PA for Excel operates the same spreadsheet functions in a very different manner than Perspectives does, this is desirable for some reasons.  The key factor here is that they operate asynchronously, which is not easy for consumption in VBA but is ideal for actual spreadsheet use.

I would suggest placing your functions into a scratch worksheet, and calculating that using the refresh worksheet and wait api's.  If you can try this, and advise on the operating time afterwards, I'd be interested to hear more.

In terms of inline consumption of udf's in VBA, if it's only a few, I can speak towards how to do it; but if there are many - a spreadsheet is best.

You can learn more at 

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.ug_cxr.2.0.0.doc/c_ug_cxr_perspectives_notes.html

and

https://ibm.github.io/paxapi

Regards,

------------------------------
Ted Phillips
------------------------------

Original Message:
Sent: Mon July 27, 2020 02:12 PM
From: Mark Valenti
Subject: Worksheet Functions used in VBA Code

Hello -

Long time TM1 user (since 1989) and I need some advice! 

We are moving to PAW and PAX slowly from TM1 10.2.  Our goal is to use PAX as the user front-end with Architect as the Admin tool.  Here is my issue: I have an application built in 10.2 that tracks vacation earned, approved, used and other indirect time metrics.  One of the reports is a simple calendar report.  The report can be run from a company top level, a specific cost center or group manager.  The report itself contains no TM1 formulas, just the format of a monthly calendar - 5 workdays across (15 columns, 3 per day) and 4 or 5 weeks (rows).  

TM1 contains a vacation cube with the following dims: month, employee and metrics.  

The user selects a month, metric and a reporting point (top level, cost center or group manager) from a VBA menu and based on the selection, VBA code loops through each day of the selected month (month dimension) and then loops through the employee (employee dimension) and if the selected metric contains a value in the vacation cube, the employee name is written out to Excel as text in the appropriate date section. 

With Perspectives 10.2, I can run the top level company level (250 employees) for a given month, and the report completes in under 15 seconds using Excel 2010.  For comparisons, Perspectives (64 bit) that comes TM1 2.0.91 - also under Excel 2010 and under Excel 2016 - runs roughly the same time.  Note - this is with PAX add-ins disabled.

However, running just PAX with the Perspectives add-in disabled, the same report completes in 12 - 15 minutes!   The VBA code calls just a few TM1 worksheet functions - I.E. 

sEmployee = Run("subnm", sDimEmp, sSubset, dDateNo, "firstlast")
   
I've submitted a ticket to IBM support and was told that the VBA calls to TM1 Worksheet functions using PAX and the IBM Cognos Office Reporting TM1 Add-in has not been optimized for TM1 Worksheet functions yet.

Hopefully, someone from IBM can tell me when these functions will be optimized or if there is another approach I can take?

Thanks

------------------------------
Mark Valenti
------------------------------

#PlanningAnalyticswithWatson

Ted -

Thanks for the suggestion.  IBM support had suggested the same, but I kept avoiding doing it this way.  Being a Perspectives user for so long, I never come across a situation that resulted in poor performance.  But with PAX things are different,   It obviously works differently, not in a bad way - just different.  What I ended up doing was creating a dynamic report for one month - with individual days as columns.  Employees populated rows.  Once the user ran the report, they can select a menu option that runs a calendar view that reads the dynamic report results and writes out to a second report formatted as a calendar showing employees on vacation grouped by day - kind of like a hanging calendar.  I know, the dynamic report tells them the same thing, but they want to visually see the results in an easier to read format.  Time wise, I was able to run the report for the entire company in about 15 seconds - similar to the performance of Perspectives.  

Thanks Again.

Mark




------------------------------
Mark Valenti
------------------------------

Original Message:
Sent: Tue August 04, 2020 01:56 PM
From: Ted Phillips
Subject: Worksheet Functions used in VBA Code

Mark,

PA for Excel operates the same spreadsheet functions in a very different manner than Perspectives does, this is desirable for some reasons.  The key factor here is that they operate asynchronously, which is not easy for consumption in VBA but is ideal for actual spreadsheet use.

I would suggest placing your functions into a scratch worksheet, and calculating that using the refresh worksheet and wait api's.  If you can try this, and advise on the operating time afterwards, I'd be interested to hear more.

In terms of inline consumption of udf's in VBA, if it's only a few, I can speak towards how to do it; but if there are many - a spreadsheet is best.

You can learn more at 

https://www.ibm.com/support/knowledgecenter/SSD29G_2.0.0/com.ibm.swg.ba.cognos.ug_cxr.2.0.0.doc/c_ug_cxr_perspectives_notes.html

and

https://ibm.github.io/paxapi

Regards,

------------------------------
Ted Phillips

Original Message:
Sent: Mon July 27, 2020 02:12 PM
From: Mark Valenti
Subject: Worksheet Functions used in VBA Code

Hello -

Long time TM1 user (since 1989) and I need some advice! 

We are moving to PAW and PAX slowly from TM1 10.2.  Our goal is to use PAX as the user front-end with Architect as the Admin tool.  Here is my issue: I have an application built in 10.2 that tracks vacation earned, approved, used and other indirect time metrics.  One of the reports is a simple calendar report.  The report can be run from a company top level, a specific cost center or group manager.  The report itself contains no TM1 formulas, just the format of a monthly calendar - 5 workdays across (15 columns, 3 per day) and 4 or 5 weeks (rows).  

TM1 contains a vacation cube with the following dims: month, employee and metrics.  

The user selects a month, metric and a reporting point (top level, cost center or group manager) from a VBA menu and based on the selection, VBA code loops through each day of the selected month (month dimension) and then loops through the employee (employee dimension) and if the selected metric contains a value in the vacation cube, the employee name is written out to Excel as text in the appropriate date section. 

With Perspectives 10.2, I can run the top level company level (250 employees) for a given month, and the report completes in under 15 seconds using Excel 2010.  For comparisons, Perspectives (64 bit) that comes TM1 2.0.91 - also under Excel 2010 and under Excel 2016 - runs roughly the same time.  Note - this is with PAX add-ins disabled.

However, running just PAX with the Perspectives add-in disabled, the same report completes in 12 - 15 minutes!   The VBA code calls just a few TM1 worksheet functions - I.E. 

sEmployee = Run("subnm", sDimEmp, sSubset, dDateNo, "firstlast")
   
I've submitted a ticket to IBM support and was told that the VBA calls to TM1 Worksheet functions using PAX and the IBM Cognos Office Reporting TM1 Add-in has not been optimized for TM1 Worksheet functions yet.

Hopefully, someone from IBM can tell me when these functions will be optimized or if there is another approach I can take?

Thanks

------------------------------
Mark Valenti
------------------------------

#PlanningAnalyticswithWatson

Greetings,

I came across this post. I am new to TM1 and Planning Analytics and helping my user on this.

We have an Excel connected to Planning Analytics with Cube Name etc. and cells have DBRW formula for filter against dimension values that are hard coded in the cells. Currently the user types in the Qtr, Year, Line of Business and Company, hits refresh, saves the excel as LOB name and goes through this for all Lines of Business.

We would like to use VB Macro to connect to Planning Analytics and loop through for various filter criteria, refresh excel data for each set of filter criteria, save the excel As specific to the filtered criteria.

Appreciate if anyone can share the VB macro code so that I have a starting point.

Regards

------------------------------
Lakshmi Jairam
------------------------------

Original Message:
Sent: Mon July 27, 2020 02:12 PM
From: Mark Valenti
Subject: Worksheet Functions used in VBA Code

Hello -

Long time TM1 user (since 1989) and I need some advice! 

We are moving to PAW and PAX slowly from TM1 10.2.  Our goal is to use PAX as the user front-end with Architect as the Admin tool.  Here is my issue: I have an application built in 10.2 that tracks vacation earned, approved, used and other indirect time metrics.  One of the reports is a simple calendar report.  The report can be run from a company top level, a specific cost center or group manager.  The report itself contains no TM1 formulas, just the format of a monthly calendar - 5 workdays across (15 columns, 3 per day) and 4 or 5 weeks (rows).  

TM1 contains a vacation cube with the following dims: month, employee and metrics.  

The user selects a month, metric and a reporting point (top level, cost center or group manager) from a VBA menu and based on the selection, VBA code loops through each day of the selected month (month dimension) and then loops through the employee (employee dimension) and if the selected metric contains a value in the vacation cube, the employee name is written out to Excel as text in the appropriate date section. 

With Perspectives 10.2, I can run the top level company level (250 employees) for a given month, and the report completes in under 15 seconds using Excel 2010.  For comparisons, Perspectives (64 bit) that comes TM1 2.0.91 - also under Excel 2010 and under Excel 2016 - runs roughly the same time.  Note - this is with PAX add-ins disabled.

However, running just PAX with the Perspectives add-in disabled, the same report completes in 12 - 15 minutes!   The VBA code calls just a few TM1 worksheet functions - I.E. 

sEmployee = Run("subnm", sDimEmp, sSubset, dDateNo, "firstlast")
   
I've submitted a ticket to IBM support and was told that the VBA calls to TM1 Worksheet functions using PAX and the IBM Cognos Office Reporting TM1 Add-in has not been optimized for TM1 Worksheet functions yet.

Hopefully, someone from IBM can tell me when these functions will be optimized or if there is another approach I can take?

Thanks

------------------------------
Mark Valenti
------------------------------

#PlanningAnalyticswithWatson

Original Message:
Sent: 10/9/2020 5:10:00 PM
From: Lakshmi Jairam
Subject: RE: Worksheet Functions used in VBA Code

Greetings,

I came across this post. I am new to TM1 and Planning Analytics and helping my user on this.

We have an Excel connected to Planning Analytics with Cube Name etc. and cells have DBRW formula for filter against dimension values that are hard coded in the cells. Currently the user types in the Qtr, Year, Line of Business and Company, hits refresh, saves the excel as LOB name and goes through this for all Lines of Business.

We would like to use VB Macro to connect to Planning Analytics and loop through for various filter criteria, refresh excel data for each set of filter criteria, save the excel As specific to the filtered criteria.

Appreciate if anyone can share the VB macro code so that I have a starting point.

Regards

------------------------------
Lakshmi Jairam
------------------------------

Original Message:
Sent: Mon July 27, 2020 02:12 PM
From: Mark Valenti
Subject: Worksheet Functions used in VBA Code

Hello -

Long time TM1 user (since 1989) and I need some advice! 

We are moving to PAW and PAX slowly from TM1 10.2.  Our goal is to use PAX as the user front-end with Architect as the Admin tool.  Here is my issue: I have an application built in 10.2 that tracks vacation earned, approved, used and other indirect time metrics.  One of the reports is a simple calendar report.  The report can be run from a company top level, a specific cost center or group manager.  The report itself contains no TM1 formulas, just the format of a monthly calendar - 5 workdays across (15 columns, 3 per day) and 4 or 5 weeks (rows).  

TM1 contains a vacation cube with the following dims: month, employee and metrics.  

The user selects a month, metric and a reporting point (top level, cost center or group manager) from a VBA menu and based on the selection, VBA code loops through each day of the selected month (month dimension) and then loops through the employee (employee dimension) and if the selected metric contains a value in the vacation cube, the employee name is written out to Excel as text in the appropriate date section. 

With Perspectives 10.2, I can run the top level company level (250 employees) for a given month, and the report completes in under 15 seconds using Excel 2010.  For comparisons, Perspectives (64 bit) that comes TM1 2.0.91 - also under Excel 2010 and under Excel 2016 - runs roughly the same time.  Note - this is with PAX add-ins disabled.

However, running just PAX with the Perspectives add-in disabled, the same report completes in 12 - 15 minutes!   The VBA code calls just a few TM1 worksheet functions - I.E. 

sEmployee = Run("subnm", sDimEmp, sSubset, dDateNo, "firstlast")
   
I've submitted a ticket to IBM support and was told that the VBA calls to TM1 Worksheet functions using PAX and the IBM Cognos Office Reporting TM1 Add-in has not been optimized for TM1 Worksheet functions yet.

Hopefully, someone from IBM can tell me when these functions will be optimized or if there is another approach I can take?

Thanks

------------------------------
Mark Valenti
------------------------------
#PlanningAnalyticswithWatson