Microsoft Power BI


What is Enable Load and Include In Report Refresh in the power bi transform data for each query?



“Include In Report Refresh” means query is automatically refreshed when you press “Refresh” button on the ribbon. “Enable Load” means query results are available for report builder. Otherwise you may use it in your other queries (for example to merge data), but it is not shown in the report builder.

If you have a query that use a other query, that is unchecked “Include In Report Refresh”, then both query’s are updated when you update the first query.

This works in Power BI Desktop but not in Power BI service at 2022, but can have changed. If you unchecked Include in report refresh – The functionality works only in Power BI Desktop and it will not have any impact in Power BI Service i.e. if you refresh report in Power BI service, this will still refresh all the queries even through Include in report refresh is unchecked.


More Information: 

Keep The Existing Data In Your Power BI Dataset And Add New Data To It Using Incremental Refresh


Cognos Controller 11.0.1200
Microsoft Windows 2019 server
Microsoft SQL 2016 server

How install Cognos Controller 11?


This are an example of the steps to install Cognos Controller 11.  (Some information below may be incorrect or not needed)

For best guides follow the IBM documentation.

Before any work with Cognos Controller – take a extra SQL backup of the database.

Download the software from here:

You need to get both the RTM version and any fix pack you need to install.

Install IIS on the server that will run Cognos Controller. Microsoft .NET Framework 4.7.2 must be installed on the Controller Web Services Server computer. Install sqlncli.msi driver version 11.4.7462.6 or later version for SQL server.

Select the following when you add the Web Server (IIS) role to the windows server:

  • .NET Framework 4.5 and 3.5
  • ASP
  • CGI
  • ISAPI Extensions
  • ISAPI Filters
  • .NET Extensibility
  • IIS Management Console
  • IIS Management Scripts and Tools
  • HTTP Activation
  • HTTP Redirection
  • Directory Browsing

Cognos Controller 11 only supports Microsoft SQL server database,  you need to create one database, if you not already have one from an old version of Cognos Controller. Ensure that the database collation sequence is case insensitive, and is the same for both the SQL server (tempdb database) and the Controller database. (Recommended:  SQL_Latin1_General_CP1_CI_AS ) Controller tables owned by dbo are not supported. If you have dbo as the default table owner you must change it, easiest is to create a new database from start in SSMS.

To create a schema named cognos:

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'cognos'))

To change schema on one table:

TRANSFER dbo.Employees

To change default schema for a user in SQL:



If you do a upgrade of Cognos Controller 11 on a old server, you need to uninstall the previous Cognos Controller 10 version first.

Check if the anti-virus software on the Windows server is compatible with you installing of software.

Copy the installation media to the server and unzip it.

Run (as a administrator) the issetup.exe from folder C:\install\cntrl_11.0.1_win_ml.tar\winx64h\ to install Cognos Controller.

Click Next

Mark I agree and Click Next

Mark non-production (if not production server) and click Next

Click Yes

Normally you select all. In some cases the FAP is only installed on your TM1 server. Controller Web is only needed if you going to use it.

Click Next.

Click Next

Click Next

Wait until it is done

Click Finish.

In the log file you can find this messages:
Install component: VFP
Component Version: VFP-AW64-ML-RTM-9.0.2120.0
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-9.0.2120.0-inst.ver” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-inst.ver” successfully extracted.
Successfully created directory “C:\Windows\system32\server\”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll” to file “C:\Windows\system32\server\vfp9r.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9r.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9r.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9r.dll”.
Successfully set value “C:\Windows\system32\server\vfp9r.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll” to file “C:\Windows\system32\server\vfp9renu.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9renu.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9renu.dll””.
Error: Application has returned exit code “4”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9renu.dll”.
Successfully set value “C:\Windows\system32\server\vfp9renu.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll” to file “C:\Windows\system32\server\vfp9t.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9t.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfp9t.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfp9t.dll”.
Successfully set value “C:\Windows\system32\server\vfp9t.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-9.0.2120.0-inst.ver”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfp-winx64h-app-inst.ver”.
Successfully added value “1” for key “VFP” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Install component: VFPCOM
Component Version: VFPCOM-AW64-ML-RTM-9.0.2123.0
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-9.0.2123.0-inst.ver” successfully extracted.
File “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-inst.ver” successfully extracted.
Successfully copied file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll” to file “C:\Windows\system32\server\vfpcom.dll”.
Self registration of file using command line “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfpcom.dll””.
Error: Failed system command “C:\Windows\system32\regsvr32.exe /s “C:\Windows\system32\server\vfpcom.dll””.
Error: Application has returned exit code “3”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\server\vfpcom.dll”.
Successfully set value “C:\Windows\system32\server\vfpcom.dll” for key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\SharedDLLs”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-9.0.2123.0-inst.ver”.
Successfully deleted file “C:\Users\ADMINI~1\AppData\Local\Temp\COG_2\vers\vfpcom-winx64h-app-inst.ver”.
Successfully added value “1” for key “VFPCOM” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error in log file show that the COM objects for Cognos Controller was not installed. Uninstall cognos controller and do the installation again.


