by ·Comments Off on Upgrade Planning Analytics Local

Product:

Planning Analytics 2.0.9.17  TM1-AW64-ML-RTM-11.0.917.9-0
Microsoft Windows 2019 server

Issue:

How upgrade TM1 on a server?

Solution:

Follow IBM instructions and do a in-place upgrade.

https://www.ibm.com/docs/bg/planning-analytics/2.0.0?topic=configuration-upgrading-planning-analytics-local 

Please remember to take backup of the important files.

The samples database is overwritten, take a backup of this folder first:

D:\Program Files\ibm\cognos\tm1_64\samples\tm1

The preserve file list the files that are not replaced, take a backup of that tm1preserved.txt file in folder:

D:\Program Files\ibm\cognos\tm1web\configuration

Backup the tm1web_config.xml for TM1WEB from:

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\configuration

Backup server.xml and jvm.options and bootstrap.properties files from folder:

D:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web

Server.xml contain the port that tm1web will use for access by users.

Backup the configuration in cognos configuration:

  1. Open Cognos Configuration.
  2. Click File > Export As.
  3. Select a location and enter a file name for the XML file.
  4. Click Save.

Backup of your PAA Agent bootstrap.properties file in folder:

D:\Program Files\ibm\cognos\tm1_64\paa_agent\wlp\usr\servers\kate-agent

Export the TM1 applications as a backup – the data is saved in the TM1 data folder.

  1. Open the TM1 Cognos Applications portal.
  2. Click the Export Application icon under the Actions column.
  3. From the File Download dialog box, click Save.
  4. Navigate to the directory to where you want to save the export file.
  5. Click Save.

Save the Tm1p.ini client configuration file for Cognos TM1 Architect and Cognos TM1 Perspectives from:

  • C:\Users\%username%\AppData\Roaming\Applix\TM1
  • C:\ProgramData\Applix\TM1

Maybe uninstall Planning Analytics Performance modeler from the windows server before you install the upgrade of TM1, to ensure you have the latest version installed.

 

Later version of Planning Analytics recommend change in TM1S.CFG file:

If you plan to use Cognos TM1 Applications without Microsoft Excel installed on the web server where you are running Cognos TM1 Web, you will need to explicitly set the ExcelWebPublishEnabled parameter to True (T) in the tm1s.cfg configuration file for your TM1 servers. Earlier versions of Cognos TM1 Contributor did not require this parameter to be set.

If the action button in tm1 websheet are wrong after the upgrade:

  1. Open the Excel sheet that contains the Action Button.
  2. Right-click the button, then click Properties.
  3. Clear and re-select the Resize Button to Caption option.
  4. Click OK.
  5. Publish the websheet again.

 

More Information:

https://www.ibm.com/support/pages/ibm-planning-analytics-20-fix-lists 

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=local-prerequisites-upgrading

https://exploringtm1.com/how-to-upgrade-planning-analytics-tm1/

https://www.ibm.com/support/pages/how-configure-tm1-application-web-connect-tm1-web-2092-ibm-planning-analytics-spreadsheet-services

https://www.pschwan.de/tm1-planning-analytics/ibm-tm1-planning-analytics-upgrade-per-skript

by ·Comments Off on How import a csv file to SQL database one time?

Product:

Microsoft SQL server

Issue:

How to import a txt file to a table inside SQL server?

Solution:

Download the file you want to import to your laptop or computer where SSMS is installed.

Right click on your database and select Tasks – Import Flat File.

Select the csv file and enter the name of the new table

 

 

Set a primary key, and change any date formatted columns to string or correct date format.

 

Then you can check the new table inside SSMS.

To use a small set in a new table, create a new table first:

CREATE TABLE [dbo].[Company](
[index] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Country] [nvarchar](50) NULL,
[Employess] [int] NULL
) ON [PRIMARY]
GO

Then to copy the data over to that new table use:

INSERT INTO [AdventureWorksDW2019].[dbo].[Company] ([index], [name], [country], [employess] )
SELECT [index], [name] , [country] , [Number_of_employees]
FROM [AdventureWorksDW2019].[dbo].[organizations] 
-- [WHERE condition];

 

Use [  ] around columns names to ensure they are not misunderstood.

https://www.geeksforgeeks.org/how-to-use-reserved-words-as-column-names-in-sql/

 

More Information:

https://www.sqlshack.com/import-flat-file-sql-server-database-using-import-flat-file-wizard/

https://www.sqlservergeeks.com/sql-server-import-flat-file-using-ssms/

You can get sample data from this sites:

