BPC 10.0 SP20: automatic load from 0CALDAY to Time dimension in Rate model

[Former Member]

Hello BPC experts,

I have an issue with loading transaction data for the Rate model.

We used 0FISCPER before to load into Time Dimension but client wants to use 0CALDAY instead.

Reason is that there are 2 rates in BW for the same fiscal period.

Therefore, to differentiate them, we will use 0CALDAY where one rate starts on 01.12.2016 (used for AVG rate) and the second rate is set at 31.12.2016. (used for Closing rate)

Settings were like these before and properly worked :

Now with 0CALDAY, the changes are like those :

In the transformation file, the only change is in the Time row where 0FISCPER is replaced by 0CALDAY.

Conversion file currently has 1 to 1 link for testing purpose. But it does not work.

When validating the transformation, I got a green message but there is no accepted record.

Format of 0CALDAY is like the following picture in BW :

From what I can understand, it looks like BPC doesn't recognize the external format provided in the conversion file. I did try to change the Excel Format cell into Date, Text, General, Number, etc. but it did not change the result.

I also tried to write 01/01/2015, 01-01-2015, 01012015, 20150101, 2015/01/01, 2015-01-01 in the conversion file but it did not work either.

What could solve this issue please ?

Thank you in advance !
.

Dat

PS : I know that the transformation file is using *STR(CLO) instead of *STR(AVG) on the screenshots but that does unfortunately not change the problem.

[former_member186338 · Accepted solution]

0CALDAY stores date in YYYYMMDD format.

[former_member186338 · Accepted solution]

To be 100% correct:

YYYYMMDD - internal format

DD.MM.YYYY - external format

Excel format of the cell - TEXT (as default)

[former_member186338 · Accepted solution]

Just tested master data load from 0CALDAY with the conversion file:

with:

Result: