SubSet for Date Range - MDX

bxmsw

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);

670648633

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!

fuanxx

Hi CiskoWalt,

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

Thanks

Evgeny

AM哥

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

luozhongli

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

kangbei

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);

cswing_5

A comprehensive posting on Dates in TM1 can be found here: