Skip to Content
2

Linking models problems, cause blending problems

Feb 01 at 07:14 PM

229

avatar image

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.

error.png (52.0 kB)
model.png (25.7 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Juan Elías Rivero Domínguez Mar 16 at 01:04 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Julian Jimenez
Feb 05 at 10:54 PM
2

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

Show 5 Share
10 |10000 characters needed characters left characters exceeded

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.

0

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

0

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.

0

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

1

Thanks Julian. I'm waiting for SAP.

Regards.

0
Tammy Powlas
Feb 01 at 07:48 PM
1

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."

Show 3 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Juan Elías Rivero Domínguez

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

0

Hello Tammy.

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

Regards.

0
K. Clemens Mar 05 at 12:51 PM
1

Hi Juan,

Try including 'Category' into your linked dimensions.

Kind regards,

Clemens

Share
10 |10000 characters needed characters left characters exceeded