https://www.stats.govt.nz/large-datasets/csv-files-for-download/

https://people.sc.fsu.edu/~jburkardt/data/csv/csv.html

https://www.datablist.com/learn/csv/download-sample-csv-files

https://www.tutorialspoint.com/sql/sql-insert-query.htm

by ·Comments Off on List user and logins in SQL

Product:

Microsoft SQL server

Issue:

What user is connected with what SQL login?

Solution:

Run this query’s:

SELECT A.name as userName, B.name as login 
FROM sys.sysusers A 
FULL OUTER JOIN sys.sql_logins B 
ON A.sid = B.sid

 

https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysusers-transact-sql?view=sql-server-ver16

select sp.name as login,
sp.type_desc as login_type,
sl.password_hash,
sp.create_date,
sp.modify_date,
case when sp.is_disabled = 1 then 'Disabled'
else 'Enabled' end as status
from sys.server_principals sp
left join sys.sql_logins sl
on sp.principal_id = sl.principal_id
where sp.type not in ('G', 'R')
order by sp.name;

https://dataedo.com/kb/query/sql-server/list-logins-on-server

SELECT *
FROM master.sys.sql_logins;

More SQL information:

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

by ·Comments Off on How run a job in ADF manually?

Product:

Microsoft Azure Data Factory

Issue:

How do i start a pipeline to run now and not wait for the scheduled run?

Solution:

Go to https://portal.azure.com/ and select you Azure Data Factory resource.

Click on button “Launch Studio”.

Click on pencil icon (author) on the left.

Expand Pipeline, so you see a list of your pipeline.

Double click on the one you want to start.

Then on top you click on Trigger to run that job.

Select “Trigger Now”

Click OK on Pipeline Run dialog.

Click on the monitor icon – and see if it is started under pipeline runs.

 

 

More Information:

https://learn.microsoft.com/en-us/azure/data-factory/concepts-pipeline-execution-triggers 

https://www.mssqltips.com/sqlservertutorial/9398/building-an-azure-data-factory-pipeline-manually/

by ·Comments Off on The remote server returned an error: (404) Not Found.

Product:

Planning Analytics Workspace 73

Issue:

When starting EXCEL and login to TM1 with PAFE you get a error after you have logged in.

The remote server returned an error: (404) Not Found.

