Skip navigation.
spilling the beans

TM1 blogs


Thu, 2013-12-19 00:00
When a dynamic subset is chosen in a cube view it does not stay and goes back to being blank.


Thu, 2013-12-19 00:00
When french users are trying to enter numbers using the numeric keypad the Dot (.) is suppose to be interpreted as a (,) but it


Thu, 2013-12-19 00:00
Copy and pasting of multiple cells does not work at a Consolidated level in TM1Web 10.2


Thu, 2013-12-19 00:00
The "-" or minus button on the numberpad section of a full size keyboard with the Perspectives addin loaded turns on the


Thu, 2013-12-19 00:00
Cannot open websheet out in TM1web. The websheet has freeze pane in it and when opening it out in TM1web, it will display

Creating scorecards in TM1

Mon, 2013-12-09 19:24

The following example is a demo from the IBM Cognos TM1: Create Scorecards (v10.2) one-day instructor-led course.  This course teaches scorecard developers how to create scorecarding solutions in TM1.

Participants learn how to create all of the necessary scorecarding objects such as metrics cubes, diagrams, and maps, deploy the solution to the Web for end-user consumption, and create an engaging dashboard presentation for the solution. If you would like to know more about this training course or other training courses, please visit the IBM Training Course Catalog.

This demo takes you through creating a scorecarding solution for the sample Great Outdoors company. You start by examining an example scorecarding application to understand how users interact with these applications and the objects they contain.



TM1 Applications and cross dimensional security or Access tables with NoData in TM1

Fri, 2013-12-06 12:19

Got questions about cross dimensional security in TM1 Applications aka Contributor quite a few times, so it’s obviously time to write something up and put out here for linking in future ) Cross dimensional security is quite a mouthful, so for us ex-Enterprise Planning people: this is a post on how to do something like Access Tables in TM1. Don’t get overexcited, though, there’s no “good-enough” solution that I’m aware of, more like a bunch of workarounds. I’ll list them here with the pro’s and con’s of each and typical usage scenarios. I’ll pay a bit more attention to one of them (dynamic MDX-subsets with an auxiliary flag cube), cause it’s not very intuitive (for non-hardcore TM1 developers) and is actually quite useful.

Problem overview

Let’s set up the scene. I’ll use the SData server that comes as a sample with your TM1 installation.

This model is about a company selling cars worldwide. I’ve mocked up a very simple TM1 contributor application to let them collect their sales budgets by countries:

The workflow tree to left lists countries rolling up to regions. Opening a node for specific country (Denmark, in this case) allows us to input sales for a particular Model (L Series 2WD) and get our gross margin easily. Country managers can submit their budget, region managers review it and etc.

All good so far but what if we don’t sell all models in all countries? Only “L Series” in Denmark, while we’re at it.

Pretty typical scenario, but quite a pain to implement in TM1. And a big shock to all EP consultants so used to access tables (just say Denmark is eligible for this set of Models, Norway for this and etc). And a puzzle for TM1 old-timers who prefer Excel-built user-forms with TM1Web to Contributor — they just don’t see the problem at all )

A bit of back-end discussion

Hopefully it’s just a matter of time before this functionality gets implemented in TM1 Contributor, but it actually requires quite a bit of additional engine work.
Current TM1 security works on the “user”-basis, so you know what a person can see in the cube. Unfortunately, that’s not enough, to fully implement access tables we need “who can see what where” component, essentially adding this approval hierarchy dimension to whole security process, so while it’s definitely doable, it might take a while for the smart folks in labs to carve it out. I’d guess that it’ll require another set of control cubes (very similar to the one I’ll describe later) and an additional view configuration each time an approval hierarchy node is selected.
On the other hand if they just add dynamic subset refresh to title subsets — that’ll solve everything straight away!

So back to the issue, what options do we have now? Again, if you know a better solution — please don’t hide it )

Approaches TM1 dimension element security

So we say that Lucas from Denmark has access only to L Series in model dimension. And it’ll work perfectly, he won’t see any other models when he logs in.
Up until it turns out that he’s also responsible for Norway while Norway’s country manager is on leave. And Norway sells only S Series, but our poor Lucas will see both when logs into either country, because he now has access to those elements in the dimension. And he can even add data to the “wrong” model, making the whole thing quite shaky.

