How to restrict the Products per Cost Centre (not the user)

[Mark H]

Folks,

Want to build a cube for TM1 Contributor cube that has relationship between the Product Dim and Cost Centre dim. Not the User.

TM1 security is based on the User/User Group. I want to design the model for the Cost Centre not the person so that it makes no difference who is logging in – Cost Centre ‘A’ must only see Products 1-20 or in another example the IT CostCentre must only see only IT expenses and so on.

TM1 only offers a relationship between two or more dimensions (other than User) if we use Cell Security. This however can only go as far as ‘None’. This only removes data from display and does not remove the row/col combinations of Products and Cost Centre from the grid. In our example we have many 1000’s of Products so cannot offer the Cost Centre opportunity to find just their 20 Products from a list of 30k items.

We are seeking to understand how other companies handle this ‘basic’ characteristic in forecasting/planning.

If my assertions above are correct then I do have some other highly complex ideas of how to deliver this including:

1. creating many separate tailored models and linking them together at the end but that will be introducing big complexity for us.
2. Dynamic subsets based on user entering the model (seems cannot do this)
3. User selecting own subset once entered the model. This works but means creating 2000 subsets (as have 2000 cost centres)
4. Creating a ‘MyProducts’ flag in the months dim. Populating per Cost Centre, then set the view to Suppress Zero in the input cube using this flag. Again this works but user could just remove the zero suppression by accident.

Appreciate any pointers here.

[jim wood]

This isn't something that is difficult to do it's just a bit manually intensive. What I would would is create a lookup cube with 2 dimensions in it. (product and cost centre) Next insert a 1 in to the cube were you want the relationship to work. Next add the following rule to your cube:

Code: Select all

[] = N: IF(DB('lookup',!product,!CostCentre) =1, continue,0);

I hope that helps,

Jim.

Edited as I forgot the semi colon. I know, I know.

[Mark H]

@Jim, not sure if you have understood.

I want to restrict access to only 'valid' combinations in the Sales cube.

Open model for Cost Centre 'A' and see just the Products that belong to that cost centre.
Open model for Cost Centre 'B' and see just the Products for Cost Centre B.

I have already built the relationship and forced it into the target 'Sales' cube using a 'My Products' flag and zero suppression works on that.

Are you suggesting somthing different?

Thanks

M

[lotsaram]

All that you need to do is create an attribute in the product dimension to match to cost center. Then create a dynamic subset something along the lines of:

Code: Select all

{Filter(
  {TM1FILTERBYLEVEL( {TM1SUBSETALL( [Product] )}, 0)},
  [Product].[Cost Center] = [Cost Center].CurrentMember.Name
)}

Such a dynamic subset will work and return members ONLY in the context of a view where Cost Center is a title element, but work it does and it is very elegant. Then just have this subset in the view to determine the rowset of products.
This is discussed further here

[Mark H]

@lotsaram

Ok, this is promising. But will not work in the event where there is overlap between cost centres. Many cost centres will share the same products so the Attribute or dynamic subset needs to be multi-dimensional.

Will digest this....

Thanks

M

[lotsaram]

Another thought.
If you were just looking at actuals then simple zero suppression would show the "valid" combinations of product and cost center, but since you specifically mentioned Contributor I guess there is an element of forecasting involved and so zero suppression isn't an option if the data is yet to be populated.

But maybe zero suppression can be applied, ... what's the layout of the view that you want users to use in Contributor? If you have months or weeks or some other kind of time dimensionality across columns (which would be the most common Contributor layout I'd guess), and assuming that what defines a "valid product to cost center relationship" probably has something to do with whether actuals have been recorded then you could have an artificial "month" element called "valid product". If there are actuals against the product/cost center combination then have a rule in this measure with simple boolean 1/0 and feed it from all real months for the current year and probably into the next year for good measure. It would give you an extra column as the indicator of a valid product that you could shove to the far right of the view, and you can then apply zero suppression for valid products just like for actuals. It is a bit artificial but relatively simple to implement and may work in your situation.

It may seem a bit clunky to force such a measure into a dimension where "it doesn't belong" but with Contributor and the constraints of the single view / single cube interface you get forced to make all sorts of bastardized and creative designs sometimes to achieve the result.

[Mark H]

@lotsaram

The zero suppression method is already working to my satisfaction but my superiors want to see a real script/filter at work. The problem is of course that the User can remove the zero suppression and even slcing/dicing can undo the formatting.

I have done this:

1. create a selection management cube(that could be populated from the Pricing data to activate permitted combination)
2. Passed this flag into a month called 'My SKUs' in the Input cube.
3. Configured the View to use zero suppression.

This gives me full flexibility as any cost centre can be managed independently and cost centres and sell some of the same products.

I have attached the screens showing the work.

What do i want now? A dynamic subset that can filter on multi-diemnsional data values (where combination/tuple=1) or a multi-dimensional attribute.

Manage Selections

manage selections.JPG (33.16 KiB) Viewed 4581 times

Pass selection to input cube

pass selections to Input cube.JPG (54.52 KiB) Viewed 4581 times

Pass selection to input cube

pass selections to Input cube.JPG (54.52 KiB) Viewed 4581 times

Many thanks

Mark