When you export SAC Planning data to ACDOCP.

SAC Date format is YYYYMM like 202201 and it has a property MonthDesc that has values Jan, Feb and so on.

ACDOCP Date for Fiscal Period should be MM like 01,02… 12.

When we run the retraction from SAC to S/4HANA, there is no way to manipulate exporting data because it is simple export.

Therefore, you need to make a staging model so that we can export data after change the date format from SAC style to S/4 HANA Group Reporting style.

Unfortunately, SAC Date dimension does not have a property of month number like 01, 02 because its ID already has that information like 202201.

The problem is… SAC Data Action script does not support substring functions yet.

Therefore, you may use IF statement for comparing month value one by one but that is a little bit clumsy because similarv12 IF statements are in the data action as below code.

//Reformat records for Jan (“01″)
IF [d/Date].[p/MONTHDESC]=”Jan” THEN data([d/CurrencyRetract]=[d/CompanyCode].[p/currency],[d/Routing]=[d/CompanyCode].[p/RetractionOpEx],[d/FiscalYearPeriod]=[d/Date].[p/YEARDESC],[d/FiscalPeriod]=”01″)=RESULTLOOKUP([d/Version]=%SourceVersion%)
ENDIF
//Reformat records for Feb (“02″)
IF [d/Date].[p/MONTHDESC]=”Feb” THEN data([d/CurrencyRetract]=[d/CompanyCode].[p/currency],[d/Routing]=[d/CompanyCode].[p/RetractionOpEx],[d/FiscalYearPeriod]=[d/Date].[p/YEARDESC],[d/FiscalPeriod]=”02″)=RESULTLOOKUP([d/Version]=%SourceVersion%)
ENDIF
//Reformat records for Mar (“03″)
IF [d/Date].[p/MONTHDESC]=”Mar” THEN data([d/CurrencyRetract]=[d/CompanyCode].[p/currency],[d/Routing]=[d/CompanyCode].[p/RetractionOpEx],[d/FiscalYearPeriod]=[d/Date].[p/YEARDESC],[d/FiscalPeriod]=”03″)=RESULTLOOKUP([d/Version]=%SourceVersion%)
ENDIF

Instead of doing it, we can have a dimension in the staging model that has FiscalPeriod with a property that has month number and description (Jan,Feb…).

Here is the screenshot.

Then we can copy data from Planning model to Staging model using the the property MonthDesc of Date dimension in the Planning model, which is Jan, Feb,…. Dec.

//Copy all months data from Date dimension to Fiscal Period to Jan, Feb Mar…
data([d/AccountRetract]=[d/AccountRetract].[p/MappingACDOCP],[d/CurrencyRetract]=[d/CompanyCode].[p/currency],[d/FiscalYearPeriod]=[d/Date].[p/YEARDESC],[d/FiscalPeriod]=[d/Date].[p/MONTHDESC])=RESULTLOOKUP([d/Version]=%SourceVersion%)

Then the data action needs the second step for copying data from staging model to staging model for converting Month type.

data([d/FiscalPeriod]=[d/FiscalPeriod].[p/Month])=RESULTLOOKUP()

For example, if the Planning model has a record that the date dimension value is 2022.01.

Then it will be copied to staging model then the FiscalPeriod will be ‘Jan’.

After execute the second step, staging model will have 2 records one has FiscalPeriod value as  ‘Jan’ and the other one has ’01’.

The last step of the data action is deleting data of the first step.

DELETE()

This step will delete the record that has ‘Jan’ value in the FiscalPeriod.

In summary,

1. Copy data from Planning model to Staging model using crossmodel copy feature.
2. Copy data from Staging model to Staging model using the MonthDesc property of Date Dimension so that FiscalPeriod can have month data like Jan, Feb…
3. Copy data from Staging model to Staging model using the property of FiscalPeriod so that FiscalPeriod can have numeric month data like 01,02…
4. Delete the records from the staging model that has string value (Jan,Feb…).

Instead of having multiple IF statement, this looks clean. 🙂

If anyone has better idea, please let me know.