Pro’s: works perfectly
Con’s: only if you have a rigid 1 person – 1 approval node relationship and I’m yet to see this in real life.

Ok, you’d think, we’ll just configure an additional step of

Cell security

Adding these country-model relationship as a input restriction rule. Only L Series in Denmark, S Series in Norway, easy as.
The problem is that Lucas will still see both set of models in either country when he logs in, he just won’t be able to input data in the wrong country – model combination. Which is an improvement, but he’ll still complain about models from other country cluttering his input form.

Pro’s: your data is correct
Con’s: user interface is far from ideal

So you’d think ok, now we just need to hide those unneeded models by introducing


Zero-suppression can be used to hide all unneeded combinations. Two caveats:
1) you’d need a nonzero element on the needed intersections, so you add a “flag” element to row or column and then apply rules to set it in needed intersections. Looks tad ugly, but not a major issue
2) zero suppression works only on rows or columns, so you need to drag your model dimension to rows and fix the view. This is a major limitation.


will work and works for higher levels of approval hierarchy (flags will be summed up) so Europe manager will see both Norway and Denmark car sets you can use rules to set flags, so they can be quite flexible

Con’s: see above and

performance can seriously degrade, because you’d need to populate and feed all “potential” input scenarios, causing overfeeding (there are no real cells, only flags). You’ll essentially turn off the whole sparse processing algorithm, can be quite a show stopper

Let’s assume that putting the secured dimension on rows / columns isn’t a problem flags are actually a comfortable solution. We can avoid the ugliness of needing a “flag” element using MDX subsets method described below, so if rows / columns requirement is not critical I’d consider that method instead. We’re introducing other kind of ugliness there, so it’s not a clear cut.

Let’s explore other approaches to the problem before we jump to MDX.

Conditional Pick-lists

A very powerful feature to limit one dimension based on another is using the IF condition in picklist rule to pick a corresponding subset. So if your data is already in some sort of flat / line-item input, conditional pick lists are an ideal solution. You’d most likely need another cube to transform this pick-list dimension to a real one for analysis, but you’d need it anyway when you go the line-item way. It’s quite a common scenario with employee / contract management applications, but for the Sales application we’re discussing here it’ll be quite painful, imagine selecting a “model” for each of the input rows.


works as described, with various subsets pregenerated in TIs


requires additional cube for analysis not suitable for all types of applications Worksheets

I’ll just briefly touch this: since you can add worksheets into TM1 applications, you can add an Active Form that will be fully generic based your requirements. I never did this due to quite a lot of reasons (maintenance, performance, development just to name a few), but worth listing it here.
Pro’s: fully flexible

different development approach (MDX in Perspectives) can be hard to maintain another interface can be hard to explain to users MDX subsets

Dynamic subsets are a quite useful concept, they are MDX expressions returning set of dimension elements. Each time user requests a view with a dynamic subset, it’s expression is evaluated and results are returned for display. Other currently selected dimension elements (so called context) can be passed into dynamic expression making it quite flexible. Biggest downside is that this subset needs to be in columns or rows to be reevaluated when context changes.

If that’s not a problem, then we can build quite a flexible solution. Key idea is to use an additional cube to hold our flags (show or not) and query it to define what should be shown.

Let’s look at it for the car sales example we’re discussing:
1) Let’s create a “flag” cube with 2 dimensions, Approval Hierarchy (region) and the one we want to filter (model)

1s in intersection are “show” the model for country. Note how the higher levels of approval hierarchy (Scandinavia, Europe) get their values automagically by consolidation.
2) Let’s create a new subset in model dimension. We’ll use the following MDX expression.

{FILTER( TM1FILTERBYLEVEL( {TM1SUBSETALL([model])} ,0), [SalesCubeFlag].([region].CurrentMember) >=1 )}

Walking through it from inside out:

TM1SUBSETALL to select all models TM1FilterByLevel to select only leaf level elements [SalesCubeFlag].([region].CurrentMember) select value for [SalesCubeFlag] for currently selected region in context return only the models having value greater or equal to 1

Let’s assign it to view and see how it works in the application:
I’ve added models in rows of the cube view:

Let’s log on as Denmark:

As Norway:

