Populating properties of a public dimension through a logic in the import job itself?

[phadke_manas]

Hello Experts,

We have a public dimension having 4 properties L1, L2, L3 and L4 which we're populating through an import job using a SuccessFactors query. There's no BW or Datasphere in the client landscape and we're importing directly from SuccessFactors into SAC. L1, L2, L3 and L4 are names and data type text. We have some id's where we get L2 and/or L3 blank and hence a logic needs to be applied as below.

If L3 is blank, populate L2 value in L3, If L2 is also blank, populate L1 value in L3, If L1 is also blank then do nothing
If L2 is blank, populate L1 value in L2, If L1 is also blank then do nothing

Can the above logic be implemented through wrangling in the SAC import job for the public dimension itself? We would like to know the feasibility of this option because this is the preferred approach in SAC without any changes on the SuccessFactors side.
Assume the other approach would be through scripting and using an advanced formula data action after the dimension is loaded from the query? Any other options in SAC to get the desired result?

Regards,

Manas Phadke

SAP Analytics CloudSAP Analytics Cloud for planning 

[N1kh1l · Accepted solution]

@phadke_manas 

Have not tried with SF but I think it should be feasible. L1,L2,L3,L4 are original SF columns. In The Data Wrangling it should be possible to create custom column and use IF formulas with ISNULL or LENGTH syntax logic to create 2 extra custom columns  L2_SAC and L3_SAC . You can put the logic here. Then finally map these custom columns to SAC dimension properties.

I tried with Excel file as a source and it worked but you have to test it with SF as source.

Now you can map L1, L2_SAC,L3_SAC and L4 to SAC dimension.

Transformation used in Wrangling

IF(LENGTH([L2])=0, [L1], [L2] ) // For L2_SAC
IF(LENGTH([L3])=0, [L2_SAC], [L3] ) // For L3_SAC

Your to be view. Ignore the ordering of columns.

Hope this helps !!

Nikhil

[phadke_manas · Accepted solution]

@N1kh1l 

Thanks for your inputs Nikhil. Using a calculated column for both L3 and L2 with below formula did the trick!

IF([L3]="",
        IF([L2]="",
                [L1],
                [L2]
        ),
        [L3]
)