Cube View by using Dynamic Time Dimension SubSet

[WayneBo]

Hi all:

I have a question regarding how to create a dynamic time dimension subset ( I am using continuous time dimension type ).

Little back ground:

I need to load rolling 21 days data into a cube using TI process on daily scheduled basis. (ie, delete old one, and load again by fresh downloaded csv)
I using TI to create a 21 days window data view with all the leaf level, then TI will using ViewZeroOut to clear all the leaf data on the cube View.
TI then load freshly downloaded csv with lineup 21 rolling days data and load into cube, then, TI will delete this cube view, and ready for next day.

Problem I am facing:

So, in this cube, during the creating view process, I have hard coded all the leaf elements in the SubSetElementInsert, because, there wouldn't be any changes, then, assign all the subset created to the create view.

However, I have a problem how create a this dynamic rolling 21 days subset from the time dimension.
I have a feeling that I might need to come cross the MDX, but not very confident with the syntax, and if any one could please give a hint.
I have loads of different attributes on the time dimension, ( please see in screenshot ), please also provide you thoughts and helps.

Thanks a lot
W

[gtonkin]

I normally do the following on my dimension called Period:
A chore runs just after midnight and updates a subset called _S-Current Period with today's date.

For my Last 32 days subset I then have the following MDX:

Code: Select all

TAIL(TM1SORT(
{LASTPERIODS(32,
TM1Member([Period].[_S-Current Period].ltem(0),0)
) },
DESC),32)

The TM1Member function I use will get the first element in the referenced subset.
Sorting DESC gives me today's data at the top as generally Period is on titles and you want this first in the list.
Changing to ASC and having the subset on rows should work fine for your requirements.

[lotsaram]

I normally do the following on my dimension called Period:
A chore runs just after midnight and updates a subset called _S-Current Period with today's date.

For my Last 32 days subset I then have the following MDX:

Code: Select all

TAIL(TM1SORT(
{LASTPERIODS(32,
TM1Member([Period].[_S-Current Period].ltem(0),0)
) },
DESC),32)

I usually use a very similar logic except that my time dimensions normally contain "relative time members" and not just relative time subsets. E.g. a single child consolidation called "Current Month", another one called "Today" and "Yesterday" etc. This makes it very easy for users setting up standard views and reports.

In the OP's use case there could be a rollup (or subset) created each night va TI just called "Trailing 21 days" (or similar).

HOWEVER! Such relative time rollups break the leveling concept of the hierarchy/dimension and it can play havoc with MDX like LastPeriods or elementFrom:elementTo sets. So to avoid this you need to be really careful with dimension ordering and making sure that all your relative time consolidatiosn are defined LAST, right at the end of the hierarchy

[WayneBo]

Thanks guys for help.

In my case, what should be the MDX code exactly? Not very familiar with MDX query, could you guys kindly help.

Thanks
W

[lotsaram]

Thanks guys for help.

In my case, what should be the MDX code exactly? Not very familiar with MDX query, could you guys kindly help.

Thanks
W

"Gimme teh codez" type demands generally don't go down too well here (or anywhere for that matter).

gtonkin already gave you something pretty much as close as you can get. All you need is to
- create a subset with one member being the current day
- change # of elements from 32 to <your magic number>
- make sure the levelling structure of your date dimension doesn't impact the function of the LastPeriods function

No one can do any better without you first providing relevant information such as the name of the dimension, naming convention for the elements and the leveling structure of the dimension.