Continuous time link to discrete time dimension

[WayneBo]

Hi All:

I have a question regarding to the 2 different types of time dimension (continuous vs discrete)

I have an occupancy cube using continuous time dimension on daily level to record the daily occupancy number.
Continuous time dimension has 4 levels, Year-Month-Week-Day.

Then, I have budget cube using the discrete time dimension ( have year dimension, month dimension ).
In this budget cube, I have budgeted occupancy on monthly level.

Now, I want to compare the actual vs budget occupancy on a daily level.
I am able to do a rule in budget cube to calculate the daily budget number by divide the monthly budget number by the month days (Dim Month attributes).

But I don't know how do I transfer the calculated daily budget number ( which on month level in a discrete time in budget cube) to the daily occupancy cube ( which is using the continuous time on daily level) in the relevant (correct) month and year.

Could and TM1 expert kindly provide help.

Thank you
W

[orlando]

Hi All:


But I don't know how do I transfer the calculated daily budget number ( which on month level in a discrete time in budget cube) to the daily occupancy cube ( which is using the continuous time on daily level) in the relevant (correct) month and year.

Hi,

here is my very fast idea - early in the morning

You need a "Month" Attribute on the day.

You then have a rule in your actuals cube, that reads the Month Attribute from the day in a DB rule to get the figures from the budget cube.

Could be somethink like that

['budget_Element_in_actual_cube'] = N: DB('budgetCube', Dim1, Dim2, ATTRS('DimWithDay', !DimWithDay, 'Month'), 'DailyBudget');

Best regards,

orlando

[WayneBo]

Hi Orlando:

Thanks for your help.

I did small sample test, and it works.

But that bring me another question.

How do I mass edit my continuous time dimension attributes?
When I built the continuous time dimension, I didn't populate the Year and Month attributes, and this dimension been used in so many cubes already.

Is there a way that I can mass edit the attributes of a dimension after the dimension been created already?

Please kindly advise.

Thank you
W

[orlando]

Hi Orlando:


Is there a way that I can mass edit the attributes of a dimension after the dimension been created already?

How does you dimension look like? Could you post a screenshot?

[Steve Rowe]

Is there a way that I can mass edit the attributes of a dimension after the dimension been created already?

Use a TI process to with the a level zero subset of the dimension as a datasource and write the attribute to the dimension
or
use the DBSA formula to write the value from Excel

[WayneBo]

Hi Orlando:

Thanks for your help.

The below is my dimension screen shot.

As you can see, I am at the moment, manually adding the year number and month number to the 2 newly add attributes of the dimension on day level.

Just wandering , if there is a fast way / automatic way to do this process, maybe by using rules or formulas?

Thanks a lot.
W

TimeDimension.png (217.62 KiB) Viewed 6324 times

[gtonkin]

You could add an alias to your existing Month dimension to store 01, 02 etc. for Jan, Feb and so on.

On your Time dimension add a new attribute for Month and a rule to derive the Month (Jan, Feb...) by parsing the month from the element (subst first 2 characters) against your month dimension.

You may want to also create a Year element and populate it by rule using a subst on characters 7-10

You will need to consider rules for your leaf levels as well as each other level in the various hierarchies.

Hope this helps.

[orlando]

Hi Orlando:

Just wandering , if there is a fast way / automatic way to do this process, maybe by using rules or formulas?

TimeDimension.png

Hi,

yep - there is.
Rule or TI.

If you're using a Rule - you don't have to worry about new elements

somethink like that should work - https://exploringtm1.com/subst-tm1-function-use-syntax/

Year = SUBST(!DimensionWithDay, 7, 4);

for the day u the same

if you don't like a rule you can use SUSBT in an TI.

best regards and have a nice weekend
orlando

[WayneBo]

Thanks Orlando, thats help a lot.

W