企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 567|回复: 7

Load from Cube1 to Cube2 using DB rule

[复制链接]

85

主题

433

帖子

616

积分

高级会员

Rank: 4

积分
616
QQ
发表于 2014-3-15 11:46:45 | 显示全部楼层 |阅读模式
Hi gurus!

So im trying to do a simple rule and i cant get it to work the way im expecting it. Heres what i want to do:

Cube1 has several dimensions but only one im interested in for now. Its called Account. The Account dim has loads of elements that i do not want to load into cube2. I only want to load the consolidated level from dimension Account.

So i built a new Account dimension called AccountNew that has elements that corresponds to the consolidated level in dimension Account. I also created an attribute in Account for the consolidated level. I hope you are following me .

The rule i wrote looks like this in the Cube2 rules.
['Amount'] = DB('Cube1',!Fiscal Period,ATTRS('Account',!Account,'AccountCLevel'),'Amount');

This gives me a syntax error so i followed this thread  and changed the rule to an explicit account

['Amount'] = DB('Cube1',!Fiscal Period,ATTRS('Account','1234','AccountCLevel'),'Amount');

This compiles but i get no values in my target cube (Cube2). It even went so far that im doing trial and error , witn =N: and =C: but the same result.

Does anyone have any ideas how to fix this?

Thanks alot.
Peter

本帖子中包含更多资源

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

x
回复

使用道具 举报

80

主题

402

帖子

587

积分

高级会员

Rank: 4

积分
587
QQ
发表于 2014-3-15 12:59:34 | 显示全部楼层
Two things:-

On the right hand side you may only reference dimensions of the target cube using the ! notation. The meaning of !dimension is "the item name in dimension of the cell being targeted".

You don't need the attribute. If the members of AccountNew match the consolidated members of Account then !AccountNew used in the DB expression will select the appropriate consolidated values from the source cube.

Try something like
Code: ['Amount'] = DB('Cube1',!Fiscal Period,!AccountNew,'Amount');

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

74

主题

421

帖子

580

积分

高级会员

Rank: 4

积分
580
QQ
发表于 2014-3-15 14:34:10 | 显示全部楼层
I would add that using the !Dimension is almost the same thing as joining two tables in SQL on a certain field. The two fields in the two tables don't have to be identical, the JOIN will only bring back when the values match. It will work the same way in TM1, only the dimension elements that have the same name will be brought back in the DB function.
回复 支持 反对

使用道具 举报

67

主题

407

帖子

573

积分

高级会员

Rank: 4

积分
573
QQ
发表于 2014-3-15 14:40:10 | 显示全部楼层
Thanks for your replies!

So it doesnt matter if its consolidated level or elemetn level? IT just brings over the values that match?

Another question. I have 10 dimensions in cube2 and only 2 in Cube1. I can still write the DB function as stated above?

Br
Peter
回复 支持 反对

使用道具 举报

86

主题

402

帖子

589

积分

高级会员

Rank: 4

积分
589
QQ
发表于 2014-3-15 14:47:00 | 显示全部楼层
A.Pete wrote:I have 10 dimensions in cube2 and only 2 in Cube1. I can still write the DB function as stated above?
No, not exactly. When dimensionality does not match you have to tell TM1 exactly where to get and where to put the unmatched cells. For example, if in the target cube you have a Period dimension, but in the source you don't you would need to hard code the Period element in the target cube into the formula, much like this:

['Jan 2012','Amount'] = DB('Cube1',!Period,!AccountNew,'Amount');

For all dimensions that exist in the target, but not the source, you have to hard code an element into the left hand side of the rule statement for the nonmatched dimensions. The converse is true for dimensions that exist in the source and not the target. You have to hard code an element for each of the nonmatched dimensions on the right hand side of the rule statement.
回复 支持 反对

使用道具 举报

85

主题

419

帖子

604

积分

高级会员

Rank: 4

积分
604
QQ
发表于 2014-3-15 16:23:39 | 显示全部楼层
You need to specify in the DB statement the top item of each of the dimensions that are not in the target. It will not do the aggregation over those dimensions automatically. The order of the items in the DB statement is the order of dimensions in the source.

[EDIT] Beaten to the punch by Tomok
回复 支持 反对

使用道具 举报

73

主题

409

帖子

584

积分

高级会员

Rank: 4

积分
584
QQ
发表于 2014-3-15 16:26:07 | 显示全部楼层
As Duncan mentioned, in most cases you would hard code a top level element in the nonmatched dimensions to pull all the data over. However, this really is dependent on the data. Sometimes you may not want all of the data, like a Version dimension, where you have that in the source cube and not the target. It probably wouldn't make sense to pull over all the versions into the target, you probably would want just one version. In any case, it all depends on the data.
回复 支持 反对

使用道具 举报

87

主题

373

帖子

564

积分

高级会员

Rank: 4

积分
564
QQ
发表于 2014-3-15 16:31:42 | 显示全部楼层
You guys are the best.
Even though i haven't solved this yet i think i understand it better than i was this morning. So thanks for taking your time to educate a newbie

Br
Peter

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2020-11-28 22:33 , Processed in 0.141142 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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