Programmatically Update Websheets to TM1 Web

[deadsea]

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]

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

[lotsaram]

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 ...

[gtonkin]

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]

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]

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.

[Wim Gielis]

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

[Wim Gielis]

Alternative for the nested IF-formulas:

Code: Select all

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

[paulsimon]

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]

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.

[Alan Kirk]

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:

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.

[paulsimon]

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]

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]

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!

[macsir]

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.

[Drg]

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)