企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 460|回复: 6

SubSet for Date Range - MDX

[复制链接]

73

主题

409

帖子

576

积分

中级会员

Rank: 3Rank: 3

积分
576
QQ
发表于 2014-3-18 23:09:13 | 显示全部楼层 |阅读模式
Hello,

I am using a parameter cube that has a start date and an end date to specify a date range that will be used by an MDX query to populate a
dynamic subset. The Dynamic subset will be used by a view to determine which records should be zeroed out before loading data.

The paper from Philip Bichard titled " Creating Dynamic Susbsets in Applix TM1 using MDX" states that :

"A range of contiguous members from the same level can be selected by specifying the first and last member of the set you require with a colon between them."

The Process works when all dates are in the same month

Start Date: 4/1/2013
End Date: 4/30/2013


The Process will fail if any of the dates are from a different month:

Start Date: 4/30/2013
End Date: 5/1/2013

This will return all 30 days in April and 31 Days in May

Why would this fail? The elements are contigous and are from the same level?

If I can not use MDX, what can I do to achieve my goal?

Thanks,

Walt













The code in the Prolog is:

#^^^ GLOBAL VARIABLES

sCubeRef = 'VodDailySales';
sViewRef ='zzTiRowsToUpdateView';
vError = 0;

#^^^ VodDay dim and Subset to maintain
sDimDatesRef = 'VodDay';
sSubNameDatesRef = 'zzTiDatesToUpdate';

#^^^ Date Ranges provided by user in VodParamerters cube
sZeroOutStartDate = CellGetS('VodParameters', 'ZeroOutStartDate','ParmValue');
sZeroOutEndDate = CellGetS('VodParameters', 'ZeroOutEndDate','ParmValue');

#^^^ Ensure that values Provided have corresponding values in Dim.
IF(dimix(sDimDatesRef,sZeroOutStartDate)=0);
vError = 1;
ItemReject('Error ' | sZeroOutStartDate | 'does not exist in the dimension' | sDimDatesRef);
ENDIF;

IF(dimix(sDimDatesRef,sZeroOutEndDate)=0);
vError = 1;
ItemReject('Error ' | sZeroOutEndDate | 'does not exist in the VodDay dimension' | sDimDatesRef);
ENDIF;

#^^^ Drop the existing 'zzTiRowsToUpdateView' View and SubSet

IF ( ViewExists ( sCubeRef , sViewRef ) = 1);
ViewDestroy ( sCubeRef , sViewRef );
ENDIF;
IF ( SubsetExists ( sDimDatesRef , sSubNameDatesRef) = 1);
SubsetDestroy ( sDimDatesRef, sSubNameDatesRef );
ENDIF;


#^^^ Create a subset on the VodDay dimension using values provided by user

SUBSETCREATEBYMDX('zzTiDatesToUpdate','{[VodDay].[' | sZeroOutStartDate | ']:[VodDay].[' | sZeroOutEndDate | ']}');

#^^^ Create a view on the VodDaily Sales cube
ViewCreate(sCubeRef, sViewRef);


#^^^ Maintain Temporary Stage Dimension's and Subset

# Declare global variables

sDimRefStage = 'zzVodStageMSOsOnFileForSubset';
sSubRefStage = 'zzTiVodAllMSOsOnFile';

sDimRefMSO = 'VodMSO';
sSubRefMSO = 'zzTiMSOToUpdate';

# Determine if the Dimension zzVodStageMSOsOnFileForSubset exists; if not, create it.

IF (DimensionExists (sDimRefStage) = 0 ) ;
DimensionCreate(sDimRefStage);
ENDIF ;

# Determine if the subset zzTiVodAllMSOsOnFile exists; if not, create it.

IF (SubSetExists (sDimRefStage, sSubRefStage) = 0 ) ;
SubSetCreate (sDimRefStage, sSubRefStage);
ENDIF ;


# Determine if the subset zzTiUniqueMSOsOnFile exists; if not, create it.

IF (SubSetExists (sDimRefMSO, sSubRefMSO ) = 0 ) ;
SubSetCreate (sDimRefMSO, sSubRefMSO );
ENDIF ;


#^^^ Assign the zzTiUniqueMSOsOnFile Subset to the View.
ViewSubsetAssign(sCubeRef, sViewRef, sDimRefMSO, sSubRefMSO);


#^^^ Assign the Dates Subset to the View. All other dimensions will use "All" elements;
ViewSubsetAssign(sCubeRef, sViewRef, sDimDatesRef, sSubNameDatesRef);
回复

使用道具 举报

81

主题

389

帖子

572

积分

中级会员

