Script logic - Destination app ?

[Former Member]

I need some help with Destination app script logic to copy data from MODEL_A to MODEL_B.

Model Dimensions

------

I need to copy a record set below from Model A to Model B using Destination App.

The record set in model A is below screenshot. Notice that

FNO_STATUS, D_VALUE, PROJECTTYPE are total node in Source model and can be skipped. ( I may be wrong on that)

RPTCURR, FNO_DATASRC, FNO_CATEGORY, ACCOUNT are constant values.

TIME should be a single value selection from the DOM package selection prompt.

Only a specific combination of REGION and FUNCTION from MODEL A should be transferred to a specific combination of BUSINESSUNIT and PID in Model B (Destination)

This is what I have so far..
( Wont let me copy paste so I had to type it here... sorry for any typos)

*XDIM_MEMBERSET FNO_STATUS = BAS(TOT_STATUS)

*XDIM_MEMBERSET D_VALUE = BAS(TOT_VALUE)

*XDIM_MEMBERSER PROJECTTYPE = BAS(TOTAL_PRJTYPE)

*XDIM_MEMBERSET RPTCUR = USD

*XDIM_MEMBERSET FNO_DATASRC = BAS(TOTAL_ADJ)

*XDIM_MEMBERSET FNO_CATEGORY = ACTUAL

*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_REGION)

*XDIM_MEMBERSET REGION = BAS(TOT_REGION)

*XDIM_MEMBERSET FNO_FUNCTION = BAS(LOG), BAS(MLP), REF

*XDIM_MEMBERSET FNO_ACCOUNT = BI_NON_CAPITAL

*XDIM_MEMBERSET TIME = 2016.JAN

*DESTINATION_APP = MODEL_B

*ADD_DIM DATASRC = D_IMT

*ADD_DIM CATEGORY= ACTUAL

*SKIP_DIM FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE

-----

[former_member186338 · Accepted solution]

First - use FireFox, working fine in this forum!

"Only a specific combination of REGION and FUNCTION from MODEL A should be transferred to a specific combination of BUSINESSUNIT and PID in Model B (Destination)" - but the logic is not explained and not clear from screenshot!

By the way - what dimension do you have in the source model: FNO_FUNCTION or FUNCTION? Please be accurate...

Vadim

P.S. "*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_REGION)"???

[Former Member · Accepted solution]

Hi Vadim,

Logic:

For example, Consider record 1 from source.

For all records with REGION = TOTAL_REGION (node) and FUNCTION = Logistics (node) should be copied to (aggregated) to PID =PID_loc1 and BusinessUnit = Logistics. With all other dimensions being constant or ignored in both Source and destination.

Similarly, another example

Consider record 3 from source.

REGION = Northern California and FUNCTION=Refinery should be copied to PID=PID_loc1 and BusinessUnit = Oakland.

Think of those signed data values as a result of an EPM report with dimensionality as shown.

There will NOT be any new records I just need to copy only those 8 records.

The dimension name is FUNCTION not FNO_FUNCTION. Sorry for the typos.

Thanks,

Bobby

[former_member186338 · Accepted solution]

You have to present the full matrix!

In general the only way to put this matrix is number of properties...

Looks like the number of properties will be equal to number of FUNCTION members: Refinery, Logistics, MLP... (or 2x to save target PID))The properties will be defined for REGION dimension.

Example:

REGION = Northern California

REGION.REFINERYBU=Oakland

REGION.REFINERYPID=PID_loc1

REGION.LOGISTICSBU=Logistics

REGION.REFINERYPID=PID_loc1

...

Vadim

[Former Member · Accepted solution]

I see where you are coming from. Unfortunately I cannot make any changes to the REGION or other source dimensions structure to add properties. Assuming that I can maintain those properties on REGION how would the DESTINATION_APP configuration would look like. I mean, the ADD_DIM, SKIP_DIM and the REC statement.

Also, without maintaining properties can we use multiple(8) REC /COMMIT statement to reset the ADD_DIM initialization.?

