企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 552|回复: 4

Cube View: MDX Dynamic Subset, Filtering Cube Data

[复制链接]

75

主题

409

帖子

574

积分

高级会员

Rank: 4

积分
574
QQ
发表于 2014-3-17 08:39:38 | 显示全部楼层 |阅读模式
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!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

77

主题

397

帖子

570

积分

高级会员

Rank: 4

积分
570
QQ
发表于 2014-3-17 10:02:31 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

75

主题

398

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-17 10:20:20 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

81

主题

411

帖子

598

积分

高级会员

Rank: 4

积分
598
QQ
发表于 2014-3-17 10:59:05 | 显示全部楼层
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.
回复 支持 反对

使用道具 举报

83

主题

416

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-3-17 11:38:03 | 显示全部楼层
Btw: Thanks a lot Jim for your answer  

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-7-6 02:58 , Processed in 0.259293 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表