Error: Failed to retrieve environment variable “JAVA_HOME”.
Error: Processing XML file “C:\Program Files\ibm\cognos\ccr_64\bin\wct\ccrconfigwebserver.xml” returned errors. View the file “C:\Program Files\ibm\cognos\ccr_64\instlog\isconfig-CNTRL-20240315_1517.log” for details.
Successfully added value “1” for key “CCRWEBSERVER_VERC” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error is common, but should not affect the software performance.


Error: Processing XML file “C:\Program Files\ibm\cognos\ccr_64\frontend\fcmconfigfrontendserver.xml” returned errors. View the file “C:\Program Files\ibm\cognos\ccr_64\instlog\isconfig-CNTRL-20240315_1517.log” for details.
Successfully added value “1” for key “CCRFCMFRONTEND_VERC” under section “Component List” in file “C:\Program Files\ibm\cognos\ccr_64\uninstall\uninst.ini”.

Above error we have no clue what it is.


Run the upgrade/fix pack from folder like C:\install\up_cntrl_winx64h_11.0.1200.36_ml.tar\winx64h\issetup.exe

Click Next

Click Next

Select same path as before and click Next

Click Next


Click Finish.

Reboot the windows server.

Check the cmplst.txt file that the correct controller version is installed:

LICENSE_CONTROLLER_name=IBM Cognos 8 License
CONTRL_name=IBM Cognos Controller
[Product Update Information]

CONTRL_UPDATE_name=IBM Cognos Controller Update
[Main Applications]

Copy the sqljdbc42.jar driver to folders like C:\Program Files\ibm\cognos\ccr_64\server\FAP\lib and C:\Program Files\ibm\cognos\ccr_64\server\integration and C:\Program Files\ibm\cognos\ccr_64

Start Cognos Configuration and setup the values needed, start with the database connection to your Cognos Controller database.

See more details in how to setup old cognos controller here


The provider should be SQLNCLI11.1 and data source is the name of the windows database server. You should use a db server name and not ip address.

Click test connection, click save icon, and then click run icon – to start the DBCONVERT program. Here you need to upgrade your Cognos Controller database to the new version.

Updater Kit Build FCM Web Client (classic) CCR Version DB Version Comment
11.0.1200.1015 11.0.1200.55 11.0.1200.10 11.0.1200.8 IF2 1015
11.0.1200.1002 11.0.1200.37 11.0.1200.10 11.0.1200.8 IF1 1015 Available on Passport Adv. (full installation)
11.0.1200.36 11.0.1200.28 11.0.1200.10 11.0.1200.8 11.0.1 FP2 1015 Generally available (FixCentral)
11.0.1000.8 11.0.1000.6 11.0.1000.1 11.0.1000.2 11.0.1 FP1 1013 Generally available (FixCentral) 11.0.1 1012 Available on Passport Adv. (full installation) IF1 1011 Generally available (FixCentral) RTM 1011 Available on Passport Adv. (full installation)
10.4.2200.222 10.4.2200.228 10.4.2200.79 10.4.2200.77 IF9 1010


If you have a demand for TLS 1.2 in your Windows environment or run old version of Windows, then you can get a error like below when you click “connect”.

