Data transfer with different Time Dimension Uneven Hierarchy

[appleglaze28]

I was wondering if someone can help me out with the rule?

For example I have a lookup cube determining the whether a specific date is Regular working, etc... And I have assumption cube per month to determine the standard rate per month.

I created a staging cube to store the rate to be used depending on the type of day the a specific date it. So my time dimension level is different for the assumption (Months) and lookup(Days).

My Days time dimension has a attribute of its parent.

Code: Select all

['Actual Rate' ] =IF(DB('lookup_date_status', !base_day_month, !base_year, 'Status')@='Regular' , DB('assumption_service_hour_rate', 'base_month', !base_version, !base_year, !base_service_type, !plan_hours_rate, 'Regular'),continue);
['Actual Rate' ] =IF(DB('lookup_date_status', !base_day_month, !base_year, 'Status')@='Legal', DB('assumption_service_hour_rate', 'base_month', !base_version, !base_year, !base_service_type, !plan_hours_rate, 'Legal'),continue);
['Actual Rate' ] =IF(DB('lookup_date_status', !base_day_month, !base_year, 'Status')@='Special', DB('assumption_service_hour_rate', 'base_month', !base_version, !base_year, !base_service_type, !plan_hours_rate, 'Special'),continue);

I was thinking of using the ELPAR function for the base_days_month or ATTRS function. I'm not sure where and how to add that additional condition. I tried the code below but its not working

Code: Select all

['Actual Rate' ] =IF(DB('lookup_date_status', ELPAR('base_day_month',!base_day_month,!Month), !base_year, 'Status')@='Regular' & DB('assumption_service_hour_rate', 'base_month', !base_version, !base_year, !base_service_type, !plan_hours_rate, 'Regular'),continue);

[DFranken]

Hi there

When using ELPAR it reference the parent of the n level element. In your case July 01 will reference July. Thus the ELPAR will return the value for the July consolidator.

In your Lookup Date Status Cube the July Value is empty. Thus returning "".

Try inputting the strings on month level too.


Hope that sorts it.

regards

douw

[appleglaze28]

It wont make sense though putting String on the Month Level since calculations would be done on the element level and also since not all month would be like all regular. This would just simply allow the user to determine what rate to use for that specific day depending if that day is a Special Holiday, Legal Holiday or a Regular Working holiday.

I'm not really sure how to work this out.

[DFranken]

Yea I see your problem ...

I would use the following:

['actual rate'] = DB('assumption_service_hour_rate', 'base_month', !base_version, !base_year, !base_service_type, !plan_hours_rate, DB('lookup_date_status', !base_day_month, !base_year, 'Status'));

Thus using a DB function within another DB function. The Second DB will return the rate ( string) on the !base_day_month.

d

[appleglaze28]

I think I would still encountered issues with the first DB since my lowest level would be Month(January) while the second DB will contain the days (Jan-01) and my source cube contains the day. I can't seem to get the standard rate for the month to any cube who has a much lower level than Month. Is there no way to do this?