As Europe:

So you can see this working for various detail and total nodes.


You can use rules or TIs to populate the flag cube according to your requirements You can have multiple dimensions and dynamically filter them depending on the context You can add a measure dimension to store multiple different flag logic for different applications


Double check if this works in your TM1 version. I had issues in CX 10.1 and no issues in enterprise TM1 10.1, so it might be version / fix pack quite sensitive. MDX subsets used to lock down the whole server on refresh and were avoided like plague (you just ran a TI to convert subset to static), but shouldn’t do so anymore after 10.1. I’d guess that performance would be at least as good as flags + zero suppression, worth testing if this will be an issue for you. Be careful with hierarchy levels, unlike flags this approach will display all children (even the ones you want to avoid showing), that’s why I’m selecting the lowest level of hierarchy.

I’ll leave you with a link to excellent MDX Primer, describing much more “fun” things you can invent in MDX based world. Though I’ll say that I’m quite a proponent of their extensive use, moderation is the key ) I wrote a small MDX query wrapper for TM1 a couple years ago to play with TM1 MDX syntax.


I usually select conditional pick lists, flags or MDX based on application type I’m working with. Would love to hear better options.

Some interesting links

Access Tables in TM1:
MDX with attributes

And every post is way more interesting with a cat in it, don’t you think?

TM1 and CAM Failover

Wed, 2013-11-13 10:51
If you are using TM1 with CAM security and you have Cognos setup with multiple gateway dispatchers then I would recommend checking out this post on the IBM website:

You may set multiple ServerCAMURI as follow in tm1s.cfg



ServerCAMURIRetryAttempts specify the retry count for access each dispatcher

IBM SPSS Modeler v16.0 What’s new

Tue, 2013-11-12 05:58

Although it is not officially released yet, a document that explains what’s new has been linked to the internet. You can find it by clicking here.

Some of the new key features that caught my attention are:

  • TM1 Source and Export nodes
    • With the source node you will be able to use cube views and import data into Modeler
    • With the export node you will be able to score data to an existing TM1 cube
  • New R nodes
    • In Modeler 15 FP2 an R node was introduced for modelling, R Model Build/Apply. The new release has two additional nodes, R Process and R Output. With the R Process node, you can use R scripts to do transformations and with the R Output node, you can use R scripts to perform data analysis and produce text and graphical outputs. Also R nodes will be installed as part of the base Modeler installation and not separately.
  • R in database
    • R nodes can be pushed back to some databases offering better performance. Oracle, Netezza and SAP HANA.
  • Monte Carlo Simulation
    • Simulation Source node - generate synthetic data, using a wide selection of statistical distributions
    • Fitting node – can create a preconfigured source node
    • Simulation Evaluation node - terminal node designed to evaluate fields resulting from a simulated analysis stream
  • Python scripting – use python to automates processes. Legacy scripting language will continue to be supported.
  • New Distinct node – Will handle duplicates records more effectively.
  • New Receiver Operator Characteristic (ROC) Evaluation node chart type
  • Area Under the Curve (AUC) and Gini metrics in Analysis node


Powershell with TM1Server.log

Tue, 2013-11-12 00:10
The tm1server.log is not the easiest thing to extract information from but contains golden nuggets of information.

Ideally you want to timestamp the tm1server.log after any restarts and then you can analyze a clean single session of information.

One of the easiest things you can do is use Windows powershell to then extract just the messages you want.

e.g. if i just want to explore the cube dependencies the server is producing then I just the following line within powershell

Get-Content .\tm1server.log | Where-Object {$_ -match 'TM1.Cube.Dependency'} | Set-Content tm1server.log-out.txt

You can alter the above statement to do any filtering you need

TM1 Cafe

Fri, 2013-11-08 09:40
Great overview of TM1 Cafe in 10.2 from Quebit:

IBM TM1 Watchdog

Thu, 2013-11-07 22:40
Some explanations around how to setup and use watchdog in TM1 10.2

New Releases and Product Notifications for November 2013

Wed, 2013-11-06 12:35
It was interesting to see this month the notice of withdrawal from IBM on Executive Viewer and FSR:

Top 20 Developments in IBM TM1 in recent years

