企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 447|回复: 7

Dinamic Creation of Subsets based on attribute data

[复制链接]

77

主题

392

帖子

547

积分

中级会员

Rank: 3Rank: 3

积分
547
QQ
发表于 2014-3-20 03:40:52 | 显示全部楼层 |阅读模式
Hello,

My client has a product dimension where each element has an attribute regarding market segment.

Ex:

Product category Product Attribute Segment
Cat A Prod A Market 1
Cat A Prod B Market 2
Cat B Prod C Market 1

In a TI process to create the dimension I need to create automatically some subsets for the dimension (based on each existing attribute).

In this example:

A subset for Market 1
other for Market 2
(mantaining the default subset)

Can anyone give me some help?

Thank you
回复

使用道具 举报

91

主题

437

帖子

636

积分

中级会员

Rank: 3Rank: 3

积分
636
QQ
发表于 2014-3-20 04:42:40 | 显示全部楼层
Here's MDX syntax for your two dynamic subsets:

Code: {FILTER( {TM1SUBSETALL( &#91roduct] )}, &#91roduct].[Segment] = "Market 1")}
Code: {FILTER( {TM1SUBSETALL( &#91roduct] )}, &#91roduct].[Segment] = "Market 2")}

Just paste the code in the expression window in Subset Editor and save the subset with the expression attached.

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

81

主题

410

帖子

586

积分

中级会员

Rank: 3Rank: 3

积分
586
QQ
发表于 2014-3-20 04:44:08 | 显示全部楼层
Hello qml,

Thanks for the feedback.

My problem is that I want to do this dinamically with TI after executing a TI process to update the DIM Products. And I need to create a subset for each data existing in the Segment attribute.

I haven't been able to do it but also I can't determine if this is possible (by the way, I'm using a csv file as a data source in the dim products TI process).

Thanks again for the time and help,

qml wrote:Here's MDX syntax for your two dynamic subsets:

Code: {FILTER( {TM1SUBSETALL( &#91roduct] )}, &#91roduct].[Segment] = "Market 1")}
Code: {FILTER( {TM1SUBSETALL( &#91roduct] )}, &#91roduct].[Segment] = "Market 2")}

Just paste the code in the expression window in Subset Editor and save the subset with the expression attached.

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

80

主题

407

帖子

587

积分

中级会员

Rank: 3Rank: 3

积分
587
QQ
发表于 2014-3-20 05:25:54 | 显示全部楼层
These dynamic subsets will work after your dimension gets updated with TI.
However, if you really insist on creating them as static subsets using TI (which might have som pros, I admit), then my advice is to first create dynamic subsets using SubsetCreateByMDX() and then use them as data sources for another (sub)process that will pick up all elements in them and add them to another static subset using SubsetElementInsert(). Then you can delete the dynamic subsets.
It would be really useful if your list of possible unique values of the Segment attribute was known beforehand, so that you don't have to build a separate logic to get that list.
回复 支持 反对

使用道具 举报

72

主题

369

帖子

522

积分

中级会员

Rank: 3Rank: 3

积分
522
QQ
发表于 2014-3-20 06:12:05 | 显示全部楼层
Would it be possible to do something like:

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Variable X" )}

being "Variable X" a variable to get the data in the attribute "Segment" ?

I'm trying to make this the most possible "bulletproof" solution so that the power users don't forget each time a new segment is loaded from the ERP.
qml wrote:These dynamic subsets will work after your dimension gets updated with TI.
However, if you really insist on creating them as static subsets using TI (which might have som pros, I admit), then my advice is to first create dynamic subsets using SubsetCreateByMDX() and then use them as data sources for another (sub)process that will pick up all elements in them and add them to another static subset using SubsetElementInsert(). Then you can delete the dynamic subsets.
回复 支持 反对

使用道具 举报

67

主题

399

帖子

545

积分

中级会员

Rank: 3Rank: 3

积分
545
QQ
发表于 2014-3-20 06:47:48 | 显示全部楼层
If you are using SubsetCreateByMDX you will need to make sure that the dimension has at least one element that satisfies the MDX condition. If the MDX returns an empty set then the call fails. If you are only creating subsets for the values of the attribute that appear in the current members then you will be OK. If there are other attribute values for which you need subsets you will have to create them interactively in Architect or, if it must be done in TI, add a dummy item that has the correct attribute value, create the subset then delete the item. Alternatively modify the attribute value for any existing item and then change it back afterwards. To create a subset in Architect for a non-existent attribute value you will have to modify the MDX manually as the UI for filtering by attribute values only offers current values.
回复 支持 反对

使用道具 举报

83

主题

388

帖子

552

积分

中级会员

Rank: 3Rank: 3

积分
552
QQ
发表于 2014-3-20 07:28:56 | 显示全部楼层
jviegas@bi4all.pt wrote:Would it be possible to do something like:

{FILTER( {TM1SUBSETALL( [Product] )}, [Product].[Segment] = "Variable X" )}

being "Variable X" a variable to get the data in the attribute "Segment" ?

I'm trying to make this the most possible "bulletproof" solution so that the power users don't forget each time a new segment is loaded from the ERP.
What I would suggest is to create a purely technical dimension Segment that would be updated automatically from ERP with any new segment created there. You could have a nightly chore to run this update. Then you could have a second TI, using a dynamic subset containing all segments as its data source:
Code: {TM1SUBSETALL( [Segment] )}
For each element of this subset it would (re)create a corresponding dynamic subset using the following code logic on the Metadata or Data tab:
Code: sSubsetName = 'Dynamic_Segment_' |vSegment;
If (SubsetExists ('Product', sSubsetName) = 1);
  SubsetDestroy ('Product', sSubsetName);
EndIf;
sMDX = '{FILTER( {TM1SUBSETALL( &#91roduct] )}, &#91roduct].[Segment] = "' | vSegment |'" )}';
SubsetCreatebyMDX (sSubsetName, sMDX);
As Duncan P rightly pointed out, you need to ensure that the subset definition returns at least one element at the time of creation. Otherwise this TI will throw an error.

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

82

主题

429

帖子

619

积分

中级会员

Rank: 3Rank: 3

积分
619
QQ
发表于 2014-3-20 07:29:31 | 显示全部楼层
Hello to all,

thanks for the feedbacks and sorry my late reply.

I was able to do this with a second TI Process after creating the dim (the dim has an attribute with the product segment previously loaded with a file)

In this 2nd TI process i've placed this code:

Code: ################################ SUBSET <Subset name>################################
   i = 1;
   While (i <= DimSiz('<Dim_Name>'));

### Determina Nome do elemento da dimens茫o ###
      dimelement=DimNm('<Dim_Name>', i);

### Determina o nome do <Subset name>para o elemento ###
      subex=Attrs('<Dim_Name>',dimelement,'<Attribute_name>');

## Update do subset existir e apenas para elementos que n茫o existam ##
      If(SubsetExists('<Dim_Name>',Attrs('<Dim_Name>', dimelement, '<Attribute_name>'))=1);
         top=SubsetGetSize('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'));
         count=1;
         SubElemExists=0;
         while ((count<=top)%(top=0));
            if(SubsetGetElementName('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), count)@=dimelement);
               SubElemExists=1;
            endif;
            count=count+1;
         end;
         if(SubElemExists=0);
            numelement=SubsetGetSize('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));
            SubsetElementInsert('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), dimelement, numelement);
         ENDIF;
         SubElemExists=0;

### Cria莽茫o de subset e coloca莽茫o do artigo ##
      ELSEIF ((SubsetExists('<Dim_Name>',Attrs('<Dim_Name>', dimelement, '<Attribute_name>'))=0)&(Attrs('<Dim_Name>',dimelement,'<Attribute_name>')@<>''));

## Cria o subset ##
         SubsetCreate('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));

## Insere elemento no subset ##
         numelement=SubsetGetSize('<Dim_Name>',Attrs('<Dim_Name>',dimelement,'<Attribute_name>'));
         SubsetElementInsert('<Dim_Name>', Attrs('<Dim_Name>', dimelement, '<Attribute_name>'), dimelement, numelement);
      Endif;
   i = i + 1;
   End;


Hope it helps if someone needs something like this.

Thanks again for the ideas

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-4-27 14:46 , Processed in 0.225040 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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