Planning Analytics

Microsoft Windows 2019 server


You have create a TM1 WEBSHEET in excel (Tm1 perspective) with a ACTION BUTTON that run a process.

This works fine in TM1WEB, but not in TM1 APP WEB (old contributor). When you in the web page (inside contributor session) click on the button icon, nothing happens.


To be able to click on websheet buttons in Tm1 Application Web, you need to first take ownership.

Click on the icon  take ownership.

Then the buttons in the websheet will work in the Tm1 App Web (contributor session).

When you are done, you need to relase the owner ship, so other can access that company node.



More information: 

Adding or editing data in the web client allows you to submit information to your datastore. To modify data, your system administrator must grant you access.


Data that you can edit has a white background. Read-only data has a gray background. If you are not the current owner, the data opens in a read-only view. To start adding or editing data or click on button, click Take Ownership Take ownership icon.

You can edit data only if it has a workflow state of Available Not started icon or Reserved Work in progress icon. The icons indicate the workflow state.

Ownership availability for a particular node can be changed depending on how the parent node is opened. For example, contributors and reviewers who open the parent node in IBM® Cognos® Insight are not able to take ownership of the node. See the TM1® Performance Modeler documentation and the Cognos Insight documentation for details on ownership and nodes.

After taking ownership, use the Release Release ownership icon icon to release the data so other people can use it . In Cognos TM1 Application Web, you must submit all nodes at the level at which you take ownership and you can only release ownership at the level you have taken ownership.

You can insert an Action button into a worksheet so users can run a TurboIntegrator process and/or navigate to another worksheet. Users can access these buttons when working with worksheets in Microsoft Excel with TM1, or with Websheets in TM1 Web.

The action buttons will work in TM1 application Web, too, even not stated in the IBM documentation. 


Planning Analytics 2.0.9

Microsoft Windows 2019 server


After change in Windows registry to prevent use of TLS 1.0 and TLS 1.1 communication, the ODBC driver to SQL server does not work.

ODBCOpen ( vSource, vClient, vPassword );  does not work.

Error can be: DCOM was unable to communicate with the computer SQLserver using any of the configured protocols; requested by PID

SQLState: 01000
SQL Server Error:1
Microsoft ODBC SQL Server Driver DBNETLIB ConnectionOpen SECCreateCredentials()
Connection Failed
SQLState: 08001
SQL Server Error:18
SSL Security Error


Change the ODBC driver from Microsoft SQL Server ODBC Driver Version 10.00.14393 to a new, like Microsoft SQL Server Native Client Version 11.00.7462.

Backup the registry values under [HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources], to be able to restore it.

Go to Control Panel – Administrative tools – ODBC Data Sources (32-bit), to add the new driver with the same name and selected database.


You can run below commands to set the values for disabled TLS 1.0 and TLS 1.1 on the server:

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v Enabled /t REG_DWORD /d 0 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server" /v DisabledByDefault /t REG_DWORD /d 1 /f

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v Enabled /t REG_DWORD /d 0 /f 

reg add "HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client" /v DisabledByDefault /t REG_DWORD /d 1 /f


More Information:

Planning Analytics 2.0.9


What are the shortcut keys in TM1 Architect?

Partly solution:

CTRL+I  = indent (tab text)

CTRL+S = save the TI process

CTRL+Z = undo last action


Use PAW to get a better user experience.

To find where the keys are on your keyboard, use this layouts.

United Kingdom


More Information:


Planning Analytics TM1_version=TM1-AW64-ML-RTM-11.0.913.10-0

Microsoft Windows 2019 server

Microsoft SQL Server 2012 Native Client ODBC driver version 11.2.5058.0


Can not open a connection to a Microsoft SQL database, via ODBCopen command. You can open the connection in the TM1 datasource tab and read the data, but not open in a ODBCopen command in the prolog tab. When you test the ODBC connection in Windows, it works fine with your SQL login.

You get similar error, when you try a 32 bit ODBC or 64 bit ODBC connection.

In Windows event log you get this error:

