BPC Conversion File - Same External ID for different Dimensions

[former_member313059]

Hello Experts,

I hope you can help me.

We are running BPC NW V10.0 SP13

I have a Conversion File for External to Internal Mapping on the DATASRC Dimension.

See Image 01

I would now like to use the same 'External ID' to identify the mapping rules for two Dimensions, DATASRC and CATEGORY Member ID

See Image 02

The rules that determine how the External ID is mapped are subjective rather than derived from patterns present in ID.  This means I cannot use any conditional logic to determine the mapping rules. For example, I cannot assume that all IDs that start with 99Z19 will need to be mapped the same way.

So the CATGORY Member ID needs to be hard-coded somehow.

Although it is simple enough to copy the entire worksheet and hold the mapping for each Dimension as separate w/sheets, this duplicates the amount of ongoing maintenance and turns 80 rules into 160 (a number which will rise!)

Is there a way of using javascript in the Formula column to identify the Internal Member ID for the CATEGORY Dimension.

If so, how would the Transformation File reference this?

Any help would be much appreciated

[former_member313059]

Thank You for all your suggestions.

The same External Member ID drives different rules for two different Dimensions.

And because the External Member ID is a complicated string of text, maintaining it in two separate worksheets duplicates the amount of ongoing maintenance, introduces risk and turns 80 rules into 160 (a number that will definitely rise)... but if it can't be done this way, then its simple enough to have separate w/sheets.

Thanks Again

[former_member313059]

Hi Andy

Unfortunately changing the Format from Text to General did not solve the problem.

Any other ideas?

[Former Member]

if that doesn't work then you just have to maintain 2 tabs.

They have different mapping anyway, why would it be better to have in one place?

[former_member313059]

Hi Vadim,

I am sorry, as I mentioned in my original post, the rules that determine how the External ID is mapped are subjective rather than derived from patterns present in the External ID.

For example, I cannot assume all IDs that start 99Z19 will be mapped to the same DATASRC and CATEGORY...

The DATASRC and CATEGORY that an External ID gets mapped to is based on a personal view rather than codified logic....

Is this the logic you were referring to?

[former_member186338]

If there is NO logic then you have to maintain individual conversion sheets with detailed mapping!

If there is some (I see you are trying to use Excel VLOOKUP) - then it's possible to use java script.

Vadim

[former_member313059]

Hi Andy

I now understand your explanation, I have created a 'Master' mapping sheet and updated the Trans/Con files as suggested.

As you can see from the screenshot, if I drag down the VLOOKUP formula it does not drag the 'LOOKUP VALUE' down, it references the first cell everytime.  I've tried to use standard Excel formulas in Conversion Files before and they never behave the way you would expect.  Do I need to change a setting or write the look up differently?

If I can get round this issue, then this is a great solution to the problem I am trying to solve.

Thanks

[former_member186338]

Hi Jo,

Asking second time - can you explain the logic??? Javascript is better then Excel formulas!

Vadim

[Former Member]

Hi Jo,

Try change the format of those cells from TEXT to general, see if that fix the issue.

Andy

[damovand]

Hi,

I have seen complex conditional mappings in transformation files but not in conversion files.  In conversion files the best way is to use javascript expressions.

Regards,

Leila

[former_member313059]

Sorry Vadim, I was having problems uploading the actual files.

So I have had to screenshot instead...............

Transformation File

Conversion File

As you can see, the Trans file assumes the CATEGORY is fixed to one Member.

But I want to use the External ID in the the DATASRC Conversion w/sheet and specify different CATEGORY Member IDs.

[former_member186338]

Hi Jo,

Please, use javascript for conversion!

Also, please explain the required logic for DATASRC and CATEGORY.

Vadim

[Former Member]

Hi Jo,

I've already given you the solution, in your transformation file change the line

CATEGORY=*COL(13)

add line in the conversion section:

CATEGORY=MKT_C001.XLS!CATEGORY

in the conversion file add new sheet called CATEGORY

do your mapping for the category there.

If you want to only maintain in one place then all you need to do is create a master mapping sheet with datasrc and category mapping, in your category sheet and datasrc sheet you just do a lookup to the master sheet in the 2nd column, and in the first column you do a = to the master sheet so you don't have to retype all the members in the first column.

Andy

[former_member313059]

Hi Andy

This is the type of solution I am looking for, but I am not sure I understand your explanation fully.

Having two w/sheets but how do they both contain the External ID but maintained only in one?

Could you provide an example to help me understand this better?

Thanks

[former_member186338]

Can you describe in details what do you have in original data? And provide the text of the transformation file.

Vadim

[Former Member]

Hi Jo,

Create 2 work sheet in your conversion file, one for category and one for datasrc.

Andy

[former_member313059]

Hi Andy,

Thank You for your swift response.  Whilst I realise that two separate w/sheets is a simple way to achieve this, I am trying to find a solution that avoids holding/maintaining the same External ID in two places.

Vadim, requested info to follow....

[Former Member]

Hi Jo,

If you want to maintain in one place then you can create a master mapping sheet with both category and datasrc column in, then in your conversion sheet you use lookup formula to get the correct column.

Andy

[former_member186338]

Waiting...

Vadim

[Former Member]

Hi Jo,

You need to have a separate conversion sheet/file for each dimension, what you can do for category is that use the mapping for datesrc in your category line as well, just change the internal mapping column with category members.

CATEGORY=<datasrc_infoobject>

DATESRC=<datasrc_infoobject>

something like that.

Andy

[former_member186338]

Hi Jo,

"Is there a way of using javascript in the Formula column to identify the Internal Member ID for the CATEGORY Dimension." - in Formula column???? Not here!

You have to create separate conversion files for each dimension required and you can use javascript...

But can you explain the logic to be implemented in javascript??

Vadim