** ERROR: Drivrutinen kunde inte upprätta en säker anslutning till SQL Server med SSL-kryptering (Secure Sockets Layer). Fel: “Unexpected rethrowing”.

Can it be that SQL Server 2005 that uses 3DES algorithm in TLS connection and with new versions of JRE, this algorithm is disabled, Or the database is an older database (like SQL Server 2014) which does not allow higher security standards like TLS 1.2.

Then You need to start DBCONV program from a bat file with this settings (save it in a text file called DBCONV.BAT and run it) : 

"C:\Program Files\IBM\cognos\ccr_64\bin\jre\8.0\bin\java.exe" -cp"C:\Program Files\ibm\cognos\ccr_64\bin64\jre\8.0\bin\javaw.exe" -cp "C:\ProgramFiles\ibm\cognos\ccr_64\DbConv.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\antlr-3.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-common.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-integration-server.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-integration.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ccr-xml.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\cglib-nodep-2.1_3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-bean-collections-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-beanutils-core-1.8.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-cli-1.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-codec-1.8.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-collections-3.2.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-dbcp-1.2.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-io-2.4.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-lang-2.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-logging-1.1.1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\commons-pool-1.3.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\db2jcc.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\DBConv.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\icu4j-4_8_1_1.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\jsr173_1.0_api.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\jython.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\log4j-1.2.8.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ngtm1api.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\ojdbc6.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\resolver.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\spring.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\sqljdbc42.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\stringtemplate-3.2.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xbean.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xbean_xpath.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xmlbeans-qname.jar";"C:\Program Files\ibm\cognos\ccr_64\server\integration\xmlpublic.jar"


If this is a new empty database – you need to click Create DB (this will erase the database, and create a empty controller database).

Then do a upgrade of the cognos controller database.

You should get to above picture without errors, if the upgrade of the cognos controller database was successful.

Click Close


Setup of IIS Manager:

  1. In the Internet Information Services (IIS) Manager,  select Application Pools.
  2. Create a new Controller application pool and then from the Actions pane, select Advanced Settings.
  3. Set the .Net CLR Version to v.4.0.
  4. Set Enable 32-Bit Applications to False.
  5. Set Identity to LocalSystem and time-out to 600.
  6. Click OK.
  7. If you do not have CA11 installed, you can point the ibmcognos virtual directory to a empty folder like webcontent.
  8. Expand Sites and under your web site, create the following virtual directories as shown in the table. If you are installing on the same machine as IBM Cognos Analytics, create the virtual directories under the ibmcognos virtual directory.

    controller controller_install_location\ccrvdir
    controllerbin controller_install_location\webcontent\ccr
  9. Select the controller virtual directory.
  10. double click on HTTP redirect. 
  11. Select Redirect Requests to this destination and enter the following path:

  12. Click Apply.
  13. Right-click your web site and click Add Application.
    1. Set Alias to ControllerServer.
    2. Set Application pool to Controller.
    3. In the PhysicalPath field, enter controller_install_location/ControllerProxyServer.

    4. Click OK.
  14. When you only use native login in Cognos Controller, keep the authentication in IIS to Anonymous Authentication.


On the Cognos Controller server Open C:\Program Files\IBM\cognos\ccr_64\ControllerProxyServer\Web.config and add

<add key="ccrReports" value="http://[servername]:9082/fcm.reports/report" />


Ensure you use space and not tabs inside the config file. Change the localhost settings to the servername in cognos controller configuration, so the client program can find the cognos controller server.

Go to folder C:\Program Files\ibm\cognos\ccr_64\fcmweb\wlp\etc\
Open the file server.env inside notepad++
Add the following line:
JAVA_HOME=C:/Program Files/IBM/cognos/ccr_64/fcmweb/jre

Save the changes to the file.


Setup of Cognos Configuration:

Use FQDN server name instead of only the server name.

Above should you not change, it is configured from the report server settings.

Above you must fill out with the full server and domain name.

Start the batch service in Cognos Controller.



When you run a report in Cognos Controller 11 and get a error like:

HTTP Error 404.0 – Not Found

The resource you are looking for has been removed, had its name changed, or is temporarily unavailable.

