Aggregations not working with Date Table when using Azure SQL Data Warehouse

Hi,

I am trying to build an aggregation table which can work along side with Direct Query using Azure SQL Data Warehouse as source.

The same logic worked well when I used source as Sparks.

However it is not working same with source as Azure SQL Data Warehouse.

I already implemented aggregations many times so it is not new for me however it is new as Azure SQL Data Warehouse as source.

Below is the Query showing in performance analyzer and the performance is very slow.

I really don't understand why the second query is sending extra //SQL Query which is degrading the performance.

Appreciate your kind support. Thanks.

1 - Query sending from Table source as Sparks:

// DAX Query
EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('DateTimeTable'[Year], 'DateTimeTable'[MonthNameShort], 'DateTimeTable'[Day]), "IsGrandTotalRowTotal"
),
"CountId", CALCULATE(COUNTA('Table1'[Id]))
),
[IsGrandTotalRowTotal],
0,
[CountId],
0,
'DateTimeTable'[Year],
1,
'DateTimeTable'[MonthNameShort],
1,
'DateTimeTable'[Day],
1
)

ORDER BY
[IsGrandTotalRowTotal] DESC,
[CountId] DESC,
'DateTimeTable'[Year],
'DateTimeTable'[MonthNameShort],
'DateTimeTable'[Day]

2 - Query sending from Table source as Azure SQL Data Warehouse:

// DAX Query
EVALUATE
TOPN(
502,
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP('DateTimeTable'[Year], 'DateTimeTable'[MonthNameShort], 'DateTimeTable'[Day]), "IsGrandTotalRowTotal"
),
"CountId", CALCULATE(COUNTA('Table1'[Id]))
),
[IsGrandTotalRowTotal],
0,
'DateTimeTable'[Year],
1,
'DateTimeTable'[MonthNameShort],
1,
'DateTimeTable'[Day],
1
)

ORDER BY
[IsGrandTotalRowTotal] DESC,
'DateTimeTable'[Year],
'DateTimeTable'[MonthNameShort],
'DateTimeTable'[Day]

// SQL Query

SELECT
TOP (1000001) [t18].[DateTime],
COUNT_BIG([t18].[Id])
AS [a0]
FROM
(
(select [$Table].[Id] as [Id],
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
[$Table].columnname as columname
from [dbo].[Table1] as [$Table])
)
AS [t18]
GROUP BY [t18].[DateTime]