Notice that I have made BUSINESS_UNIT and PID dimesion as fixed value. Will a REC statement (need help there) after the below scoping work just to send the first record ?.

*XDIM_MEMBERSET FNO_STATUS = BAS(TOT_STATUS)

*XDIM_MEMBERSET D_VALUE = BAS(TOT_VALUE)

*XDIM_MEMBERSET PROJECTTYPE = BAS(TOTAL_PRJTYPE)

*XDIM_MEMBERSET RPTCUR = USD

*XDIM_MEMBERSET FNO_DATASRC = BAS(TOTAL_ADJ)

*XDIM_MEMBERSET FNO_CATEGORY = ACTUAL

*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_BENEFITTYPE)

*XDIM_MEMBERSET REGION = BAS(TOT_REGION)

*XDIM_MEMBERSET FNO_FUNCTION = BAS(LOG)

*XDIM_MEMBERSET FNO_ACCOUNT =  BI_NON_CAPITAL

*XDIM_MEMBERSET TIME = 2016.JAN

*DESTINATION_APP = MODEL-B

*ADD_DIM DATASRC = D_IMT

*ADD_DIM CATEGORY = ACTUAL

*ADD_DIM BUSINESS_UNIT = Logistics

*ADD_DIM PID = PID_Loc1

*SKIP_DIM = FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE,

[former_member186338 · Accepted solution]

"I see where you are coming from. Unfortunately I cannot make any changes to the REGION or other source dimensions structure to add properties." - you have to...  there is no other reasonable way then properties to define destination in script logic. Other options - very long script with each combination hardcoded or custom logic badi...

Sample with properties:

*DESTINATION_APP = MODEL-B

*SKIP_DIM = FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE,FNO_DATASRC,FNO_ACCOUNT

*ADD_DIM DATASRC = D_IMT

...

*RENAME_DIM FUNCTION=PID, REGION = BUSINESSUNIT,FNO_CATEGORY=CATECORY

*WHEN FUNCTION

*IS Logistics