The value in cognos controller configuration report server (http://localhost/ibmcognos/cgi-bin/cognos.cgi) is pointing to a not working website server. Try to change it to http://cognosanalyticsserver/ibmcognos/bi/v1/disp and ensure that all IIS functions are installed in the Web Server.

HTTP Error 405.0 – Method Not Allowed

The page you are looking for cannot be displayed because an invalid method (HTTP verb) is being used.

Then you maybe are pointing to a none existent CA11 server in cognos controller configuration report server section. Add the “ccrReports” value in web.config to solve it (this will use the Cognos Controller internal report server).

We can’t open this file

Something went wrong.


This can be that we are using a wrong database connection, try to connect to a other SQL version than 2016.

If you do not have a adobe pdf viewer installed on your laptop. Add the value <add key=”useInternalPdfViewer” value=”true” /> to the servers web.config file. Then cognos controller should use its internal pdfviewer.

You have to reboot the windows server, for the service FCMREPORTS to get any changes to the web.config file to take affect.

You get above error when you run a report in Cognos Controller, when the java is not correct point out in the server.env file for IBM Cognos Controller Reports.


Setup of connection to Cognos Analytics:

If you use CA11 for authentication, then in cognos configuration enter the Report Server to and Dispatcher URI to

You need to create the roles Controller Administrators and Controller Users in CA11 if it is a new installation, then add the users that should use cognos controller to above roles. Follow IBM documentation for more exact steps.

And The Controller Administrators role must be a member of the Controller Users role in Cognos Analytics.

Setup of the Cognos Controller Client:

To install the cognos controller client copy the file ccrlocalclient64.msi to a local folder (c:\install) from C:\Program Files\ibm\cognos\ccr_64\webcontent\ccr folder on the the windows server.

Create a setupccr.bat file with this content:

msiexec.exe /qb /i CCRLocalClient64.msi WSSURL=””

Run the bat file as administrator to install the cognos controller client silent.

After you login to Cognos Controller client, you can check the version in help menu – system info.

If you get a error like WebView is missing, then you must install WebView on the client from  to get the Help menu to work properly inside cognos controller client.

Under Evergreen Standalone Installer, click the x64 download button.

Download and run the installer file MicrosoftEdgeWebView2RuntimeInstallerX64.exe

If you when you test menu – maintain – jobs – define, get a error like this: org.eclipse.core.runtime.adaptor.EclipseStarter failed!
Caused by: Failed to launch!
at Source)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(
… 1 more
Caused by: Unable to find instance of model of type: configproperty[ConfigPropertyModel]

Check if it works better if you use a newer SQL server version, that can communication with the TLS security that the java parts need.

You can try to setup all this:




More Information:



Microsoft Power BI desktop


How select all data from a year, when you have a date column in your table?


Inside PowerBI you can enter formulas like this;

to select last year

= Table.SelectRows(#"Changed Type1", each Date.IsInPreviousYear([Datum]))


to select specific year

= Table.SelectRows(#"Changed Type1", each Date.Year([Datum])=2022)


to select all dates before 2022

= Table.SelectRows(#"Changed Type1", each [Datum] < #date(2022, 1, 1))



More Information:

How to Calculate Year to Date and Prior YTD in Power BI


Microsoft Power BI service (in the cloud) 


When i check my report, it show old numbers, why?


It can be that you need to refresh your web browser.  Press F5 to see if it helps.

Data is first updated at the source, can be your SQL database server.

Then (if you use it) the azure dataflow need to be updated.

When that is finished, you can update your semantic model. That will give you a updated Power BI report.

But the web based report can be cached, so please also update the page in your web browser, to ensure you are updated.

(if you use direct queries in your powerbi reports, there is other implications that may give issues).


More Information: 

If the dataflow is standard, then the data is stored in Dataverse. Dataverse is like a database system; it has the concept of tables, views, and so on. Dataverse is a structured data storage option used by standard dataflows.

However, when the dataflow is analytical, the data is stored in Azure Data Lake Storage. A dataflow’s data and metadata is stored in a Common Data Model folder. Since a storage account might have multiple dataflows stored in it, a hierarchy of folders and subfolders has been introduced to help organize the data. Depending on the product the dataflow was created in, the folders and subfolders may represent workspaces (or environments), and then the dataflow’s Common Data Model folder. Inside the Common Data Model folder, both schema and data of the dataflow tables are stored. This structure follows the standards defined for Common Data Model. 

A dataflow stores the data for each table in a subfolder with the table’s name. Data for a table might be split into multiple data partitions, stored in CSV format. 

In Power BI, in addition to the standard dataflow engine, an enhanced compute engine is available for the dataflows created in Power BI Premium workspaces. You can configure this setting in the Power BI admin portal, under the Premium capacity settings. The enhanced compute engine is available in Premium P1 or A3 capacities and above. The enhanced compute engine reduces the refresh time required for long-running extract, transform, load (ETL) steps over computed tables, such as joins, distinct, filters, and group by. It also provides the ability to perform DirectQuery over tables from the Power BI semantic model. More information: The enhanced compute engine

Power Platform dataflows are data transformation services empowered by the Power Query engine and hosted in the cloud. These dataflows get data from different data sources and, after applying transformations, store it either in Dataverse or in Azure Data Lake Storage.

Dataflows are created using Power Query Online. Once you create them, the “M” scripts are available for review or for changes, but you do not need to write any line of code by yourself. It makes creating dataflows in Power Bi a code-free solution, just like Azure Synapse and ADF.

With Power BI dataflows, you can develop ETL processes which can be used to connect with business data from multiple data sources. Data imported by Power BI dataflows is stored in Azure Data Lake (Gen2), which is known for having massive scalability. 

Power BI semantic models can store data in a highly compressed in-memory cache for optimized query performance, enabling fast user interactivity. With Premium capacities, large semantic models beyond the default limit can be enabled with the Large semantic model storage format setting. When enabled, semantic model size is limited by the Premium capacity size or the maximum size set by the administrator.

Large semantic models in the service don’t affect the Power BI Desktop model upload size, which is still limited to 10 GB. 

While a semantic model can be built using Power BI Desktop (in a .pbix file), it does not need to contain any visuals. Think of a semantic model as the last stop in the data pipeline before reports and dashboards are built. Thereafter, once you share a semantic model with other members of the organization, they can build any number of reports and dashboards from just that one semantic model.

Semantic models hide the complex technical details behind reports so that both technical and non-technical users can concentrate on analyzing the data and answering business questions. Sharing and reusability are two stand-out features of semantic models.

 A Power BI Desktop model is effectively an Analysis Services tabular model. 

  • Larger model sizes may not be supported by your capacity. Shared capacity can host models up to 1 GB in size, while Premium capacities can host larger models depending on the SKU. For further information, read the Power BI Premium support for large semantic models article. (Semantic models were previously known as datasets.)
  • Smaller model sizes reduce contention for capacity resources, in particular memory. It allows more models to be concurrently loaded for longer periods of time, resulting in lower eviction rates.
  • Smaller models achieve faster data refresh, resulting in lower latency reporting, higher semantic model refresh throughput, and less pressure on source system and capacity resources.
  • Smaller table row counts can result in faster calculation evaluations, which can deliver better overall query performance. 

Power BI’s Data Compression: Large Data Imports in Power BI

Planning Analytics TM1
Microsoft Windows 2019 Server


When user browse to the tm1web they get a error – random in different web-browsers e.g. chrome and edge.

Error msg:

This site can’t provide a secure connection sent an invalid response.
Try running Windows Network Diagnostics.


Check the URL you use – it need to start with HTTP:// if you do not have a certificate on your TM1WEB service.

If you by accident enter;

you get above error.

You need to enter


More Information:



Planning Analytics Tm1 Perspective

Microsoft® Excel® for Microsoft 365 MSO 64 Bit


How add TM1 Perspectiv to always be started with Excel?


After you have installed TM1 Perspective from tm1client_winx64h_2.0.919.10_ml.tar

Start Excel

Go to file – options

Click on Add-ins

Click GO at Manage Excel add-ins

Click Browse

Go find the file C:\Program Files\IBM\cognos\tm1_64\bin64\tm1p.xla

Click OK.

Now it should look like above.

Press OK.

Close Excel.


More Information:

Planning Analytics 2.0.9
Microsoft Windows 2019 server


How read in the dimension elements and hierarchy from a text file?


Create a csv file where you have on every row one element, and all its sub-elements before it.

In our example we have a 4 sub-element deep dimension.

You have to use your name of the elements in the dimension.

Create a new TM1 TI process, that take parameters for the dimension to update and where the file is located.

Pick up the file in Data Source tab, and setup the variables as “other”.

Variable names could be of that kind that it is easy to understand what they show.

Create a proplog code to get the dimension or the path from a cube if the parameters is blank:

sSubset = '$.'|getProcessName();

# if parameter is blank - get value from sys cube
IF ( LONG (pDim) <= 0 );

# get the values from sys settings cube
pDim = cellgets('sys.Settings','DimtoUpdate','Value');
pPath = cellgets('sys.Settings','FiletoImport','Value');


# set the values from parameters
sDim = pDim ;
DataSourceNameForServer = pPath ;
DataSourceNameForClient = pPath ;

# set the format of the file
DatasourceASCIIQuoteCharacter = '';
DatasourceASCIIDelimiter = ';'; 


Create in metadata section, that insert the elements into the dimension:



There is more ways to do this – but this is maybe the simplest way.

More Information: 

Catalogue your dimension creation processes in Cognos TM1 


Cognos Controller 11.0.1


Do you have a checklist of what is needed to do a upgrade to Cognos controller 11?


Best is if you create a own checklist for your environment, that you can use at every upgrade. Below is a suggested starting point, that you can develop further.

Download from here: 

Downloading IBM Cognos Controller 11.0.1 


Parts and platforms Details eImage
IBM Cognos Controller 11.0.1 Microsoft Windows Multilingual Required M0DH2ML
IBM Cognos Analytics Client Multiplatform Multilingual Optional M0BRXML
IBM Cognos Analytics installer 3.6.46 Microsoft Windows Multilingual Optional M0BRWML
IBM Cognos Analytics Server Microsoft Windows Multilingual     Optional M0BRVML


You must download the RTM program and then the fix packs.

IBM Cognos Controller 11.0.x Fix List 

IBM Cognos Analytics Fix Lists 

analytics-installer-3.7.21-win.exe (174.73 MB) (1.49 GB) (6.98 GB)

Get file up_cntrl_winx64h_11.0.1200.36_ml.tar.gz that is for Cognos Controller 11.0.1 fix pack 2.

Detail upgrade instruction here: 

Upgrading IBM Cognos Controller – IBM Documentation 

Installing fix packs – IBM Documentation 

Cognos Analytics 11.2.4 can be installed on top of CA 11.1.7.  The installation will overwrite the images folder, and upgrade the content-store database to a different schema version that will not work with the old CA11 version, therefore you must take a backup of the SQL database before.
Cognos Analytics 11.1.x end of support (EOS) is 2024-04-30 – but you can buy extended support.

Cognos Controller 11 needs to be installed fresh. You need to uninstall and remove the old version of CC 10 on your server before you install Cognos Controller 11.
Cognos Controller 10.4.2 end of support (EOS) is 30-Sep-2024.  Lifecycle policy Standard (S): minimum 3 years support with extension of 2 years.

Cognos Analytics upgrade  (if you decide to upgrade Cognos Analytics) Date
Copy the media to local hard disk on server d:\install
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup the CA11 images folders and any custom images folders you use like D:\Program Files\ibm\cognos\analytics\webcontent\bi\images or D:\Program Files\ibm\cognos\analytics\webcontent\bi\samples\images 
Backup the controller config file D:\Program Files\ibm\cognos\analytics\templates\ps\portal\ variables_CCRWeb.xml 
Backup PA variables_TM1.xml files from D:\Program Files\ibm\cognos\analytics\templates\ps\portal
Backup PA planning.html and PMHub.html files from D:\Program Files\ibm\cognos\analytics\webcontent
Backup PA tm1web.html files from D:\Program Files\ibm\cognos\analytics\webcontent\bi\tm1\web 
Backup web.config from D:\Program Files\ibm\cognos\analytics\webcontent
Backup the SQL database for content store
Backup the SQL database for audit
Take a screenshot of cognos configuration environment page
Take a screenshot of the members of cognos administrator group
Take a screenshot of the members of cognos controller administrator group
Take a screenshot of the members of cognos controller user group
Check that firewall ports are open Port 25, 80, 9300, 9362, 1433, 389 etc for BI.  
Stop the Cognos Services
Run the setup program from IBM Cognos Analytics Server
Run the setup program from ( CA 11.2.4 FP3)  
Open Cognos Configuration and click save  
Ensure the SQL driver is in the folder D:\Program Files\ibm\cognos\analytics\drivers  
Start the Cognos BI service
Check that you can login to IBMCOGNOS
Check file exist in D:\Program Files\ibm\cognos\analytics\templates\ps\portal\ variables_CCRWeb.xml 


When CA11 is up and running, then you can install Cognos Controller.

Cognos Controller 11 upgrade Date
Copy media to server d:\install
Backup all Controller database in SQL server
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup the acl files in D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer 
Backup the web.config file in D:\Program Files\ibm\cognos\ccr_64\ControllerProxyServer
Take screenshots of application pools and iis timeout settings
Take screenshots of every setting in Cognos Controller configuration
Backup all under D:\Program Files\ibm\cognos\ccr_64\fcmweb
Backup all under D:\Program Files\ibm\cognos\ccr_64\frontend
Backup the udl files in D:\Program Files\ibm\cognos\ccr_64\Data 
Backup properties file from D:\Program Files\ibm\cognos\ccr_64\server\FAP
If HTTPS is used, backup all certificate stores
Stop all IBM Cognos Controller services
Uninstall the previous version of Cognos Controller
Reboot the server
Install the new version of Cognos Controller 
Stop Internet Information Services (IIS) Manager (the Default Web Site)

Stop IBM Cognos Controller Consolidation

Go to the folder C:\Program Files\ibm\cognos\ccr_64

Search recursively for all instances of the subfolder workarea.

Delete all instances of the workarea folder that appear in the search results.

Install the new fix pack of Cognos Controller
Place the SQL driver sqljdbc42.jar in D:\Program Files\ibm\cognos\ccr_64\server\integration 
Place the SQL driver sqljdbc42.jar in D:\Program Files\ibm\cognos\ccr_64
Add back the information in Cognos Controller configuration and click save
Create the virtual directories in WWW for Cognos Controller 
Update the files for Controller Web with old information
Upgrade your Controller application databases
Create a UDL file and Upgrade your FAP database
If FAP service is on other server, install FAP there
Add back the file
Upgrade the Cognos Controller client on the server – can you login?
Start FAP service – any error in log file?
Create the Cognos Controller Client package for silent installation
Reboot windows server and check you can login to cc

More information:

Planning Analytics
Microsoft Windows 2019 server


Do you have a checklist to help out with the upgrade/installation of Planning Analytics?


Best is if you create a checklist that cover the needs of your environment, but here is a suggested to be used as a starting point.

Download from here: 

Detail upgrade instruction here: 


Action Date
Copy the media to local hard disk on server d:\install
Export configuration from cognos configuration to backup folder d:\temp\backup
Backup samples folder D:\Program Files\ibm\cognos\tm1_64\samples\tm1
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 files from folder:

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

Backup of your PAA Agent 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.
Backup home.jsp in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web
Backup CommonMessages.js in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\common\nls\sv
Backup Tm1WebAppCam.js in folder D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\standalone
Backup pa_header.svg file in D:\Program Files\ibm\cognos\tm1web\webapps\tm1web\scripts\tm1web\themes\carbon\standalone\images\login
If HTTPS is used, backup all certificate stores
Ensure that anti-virus or Windows Defender SmartScreen is turned off on the server during installation
Uninstall Planning Analytics Performance modeler from the windows server before you install the upgrade of TM1
Check that firewall ports between servers are open

Port 25, 80, 9300, 9362, 1433, 389 etc for BI. Port 5495, 5498, 5895, 5898, 8888,9510,9511,9012, 12345, 12346 , 12366 etc for PAL
Stop all related IBM Cognos TM1 and IBM Planning Analytics services and set to MANUAL
Reboot Microsoft Windows server
Install PAL from file tm1_winx64h_2.0.919.10_ml.tar (run as administrator)
Install TM1WEB from file tm1web-11.0.93-24020517-winx64h_bundle
Go into cognos configuration and click save
Reboot the Windows server
Modifications are required when FIPSOperationMode set to 1 (Basic)

— Remove or deprioritise TLS RSA CipherSuites

— TLS V1.2 or higher must be enabled

— EDCHE CipherSuites must also be enabled 

Restore configuration files that were backed up before
Start the IBM planning services  (start CA11 if needed)
Check that you can login to TM1 Architect
Check that you can login to TM1WEB
Set all related IBM Cognos TM1 and IBM Planning Analytics services to AUTOMATIC start
Reboot windows server
Check that you can run Cognos Analytics reports against the upgrade TM1 cubes


In older versions, this was also needed to be done:

Planning Analytics often uses Cognos Analytics for authentication. If CAM security is used and an SSL certificate has been enabled in Cognos, then we need to add the certificate from Cognos Analytics into Planning Analytics. This post explains the steps required to integrate Cognos Security with SSL for Planning Analytics LocalTM1Web (Planning Analytics Spreadsheet Services) and Planning Analytics Workspace.
Extract from above websites:
Navigate to the Planning Analytics server and to the directory < PA install location> \bin64\ssl and copy the ibmtm1.arm file to the Cognos Analytics server. You can copy the file anywhere, but I recommend creating a directory for it in the installation location.
In order to import this certificate we use an executable on the Cognos Analytics server called ikeyman.exe which resides in the <CA install location> \ibm-jre\jre\bin directory. Simply double click this file to open it.
Click the Open icon on the toolbar, ensure the Key Database type is set to JKS, navigate to the directory <CA Install Location> \ibm-jre\jre\lib\security, and open the file called cacerts.
Now you have loaded the certificate file it’s time to import the Planning Analytics certificate. Under the ‘key database content’ section change the drop-down value to ‘Signer Certificates’ and press the ‘Add’ button….



Planning Analytic Workspace upgrade Date
Copy the media to the server d:\install
Unzip the file
Check free disk space on the PAW windows server
Rename folder to PAW92 and place it at root
Exclude new folder d:\paw92 from anti-virus scanning
Copy the <paw_install_location>/config/paw.ps1 file from your current installation to the new installation location
Copy the <paw_install_location>/config/certs directory from your current installation to the new installation location.
If you configured SSL, copy the <paw_install_location>/config/ssl directory from your current installation to the new installation location.
Take screen capture of the configuration today in PAW administration – any existing errors will not be solved by upgrade.
Take a backup
Stop the docker containers Paw.ps1 stop
Check that no running dockers containers Docker ps
Go to the new folder
Run installation ./start.ps1 Start.ps1
Check that admin settings are correct
Check you can login to PAW website
Stop the docker containers Paw.ps1 stop
Reboot windows server, this to ensure the system will work after a reboot.
Check you can login after 30 min
Download the new kate agent
Unzip to a temp folder
To update the Planning Analytics Administration agent Windows service, run UpdatePAAAgent.bat, passing in the full path to <PA_install_location>, that is, the parent of the paa_agent directory.
Check that contain the settings for smtp email as before
Running the /clean.ps1 script removes images for the release in which it is run. Run from old folder e.g. d:\paw90
Check free disk space



More information: 

Due to a security update included in Planning Analytics, customers who have the non-default FIPSOperationMode=1 set in their Tm1s.cfg should remove or deprioritize the TLS RSA CipherSuites for a successful handshake to occur. This includes all CipherSuites that are prefixed with TLS_RSA_. This also means that TLS V1.2 or later must be enabled, as TLS 1.1 and prior do not support alternatives. In the case of TLS V1.2, the ECDHE CipherSuites must also be enabled.

Planning Analytics TM1 Perspective

Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20542) 64-bit


After installing a new TM1 Perspective, you get a message when you start excel:

Microsoft Office has identified a potential security concern. The digital signature is valid, but the signature is from a publisher whom you have not yet chosen to trust.


Press “Trust all from Publisher” and TM1 perspective will work in your excel installation.


More information: