Solved: Replacing members of one dimension using a second ...

andreag412 · ‎2024 Mar 25

Dear Community,
I have a question to ask, is it possible to replace via Data Action one or more members of a dimension using a second model with mapping new member and old member?

Specifically, there would be a first model formed in this way:

Data	Product	Customer	Entity	Account	Sell-in	Price
202401	DUMMY	CLI1	SOC1	QTY	10
202401	DUMMY	CLI1	SOC1	PRICE		20
202401	DUMMY	CLI2	SOC2	QTY	10
202401	DUMMY	CLI2	SOC2	PRICE		20
202401	DUMMY	CLI3	SOC1	QTY	10
202401	DUMMY	CLI3	SOC1	PRICE		20
202401	ART1	CLI1	SOC1	QTY	5
202401	ART1	CLI1	SOC1	PRICE		10
202401	ART1	CLI2	SOC2	QTY	5
202401	ART1	CLI2	SOC2	PRICE		10
202401	ART1	CLI3	SOC1	QTY	5
202401	ART1	CLI3	SOC1	PRICE		10
202402	DUMMY	CLI1	SOC1	QTY	10
202402	DUMMY	CLI1	SOC1	PRICE		10
202402	DUMMY	CLI2	SOC2	QTY	10
202402	DUMMY	CLI2	SOC2	PRICE		10
202402	DUMMY	CLI3	SOC1	QTY	10
202402	DUMMY	CLI3	SOC1	PRICE		10

The second model would be formed with a mapping for the product dimension:

Data	ProductOld	ProductNew	KPITech
202401	DUMMY	ART2	0
202402	DUMMY	ART2	0

The desired would be to replace the 'Dummy' product of the first model according to the logic of the mapping proposed in the second model.
The result would then be this:

Data	Product	Customer	Entity	Account	Sell-in	Price
202401	ART2	CLI1	SOC1	QTY	10
202401	ART2	CLI1	SOC1	PRICE		20
202401	ART2	CLI2	SOC2	QTY	10
202401	ART2	CLI2	SOC2	PRICE		20
202401	ART2	CLI3	SOC1	QTY	10
202401	ART2	CLI3	SOC1	PRICE		20
202401	ART1	CLI1	SOC1	QTY	5
202401	ART1	CLI1	SOC1	PRICE		10
202401	ART1	CLI2	SOC2	QTY	5
202401	ART1	CLI2	SOC2	PRICE		10
202401	ART1	CLI3	SOC1	QTY	5
202401	ART1	CLI3	SOC1	PRICE		10
202402	ART2	CLI1	SOC1	QTY	10
202402	ART2	CLI1	SOC1	PRICE		10
202402	ART2	CLI2	SOC2	QTY	10
202402	ART2	CLI2	SOC2	PRICE		10
202402	ART2	CLI3	SOC1	QTY	10
202402	ART2	CLI3	SOC1	PRICE		10

I tried a couple of solutions using the MODEL and LINK function, but without success.
I thank anyone who can help

hartmut_koerner · ‎2024 Apr 08

I see no other way than including the ProductNew dimension also in your first model. Then you can copy over the data from the second model. This is necessary because the LINK function has some limitations. In the mapping table, enter a 1 instead of 0. Then you can multiply with the mapping data and redirect from ProductNew to Product. Also take care that the old data are deleted in case a mapping exists and also consider the case that there exist already some data for the new product:

MODEL [Mod2]
ENDMODEL

VARIABLEMEMBER #Tech1 OF [d/Measures]
VARIABLEMEMBER #Tech2 OF [d/Measures]
VARIABLEMEMBER #Quantity OF [d/Measures]

// Copy over mapping from second model
DATA([d/Measures] = #Tech1, [d/Product] = [Mod2].[d/Product], [d/ProductNew] = [Mod2].[d/ProductNew]) = LINK([Mod2], [d/Version] = "public.Actual", [d/Measures] = "Tech")
// Aggregate to get a list of mapped members
DATA([d/Measures] = #Tech2, [d/ProductNew] = "#") = RESULTLOOKUP([d/Measures] = #Tech1)
// Do mapping by multiplying with 1 a redirect to ProductNew
DATA([d/Measures] = #Quantity, [d/Product] = [d/ProductNew].[p/ID], [d/ProductNew] = "#")
= RESULTLOOKUP([d/Measures] = "Quantity", [d/ProductNew] = "#") * RESULTLOOKUP([d/Measures] = #Tech1)
// Delete in case mapping exists
IF RESULTLOOKUP([d/Measures] = #Tech2) != 0 THEN
	DELETE([d/Measures] = "Quantity")
ENDIF
// Add in case there are already data on the mapped product
DATA([d/Measures] = "Quantity") = RESULTLOOKUP([d/Measures] = "Quantity") + RESULTLOOKUP([d/Measures] = #Quantity)

Before Mapping