Faulting application name: tm1odbcproxy32.exe, version:, time stamp: 0x62586410
Faulting module name: tm1odbcproxy32.exe, version:, time stamp: 0x62586410
Exception code: 0xc0000005
Fault offset: 0x000058f8
Faulting process id: 0xfc8
Faulting application start time: 0x01d93a5fe5481324
Faulting application path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Faulting module path: C:\Program Files\ibm\cognos\tm1_64\bin64\tm1odbcproxy32.exe
Report Id: cadfaa7a-2357-4370-9ecf-3c6500029586
Faulting package full name:
Faulting package-relative application ID:


The connection needs to use UNICODE. Use ODBCOPENex instead.

Format is: ODBCOPENEx (dataset name, dataset client name, client password, (use-Unicode-interface flag) )

In TM1 Architect data source tab, you get same error if you uncheck the “use unicode” checkbox.

In prolog enter this code:


#--- debug setup
sDEBUGfile = 'debugfile3.txt';
sDEBUGpath = 'c:\temp\';
sDEBUG1 = sDEBUGpath | sDEBUGfile ;

#-- setup the database connection
sODBCname = 'windows2016';
sUser = 'donald';
sPassword = 'Password!';

#-- open the connection to the database with unicode by adding the 1 parameter
ODBCOpenEx(sODBCname, sUser, sPassword,1);

#-- create the sql statement - adjust for your test database
sSQL = 'TRUNCATE TABLE AdventureWorksLT2019.dbo.Lista';

ASCIIOutput ( sDEBUG1, sSQL, 'check sql syntax');

#-- execute the SQL statement
ODBCOutput (sODBCname , ( sSQL) );

You can open the SQL ODBC connection in PROLOG, even when other data sources are in use.

sODBCname = ‘windows2016’; should be the name of the ODBC connection you want to use, it can be case-sensitive.

Please try to change ODBC driver in Windows Control Panel on your Windows Server. A new ODBC driver can help.


 More Information:


Planning Analytics 2.0.9x


When is metadata tab executed?


If the data view contain calculated cells, then you may need to add this in the prolog:

ViewExtractSkipCalcsSet ( sCubeName, sViewName, 0 );

to make the metadata be processed with the calculated values.

Metadata and data is only processed, if you have any data in the variables.


From Alan Kirk at

These are the roles of the tabs in a TI process:

This runs ONCE, before the data source (if any) is connected to, read or even looked at by the process.
You can use it to:

  • Create a data source by defining a view and its subsets;
  • Check for the existence of a file and run a batch file to rename or move it;
  • Change the data source from a view to a text file to a dimension subset or whatever you need it to be;
  • Dynamically assign a different data source (a different file name, a new view name or whatever) to the process at run time;
  • Define any constants;
  • Write information to a log file or to the server log;
  • Insert new elements to a dimension if you have ones that do not come from your data source;
  • Pretty much anything that you need to do before the process even looks at the data source, if any.
  • The fact that it runs before you connect to the data source is what allows you to change the data source on this tab as mentioned above using functions like DataSourceType and DatasourceNameForServer.

There is one proviso with this; one thing that you can’t do within a TI itself is to change its variable list or the data types. Normally you would define those by using an example file or view at the time that you write the process.

This loops through the data source (if any) ONCE. That is, for every record in the data source, the values in each column will be read into the corresponding process variables, then the code on the metadata tab will be executed, then the process will move onto the next row of the data source and the whole process repeats.

The purpose of the tab is to create any metadata (cubes, dimensions etc) that you will need to store the data that you upload on the Data tab.

When you use functions like DimensionElementInsert, changes are made to a copy of the dimension.

After the last record has been processed on the Metadata tab, the real dimension will be overwritten by the copy. If you did any insertions on the Prolog tab, these will also be added at that point.

Typically you will be using element names from your data source’s variables to do the element insertion. If you have a hard coded element name as you have in your example code, the TI will add the element on the first pass if it needs to, and spend every other pass saying “Nope, it’s already there, Nope, it’s already there, Nope, it’s already there, etc”. This is not what we call “optimum code efficiency”. That’s why insertions like that are generally done on the Prolog tab.

