Month: March 2023

How set security on tm1web

March 30, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

How set that a user group only see some tm1 web sheets and not all in tm1 web for a TM1 instance?

Solution:

Inside TM1 Architect, you create the Applications, and add web sheets or files or views to the application. Create a extra folder (application) for normal users and a folder for admin users. Then drag the views to the different folders, and publish the web sheet to the different folders. This give that you have two folders under the main application folder.

Right click on your first application – and select new – application, that gives you a new folder.

Then right click on the top folder and select security – security assignments.

Set NONE for everyone except for the administrator group, on the admins folder. This will give that only people part of the admin group will have access to the folder and any content inside it, like web sheets or links.

In the left you see the objects, like the folders, and then in columns you have the groups you have defined. If you use CAM security, you can add the CA groups to columns. Mark on cell for a user group and the folder you want to change security for, set NONE, if no access should exist. Click on OK.

By creating applications (folders) and set security on then, you can add views and websheet to the folder, and they will have the same access as the folder.

Remember to make the folder and tm1websheets public, after you have made them part of the application.

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=developers-organizing-objects-in-tm1-applications

Creating a TM1 Contributor Model

http://web.archive.org/web/20180425055420/http://tm1up.com/10-tm1-applications.html#more-1345

How create a parameter cube

March 27, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

How easy create a parameter cube, where we can store common values like file paths or servername, that change when we move the TM1 application between servers.

Suggested solution:

Create a TI process with this PROLOG – update the values to your need:

#--------------------------------------------------------------------
# process to create a simple parameter cube
#--------------------------------------------------------------------

CubeName1 = 'SYS.ParameterCube';
DimName1 = 'sys.value' ;
DimName2 = 'sys.function' ;

# create dimension
IF ( DimensionExists ( DimName1 ) = 0 ) ;
DimensionCreate ( DimName1 ) ;
ENDIF ;

IF ( DimensionExists ( DimName2 ) = 0 ) ;
DimensionCreate ( DimName2 ) ;
ENDIF ;

#add elements to dimension
Elname1 = 'ExportFileArea';
Elname2 ='String';
Elname3 ='Number';
Elname4 = 'Explanation' ;
ElType1 = 's'; 
ElType2 = 'n';

DimensionElementInsert (DimName1, '' ,ElName2, ElType1 ) ;
DimensionElementInsert (DimName1, '' ,ElName3, ElType2 ) ;
DimensionElementInsert (DimName1, '' ,ElName4, ElType1 ) ;
DimensionElementInsert (DimName2, '' ,ElName1, ElType1 ) ;

# then only add extra rows for other functions
# Elname5 = 'Servername';
# DimensionElementInsert (DimName2, '' ,ElName5, ElType1 ) ;

# create a cube
IF( CubeExists( CubeName1 ) = 0 ) ;
CubeCreate ( CubeName1 , DimName2 , DimName1 ) ;
ENDIF ;


# add default values to the matrix
sFirstPath = '\\servername\filesharename\' ;
sExplain = 'The path to be used in beginning of filepath used in file exchange' ;

CellPutS ( sFirstPath , CubeName1 , Elname1 , Elname2 ) ;
CellPutS ( sExplain , CubeName1 , Elname1 , Elname4 ) ;

Run the TI process to create the dimensions and cube.

Then on the TI process that should use the parameters, include a code like this:

# use the values to write a file

# define variables
CubeName1 = 'SYS.ParameterCube';
Elname1 = 'ExportFileArea';
Elname2 ='String';
vFile = 'magicfile.txt';

# get value from parameter cube
vReplacePath = cellgets ( CubeName1 , Elname1, Elname2 ) ;

# create the new path and file name
vPath = vReplacePath | 'data\' ;
vFilePath = vPath | vFile ;

# write to the file
ASCIIOUTPUT ( vFilePath , 'This is the text written to the file' );

This code can be improved – that is up to you.

More Information:

https://exploringtm1.com/asciioutput-tm1-function-use-syntax/

https://www.wimgielis.com/tm1_clearingbigchunksofdata_EN.htm

https://exploringtm1.com/dimensionelementinsert-tm1-function-use-syntax-and-examples/

https://exploringtm1.com/cellputs-tm1-function-use-syntax/

DB21061E Command line environment not initialized.

March 23, 2023 by Roger·Comments Off

Product:

DB2 connect

Issue:

How see what server is in use for the DB2 ODBC connection, when the setup of the DB2 ODBC connection does not work.

Suggested solution:

Go to a command prompt, and go to the folder where the DB2 active client files are installed. Can be C:\Program Files\IBM\SQLLIB\BIN folder.

