Birst security filtering


Quite a while since the last blog post but here we go again! As I’ve been involved with Birst lately it’s time to blog some under-the-hood stuff about Birst respectively.

I want to share the experience that is mentioned there and there around the Birst community articles. It’s about securing dimension attributes and how they affect the related fact measures depending on how the data sources are configured.

In my example I have the following fact, dimension and “access stable” data sets:




I want to secure the fact data depending on which business unit (BU) the user belongs to. As we can see the test user should be able to see only the HR fact data.

By using the basic security filtering feature via birst it can be accomplished:



Then when using Visualizer under the test users’ account we can see that the filtering works OK. Or does it?

Depends on what you consider as the correct output. By default, Birst is applying the security filter ONLY when the corresponding dimension is used in the visualization:


As we can see if we only choose the sales measure and no dimension attributes it shows the total amount of sales and doesn’t apply the security filter behind the dimension attribute (as it’s not being explicitly used).

At some times we may want to filter the underlying fact measure data “automatically” without explicitly adding the dimension attribute to the visualization. IMO I find this problematic as then you violate the “one set of numbers”-rule as every user could see different total amount depending on how their data is filtered via security filters.

Anyhow, if this is acknowledged and there’s no problem with the output the way to do it in Birst is to check the filtered attribute field as a Measure (not so “logical” I think but it works :)). Then we’ll end up having filtered total amount without using the dimension attribute:



TM1 Maximum overdrive! Running TI processes in parallel with multiple cores

The Problem

Since the release of IBM Cognos TM1 10.2 it has been possible to leverage multi-core CPUs into 100% action as the MTQ (Multi-Threaded Query) allows to spread queries along multiple cores. Unfortunately this applies only to queries (as figure 2 here illustrates), therefore the TI processes are still ran in single thread and can only use one core at a time. This quite annoying because if you have for ex. 64 cores you end up running CPU-intensive TI processes only with one core! With some serious magic this limitation can be of course turned around with little help of tm1runTI.exe tool. With it’s help we can start multiple TI processes at the same time via command prompt and achieve the consumption of more than one core at the same time!

The Solution

This trick consists of three separate “modules”

  • tm1runti_execute_parametrized.bat – Parametrized batch file that wraps the execution of tm1runTI.exe
  • Maintenance.Execute.Tm1RunTI.Parametrized – Universal parametrized wrapper TI process that wraps the calling of the batch file above. Can be used to run any TI process in parallel execution mode.
  • A parametrized TI process that can safely be ran simultaenously to avoid locking. For example a version copy process parametrized by year/month etc. See details in the “Usage example” section.
The parametrized batch file code

The parametrized batch file code

The batch file itself is very straightforward: it needs authentication information and the name of the process that is going to be executed in parallel.

The parametrized wrapper TI process (Maintenance.Execute.Tm1RunTI.Parametrized) also takes parameters: TI process that is going to be executed in parallel and it’s parameter names and corresponding values:

Parametrized TI process parameters

Parametrized TI process parameters

The actual code is also nice and simple. It just checks whether the parameter values are given and generates a command prompt batch file call and the executes it in the command prompt:

The parametrized TI process code

The parametrized TI process code

Usage example

Now that the core modules are introduced it’s time for and usage example. Lets say you have multiple big cubes and you want to make copies of versions in each of the cube periodically. With big cubes and big data amounts that could take long as TM1 only uses 1 core for the TI process execution. But here we can now make a big difference: we can divide the copying of the versions by year and do the copying for each cube at the same time (the only limitation here is the amount of cores available).

Here is the example code:

Sample usage code

Sample usage code

What we do here is that in the outer while loop (has been left out of the picture) we iterate trough all the cubes that we want to process with the version copying. Then in the inner while loop we go through years between 2006 and 2018. Depending on the amount of cubes we could end up with for example +100 separate TI process calls. As they are separated from each other by the time dimension they dont lock each other and can be ran at the same time (this is the most important part: one must design the runnable TI process in way that when it’s executed in separate threads the executions dont lock up each other!).

So, when we bring this piece of code into action we sure can take everything out of the expensive PVU’s as we can see here:

Task manager seeing red

Task manager seeing red

TM1Top: TM1 having busy time processing

TM1Top: TM1 having busy time processing


The 10.2.2 introduction of MTQ was a relief and it’s a feature that should have been there 10 years ago when multi-core CPU’s arrived. Unfortunately TM1 still lacks the out-of-the-box feature to execute TI processes in parallel but with this little trick we can overcome this limitation and reveal the full power of the CPU in our TM1 solution!

TM1: Application Maintenance utility and CAM security. The INVALID_IDENTITY challenge.

In the newest TM1 versions it’s possible to refresh the TM1 Applications user permissions and do some other maintenance work via command line. The bad thing is that is still has some issues when working with CAM security (which happens to be the case almost in 100% of the production environments). One may encounter some INVALID_IDENTITY – related error messages like this: INVALID_IDENTITY

IBM’s official answer says that one must use SDK and develop some custom code to get a grip on a cookie value that is set by the IBM Cognos BI portal. Luckily fellow TM1 gurus found also an alternative way of fetching the cookie “cam_passport” value but unfortunately the value could keep changing multiple times a day. Now if you have automated the maintenance utility it may keep going for a while but when the cookie value expires the utility stops working. And that’s bad.

So we need a way to automatically fetch the “cam_passport” cookie value when executing the application maintenance utility (app_maintenance.bat). Once again PowerShell comes to rescue! Here’s what you should do inside a PowerShell script:

$url = "https:///ibmcognos/cgi-bin/cognosisapi.dll?"
$cookiejar = New-Object System.Net.CookieContainer
$webrequest = [System.Net.HTTPWebRequest]::Create($url);
$webrequest.CookieContainer = $cookiejar
$credut = New-Object System.Net.NetworkCredential;
$credut.UserName = "username";
$credut.Password = "password";
$credut.Domain = ""
$webrequest.Credentials = $credut
$response = $webrequest.GetResponse()
$cookies = $cookiejar.GetCookies($url)

$cookies["cam_passport"].Value | Out-File "d:\temp\cam_passport.txt"

Basically what it does is make a http request to the IBM Cognos portal using the credentials provided (these must be the credentials of a TM1 admin user that has priviledges to run the app_maintenance.bat). On the last line it writes the “cam_passport” cookie value into a temporary file.

Save the script into a file for eg. “Update_CAM_passport.ps1” and run it via cmd command line by:

powershell.exe -Noninteractive -Noprofile -Command 

Now, to make sure that the cam_passport is valid through all times you can schedule the command above to Windows Scheduler and put it running say every 30 minutes.

The final step is to call the app_maintenance.bat by giving it the recently fetched cam_passport value as parameter and we can achieve it as follows (a batch file contents):

@Echo on 
timeout 2
set /p p_cam_password=<d:\temp\cam_passport.txt
"D:\Program Files\ibm\cognos\tm1_64\webapps/pmpsvc/WEB-INF/tools/app_maintenance.bat" 
-op refreshrights  -serviceurl 
-credentials CAM:%p_cam_password% -app <application_id>

Where the application_id is the unique id of your application.

Se on siinä! As we would say in finnish. That’s about it. Now you can sleep safely knowing that your automated application maintenance utility keeps running even if the cam_passport value changes.

TM1: Run SQL Server Job from TI process


TM1 Turbo Integrator is quite limited when it comes to loading data from different sources and transforming and cleaning the data before it can be loaded to cubes. Therefore you might have a dedicated data repository for your TM1 solution for example a data warehouse. Sometimes you need to refresh the DW data before it can be loaded to your TM1 solution. Then you face the question: how to manually execute data warehouse refresh ETL jobs? Of course you can always use SQL Server Management Studio and run the jobs there but if there is a need for the end users to manually refresh the data during working hours it would be nice to provide them a single-platform solution do accomplish this.

So lets face it: maybe you already have some user interface for the end-users to execute some TM1 solution’s internal TI-processes. It could be via TM1 Perspectives web sheets on TM1Web,  straight from TM1 Architect etc. Wouldn’t it be nice to have the ability to run external DW loading processes from the same place? Here is how to accomplish it.

Set up the logins and jobs in SQL Server


First of all you need to ensure that the user account that will be used when from TI process connects SQL Server has all the needed permissions in the SQL Server instance. At least the three following things must be ensured:

  • Login has SQLAgentUserRole checked in the msdb database. Then the login will have permissions on only local jobs and job schedules that it owns
  • Login has the required permissions to access the source files etc. That can be solved by defining a proxy account and I have described it in my previous blog post.
  • The job must be owned by the same user you will use when connecting from TM1. This is because we don’t want to throw admin permissions around and we want the user to have only rights to one or couple of jobs not all of them


Set up TI process to run the job: zSQLJob_StartJob

To accomplish we need two different TI processes: one to actually execute the job and one to determine whether the job execution has ended or not. Let’s start with the main process zSQLJob_StartJob. It has one parameter p_sql_job_name which will contain the name of the job that will be executed. The process itself is quite straightforward and here is the complete code:


What it does is basically just opens the ODBC connection to the msdb database of the SQL Server instance and then executes the job. The magic here is that the process won’t finish until the job on SQL Server has finished. To have this functionality we need to poll the msdb database every 5 seconds and find out if the job has finished or not.

Set up TI process to determine the status of the running job: zSQLJob_CheckJobStatus

To find out whether the job has finished or not we just query the job status directly from the msdb database. To do that we use the following SQL query and put it in the “Data Source” tab like this:

select job.Name, job.job_ID, job.Originating_Server,
activity.run_requested_Date, datediff(minute, activity.run_requested_Date, getdate()) as Elapsed
from msdb.dbo.sysjobs_view job
inner join msdb.dbo.sysjobactivity activity
on (job.job_id = activity.job_id)
where run_Requested_date is not null and stop_execution_date is null
and = 'JOHIDW_Update_Publishing' ) THEN 1 ELSE 0 END AS JOB_RUNNING_STATE


The only magic with this process is to save the single result value to the global variable vnJOBSTATE_global that we defined in the previous master process. If the value is 1 it means that the job is still running and the master process keeps polling the msdb database every 5 seconds. When the job finishes the value turns to 0 and the master process can finish as well.


There may be times when you need to execute SQL Server jobs to make sure your data to be loaded to TM1 is up-to-date. With this example you can do a nice and easy integration between TM1 and SQL Server and allow end-users to execute simple SQL Server jobs if needed.

Using Prompt API for IBM Cognos BI Report Studio

Prompt API for IBM Cognos BI

Prompt API for IBM Cognos BI

Default values for report prompts? It doesn’t sound very difficult does it? Well as far as the default values are static you can define them easily with your report properties but in the real business world there’s no such thing as static. Change is just around the corner and so we need to have more dynamic reporting solutions. Making dynamic default values for report prompts is something that should be a out-of-the-box-feature (as it is in Microsoft SQL Server Reporting Services for example) but with IBM Cognos BI Report Studio it has been about making some JavaScript/HTML/DOM hacks. Now they’ve released Prompt API for IBM Cognos BI to tackle down this issue. Well, it’s still Javascript but at least there is a documented API how to use it. Let’s give it a shot.

In my simple example I’ve created a report with two prompts and one HTML Item. The first “kkprompt”-prompt fetches the default value that is going to be selected in the another prompt. You can later on hide the first prompt box because it doesn’t need any user interaction. Now, to get the “defaultselprompt” prompt working with dynamic default value we need to add the following code inside the HTML Item.


And that’s pretty much it! I’m still amazed that this kind of feature is behind some javascript code and not available in the prompt box properties but hey, maybe in the next major release?


SQL Server 2012 Integration Services DLL Hell: Microsoft Visual Studio Tools for Applications Runtime 3.0

One of the major drawbacks of the SQL Server Integration Services (SSIS) is the mixture of 32bit and 64bit environments:

  • Design & development time environment (SQL Server Data Tools) is running in 32bit though you can debug the package in 64bit mode
  • Production packages can be run in 32bit or 64bit whatever you choose in the package properties

Well, there are moments when this arrangement feels like a gift from god but for me it was once again a real pain in the ass. I was recently working in a fresh installation of SQL Server 2012 and Data Tools. When I was trying to edit a Script Task in my SSIS package I faced the following error message:

Could not load file or assembly ‘Microsoft.VisualStudio.Tools.Applications.Core

Fresh installation and everything seems like to be in order but no, the script editor won’t open up. After investigation I found out that when the moon and the stars are aligned perfectly there is a small possibility that you miss one assembly file if the installation of SQL Server and related components is done in the wrong order. The thing is that to be able to run the package in both 32bit and 64bit there has to be same assemblies respectively: ones for 32bit and the others for 64bit. My problem was that I was missing the 32bit version of Microsoft.VisualStudio.Tools.Applications.Core.

From the “Add / Remove Applications” of Windows I noticed that yes, I have the Microsoft Visual Studio Tools for Applications x64 Runtime 3.0 but what i was missing was the x32 version of the assembly. So grabbing the MSI installer file from the installation media (redist/VSTA/runtime/x86/VSTA_RT30.msi) and installing it fixed the problem.

Beware of DLL hell!


Scheduled backup solution for TM1 with 7zbackup


Working with in-memory applications like TM1 it’s essential to have a working backup strategy. TM1 doesn’t provide any built-in functionality for this but with little help from 3rd party tools one can easily build up a lightweight and working backup solution. What you need is 7zbackup, 7-Zip 9.20 and Windows PowerShell 2.0.

Getting started, step 1: Modify the “selection file”

(Notice: I personally needed to go back to version 7zbackup 1.8.5 stable to get the script running succesfully.)

After installing the required applications start with modifying the 7zbackup “selection” config file. That’s the file that contains the configuration needed to run through the backup process. There are many different parameters that you can modify but let’s just go through the most important ones.


Here you define what to back up. Datafiles folder is the heart of TM1 so you need to back it up entirely. If you want you can also include the logfiles folder but its not necessary.


As it says in the comment line This variable holds the number of historical archive backups you want to keep on target media. I tend to play safe and there’s disk space available nowadays so two months of rotation should be enough. Just remember to copy the actual backup zips to remote location periodically so you got also a real disaster recovery possibility.

There are also other parameters you may like to config but this will do it for us now.

Step 2: Set up a batch file to run the PowerShell script

Next we set up a classic bat-file to make it possible to execute the PowerShell script from windows scheduler. Here is the contents of the bat-file:

@ECHO off
for /f "tokens=1" %%a in ('powershell.exe ""') do set WD=%%a
powershell.exe -Noninteractive -Noprofile -Command "&{<path_to_your_7zbackup_folder>\7zBackup.ps1 --type full --selection <full_path_to_your_selection_file> --destpath <path_to_your_backup_destination_folder> --logfile <path_to_your_7zbackup_logging_folder>\daily_backup_%WD%.txt}"

Basically what it does is that it runs the 7zBackup.ps1 script file telling it to take a full backup, use the settings from the selection file, put the zipped backup in the given destination folder and put the 7zBackup own logging files in given directory and give the logfile a timestamped name.

If you haven’t configured the script execution policy you may encounter this error message:

File 7zBackup.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details.

That means you need to set the execution policy to allow executing custom scripts. More information here.

Step 3: Schedule the backup process with Windows Scheduler

The last thing to do is to schedule the backup process on daily basis (or more often if you like). There are 5 tasks to set up there:

  1. Run SaveDataAll() on the TM1 server to make sure that all the data is written to disk before the backup starts
  2. Shutdown the TM1 server: NET STOP <your_tm1_server_service_name>
  3. Execute the backup bat file (created in step 2)
  4. Start the TM1 server again: NET START <your_tm1_server_service_name>
  5. (OPTIONAL): Copy the newly created backup zip file from destination folder to your remote backup storage location.

Here we are! Happy backuping!