Cognos.Office.Tm1.Connections.Tm1WebException: The remote server returned an error: (404) Not Found. —> System.Net.WebException: The remote server returned an error: (404) Not Found.
at System.Net.HttpWebRequest.GetResponse()
at Cognos.Office.Tm1.Connections.Tm1Request.GetResponse()
— End of inner exception stack trace —
at Cognos.Office.Tm1.Connections.Tm1Connection.HandleWebException(Tm1WebException x, Uri path)
at Cognos.Office.Tm1.Connections.Tm1Connection.GetResponse(Tm1Request request)
at Cognos.Office.Tm1.Connections.Tm1Connection.SendRequest(Tm1Request webRequest, AcceptReturnEnum returns)
at Cognos.Office.Tm1.Connections.Tm1Connection.SendRequest(String method, String path, String postData, AcceptReturnEnum returns, Int32 timeout)
at Cognos.Office.Tm1.Connections.Tm1Connection.Get(String path, AcceptReturnEnum file, Int32 timeout)
at Cognos.Office.Tm1.Rest.Tm1RestCapabilities..ctor(Tm1Connection connection, String server, Int32 timeout)
at Cognos.Office.Tm1.Rest.Tm1RestConnection.RefreshCapabilities(String server, String path)
at Cognos.Office.Tm1.Tm1DataSource.On_LogOn(String server)
at Cognos.Office.Tm1.Tm1DataSource.Logon(LogonType logonType, DataSourcePropertiesCollection properties)
at Cognos.Office.Framework.Communications.DataSourceManager.LogOn(String dsType, String connectionString, String serverName, IDataSource& source

In the background can Internet Explorer be open and show:

The webpage cannot be found
HTTP 404
Most likely causes:
•There might be a typing error in the address.
•If you clicked on a link, it may be out of date.

 

Suggested solution:

Check that you have a version of PAFE that is close to PAW version. https://www.ibm.com/support/pages/ibm-planning-analytics-microsoft-excel-conformance-requirements 

Inside excel on the IBM Planning Analytics ribbon select help – about. It should list version in lower right corner – like 2.0.73

If the help drop down menu show more than “using IBM Planning Analytics for Microsoft Excel” then you can have a old version.

Close excel.

Download a later version of PAFE from IBM.  https://www.ibm.com/support/pages/download-ibm-planning-analytics-local-v20-planning-analytics-microsoft-excel-release-83-fix-central

Check your version of excel – if it is 64 bit – click on the 64 bit xll file to start pafe ( IBM_PAfE_x64_2.0.73.3.xll ).

Select the IBM Planning Analytic ribbon and try to connect again.

Under options – IBM connections – you should point to the PAW servers URL.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=configuration-installing-configuring-planning-analytics-microsoft-excel

More Information:

https://exploringtm1.com/planning-analytics-for-excel-connection-to-tm1/

To add the xll to excel, do this:

  1. Download the Planning Analytics for Microsoft Excel .xll file from IBM Support Fix Central.
  2.  Rename the file to PAFEexcel.xll and move the .xll file to your desired directory. ( like c:\install ) (when you upgrade PAFE file in future you only need to replace the file on the computer – as the name is the same, the excel connection should stay working).
  3. Launch Microsoft Excel.

    When you install the Planning Analytics for Microsoft Excel .xll as an add-in to Excel, do not double-click the .xll file to open Planning Analytics for Microsoft Excel. This causes Excel to crash.

  4. Click the Developer tab.
  5. Click Excel Addins in the ribbon.
  6. Click Browse….
  7. Select the Planning Analytics for Microsoft Excel .xll file.
  8. Click OK.
  9. Go to file – options – trust center
  10. Click on Trust Locations
  11. Click on Add new location button
  12. Add the path to where you have saved your xll file.

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=icpame-downloading-upgrading-planning-analytics-microsoft-excel-single-xll-add-in-versions-2065-later

by ·Comments Off on What are the tables function in Content Store

Product:

Cognos Analytics

Problem:

What are the tables for in CA11 database?

Solution:

This is a old list for Cognos 8, maybe it is still valid.

CMSYSPROPS This table has the Content Store Version.
CMOBJNAMES This table has the names of all the objects in the content store.
CMOBJPROPS1 Users, Roles \ Group Distribution list and contact information such as Email, phone number, Fax Given name etc are stored in this table
CMOBJPROPS2 Report scheduling information is stored. This table has fields like hour, day week etc
CMOBJPROPS3 Stores Screen Tip and Object description provided while creating the objects are stored here
CMOBJPROPS4 Stores the printer paper setting details like height and width of A3 , A4 , letter and 11X17 paper orientations
CMOBJPROPS6 Has the details of the all packages which were published using the FM.
CMOBJPROPS7 This table stores the XML of all reports and models. This is basically to maintain the metadata about the structure of the reports and models.
CMOBJPROPS10 Contact information is stored in this table. This table has columns like Contact Email and Contact.
CMOBJPROPS11 This table stores the data sources configuration details like connect string, the cube location etc.
CMOBJPROPS13 It stores the names of parameter passed to the range prompts
CMOBJPROPS14 Has details regarding the versions, creation time etc of the objects in the content store.
CMOBJPROPS16 This table provides the status of the multiple services of Cognos like LogService, MonitorService, ReportService, SystemService, JobService
CMOBJPROPS17 Stores performance details of each component like “query Studio, Analysis Studio, Event Studio” etc
CMOBJPROPS18 This table stores the drill path from the source to the final target report.
CMOBJPROPS20 Stores details regarding which are all the reports for which prompting has been enabled. And also has the details of which are the reports which has the default report options overridden.
CMOBJPROPS24 Stores the printer configuration details
CMOBJPROPS25 Stores the data regarding the objects deployed, like the deployed folder, the reports, the number of folders present in the deployment archive, etc
CMOBJPROPS26 This table stores the data about all the packages imported / exported in C8, with the properties selected during the process.
CMOBJPROPS27 Has the details regarding the data source created in the content store using Cubes.
CMOBJPROPS30 Stores the registration , service description etc details about portlets in this table
CMOBJPROPS31 Has the custom logging level for each of the Cognos services, CMID can be be linked to CMOBJNAMES for the names of each of the services
CMOBJPROPS32 Has the details of the stored procedures used as the query items in the FM model.
CMOBJPROPS33 Detail related to users, user groups user roles.
CMOBJPROPS34 Has the details regarding the drill through parameters of the drill through reports. The parameter assign values are present in coded format
CMOBJPROPS36 Has the list of all the Models published using framework manager
CMOBJPROPS37 This table has the details of the routing sets configured for server / load balancing for each package published in the content store
CMOBJPROPS38 Configuration details about number of items to retrieve in studios, for a package are saved under this table.
CMOBJPROPS39 Has values for properties of reports and views. PROPID can be linked to CMPROPERTIES for property names
CMOBJPROPS52 Contains the properties for the connections
CMOBJPROPS55 Has the URI for icons for each entry in Cognos Connection
CMLOCALES Has the locale ids associated with each language supported by Cognos 8

 

More Information:

https://www.envisn.com/envisn-cognos-blog/bid/43348/Demystify-Your-Cognos-Content-Store-Part-II 

https://www.cognoise.com/index.php?topic=28289.0

Below will list all reports

With CognosContent AS (
Select a.CMID, a.PCMID, b.NAME, b.Name as Parent, c.name as Content
,Cast(b.NAme as varchar(max)) as path
, 0 as level
from cmObjects a
inner join CMOBJNAMES b on a.CMID = b.CMID
inner join CMCLASSES c on a.CLASSID = c.CLASSID
Where b.ISDEFAULT = 1
and a.PCMID =0
and a.CMID = 2
UNION ALL
Select a.CMID, a.PCMID, b.NAME as parent, d.NAME, c.NAME
, d.path + '\' + CAST (b.Name as varchar(Max)) as path
, d.level + 1 as level
from cmObjects a
inner join CMOBJNAMES b on a.CMID = b.CMID
inner join CMCLASSES c on a.CLASSID = c.CLASSID
inner join CognosContent d on a.PCMID = d.CMID
Where b.ISDEFAULT = 1
)
Select NAME as ReportName, path, level, content from CognosContent
Where
Content = 'report' or Content = 'dashboard' or content = 'analysis'
order by path

 

 

by ·Comments Off on How list the active schedules in Cognos Analytics?

Product:

Cognos Analytics 11
Microsoft Windows 2019 server
Microsoft SQL server

Issue:

How can i see in a report the schedules that are active in CA11?

Solution:

The information is in the CA11 Content Store database. You have to make a SQL query like this:

SELECT  [CMID]
,[TASKID]
,[STARTDATE]
,[ENDDATE]
,[ENDTYPE]
,[EVERYNPERIODS]
,[DAILYPERIOD]
,[MONTHLYABSDAY]
,[MONTHLYRELDAY]
,[MONTHLYRELWEEK]
,[TYPE]
,[YEARLYABSDAY]
,[YEARLYABSMONTH]
,[YEARLYRELDAY]
,[YEARLYRELMONTH]
,[YEARLYRELWEEK]
,[ACTIVE]
,[WEEKLYMONDAY]
,[WEEKLYTUESDAY]
,[WEEKLYWEDNESDAY]
,[WEEKLYTHURSDAY]
,[WEEKLYFRIDAY]
,[WEEKLYSATURDAY]
,[WEEKLYSUNDAY]
,[PRIORITY]
,[INTRARECURSTART]
,[INTRARECUREND]
,[INTRARECURINTERVAL]
FROM [dbo].[CMOBJPROPS2]
Where ACTIVE = 1

 

To list all objects name use:

select ob2.cmid,
c.name as className,
n.name as objectName
from [dbo].[CMOBJPROPS2] p
inner join CMOBJPROPS26 o on p.cmid=o.cmid
inner join CMOBJECTS ob on ob.cmid=o.cmid
inner join CMOBJECTS ob2 on ob.pcmid=ob2.cmid
inner join CMOBJNAMES n on n.cmid=ob2.cmid
inner join CMCLASSES c on ob2.classid=c.classid

 

More Information:

https://www.ibm.com/docs/en/cognos-analytics/11.1.0?topic=mobile-enable-disable-scheduling

https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?GroupId=3061&MessageKey=439d5b09-46a1-4fe1-988f-e468bb5c2254&CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=digestviewer 

by ·Comments Off on Principal ‘xyz’ could not be found or this principal type is not supported

Product:

Microsoft Azure SQL

Issue:

Error when try to create a user in AZURE SQL from a Managed Identity Object ID.

Principal ‘xyz’ could not be found or this principal type is not supported.

Cannot add the principal ‘xyz’, because it does not exist or you do not have permission.

Solution:

Use the managed resource name instead of the object id.  Managed Identity can be replaced with the resource name, when referring to the object.

Background:

To make Azure Data Factory to connect to a Azure SQL resource with Managed Identity, you need to create a Managed identity for the ADF resource.

Then you can add the resource to AZURE SQL with below command to give it access to the database.

Login to the AZURE SQL server with SSMS, use the Azure SQL server name to connect.
Select the database and click New Query:

CREATE USER [adf_name] FROM EXTERNAL PROVIDER

ALTER ROLE [db_owner] ADD MEMBER [adf_name]

By adding a USER direct to the database, and not create a login in SQL, the user must provided the database name when it connects to AZURE SQL.

 

If you are Owner of the Azure database, then you do not need to be AAD Admin to be able to perform the change in SSMS.

“Azure Active Directory authentication allows you to centrally manage identity and access to your Azure SQL Database.”

 

More Information:

Connect Azure SQL from Data Factory using Managed Identity

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal

https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-service-principal-tutorial?view=azuresql

https://crmchap.co.uk/principal-could-not-be-found-or-this-principal-type-is-not-supported-error-azure-sql-server/

https://www.data4v.com/managed-identity-between-azure-data-factory-and-azure-storage/

by ·Comments Off on java.lang.ArrayIndexOutOfBoundsException: Array index out of range

Product:

Planning Analytics 2.0.9.3

Microsoft Windows 2019 server

Issue:

A few users can not see views in TM1WEB. They get a blank screen. If they become ADMIN, then the view works.

The issue is only for a few cubes. Other cubes work well.

Inside TM1WEB log you find this error:

[Default Executor-thread-9556] ERROR ([]) com.ibm.cognos.tm1.web.services.CubeViewService – [] Exception Error: null Stack Trace: java.lang.reflect.InvocationTargetException

Caused by: java.lang.ArrayIndexOutOfBoundsException
at com.applix.tm1.TM1Val.getArrayVal(TM1Val.java:310)

Log files are found at D:\Program Files\ibm\cognos\tm1web\wlp\usr\servers\tm1web\logs\messages.log and

D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\WEB-INF\logs\tm1web.log

Solution:

The user that have issues, does not have READ rights to the picklist in the cube where the view is. They have access rights to the cube and dimensions, but not to the picklist cube that is used by the rule in the cube.

Turn on “display control objects” in TM1 Architect.

Go to Cube – Security Assignments.

Check that, the group the users are part of, have READ for the hidden picklist for the cubes they have problems with.

They start with }PickList_