Run command db2 list node directory to see what server you are connecting to.

If it does not work, start the program db2cwadmin to get a CMD prompt where you can run the db2 commands.

In windows registry you can see part of the ODBC settings in your setup;

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
“PROD”=”IBM DB2 ODBC DRIVER – DB2COPY1”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\PROD]
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER]
“UsageCount”=dword:00000001
“Driver”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\Program Files\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – DB2COPY1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\SQLLIB\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\IBM DB2 ODBC DRIVER – IBMDBCL1]
“UsageCount”=dword:00000001
“Driver”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2CLIO.DLL”
“Setup”=”C:\\PROGRA~1\\IBM\\IBMDAT~1\\BIN\\DB2ODBC64.DLL”
“CPTimeout”=”60”

More of the DB2 connection is stored in files, that are updated when you run command like this:

Use a bat file to call the config file:

if not exist "\Program Files\IBM\SQLLIB\BIN" goto stopp
cd \Program Files\IBM\SQLLIB\BIN
db2cmd db2setcp " db2 -tvf C:\db2script\catalog.sql " 
:stopp

Catalog.sql file can contain command like this;

UNCATALOG NODE db2prod;
CATALOG TCPIP NODE db2prod
REMOTE db2servername.domain.com
SERVER 60000
REMOTE_INSTANCE db2prod
OSTYPE LINUX;

UNCATALOG DATABASE PROD;
CATALOG DATABASE PROD
AS PROD
AT NODE db2prod
AUTHENTICATION SERVER;

catalog odbc data source PROD;

(PROD is the database name and the ODBC connection name, change to your need)

Also check the C:\Windows\System32\drivers\etc\hosts file for any hard coded references to the DB2 server.

Add row like this in the HOSTS file, to point to the DB2 server ip to maybe improve connection start speed;

# the new DB2server values
10.20.30.40 db2servername.domain.com

More Information:

https://www.dbisoftware.com/db2nightshow/20181214DB2NightZ92.pdf

https://www.scriptcase.net/docs/en_us/v81/connecting-your-database/ibm-db2/windows/connecting-with-ibm-db2

https://docs.rackspace.com/blog/ibm-db2-database-administration/

https://en.wikiversity.org/wiki/Hosts_file/Edit

https://helgeklein.com/blog/where-is-the-hosts-file-on-windows-x64/

https://www.inmotionhosting.com/support/website/modifying-your-hosts-file/

How show network diagram of Azure?

March 22, 2023 by Roger·Comments Off

Product:

Microsoft Azure Network

Issue:

Can I show a diagram over the network in my Azure subscription?

Solution:

Log into the Azure portal with an account that has the necessary permissions.
On the top, left corner of the portal, select All services.
In the All services filter box, enter Network Watcher. When Network Watcher appears in the results, select it.
Select Topology. The network watchers are created in a resource group named NetworkWatcherRG.
Select a subscription, the resource group of a virtual network you want to view the topology for, and then select the virtual network.

In Azure you can select the Virtual network, and click on Diagram on the left side.

Then you get a picture of the resource you have in Azure.

More Information:

https://learn.microsoft.com/en-us/azure/network-watcher/view-network-topology

https://learn.microsoft.com/en-us/azure/security/fundamentals/infrastructure-network

List only the latest records from a date column

March 20, 2023 by Roger·Comments Off

Product:

Microsoft SQL server 2016

Issue:

How list only the latest rows with the same date in the update column?

Solution:

Create a view – who will only list the latest row based on the date column;

CREATE VIEW [DM].[AccountView-Latest]
AS
SELECT [key_dimAccount]
,[Name]
,[InsertDate]
,[UpdateDate]
FROM [DM].[dimAccount]
WHERE UpdateDate = ( SELECT MAX(UpdateDate) FROM [DM].[dimAccount ] )

The where statement will only take the latest date (max) from the updatedate column – should give a list with only the records that are updated at same date.

More Information:

https://www.w3resource.com/sql/aggregate-functions/max-date.php

https://www.w3schools.com/sql/func_sqlserver_getdate.asp

When did some one access the database?

March 15, 2023 by Roger·Comments Off

Product:

Microsoft SQL server 2016

Issue:

How do i see when someone last accessed the database?

Solution:

Use any of this SQL querys;

SELECT d.name,
last_user_seek = MAX(last_user_seek),
last_user_scan = MAX(last_user_scan),
last_user_lookup = MAX(last_user_lookup),
last_user_update = MAX(last_user_update)
FROM sys.dm_db_index_usage_stats AS i
JOIN sys.databases AS d ON i.database_id=d.database_id
GROUP BY d.name

