Linking models problems, cause blending problems

[Former Member]

Hello everyone,

I'm having some problems with data in SAP Analytics Cloud.

I generated 2 queries and 2 models.

/*Budget values*/

SELECT t0.U_Anio AS Anio, t0.U_Anio AS AnioD, LEFT(t1.u_mes,2) AS MesN, t1.u_mes AS MesNn, 
FORMAT(T1.[U_Meta],'N','de-de') AS Meta_Venta,
FORMAT(T1.[U_Metap],'N','de-de')  AS Venta_A 
FROM [dbo].[@TABLEM] T0
INNER JOIN [dbo].[@TABLED]T1 ON T0.DocEntry=T1.DocEntry
Group by t1.U_Mes, t1.U_Meta, t0.U_Anio, T1.U_Meta, T1.U_metap

With this result

Anio	AnioD	MesN	MesNn	        Meta_Venta	Venta_A
2017	2017	01	01-Enero	262.500,00	570.553,71
2017	2017	02	02-Febrero	262.500,00	235.241,70
2017	2017	03	03-Marzo	262.500,00	553.830,22
2017	2017	04	04-Abril	262.500,00	603.150,65
2017	2017	05	05-Mayo	        262.500,00	606.786,19
2017	2017	06	06-Junio	262.500,00	226.931,13
2017	2017	07	07-Julio	487.500,00	263.885,27
2017	2017	08	08-Agosto	487.500,00	512.991,46
2017	2017	09	09-Septiembre	487.500,00	267.188,01
2017	2017	10	10-Octubre	487.500,00	516.395,52
2017	2017	11	11-Noviembre	487.500,00	425.442,75
2017	2017	12	12-Diciembre	487.500,00	537.813,38

And other query:

/*Real Values*/

SELECT T1.Anio As Anio, T1.Anio As AnioD,
CASE When T1.Mes='1' Then '01' When T1.Mes='2' Then '02' When T1.Mes='3' Then '03' When T1.Mes='4' Then '04' When T1.Mes='5' Then '05' When T1.Mes='6' Then '06' When T1.Mes='7' Then '07' When T1.Mes='8' Then '08' When T1.Mes='9' Then '09' When T1.Mes='10' Then '10' When T1.Mes='11' Then '11' When T1.Mes='12' Then '12' END MesN,
CASE When T1.Mes='01' Then 'Enero' When T1.Mes='02' Then 'Febrero' When T1.Mes='03' Then 'Marzo' When T1.Mes='04' Then 'Abril' When T1.Mes='05' Then 'Mayo' When T1.Mes='06' Then 'Junio' When T1.Mes='07' Then 'Julio' When T1.Mes='08' Then 'Agosto' When T1.Mes='09' Then 'Septiembre' When T1.Mes='10' Then 'Octubre' When T1.Mes='11' Then 'Noviembre' When T1.Mes='12' Then 'Diciembre' END Mesn,
(CASE When T1.Mes='1' Then '01' When T1.Mes='2' Then '02' When T1.Mes='3' Then '03' When T1.Mes='4' Then '04' When T1.Mes='5' Then '05' When T1.Mes='6' Then '06' When T1.Mes='7' Then '07' When T1.Mes='8' Then '08' When T1.Mes='9' Then '09' When T1.Mes='10' Then '10' When T1.Mes='11' Then '11' When T1.Mes='12' Then '12' END + '-' + CASE When T1.Mes='01' Then 'Enero' When T1.Mes='02' Then 'Febrero' When T1.Mes='03' Then 'Marzo' When T1.Mes='04' Then 'Abril' When T1.Mes='05' Then 'Mayo' When T1.Mes='06' Then 'Junio' When T1.Mes='07' Then 'Julio' When T1.Mes='08' Then 'Agosto' When T1.Mes='09' Then 'Septiembre' When T1.Mes='10' Then 'Octubre' When T1.Mes='11' Then 'Noviembre' When T1.Mes='12' Then 'Diciembre' END) AS MesNn,
FORMAT(SUM(T1.Total_VentaME),'N','de-de') AS Real_Venta
FROM Sales T1
WHERE T1.Mes IN ('01','02','03','04','05','06','07','08','09','10','11','12')
GROUP BY T1.Mes, T1.Anio

With this results (I will show a preview in images).

Everything works in the modeller. The trouble is when i want link this models in a chart. It always causes a problem with the data dupplicating it. And says a message:

"Blending caused some values to be aggregated for these measures: Meta_Venta Blending caused some values to be duplicated for these measures:Meta_Venta

I'm going to share the print.

error.png

model.png

What can you suggest me? Any help and any idea will be appreciated.

Thanks.

[Former Member · Accepted solution]

Hello everybody.

The problem was solved. The issue was that the both models must contain data for the blended years else the warning appears.

Thank you to everyone.

Regards.

[julian_jimenez]

Hi Juan,

I don't have access to your data and story to completely understand the calculations. Because you are linking by dates that one seems to be unique (month-year) with another having multiple entries: year, the linking would be inconsistent.

If the month-year combination is unique. Can you link only by this dimension? It works as expected?

Cheers,

Julian

[Former Member]

Thank you very much for your answer. What do you mean about an unique combination? If i use multiple entries, and filter the data by example, only a month, shows me the value 4500000 to every individual month. I setted month-year in the model link and the error is the same.

Previously, this works fine, but I don't know what happpen.

Thanks Julian.

[julian_jimenez]

Hi Juan,

What I meant about the unique combination is that, for example if you have a hierarchy time and you join by year two models, when you drill down, it will aggregate any item that shares that year.

If this was working before but not now the most likely effect is a filter applied somewhere. At least that is my experience.

In your case you have two linked dimensions: AnioD and MesNn.

If you create a chart or table, a measure will aggregate multiple records as you have multiple entries for AnioD "2016" but only one entry for MesNn 10-Octubre. If you apply a filter by MesNn, you will have unique entries. Depending on the filter used, you may have undesirable effects as both are dates-type entries.

This won't happen if we were linking on the lowest level available only (MesNn) as long as both models share that value.

Regards,

Julian

[Former Member]

Hi Julian,

Thank you very much for your explanation. I readed carefully, understanding your explanation. Finally, I followed your example, linking only the lowest level (Month). The problem persist.

Regards.

[julian_jimenez]

Hi Juan,

Open an incident with Product Support so we can examine exactly the same data in our systems to replicate the behaviour.

Thanks,

Julian

[Former Member]

Thanks Julian. I'm waiting for SAP.

Regards.

[Former Member]

Hi Juan,

Try including 'Category' into your linked dimensions.

Kind regards,

Clemens

[TammyPowlas]

Hello Juan - please see this SAP Note - https://launchpad.support.sap.com/#/notes/2593792 - is it similar to your situation? "This issue is currently under investigation by Development."

[Former Member]

Hi Tammy, thanks for the answer. Looks very similar because I linked two dimensions from both models and added some measures by every model (Like the model pic). I switched the models order to do the test and the problem persist. I didn't refresh the data since Jan 20th, that could be why I had not seen it. In how much time this will be solved?

I really appreciate your answer Tammy.

Thanks.

[TammyPowlas]

Hi Juan - I don't work for SAP so hopefully someone from the team will respond.

[Former Member]

Hello Tammy.

Thanks for the help. I would be alert for the launchpad update.

Regards.