Increase of memory for JAVA did not solve the issue.

More Information:

https://tech.forums.softwareag.com/t/java-garbage-collection-tuning-strategy-an-example/236936

https://www.digitalocean.com/community/tutorials/java-jvm-memory-model-memory-management-in-java

https://blog.gceasy.io/2022/03/04/garbage-collection-tuning-success-story-reducing-young-gen-size/

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=lists-creating-pick-control-cubes

https://exploringtm1.com/tm1-picklists/ 

by ·Comments Off on Multiple data directories

Product:

Planning Analytics 2.0.9.13

Microsoft Windows 2019 server

Issue:

How keep settings cube values in test, when you copy the production tm1 data folder over?

Solution:

Use two data folders – one for the cube you do not want to copy and the other folder for the rest.

Setup your TM1 instance, and create the data folder.

Create your sys.settings cube and other you need in your application.

Inside TM1 Architect click on Save Data.

Stop the TM1 instance.

Create a new data folder called: data_syssettings

Move the sys.settings cube to the new folder.

Update the TM1S.CFG file for the new data folder.

# Example:
# DataBaseDirectory=C:\Program Files\Cognos\TM1\Custom\TM1Data\PlanSamp\
DataBaseDirectory=".\Data;.\Data_SysSettings"
LoggingDirectory=D:\TM1 data\FAP\Logs

Save the TM1S.CFG file and start the TM1 instance.

Changes to the cube in the other folder will only be written to that folder.

 

A other way is to store the environment values in a text file on the server, and then have a TI process that you run that will update the sys.settings with the correct values for this server. Run that TI process every time after you have copied over the data folder from PROD to TEST server.

 

More Information:

https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=architecture-data-directory-overview 

You can specify that you want IBM TM1 Server to use multiple data directories by separating the directory names with semicolons. When you specify multiple data directories, TM1 does the following.
  • Accesses cubes and dimensions from each of the specified directories. If there is a duplicate object, TM1 accesses the object from the first directory specified.
  • Writes changes to the directory where the object is located. When you create a new object, TM1 writes to the first directory you had specified.

https://www.redhat.com/sysadmin/linux-path-absolute-relative 

The ASCIIOutput and TextOutput TurboIntegrator functions do not work with the output path of \\data\s on the Planning Analytics system. Instead, use ./ to indicate a relative path to the TM1® data directory and shared folder on your Planning Analytics system in the cloud.

https://www.ibm.com/docs/sv/planning-analytics/2.0.0?topic=cloud-relative-paths-asciioutput-textoutput-functions