*REC(EXPRESSION=%VALUE%, REGION=REGION.LOGISTICSBU, FUNCTION=REGION.LOGISTICSPID

*IS Refinery

*REC(EXPRESSION=%VALUE%, REGION=REGION.REFINERYBU, FUNCTION=REGION.REFINERYPID

...

ENDWHEN

Please check dimension names, there are some errors in your code...

Vadim

[former_member186338 · Accepted solution]

P.S. How many FUNCTION members do you have?

[former_member186338 · Accepted solution]

Some correction to the script:

*WHEN FUNCTION

*IS Logistics

*WHEN REGION.LOGISTICSBU

*IS <>

*REC(EXPRESSION=%VALUE%, REGION=REGION.LOGISTICSBU, FUNCTION=REGION.LOGISTICSPID)

*ENDWHEN

*IS Refinery

*WHEN REGION.REFINERYBU //to check for empty property...

*IS <>

*REC(EXPRESSION=%VALUE%, REGION=REGION.REFINERYBU, FUNCTION=REGION.REFINERYPID)

*ENDWHEN

...

ENDWHEN

[Former Member · Accepted solution]

there are 40-45 FUNCTION dimension members.

Question about RENAME_DIM . FUNCTION and PID do not have same members so does REGION and BUSINESSUNIT. How can we use rename DIM?

[Former Member · Accepted solution]

IN REC statement *REC(EXPRESSION=%VALUE%, REGION=REGION.LOGISTICSBU, FUNCTION=REGION.LOGISTICSPID)

REGION and FUNCTION are not available in Destination Model. Will it still work ?.

I will give it a try.

[former_member186338 · Accepted solution]

"FUNCTION and PID do not have same members so does REGION and BUSINESSUNIT. How can we use rename DIM?" rename dim doesn't require to have the same members, any unused dimension name can be used!

[Former Member · Accepted solution]

Thank you. Just got confirmation that i cannot add new properties. Looks like hard coding the values in the script is the only option for me . However since we have only 8 records to transfer I am guessing it should be ok.

So if  need to transfer one record below from source to destination (below) can you help me with the script please hard coding REGION and FUNCTION on source side to BUSINESSUNIT and PID on Destination.

FNO_STATUS	D_VALUE	PROJECTTYPE	RPTCUR	FNO_DATASRC	FNO_CATEGORY	BENEFITTYPE	REGION	FUNCTIONS	ACCOUNT	TIME
TOTAL_STATUS	TOT_VALUE	PROJECTMETH	USD	TOTAL_ADJ	Actual	Total Benefit Type	Total Region	Logistics	BI_NON_CAPITAL	2016.JAN	200

destination

RPTCURRENCY	DATASRC	PID	BusinessUnit	CATEGORY	TIME
USD	INPUT	PID_Loc1	Logistics	ACTUAL	2016.JAN	200

*XDIM_MEMBERSET FNO_STATUS = BAS(TOT_STATUS)

*XDIM_MEMBERSET D_VALUE = BAS(TOT_VALUE)

*XDIM_MEMBERSET PROJECTTYPE = BAS(TOTAL_PRJTYPE)

*XDIM_MEMBERSET RPTCUR = USD

*XDIM_MEMBERSET FNO_DATASRC = BAS(TOTAL_ADJ)

*XDIM_MEMBERSET FNO_CATEGORY = ACTUAL

*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_BENEFITTYPE)

*XDIM_MEMBERSET REGION = BAS(TOT_REGION)

*XDIM_MEMBERSET FNO_FUNCTION = BAS(LOG)

*XDIM_MEMBERSET FNO_ACCOUNT =  BI_NON_CAPITAL

*XDIM_MEMBERSET TIME = 2016.JAN

*DESTINATION_APP = MODEL-B

*ADD_DIM DATASRC = D_IMT

*ADD_DIM CATEGORY = ACTUAL

*ADD_DIM BUSINESS_UNIT = Logistics

*ADD_DIM PID = PID_Loc1

*SKIP_DIM = FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE,FNO_DATASRC,FNO_ACCOUNT

*ADD_DIM DATASRC = D_IMT

...

*RENAME_DIM FUNCTION=PID, REGION = BUSINESSUNIT,FNO_CATEGORY=CATECORY

*WHEN FUNCTION

*IS Logistics

*WHEN REGION.

[former_member186338 · Accepted solution]

Worked for me for years

REGION and FUNCTION are renamed to BUSINESSUNIT and PID

Please try my corrected script - without test for empty property the script will fail!

[former_member186338 · Accepted solution]

"Just got confirmation that i cannot add new properties" - absolutely strange requirement!

Anyway, the script without properties will be:

XDIM_MEMBERSET FNO_STATUS = BAS(TOT_STATUS)

*XDIM_MEMBERSET D_VALUE = BAS(TOT_VALUE)

*XDIM_MEMBERSET PROJECTTYPE = BAS(TOTAL_PRJTYPE)

*XDIM_MEMBERSET RPTCUR = USD

*XDIM_MEMBERSET FNO_DATASRC = BAS(TOTAL_ADJ)

*XDIM_MEMBERSET FNO_CATEGORY = ACTUAL

*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_BENEFITTYPE)

*XDIM_MEMBERSET REGION = BAS(TOT_REGION)

*XDIM_MEMBERSET FUNCTION = ... //all required bas members

*XDIM_MEMBERSET FNO_ACCOUNT =  BI_NON_CAPITAL

*XDIM_MEMBERSET TIME = 2016.JAN

*DESTINATION_APP = MODEL-B

*ADD_DIM DATASRC = D_IMT

*SKIP_DIM = FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE,FNO_DATASRC,FNO_ACCOUNT

*RENAME_DIM FUNCTION=PID, REGION = BUSINESSUNIT,FNO_CATEGORY=CATECORY

*WHEN FUNCTION

*IS Logistics

*WHEN REGION

*IS ... //SomeRegion or * for all

