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

Posted at — Dec 6, 2013

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

Flags

Zero-suppression can be used to hide all unneeded combinations. Two caveats:

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
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.

Pro’s:

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.

Pros:

works as described, with various subsets pregenerated in TIs Cons:
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

Con’s:

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:

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.
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.

Pro’s:

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 Cons:
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.