Wed, 2013-11-06 12:20
Just realised a presentation that Andrew Stephens and myself did for an IBM Cognos User Group is available for download from the IBM website.

In what will always be a controversial list we called out at the time the Rule function Continue as being a real game changer for TM1 developers.

Those who have been doing TM1 for 10+ years since the days of TM1 714SR2 will remember when there was no Continue function and constant nested Ifs made for ugly looking rule files.

Check it out here:

How to use the Auto Classifier Node in SPSS Modeler 15

Tue, 2013-11-05 21:21

IBM SPSS Modeler V15.0 enables you to build predictive models to solve business issues, quickly and intuitively, without the need for programming.

In this demonstration we are going to show, how you can use the “Auto-Classifier Node”.

The Auto Classifier node can be used for nominal or binary targets. It tests and compares various models in a single run. You can select which algorithms (Decision trees, Neural Networks, KNN, …) you want and even tweak some of the properties for each algorithm so you can run different variations of a single algorithm. It makes it really easy to evaluate all algorithms at once and saves the best models for scoring or further analysis. In the end you can choose which algorithm you want to use for scoring or use them all in an ensemble!

First a brief description of the data. The data comes from the 1994 US Census database. You can find the data here from the UCI Machine Learning Repository. The goal here is to determine whether a person makes over 50K a year. It has 14 variables both categorical and numeric.

First step is to import the data. The data are in csv format so we can use the “Var. File” node to import them. All you have to do is define the source path and we are ready to import the data.

Then we can use the “Data Audit” node to inspect the data. This is one of the most useful nodes of SPSS Modeler. It will display a graph and statistics for all variables and locate if there are missing values or outliers in the data. I am going to write more about this in another tutorial.

Click here to read the full article


How to import new RAVE visualisations from Analytics Zone

Tue, 2013-11-05 00:18

For those who don’t know, The new visualization engine (RAVE) underpinning most of the IBM business analytics products is capable of some stunning visualisations. There is a gallery of sample visualisations on Analytics Zone (.com). These include (but is not limited to):

For instructions on downloading and installing these into either Cognos Express or Cognos BI check out the article on How to import the new visualisation charts into IBM Cognos BI.








TM1 Analyze and Share Data

Mon, 2013-11-04 16:57

IBM Cognos TM1 is an enterprise planning software platform that can transform your entire planning cycle, from target setting and budgeting to reporting, scorecarding, analysis and forecasting.

The following example is a workshop from the IBM Cognos TM1: Analyze and Share Data (v10.2) three-day instructor-led course available in a classroom or online environment. This course is designed to teach analysts how to use TM1 to analyze data to discover trends and exceptions, create and customize reports and templates, and contribute to plans. If you would like to know more about this training course or other training courses, please visit the IBM Training Course Catalog.

Tasks covered

  • Connecting to the Sales Plan Cube.
  • Creating an exploration and arrange the view for data entry.
  • Creating two sandboxes and view them as Flex views.
  • Entering data and commit to both Sandboxes.
  • Calculating variance between the sandbox values.
  • Merging the Optimistic sandbox with the base.


TM1 CFG File - ViewConsolidationOptimizationMethod

Sun, 2013-11-03 23:55
I see a lot of client's cfg file and its amazing how many of them are out of date or contain the wrong settings for the version of TM1 they are on.
One of the parameters that I always suggest adding in is ViewConsolidationOptimizationMethod=TREE
The reason why is because of the documentation you can find here:

Specifically its says "This setting provides the best performance in normal operations"

Cognos Report Studio RAVE - Visualization Customizer

Sun, 2013-11-03 23:42
There is now a utility available on AnalyticsZone where you can now edit the visualizations available within Cognos Report Studio:

Missing TM1 Subset Editor

Sat, 2013-11-02 09:42
If using dual screens you may occasionally move your machine to a projector or another monitor and then find you can't see your subset editor when it opens.

Somehow its thinking its still on the 2nd screen but you don't want to have to go back to your desk and plug everything back in to then move the subset editor back to the original single screen.

One quick way to get round it is to edit your tm1p.ini file but a quicker way that doesn't involve shutting down Excel is to press:

  1. ALT with Space Bar
  2. Then press M (which is the shortcut for Move)
  3. Then use your cursor keys to bring the subset editor back to your screen