*REC(EXPRESSION=%VALUE%, REGION=SomeBusinessUnit1, FUNCTION=SomePID1

*IS ... //SomeRegion or * for all

*REC(EXPRESSION=%VALUE%, REGION=SomeBusinessUnit2, FUNCTION=SomePID2

...

*ENDWHEN

*IS Refinery

*WHEN REGION

*IS ... //SomeRegion or * for all

*REC(EXPRESSION=%VALUE%, REGION=SomeBusinessUnit1, FUNCTION=SomePID1

*IS ... //SomeRegion or * for all

*REC(EXPRESSION=%VALUE%, REGION=SomeBusinessUnit2, FUNCTION=SomePID2

...

*ENDWHEN

...

*ENDWHEN

Vadim

P.S. Please be accurate with dimension names!

[Former Member · Accepted solution]

Hi Vadim, I am not sure if we are corresponding to our latest messages. Here's my latest response.

Thank you. Just got confirmation that i cannot add new properties. Looks like hard coding the values in the script is the only option for me . However since we have only 8 records to transfer I am guessing it should be ok.

So if  need to transfer one record below from source to destination (below) can you help me with the script please hard coding REGION and FUNCTION on source side to BUSINESSUNIT and PID on Destination.

FNO_STATUS	D_VALUE	PROJECTTYPE	RPTCUR	FNO_DATASRC	FNO_CATEGORY	BENEFITTYPE	REGION	FUNCTIONS	ACCOUNT	TIME
TOTAL_STATUS	TOT_VALUE	PROJECTMETH	USD	TOTAL_ADJ	Actual	Total Benefit Type	Total Region	Logistics	BI_NON_CAPITAL	2016.JAN	200

destination

RPTCURRENCY	DATASRC	PID	BusinessUnit	CATEGORY	TIME
USD	INPUT	PID_Loc1	Logistics	ACTUAL	2016.JAN	200

*XDIM_MEMBERSET FNO_STATUS = BAS(TOT_STATUS)

*XDIM_MEMBERSET D_VALUE = BAS(TOT_VALUE)

*XDIM_MEMBERSET PROJECTTYPE = BAS(TOTAL_PRJTYPE)

*XDIM_MEMBERSET RPTCUR = USD

*XDIM_MEMBERSET FNO_DATASRC = BAS(TOTAL_ADJ)

*XDIM_MEMBERSET FNO_CATEGORY = ACTUAL

*XDIM_MEMBERSET BENEFITTYPE = BAS(TOT_BENEFITTYPE)

*XDIM_MEMBERSET REGION = BAS(TOT_REGION)

*XDIM_MEMBERSET FNO_FUNCTION = BAS(LOG)

*XDIM_MEMBERSET FNO_ACCOUNT =  BI_NON_CAPITAL

*XDIM_MEMBERSET TIME = 2016.JAN

*DESTINATION_APP = MODEL-B

*ADD_DIM DATASRC = D_IMT

*ADD_DIM CATEGORY = ACTUAL

*ADD_DIM BUSINESS_UNIT = Logistics

*ADD_DIM PID = PID_Loc1

*SKIP_DIM = FNO_STATUS, D_VALUE, PROJECTTYPE, BENEFITTYPE,FNO_DATASRC,FNO_ACCOUNT

*ADD_DIM DATASRC = D_IMT

...

*RENAME_DIM FUNCTION=PID, REGION = BUSINESSUNIT,FNO_CATEGORY=CATECORY

*WHEN FUNCTION

*IS Logistics

*WHEN REGION.

[former_member186338 · Accepted solution]

Please look on my last reply:

Your script contains a lot of errors:

Can't use:

*ADD_DIM CATEGORY = ACTUAL

If you have

*RENAME_DIM FUNCTION=PID, REGION = BUSINESSUNIT,FNO_CATEGORY=CATECORY

Don't use:

*ADD_DIM BUSINESS_UNIT = Logistics

*ADD_DIM PID = PID_Loc1

will be assigned in REC!

Duplicated line:

*ADD_DIM DATASRC = D_IMT

Etc...

Vadim