Declare @last_boot datetime
Set @last_boot = (select [sqlserver_start_time] from sys.dm_os_sys_info)

select @@servername as [ServerName],'last_boot' = @last_boot, 'days_since_last_boot' = datediff(d, @last_boot, getdate())

if object_id('tempdb..##Table_usage_data') is not null
drop table ##Table_usage_data
create table ##Table_usage_data (ID int identity (1,1), [database] varchar(255),[last_user_seek] datetime,[last_user_scan] datetime, [last_update] datetime)

declare @get_last_user_activity_timestamp varchar(max)
set @get_last_user_activity_timestamp = ''
select @get_last_user_activity_timestamp = @get_last_user_activity_timestamp +
'select db_name([database_id]), max(last_user_seek), max(last_user_scan), max([last_user_update]) from sys.dm_db_index_usage_stats where db_name([database_id]) = ''' + [name] + ''' group by [database_id];' + char(10)
from sys.databases where [database_id] > 4 and [state_desc] = 'online'

insert into ##Table_usage_data ([database],[last_user_seek],[last_user_scan],[last_update])
exec (@get_last_user_activity_timestamp)

select
[database], last_user_scan,last_user_seek,last_update
from
##Table_usage_data

More Information:

https://www.dbblogger.com/post/identify-when-your-sql-database-was-last-used

What version do i have?

March 15, 2023 by Roger·Comments Off

Product:

Cognos Analytics 11.1.7

Microsoft Windows 2019 server

Issue.

How do i find the version of CA11 i have installed?

Solution:

Check the cmplst.txt file in folder D:\Program Files\ibm\cognos\analytics

The row Manifest=casrv-manifest is the closeset to find the version/build number you can compare to site at IBM.

Operating System Version: 10.0
Manifest=casrv-manifest-11.1.7-2101131319-winx64h.json
Installer=analytics-installer-2.0.20100517-win.exe

Search 11.1.7-2101131319 on the IBM page – if it is not lised, you have a interim fix pack between two other numbers.

Cognos Analytics 11.1.R7 FP3	11.1.7.3	11.1.7-2106251648	2.2.2
Cognos Analytics 11.1.R7 FP4	11.1.7.4	11.1.7-2111101256	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 6	11.1.7.4	11.1.7-2112131555	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 7	11.1.7.4	11.1.7-2112191704	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 8	11.1.7.4	11.1.7-2201050500	2.2.11
Cognos Analytics 11.1.R7 Interim Fix 9	11.1.7.4	11.1.7-2204120500	2.2.15
Cognos Analytics 11.1.R7 FP5	11.1.7.5	11.1.7-2205311318	2.2.16
Cognos Analytics 11.1.R7 FP6	11.1.7.6	11.1.7-2210041400	2.2.22

More Information:

https://www.ibm.com/support/pages/how-determine-which-version-cognos-analytics-you-have-installed

https://pmsquare.com/analytics-blog/2022/6/8/how-to-find-your-cognos-version-build-and-common-name

https://www.ibm.com/support/pages/node/6621349

Checking Driver Capabilities for database

March 8, 2023 by Roger·Comments Off

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

In tm1server.log file there is reference to wrong ODBC connection. Like this

TM1.Process Process “second.process” run from process “main.process” by user “AD/donald”
TM1.SQLAPI Checking Driver Capabilities for database “TEST”
TM1.SQLAPI Driver supports SQLFetchScroll

Possible solution:

As the Data Source tab is pointing to the TEST odbc connection, a test connection is made and written in the log file.

Change the ODBC name in the Data Source tab to the expected ODBC connection like: PROD

You can still have the TI process change the ODBC connection to correct one in the prolog tab, with code like this:

#--------------------------------------------------
# Set source with values from variables you have defined before
#--------------------------------------------------
DataSourceType='ODBC';
DatasourceNameForClient=sODBCConnection;
DatasourceNameForServer=sODBCConnection;
DatasourceUsername=sUser;
DatasourcePassword=sPassword;
DatasourceQuery = sDataSourceQuery ;


#-----------------------------------------------------------------------------
# open the connection to the database ODBC connection
#-----------------------------------------------------------------------------
ODBCOpen(sODBCconnection, sUser , sPassword );

# this will change the ODBC connection, and the metadata and data tab will use the new ODBC source.

The DatasourceNameForServer= variable will set the ODBC source used by the process when run from a chore.

More Information:

https://www.ibm.com/support/pages/tm1sqlapi-01000microsoftodbc-driver-manager-cursor-library-not-used-load-failed

https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=variables-datasourcenameforserver

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=tv-turbointegrator-local-variables