Certain Combinations within Dimensions

[Jon]

Hi all,

I’ve only just started developing with TM1 and this is my first OLAP related role so I’m still getting my head around its capabilities and limitations. I’m currently trying to work out if it possible to limit entry into a cube depending on the combination of dimensions and values being entered.

My crude crude example should help clarify what I’m asking:

Three Employees working across 3 sites. Person 1 at Site A, 2 at B, 3 at C. Could you have it so that I could only enter the salary figure for Employee 1 into Site A and grey out the other two and at the same time have Employee 2’s salary to be entered into Site B with Site A and C being grayed out.

The problem I’m trying to solve is values getting entered into the wrong site and having to go back to change them once I problem has been noticed. I know that it’s possible to check through Excel and if I can’t find a solution I’ll just compare the total of the valid combinations against the total in the dimensions to ensure they’re correct but I’m really looking for a solution within TM1

Thanks in advance for helping!

[Wim Gielis]

Hi Jon

You refer to what is called as Element security.
Do a right mouse click on the dimension and choose Elements Security Assignments...
By element and TM1 security group, assign the needed level of security.

Can you try this first please?

Wim

[lotsaram]

Element security probably won't solve 100% of this problem since the intersection of 2 or more dimensions determines what is valid. This is a typical OLAP problem where "slice and dice" along different dimensions works great provided the data is loaded from a source system where already only valid combinations are present. It can present problems when you want to use an OLAP cube structure for data entry, as you often want to do in a planning model ...

Thankfully there is an answer. Just define your valid mappings in an "auxilliory cube" that just contains the dimensions which you need to define valid combinations of and a simple measure dimension (which need only contain one element "valid"). If the dimension intersection is defined as valid for storring data then you can just hold a value of 1 at the intersection.

In your very simple example you could define a cube AUX_Site_Employee_Map with dimensions Site, Employee and Map_Measure. Store a value of 1 against the valid intercections. (Note in the real world you might want to include a cost center or time dimensions in the mapping cube as people are sure to move over time.)

Then in the data entry cube you have a rule (usually somewhere near the begining of the rule file) that will look something like:

Code: Select all

# Note area statement may not be universal but limited to dimensions which the users have write access to data via element security (such as version, currency, etc)
# Make sure you still use skipcheck if this is the only rule!
[ ] = N:
IF( DB('AUX_Site_Employee_Map', !Site, !Employee, 'Valid') = 0,
  0,
  # Stet or Continue
  CONTINUE
);

The effect of this rule is that non-valid combinations are blocked from data entry as the intersection is pre-defined as zero and the cells will be greyed out for users. Note this rule does not require feeding AND SHOULD NOT BE FED as its purpose is to prevent data entry not to actually calculate anything.

Another valid approach would be to use the same or similar mapping cube but use it as the source for a cell security rule. IMO the rule to simply prevent data entry is preferrable as there will be no impact on the security model or performance.

[Jon]

Perfect lotsaram! Thats exactly what I was looking for! I was looking along those lines but assumed that even if I zeroed it out it would be writable. It didn’t click that stopping it a rule would make it read only like I wanted.

Thanks again!