There are also newer functions like DimensionElementInsertDirect which will push the elements straight into the dimension without creating a copy. Information about such functions will be found in the Reference Guide.

IMPORTANT NOTE FOR NEW PLAYERS: If you don’t have a data source, or if you have a data source which has no records, then nothing that you have written in the Metadata tab will ever be executed. Ever.

This will again loop through each row in the data source one at a time, assigning the values in each column to variables, and doing whatever you tell it to. This may be loading values into a TM1 cube, or it may be writing values from a TM1 cube to a text file, or to another database. If you are loading values into a cube it’s assumed that you have created any necessary elements in the Metadata tab.

Note that any attributes of elements (alias names, etc) are regarded as being data for the purposes of this exercise and need to be written on the Data tab (unless you used the Direct functions mentioned above).

This is run AFTER the last Data record is processed. It is usually used to clean up whatever needs cleaning up, and maybe writing results into a control cube, according to taste.


More Information:


Microsoft Azure SQL


How list size of database?


Login to SSMS, go to your database and enter command:

EXEC sp_spaceused;


Not all commands are allowed in Azure SQL, as it is a PAAS solution. See here for diffrences: 

Permissions comparison between on-premises SQL Server and Azure SQL Database


The version of Azure SQL is the latest, and is upgraded automatic, you need to manually upgrade your databases in Azure SQL. Check the version of your database with command:

SELECT name, compatibility_level FROM sys.databases;

Azure SQL Database Compatibility with SQL Server

The version of the SQL Server database engine run by Azure SQL Database, Azure SQL Managed Instance and Azure Synapse Analytics is always ahead of the on-premises version of SQL Server, and includes the latest security fixes. This means that the patch level is always on par with or ahead of the on-premises version of SQL Server, and that the latest features available in SQL Server are available in these services. 

More Information:


Microsoft Azure SQL


Can not change default database for a SQL user in Azure SQL. User can not login to database, if it is not possible to select database in the login dialog ( like SSMS). You get this error when you try to change the default database for a SQL Azure user. If you create a SQL user that only resize in a single database, he can only login to that database. With Azure Data Studio can you select the database name during login dialog.


Add the user to the master database too, with command:

create user pbi_reader for login pbi_reader

Before that, you may have run below command to created the login:

create login pbi_reader with password = 'Password!'

Switch to the user database, enter below code to create a user there:

create user pbi_reader for login pbi_reader

grant select on schema::DM to pbi_reader

Above command will give the SQL user pbi_reader access to the tables that have schema DM.



If you are db_owner in the SQL Azure database, you can add a Azure AD account to have access, by using a command like this:


To add a user as db_datareader to a specific database, use this command:

EXEC sp_addrolemember 'db_datareader', 'pbi_reader'

To list users in database, use this command:


To be able to connect from Power BI to Azure SQL, you need to open the firewall in the database.

Power BI in the cloud, uses a span of ip address that change every week, if your database in the cloud or on-prem need to whitelist ip addresses in the firewall to gain access, it is best to eiter try to use a URL/DOMAIN in the firewall to allow PowerBI in the cloud to have access. You can create a support ticket with Microsoft Azure team and get a list of IP addresses that is used by Power BI.

For a on-prem database, the best solution is to install Azure gateway on-prem, then this box will keep the connection between on-prem and the azure based power bi service. And your on-prem databases will talk to the on-prem azure gateway.

The on-premises data gateway acts as a bridge. It provides quick and secure data transfer between on-premises data, which is data that isn’t in the cloud, and several Microsoft cloud services.

For Azure SQL in the cloud, it can be simpler to allow all cloud based services access, by inside go to your SQL server page, then to network, and mark Allow Azure services and resources to access this workspace to be enabled. This could make it possible for Power BI in the cloud to access your Azure SQL database.

You may also be able to set Azure SQL firewall with command like this (in SSMS):

 exec sp_set_firewall_rule N'Allow DB Connections', '', '';


More information:

Azure SQL Limitations compared with a SQL Server Enterprise