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

[SyedAli]

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]

[v-xicai]

Hi @SyedAli ,

The aggregated table can be created in the data source with T-SQL queries, or in Power Query, or anywhere else that you can create a grouped table.

You can learn more :

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table,

https://radacad.com/power-bi-aggregations-step-3-configure-aggregation-functions-and-test-aggregatio...,

https://docs.microsoft.com/en-us/power-bi/desktop-aggregations

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SyedAli]

Hi @v-xicai ,

Thanks for your response, I am able to create the aggregations without any issues.

What I am experirencing is when I used DATE Table to get the count it is converting treating it as DirectQuery instead of pointing to aggregated table.

Thanks