Map multi dimensions from source model to one dimension in target model using *destination_app

[tobi_gg]

Hello,

we are facing issues mapping four dimensions from soure model to one dimension in target model.

The scenario is:

We have 2 different planning models - one is used for budget on product, sales channel, customer and other levels and the second model is used for budget on ECCS positions.

After planning is done in model one, the data should be transfered to model two. Therefor each combination of sales channel, sales subchannel, keyfigure and product is mapped to an ECCS position (a lot of combinations).

Dimensions of model 1:

zm_channel (sales channel)

zm_currency

zm_cust

zm_datasrc

zm_inputyear

zm_keyfig

zm_prod (products)

zm_profitctr

zm_subchannel (sales subchannel)

zm_time

zm_vers (version)

Dimensions of model 2:

zcs_item (eccs position)

zcs_partner (partner company)

zcs_time

zcs_unit (legal entity)

zcs_version

zcurr

z_input_year

My first approach was to do the mapping in a logic script, because I'm not sure if this combination would work in a transformation/ conversion file.

This is what I did so far:

I created a data manager package with a prompt for each dimension of model one. If the package is executed this logic script is started:

_______________________________

*XDIM_MEMBERSET ZM_CHANNEL = %ZM_CHANNEL_SET%
*XDIM_MEMBERSET ZM_CURRENCY = %ZM_INPUTYEAR_SET%
*XDIM_MEMBERSET ZM_CUST = %ZM_CUST_SET%
*XDIM_MEMBERSET ZM_INPUTYEAR = %ZM_INPUTYEAR_SET%
*XDIM_MEMBERSET ZM_KEYFIG = %ZM_KEYFIG_SET%
*XDIM_MEMBERSET ZM_PROD = %ZM_PROD_SET%
*XDIM_MEMBERSET ZM_PROFITCTR = %ZM_PROFITCTR_SET%
*XDIM_MEMBERSET ZM_SUBCHANNEL = %ZM_SUBCHANNEL_SET%
*XDIM_MEMBERSET ZM_TIME = %ZM_TIME_SET%
*XDIM_MEMBERSET ZM_VERS = %ZM_VERS_SET%

//I created this variable because the attribute cons_unit of zm_profitctr has to be zcs_unit in target model
*select(%UNIT%,CONS_UNIT,ZM_PROFITCTR,ID <> "")

*DESTINATION_APP = Core

*SKIP_DIM = ZM_DATASRC
*SKIP_DIM = ZM_CUST
*SKIP_DIM = ZM_CHANNEL
*SKIP_DIM = ZM_SUBCHANNEL
*SKIP_DIM = ZM_PROD
*SKIP_DIM = ZM_KEYFIG

*ADD_DIM ZCS_PARTNER = partner_1
*ADD_DIM ZCS_UNIT = %UNIT%

*RENAME_DIM ZM_CURRENCY = ZCURR
*RENAME_DIM ZM_INPUTYEAR = Z_INPUT_YEAR
*RENAME_DIM ZM_TIME = ZCS_TIME

*RENAME_DIM ZM_VERS = ZCS_VERSION

//zm_profitctr isn't the same as zcs_item but I wasn't aware of any other solution that allows me to tell the rec statement which should be the target zcs_item

*RENAME_DIM ZM_PROFITCTR = ZCS_ITEM


*WHEN zm_channel
*IS channel_1
*WHEN zm_subchannel
*IS subchannel_1
*WHEN zm_prod
*IS product_1
*WHEN zm_keyfig
*IS keyfigure_1
*WHEN zm_cust
*IS customer_1
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
*IS customer_2
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN
*ENDWHEN

*COMMIT

This was just for testing the transfer between both models. If I execute the data manager package with zm_cust customer_1 data is transfered. But 3 records are generated. This is what I don't understand. Even if I take

*IS customer_1
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)

out of the statement an execute the script with zm_cust customer_1 one record is generated. So the script always jumps in the part that should be executed when zm_cust is customer_2...

If this is working in my next step I want to replace

*WHEN zm_cust
*IS customer_1
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
*IS customer_2
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)

by

*WHEN [zm_cust].Properties("int_ext")
*IS int
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
*IS ext
*rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)

so that the complete mapping wouldn't be that complex.

As I'm script logic beginner and the complete mapping will have more or less 1k combinations (and I don't know the impact on performance) any easier approch is much appreciated.

I have programing and database experience but without a debugger or some more

transparency (or script logic experience :)) this is really time consuming 😄

Cheers

Tobi

[former_member186338 · Accepted solution]

If you have multiple conditions mapping then in general you can't use script logic to perform data transfer. With script logic you can have only few conditions - mapped in single property.

For multiple conditions you have to write custom logic badi with a mapping table.

[tobi_gg · Accepted solution]

Okay then we will try that 🙂

Thx for your support

[former_member186338 · Accepted solution]

Due to the fact that your target profit center is an integer value you can use your model to store mapping data to be used in badi.

[former_member186338]

WHEN [zm_cust].Properties("int_ext") - incorrect syntax, look on my code!

For zcs_unit use *rename_dim for some unused dimension in source model for example zm_prod.

Then you can write zm_prod=zm_profitctr.cons_unit

Please read help on destination _app

[tobi_gg]

Thx for your reply!

I read a lot already and this syntax is what I found several times in community posts 🙂 Okay will try your code!

Could you please explain how I can use this:

For zcs_unit use *rename_dim for some unused dimension in source model for example zm_prod.
  Then you can write zm_prod=zm_profitctr.cons_unit

to map the value of zm_profitctr.cons_unit to target dimenion zcs_unit?

[former_member186338]

Once again, please read help on destination_app. Rename_dim option is explained.

[former_member186338]

Ok, let’s try to simplify things and use correct wording:

Dimension zm_cust has property int_ext with 2 possible values: int and ext. Based on this property you want to select target zm_profitctr The correct syntax will be:

*when zm_cust.int_ext

*is ext

*rec(...zm_profitctr=xxxx)

*is int

*rec(...)

*rec(...)

*endwhen

To get zcs_unit use:

zcs_unit=zm_profitctr.cons_unit

P.s I am writing from iPad and it’s very hard to post detailed code 🙂

[tobi_gg]

Hi Vadim,

no problem 🙂 your help is much appreciated!

*when zm_cust.int_ext
  *is ext
  *rec(...zm_profitctr=xxxx)
  *is int
  *rec(...)
  *rec(...)
*endwhen

This is what I meant by this part.

*WHEN [zm_cust].Properties("int_ext")
 *IS int
 *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
 *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
 *IS ext
 *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)

Good hint, where would I have to use this? In the rec statement I can't use it, gives me an error 'Invalid dimension name in keyword zcs_unit'.

Right now in this part:

*WHEN zm_channel 
*IS channel_1
   *WHEN zm_subchannel
   *IS subchannel_1
     *WHEN zm_prod
     *IS product_1
       *WHEN zm_keyfig 
       *IS keyfigure_1
         *WHEN zm_cust
         *IS customer_1
           *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 12345)
           *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
         *IS customer_2
           *rec(EXPRESSION=%VALUE%, ZM_PROFITCTR = 54321)
         *ENDWHEN
       *ENDWHEN
     *ENDWHEN
   *ENDWHEN
*ENDWHEN

the rec statement after *IS customer_2 is even executed when I select customer_1 in the data manager package prompt. That is what I don't get 😕

Is the logic script the only possible way for such a mapping? I'm a bit afraid of performance aspects because it will become a long script.. 😄

For testing I replaced this part

*ADD_DIM ZCS_UNIT = %UNIT

by this

*ADD_DIM ZCS_UNIT = unit_1

When I select 'unit_1, unit_2, unit_3, ...' in the data manager package prompt this:

*ADD_DIM ZCS_UNIT = %UNIT

seems not to work. Are lists/ multi member selections not allowed here?

B.R.

Tobi

[tobi_gg]

Dimension zm_cust has property int_ext with 2 possible values: int and 
ext. Based on this property you want to select target zm_profitctr [...]

correct, but also based on product, channel, subchannel and keyfigure.

So if product would be a computer, channel would be sales, subchannel would be store, keyfigure would be amount and customer ext, then this combination would be written on zcs_item = someprofitcenter1

If product would be a computer, channel would be sales, subchannel would be store, keyfigure would be revenue and customer ext, then this combination would be written on zcs_item = someprofitcenter2.

With all channels, subchannels, products and keyfigures we have over 1.5k possible combinations (not alle make sense, but we will end up somwhere arround 1k). So this would be a long list.

[former_member186338]

Ok, now it's clear. Read my final answer.

[former_member186338]

First - strange to see dimension names prefixed with “Z” - making thing less readable.

Second - please explain your required mapping using screenshot of some excel sample table to understand your requirements.

[former_member186338]

P.S. You can’t use transformation and conversion to move data between 2 BPC models!

[tobi_gg]

Hi Vadim,

thx for your reply. Hopefully this helps:

Example 1: Customer flagged as internal -> result needs to be written on two positions

Example 2: Customer flagges as external -> result needs to be written on one position

Most is just dummy data for explanation.

Each other combination of zm_channel, zm_subchannel, zm_prod and zm_keyfig (with dependancy on customer flag int_ext) would be written on another zcs_item.

Tobi

[tobi_gg]

I thought so 😞