Rank: 3Rank: 3

积分
572
QQ
发表于 2014-3-19 00:25:49 | 显示全部楼层
Not sure why the MDX is failing but this ought to be pretty straight forward to do "old school" fashion by

0.1. Add you start date to the subset.
1. Converting your start date to a TM1 date serial.
2. Incrementing the serial by 1.
3. Convert it back to your date format.
4. Add the element from step 3 to the subset.
5. Repeat until end date is reached.

HTH even if its not what you were looking for!
回复 支持 反对

使用道具 举报

87

主题

373

帖子

560

积分

中级会员

Rank: 3Rank: 3

积分
560
QQ
发表于 2014-3-19 01:16:06 | 显示全部楼层
Hi CiskoWalt,

You syntax appears to be correct... can you please submit the error screen dump?

Thanks

Evgeny
回复 支持 反对

使用道具 举报

72

主题

394

帖子

548

积分

中级会员

Rank: 3Rank: 3

积分
548
QQ
发表于 2014-3-19 01:31:25 | 显示全部楼层
Evgeny,

Thanks for your help.  There is no error message.  Probably the best way to see that the MDX does not work as expected is to use the 'Expression Window' to view the generated subset:

You can see that we used the data range 4/30/2013 to 5/1/2013 but the data returend is all elements from 4/1/2013 to 5/31/2013.

                                                                                        MDX_Dim.png (129.25 KiB) Viewed 428 times

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

69

主题

368

帖子

513

积分

中级会员

Rank: 3Rank: 3

积分
513
QQ
发表于 2014-3-19 02:05:11 | 显示全部楼层
I think the issue is that there are 2 hierarchies in the VodDay dimension.  One for All Years the the other for Relative Times


                                                                                        Hierarchy.png (67.23 KiB) Viewed 428 times
回复 支持 反对

使用道具 举报

85

主题

427

帖子

613

积分

中级会员

Rank: 3Rank: 3

积分
613
QQ
发表于 2014-3-19 03:01:51 | 显示全部楼层
thanks eveyone for your help.

I found a post on this site and followed the same process:

1 Add the TM1 date serial number as an attribure to the Date dimension


#^^^  This process maintains a view that is associated with the VodDailySales Cube
#^^^
#^^^

#^^^ GLOBAL VARIABLES
  sCubeRef = 'VodDailySales';
  sViewRef ='zzTiRowsToUpdateView';
  sProLogMinorErrorCount = 0;


#^^^  VodDay dim and Subset to maintain
  sDimDatesRef = 'VodDay';
  sSubNameDatesRef = 'zzTiDatesToUpdate';

#^^^ Date Ranges provided by user in VodParamerters cube

  sZeroOutStartDate = CellGetS('VodParameters', 'ZeroOutStartDate','ParmValue');
  sZeroOutEndDate = CellGetS('VodParameters', 'ZeroOutEndDate','ParmValue');

#^^^ Get the TM1 Serial Date number. Stored as an attribute. This permits the user to enter 'YYYY-MM-DD' or 'DD-MM-YYYY'
  sTM1SerialStartDate = ATTRS( 'VodDay', sZeroOutStartDate, 'TM1DateSerialNo' );
  sTM1SerialEndDate = ATTRS( 'VodDay', sZeroOutEndDate, 'TM1DateSerialNo' );

.............



#^^^ Create a subset on the VodDay dimension using values provided by user

sMDX= 'INTERSECT(
     {FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [VodDay] )}, 0)},[VodDay].[TM1DateSerialNo] >= ''' | sTM1SerialStartDate | ''' )},
     {FILTER({TM1FILTERBYLEVEL({TM1SUBSETALL( [VodDay] )}, 0)},[VodDay].[TM1DateSerialNo] <= ''' | sTM1SerialEndDate | ''' )}) ';

  SUBSETCREATEBYMDX('zzTiDatesToUpdate',sMDX);


#^^^ Create a view on the VodDaily Sales cube
    ViewCreate(sCubeRef, sViewRef);

---------

#^^^ Assign the Dates Subset to the View.  All other dimensions will use "All" elements;
   ViewSubsetAssign(sCubeRef, sViewRef, sDimDatesRef, sSubNameDatesRef);
回复 支持 反对

使用道具 举报

74

主题

392

帖子

558

积分

中级会员

Rank: 3Rank: 3

积分
558
QQ
发表于 2014-3-19 03:54:48 | 显示全部楼层
A comprehensive posting on Dates in TM1 can be found here:

本帖子中包含更多资源

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

x
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2018-4-25 20:15 , Processed in 0.185116 second(s), 38 queries .

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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