Programmatically Update Websheets to TM1 Web

Post Reply
deadsea
Posts: 10
Joined: Mon Aug 31, 2015 9:07 pm
OLAP Product: PowerPlay and Tm1
Version: 10 2 1
Excel Version: 2010

Programmatically Update Websheets to TM1 Web

Post by deadsea » Thu Oct 22, 2015 6:04 pm

Hello All:

I am trying to find out if there is a way to programmatically update the web sheets to TM1Web. Essentially, I am looking for API/Macro or some other hack that would allow me to update 100's of web sheets under various Application folders. Currently any change to web sheets requires us to manually open each one of them and select Update within TM1 Perspectives which, as you can imagine, a mind numbing and cumbersome exercise.

Although fairly new the TM1, I have used plenty of other OLAP/BI tools where these tasks are performed in batch or have at least an API call available to construct our own.

I have searched everywhere but unable to locate anything.

Thank you any comments/feedback/alternate ideas you may have on this topic.

declanr
MVP
Posts: 1619
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by declanr » Thu Oct 22, 2015 8:52 pm

Websheets are just excel files stored in the externals folder so simple VBA should do the trick. With relevant restarts.
Declan Rodger

lotsaram
MVP
Posts: 3250
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Programmatically Update Websheets to TM1 Web

Post by lotsaram » Thu Oct 22, 2015 11:02 pm

deadsea wrote:Hello All:

I am trying to find out if there is a way to programmatically update the web sheets to TM1Web. Essentially, I am looking for API/Macro or some other hack that would allow me to update 100's of web sheets under various Application folders. Currently any change to web sheets requires us to manually open each one of them and select Update within TM1 Perspectives which, as you can imagine, a mind numbing and cumbersome exercise.

Although fairly new the TM1, I have used plenty of other OLAP/BI tools where these tasks are performed in batch or have at least an API call available to construct our own.

I have searched everywhere but unable to locate anything.

Thank you any comments/feedback/alternate ideas you may have on this topic.
What exactly are you saying and why do you need to to "update" all websheets? Do you need to change a server name reference? If so then a VB script can do this for you.
If you need to recalculate the websheets, why? As websheets recalculate upon open/reactivation, so these should be no need to do this ...
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

User avatar
gtonkin
MVP
Posts: 667
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by gtonkin » Fri Oct 23, 2015 6:57 am

If you are updating because you have references in a SubNM to a year e.g. 2015 or to a Version/Scenario e.g. Budget 2015 then I would highly recommend that you update do the following:
-Create a named range on a reference sheet with your TM1 server name and relevant dimensions e.g. Dim.Year
-Create subsets with the relevant items for use in the websheets, for each dimension e.g. Year/Version/Scenario
-Save the subsets as public with a standardized name e.g. _S-Template
-Update the subnms to point to the subset and return item 1 e.g. SUBNM(Dim.Year,"_S-Template",1)

At least this way, you can control what is in the websheets through an update of the Subsets. Also, by making named ranges for the server and for other TM1 objects, linked to the server named range, a move from Dev/Test/UAT is fairly simple - a quick VBA script to open, update, save and close as per Declan's suggestion.

deadsea
Posts: 10
Joined: Mon Aug 31, 2015 9:07 pm
OLAP Product: PowerPlay and Tm1
Version: 10 2 1
Excel Version: 2010

Re: Programmatically Update Websheets to TM1 Web

Post by deadsea » Fri Oct 23, 2015 3:25 pm

declanr, lotsaram, and gtonkin: Thank you for feedback.

declanr/lotsaram: You are correct in your assumption in that we need to update our server variable to point to a different server name.Currently I have created a VB script that opens each websheet we have designed and updates the server variable in them. However, once the web sheets are updated we use the TM1 Perspectives (Excel) to manually click on "Update" in TM1 Perspectives for each web sheet on the server.

You suggestion to update the deployed web sheets in }External folder, which I just tested does indeed work, however, the gap with this approach is that we have a "tm1websheets" folder which contains various application specific folders, which in turn contain the applications web sheets. This is where various developers/users will place their web sheets which will then get published or updated.

As I can not find much information online, my assumption is that "}External" folder gets the web sheet XLSX files only once they are published on TM1Web via "Upload"/"Update" from Perspectives. And if this is the case, then we will essentially need to make the }External folder our "working folder" for all web sheet development work, which is probably not going work and may not be the best approach.

I suppose that if we have dev/uat/prod instances and we perform migration between environments strictly via data/}External folder then we may only need to do update of all web sheets in }External folder once, however, this is not feasible at the moment and we need to have a separate web sheets folder where various TM1 developers "upload" their developed web sheets in different environments.

