With rollup and linked server: why doesn't it calculate the amount correctly?

Question

SQL Server

Good afternoon!
There is a table

CREATE TABLE [dbo].[TestRollup]
(
  [Dim1] [nvarchar](255) NULL,
  [Dim2] [nvarchar](255) NULL,
  [Dim3] [nvarchar](255) NULL,
  [Metric] [float] NULL
)

INSERT INTO [dbo].[TestRollup]
  ([Dim1],[Dim2],[Dim3],[Metric])
VALUES
  ('TestDim1','Dim2Value1','Dim3Value1',1),
  ('TestDim1','Dim2Value1','Dim3Value2',3),
  ('TestDim1','Dim2Value2','Dim3Value1',7),
  ('TestDim1','Dim2Value2','Dim3Value3',5),
  ('TestDim1','Dim2Value3','Dim3Value2',NULL),
  ('TestDim1','Dim2Value3','Dim3Value2',NULL),
  ('TestDim1','Dim2Value3','Dim3Value5',2),
  ('TestDim1','Dim2Value3','Dim3Value5',1)

request

SELECT
  [Dim1]
  ,[Dim2]
  ,[Dim3]
  ,SUM([Metric]) Sum_Metric
  ,GROUPING(Dim1) Grouping_Dim1
  ,GROUPING(Dim2) Grouping_Dim2
  ,GROUPING(Dim3) Grouping_Dim3
FROM [dbo].[TestRollup]
GROUP BY [Dim1],[Dim2],[Dim3]
WITH ROLLUP

is executed correctly, the Sum_Metric column is considered correct.
But the next request

SELECT
  [Dim1]
  ,[Dim2]
  ,[Dim3]
  ,SUM([Metric]) Sum_Metric
  ,GROUPING(Dim1) Grouping_Dim1
  ,GROUPING(Dim2) Grouping_Dim2
  ,GROUPING(Dim3) Grouping_Dim3
FROM <linked_server>.<name_db>.[dbo].[TestRollup]
GROUP BY [Dim1],[Dim2],[Dim3]
WITH ROLLUP

returns NULL for some subtotals.
Those. if the data is taken from a linked server and there are NULL values ​​for the Metric field, then the sum for the subtotal will be NULL. What could be the problem? maybe it's some link settings?

Answer 1

SUM() ignores NULL during aggregation and returns NULL if no match was found for the given criteria. If you have in the table exactly the data that you brought in INSERT, then for the combination 'TestDim1','Dim2Value3','Dim3Value2' SUM(metric) should be NULL.