Cube View: MDX Dynamic Subset, Filtering Cube Data

[mama]

Hi,

I have worked a few years with Cognos Planning and I started to enjoy working with TM1 now. BUT: It was very simple to build Access Tables in Cognos Planning over multiple dimensions. It was very easy to say country A should see product A and country B should only see product B. I find it quite challenging in TM1...

I thought I already had the solution but it seems that it is not working perfectly. Imagine this:

I have a cube "Revenue by Country and Product" with three dimensions: Country, Product, Revenue. Switzerland should see products A and B, Germany should see products A, B and C.

To achieve this I created a "subset cube" which holds the dimensions Country and Product and an additional dimension Subset. Then I have created a dynamic subset on the Product dimension:
FILTER(TM1SUBSETALL([_Test Prod]),[_Test Subset Land - Prod].([_Test Subset].[Subset])<>0)

I am using that Subset in the cube "Revenue by Country and Product".

Now I open a cube view and I have the Product dimension in the context and the dimensions Country and Revenue in the rows and columns. It works perfectly. When I filter (in the context) on Switzerland I only see the Products A and B in the rows. When I filter on Germany I see the Products A, B and C. At this point I thought I made it and was very proud...

BUT: When I have the dimensions Products and Country in the rows and columns, it is not working anymore. I only see product A and B. C disappeared.

If I use both Country and Product in the columns (nested), I see Product A and B under Switzerland and only Product A and B under Germany - which is wrong. I expected to see Product A and B under Switzerland and Product A, B and C under Germany.

I see the same behaviour when I am opening the dynamic subset and click on Update in the Expression Window. It always shows the products which are valid for Switzerland - but I don't have the cube open. Switzerland is the first element in my Country dimension. Can it be that by default it refers to the first element?

Is the subset expression wrong? Some pictures enclosed...

Please help...

Thanks!

[jim wood]

I'm not sure if this will fit but consider creating a lookup cube to hold by country which products the country can see? Then from there have a security group for each country. In the element security cube for product have a rule that limits access by the said lookup cube. If there no relationship between user and country then this won't work.

I guess you could also consider doing something in excel. Have the said lookup cube. Then use a TI process to create a subset in product relating to the country. Then within excel use SubNm to display the product subset based on the country selected. I don't think you can do this within a cube view as I don't think MDX can relate to conditions outside the current dimension.

I hope this helps,

Jim.

[mama]

Hi Jim,

Thanks a lot for your answer.

I think option 1 is not really an option in my case because it has nothing to do with users. The real case behind my quesion is a bit different but the problem remains the same. I wanted to simplify the problem and thought that the product/customer example would be good...

Imagine this: I want to see the year (version) dimension in the columns, nested with the calculation dimension. In actual years (that would be 2013, 2012, 2011...) I want to see Amount, Hours and Amount per Hour. In the future year 2014 (budget year) I would like to see Amount, Hours, Amount per Hour, and Overwrite. But I don't want to see that additional element "Overwrite" in the actual years. See picture below for more details.

So you think this is not possible in TM1 Web Application (Contributor) or Cognos Insight or in an Architect cube view. Is this only possible in Excel?

Thanks.

[jim wood]

The ability to see or not an element in TM1 is controlled by security. You can create a subset that will not show an element but upon them pressing the all button they will see everything they are entitled to see. You can create excel reports that don't show certain elements as I mentioned, you can also create pre-canned views that will only show the elements you require them to see, but I don't you can't do what I think you are trying to do. If you do a search this has been discussed before and some of the options mentioned may be helpful to you,

Jim.

[mama]

Btw: Thanks a lot Jim for your answer