Is there an alternative approach or other options, you would recommend?

Thanks for all the feedback and assistance.

PS: gtonkin, a good chunk of what you stated was implemented during out current release cycle and with the exception of manual updates of web sheets to TM1Web, we are almost finished re factoring our previous years work!

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by tomok » Fri Oct 23, 2015 5:16 pm

If the only reason for changing the reports is to replace the server name why not build the sheets with a dynamic server name that picks the server from a list based on the TM1USER function and a few IF statements. Here's how:
  • 1. Create a new tab in the workbook (or use the same tab, I just like to be clean)
    2. In column A put the list of server names you have in the order of precedence (we'll say DEV, UAT, PROD with DEV at the top and PROD at the bottom)
    3. In the second column put the following formula: =IF(TM1USER(A1<>(""),1,0) (this example is for first row)
    4. Copy the formula down to match the list of servers
    5. Just below the server name list in column A put the following formula: =IF(B1=1,A1,IF(B2=1,A2,IF(B3=1,A3)))
    6. Name the above cell "Server"
    7. Hide the tab
Now you you can change all your formula cell references to Server&":DimOrCube...." instead of "DEV:DimOrCube...." So, if you are logged into PROD the value in Server will be "PROD". If logged into UAT it will be "UAT" and likewise for DEV. No need to republish as you move through environments. The only caveat to this approach is that if you are logged into more than one server in that list then the value in cell Server will be the bottom-most server from the list in Column A.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

Wim Gielis
MVP
Posts: 2083
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by Wim Gielis » Fri Oct 23, 2015 8:39 pm

tomok wrote:If the only reason for changing the reports is to replace the server name why not build the sheets with a dynamic server name that picks the server from a list based on the TM1USER function and a few IF statements. Here's how:
  • 1. Create a new tab in the workbook (or use the same tab, I just like to be clean)
    2. In column A put the list of server names you have in the order of precedence (we'll say DEV, UAT, PROD with DEV at the top and PROD at the bottom)
    3. In the second column put the following formula: =IF(TM1USER(A1<>(""),1,0) (this example is for first row)
    4. Copy the formula down to match the list of servers
    5. Just below the server name list in column A put the following formula: =IF(B1=1,A1,IF(B2=1,A2,IF(B3=1,A3)))
    6. Name the above cell "Server"
    7. Hide the tab
Now you you can change all your formula cell references to Server&":DimOrCube...." instead of "DEV:DimOrCube...." So, if you are logged into PROD the value in Server will be "PROD". If logged into UAT it will be "UAT" and likewise for DEV. No need to republish as you move through environments. The only caveat to this approach is that if you are logged into more than one server in that list then the value in cell Server will be the bottom-most server from the list in Column A.

Code: Select all

=IF(TM1USER(A1)<>"",1,0)
and

Server will be the top-most server from the list in Column A
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Wim Gielis
MVP
Posts: 2083
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by Wim Gielis » Fri Oct 23, 2015 8:42 pm

Alternative for the nested IF-formulas:

Code: Select all

=IFNA(INDEX(A1:A3,MATCH(1,B1:B3,0)),"")
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

User avatar
paulsimon
MVP
Posts: 694
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by paulsimon » Sat Oct 24, 2015 9:38 pm

Hi

I am not sure that any sort of dynamic reference is going to work as the long standing problem with TM1 is that SUBNM formula cannot take a cell reference for the Server Name. If you do this the double click functionality stops working.

However, if, all you want to do is to change the server name, then it should be simple enough to run a VBA Macro over the sheets both those in the Externals folder and those in your source folders. That is what I do. I have a macro that does this as part of my standard utilities. It is nothing more than a find and replace.

In practice, I only need the source web sheets on the dev server. Only the sheet in the }Externals folder needs to be promoted to the test, pre-prod, prod, etc servers. At the time of promotion I just run the macro to replace the server name.

Regards

Paul Simon

tomok
MVP
Posts: 2561
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by tomok » Sun Oct 25, 2015 12:57 am

paulsimon wrote:I am not sure that any sort of dynamic reference is going to work as the long standing problem with TM1 is that SUBNM formula cannot take a cell reference for the Server Name. If you do this the double click functionality stops working.
I've been doing this for quite a few years and the double click functionality is fine as long as you're on at least Office 2010. Anything older than that and you run into problems.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

User avatar
Alan Kirk
Site Admin
Posts: 5926
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by Alan Kirk » Sun Oct 25, 2015 2:04 am

tomok wrote:
paulsimon wrote:I am not sure that any sort of dynamic reference is going to work as the long standing problem with TM1 is that SUBNM formula cannot take a cell reference for the Server Name. If you do this the double click functionality stops working.
I've been doing this for quite a few years and the double click functionality is fine as long as you're on at least Office 2010. Anything older than that and you run into problems.
There's a reference to this in the FAQ; the behaviour of Subnm changed in 9.4.1 FP1. To quote from the relevant post:
I wrote:This bug occurred when the function was changed to allow it to use cell references as arguments. Prior to 9.4.1 FP1, if you used a cell reference for, say, the server name, the double click functionality would not work. It does in FP1, but you must specify all of the arguments for the function.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
paulsimon
MVP
Posts: 694
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by paulsimon » Sun Oct 25, 2015 11:03 pm

Hi

Thanks for that - good to see that they finally corrected that. However, at present both my clients are still using Excel 2007.

Regards

Paul Simon

deadsea
Posts: 10
Joined: Mon Aug 31, 2015 9:07 pm
OLAP Product: PowerPlay and Tm1
Version: 10 2 1
Excel Version: 2010

Re: Programmatically Update Websheets to TM1 Web

Post by deadsea » Wed Oct 28, 2015 12:49 pm

tomok wrote:If the only reason for changing the reports is to replace the server name why not build the sheets with a dynamic server name that picks the server from a list based on the TM1USER function and a few IF statements. Here's how:
  • 1. Create a new tab in the workbook (or use the same tab, I just like to be clean)
    2. In column A put the list of server names you have in the order of precedence (we'll say DEV, UAT, PROD with DEV at the top and PROD at the bottom)
    3. In the second column put the following formula: =IF(TM1USER(A1<>(""),1,0) (this example is for first row)
    4. Copy the formula down to match the list of servers
    5. Just below the server name list in column A put the following formula: =IF(B1=1,A1,IF(B2=1,A2,IF(B3=1,A3)))
    6. Name the above cell "Server"
    7. Hide the tab
Now you you can change all your formula cell references to Server&":DimOrCube...." instead of "DEV:DimOrCube...." So, if you are logged into PROD the value in Server will be "PROD". If logged into UAT it will be "UAT" and likewise for DEV. No need to republish as you move through environments. The only caveat to this approach is that if you are logged into more than one server in that list then the value in cell Server will be the bottom-most server from the list in Column A.
Thanks tomok for your suggestion.

I tried this but then realized that, although a nice solution, this will not work in our environment because we have multiple servers in each of our DEV, UAT, and PROD instances for different applications. Also, when our power users/developers are working on websheets they tend to connect with multiple servers in which case your formula using TM1USER will return 1 for various servers.

deadsea
Posts: 10
Joined: Mon Aug 31, 2015 9:07 pm
OLAP Product: PowerPlay and Tm1
Version: 10 2 1
Excel Version: 2010

Re: Programmatically Update Websheets to TM1 Web

Post by deadsea » Wed Oct 28, 2015 12:52 pm

Thank you Paul, Alan, and tomok.

At this point, its looking like we will have to implement the strategy to "change once in DEV and promote" to other environments from the }Externals directory. From there on I will just run my VB script that will change the server name.

I still feel an enhancement to provide GUI automation features are imperative for a COTS product based on a true SOA based architecture. I think IBM will probably implement it as after working with their IBM Cognos 10 BI API, I have been impressed after having worked with various vendors in the past. So I will log a request in near future to get this placed in queue, who knows when they will do it but it can never hurt!

User avatar
macsir
Community Contributor
Posts: 628
Joined: Wed May 30, 2012 6:50 am
OLAP Product: TM1, SSAS
Version: 9.5 - 10.2
Excel Version: 97 - 2013
Contact:

Re: Programmatically Update Websheets to TM1 Web

Post by macsir » Tue Jun 25, 2019 5:30 am

Just want to share, with REST API, it is possible now.
It can GET(download) and PATCH(update) now at least in 10.2.2 FP7.
In TM1,the answer is always yes though sometimes with a but....
http://tm1sir.blogspot.com.au/

Drg
Posts: 114
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: Programmatically Update Websheets to TM1 Web

Post by Drg » Thu Jul 04, 2019 6:15 am

We use the following strategy to change the destination server:
We unpack the excel file and use the powershell scripts to change the server name in all files.
We also use similar logic for the BI contentstore exel.
There is one thing, but namely if you have active buttons in the forms of prospects, then I remember how you need to change the name of the server too, and here at the stage of creating the files it’s better to use the link target but if you did it, you can change it. Just a couple of days ago, I wrote a similar script to replace the server name in perspectives, if you need someone, I can transfer my raw scripts (they are actually written for one-time use)

Post Reply