SubSet for Date Range - MDX

Post Reply
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

SubSet for Date Range - MDX

Post by CiskoWalt »

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




[img]
DATES.png
DATES.png (72.7 KiB) Viewed 7686 times
[/img]








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);
User avatar
Steve Rowe
Site Admin
Posts: 2410
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: SubSet for Date Range - MDX

Post by Steve Rowe »

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!
Technical Director
www.infocat.co.uk
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: SubSet for Date Range - MDX

Post by EvgenyT »

Hi CiskoWalt,

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

Thanks

Evgeny
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

Re: SubSet for Date Range - MDX

Post by CiskoWalt »

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
MDX_Dim.png (129.25 KiB) Viewed 7642 times
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

Re: SubSet for Date Range - MDX

Post by CiskoWalt »

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
Hierarchy.png (67.23 KiB) Viewed 7642 times
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

SubSet for Date Range - MDX

Post by CiskoWalt »

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);
Edward Stuart
Community Contributor
Posts: 247
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: SubSet for Date Range - MDX

Post by Edward Stuart »

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

http://www.tm1forum.com/viewtopic.php?f=21&t=2441
Post Reply