Direct Query optimization with date table

[IMett]

Hi everyone,

I have a simplified model with a calender table [BLAllgemein Datum] and a Fact Table [BL_Vertrag FAKT ...] looking like this.

The fact table has
- 1 summable column "AnzahlLaufenderVertrag" and 
- 2 date columns [StatistischGueltigAbDatum] and [StatistischGueltigBisDatum] which indicate, in which date range the particular row is valid.

The calender table includes the dates and some transformations on them, nothing special.

All I want to do is to calculate and display this measure __AnzVerträgeBestand, which will calculate the sum of  [AnzahlLaufenderVertrag] for each date in the calender table, under the condition that the date range in the fact table includes this date.

__AnzVerträgeBestand =
VAR _Date =
    MAX ( 'BLAllgemein Datum'[Datum] )
VAR _result =
    CALCULATE (
        SUM ( 'BL_VERTRAG FAKT_BESTAND Table'[AnzahlLaufenderVertrag] ),
        'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigabDatum] <= _Date,
        'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigbisDatum] > _Date,
        REMOVEFILTERS ( 'BLAllgemein Datum' )
    )
RETURN
    _result

Both tables are located in an MS SQL database and I try to query them in direct query mode.
Unfortunately, the query is very slow taking about 14 seconds on my small test fact table with about 100.000 rows. In production, there are about 20 million rows, and the query runs several minutes.

I've done some research and noticed, that Power BI generates an extremely long an inefficient SQL query that looks like this:

// Direct Query

SELECT 
TOP (1000001) [semijoin1].[c1],[basetable0].[c43],SUM(
CAST([a0] as BIGINT)
)
AS [a0]
FROM 
(
(

SELECT [t3].[StatistischGueltigabDatum] AS [c43],[t3].[StatistischGueltigbisDatum] AS [c44],[t3].[AnzahlLaufenderVertrag] AS [a0]
FROM 
(
(
select [AnzahlLaufenderVertrag],
[StatistischGueltigabDatum],
[StatistischGueltigbisDatum]
from [BL_VERTRAG].[FAKT_BESTAND] as [$Table]
)
)
AS [t3]
WHERE 
(
([t3].[StatistischGueltigabDatum] IN (CAST( '20150205 00:00:00' AS datetime),CAST( '20140428 00:00:00' AS datetime),CAST( '20131111 00:00:00' AS datetime),CAST( '20140331 00:00:00' AS datetime),CAST( '20201001 00:00:00' AS datetime),CAST( '20080728 00:00:00' AS datetime),CAST( '20060401 00:00:00' AS datetime),CAST( '20191004 00:00:00' AS datetime),CAST( '20190601 00:00:00' AS datetime),CAST( '20150325 00:00:00' AS datetime),CAST( '20181116 00:00:00' AS datetime),CAST( '20160803 00:00:00' AS datetime)
,CAST( '20110908 00:00:00' AS datetime),CAST( '20100804 00:00:00' AS datetime),CAST( '20190410 00:00:00' AS datetime),CAST( '20210119 00:00:00' AS datetime),CAST( '20161027 00:00:00' AS datetime),CAST( '20180612 00:00:00' AS datetime),CAST( '20120201 00:00:00' AS datetime),CAST( '20120711 00:00:00' AS datetime),CAST( '20150616 00:00:00' AS datetime),CAST( '20140901 00:00:00' AS datetime),CAST( '20120301 00:00:00' AS datetime),CAST( '20150323 00:00:00' AS datetime),CAST( '20150820 00:00:00' AS datetime)
,CAST( '20090306 00:00:00' AS datetime),CAST( '20141223 00:00:00' AS datetime),CAST( '20141212 00:00:00' AS datetime),CAST( '20200328 00:00:00' AS datetime),CAST( '20090610 00:00:00' AS datetime),CAST( '20130820 00:00:00' AS datetime),CAST( '20200701 00:00:00' AS datetime),CAST( '20150411 00:00:00' AS datetime),CAST( '20110214 00:00:00' AS datetime),CAST( '20080703 00:00:00' AS datetime),CAST( '20210304 00:00:00' AS datetime),CAST( '20140602 00:00:00' AS datetime),CAST( '20191021 00:00:00' AS datetime)
,CAST( '20101001 00:00:00' AS datetime),CAST( '20190904 00:00:00' AS datetime),CAST( '20110610 00:00:00' AS datetime),CAST( '20120404 00:00:00' AS datetime),CAST( '20170418 00:00:00' AS datetime),CAST( '20060207 00:00:00' AS datetime),CAST( '20150422 00:00:00' AS datetime),CAST( '20191119 00:00:00' AS datetime),CAST( '20160905 00:00:00' AS datetime),CAST( '20190710 00:00:00' AS datetime),CAST( '20150930 00:00:00' AS datetime),CAST( '20210301 00:00:00' AS datetime),CAST( '20181203 00:00:00' AS datetime)
,CAST( '20190502 00:00:00' AS datetime),CAST( '20190312 00:00:00' AS datetime),CAST( '20100308 00:00:00' AS datetime),CAST( '20181115 00:00:00' AS datetime),CAST( '20080620 00:00:00' AS datetime),CAST( '20171012 00:00:00' AS datetime),CAST( '20170602 00:00:00' AS datetime),CAST( '20170111 00:00:00' AS datetime),CAST( '20170621 00:00:00' AS datetime),CAST( '20191017 00:00:00' AS datetime),CAST( '20120809 00:00:00' AS datetime),CAST( '20150708 00:00:00' AS datetime),CAST( '20150226 00:00:00' AS datetime)
,CAST( '20181213 00:00:00' AS datetime),CAST( '20200601 00:00:00' AS datetime),CAST( '20090615 00:00:00' AS datetime),CAST( '20150922 00:00:00' AS datetime),CAST( '20060901 00:00:00' AS datetime),CAST( '20160726 00:00:00' AS datetime),CAST( '20171209 00:00:00' AS datetime),CAST( '20140923 00:00:00' AS datetime),CAST( '20120703 00:00:00' AS datetime),CAST( '20190101 00:00:00' AS datetime),CAST( '20190409 00:00:00' AS datetime),CAST( '20100112 00:00:00' AS datetime),CAST( '20190515 00:00:00' AS datetime)
,CAST( '20170316 00:00:00' AS datetime),CAST( '20190902 00:00:00' AS datetime),CAST( '20160503 00:00:00' AS datetime),CAST( '20180524 00:00:00' AS datetime),CAST( '20101103 00:00:00' AS datetime),CAST( '20150910 00:00:00' AS datetime),CAST( '20121116 00:00:00' AS datetime),CAST( '20170201 00:00:00' AS datetime),CAST( '20131024 00:00:00' AS datetime),CAST( '20180111 00:00:00' AS datetime),CAST( '20210128 00:00:00' AS datetime),CAST( '20090304 00:00:00' AS datetime),CAST( '20180120 00:00:00' AS datetime)
,CAST( '20180829 00:00:00' AS datetime),CAST( '20180314 00:00:00' AS datetime),CAST( '20130701 00:00:00' AS datetime),CAST( '20080616 00:00:00' AS datetime),CAST( '20190201 00:00:00' AS datetime),CAST( '20170303 00:00:00' AS datetime),CAST( '20180717 00:00:00' AS datetime),CAST( '20110727 00:00:00' AS datetime),CAST( '20120725 00:00:00' AS datetime),CAST( '20130228 00:00:00' AS datetime),CAST( '20151015 00:00:00' AS datetime),CAST( '20161031 00:00:00' AS datetime),CAST( '20171001 00:00:00' AS datetime)
,CAST( '20201007 00:00:00' AS datetime),CAST( '20160707 00:00:00' AS datetime),CAST( '20170815 00:00:00' AS datetime),CAST( '20200203 00:00:00' AS datetime)))
)
)
AS [basetable0]

INNER JOIN


(

(SELECT 44198 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44199 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44200 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44201 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44202 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44203 AS [c1],CAST( '20210115 00:00:00' AS datetime) AS [c44] ) UNION ALL
....
(SELECT 44560 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44561 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) UNION ALL 
(SELECT 44562 AS [c1],CAST( '20220401 00:00:00' AS datetime) AS [c44] ) 
)) AS [semijoin1] on 
(

([semijoin1].[c44] = [basetable0].[c44])

)
)

GROUP BY [semijoin1].[c1],[basetable0].[c43] 

This is already very long, but in fact the query has about 7.000 rows with tons of Casts of datetime values.

On the other hand, I have recreated the query in T-SQL myself, coming up with this solution:

SELECT 
TOP (1000001) 
[t4].[Datum],
SUM(CAST([t5].[AnzahlLaufenderVertrag] as BIGINT)) AS [a0]
FROM 
((
select 
SUM([AnzahlLaufenderVertrag]) as AnzahlLaufenderVertrag,
[StatistischGueltigabDatum],
[StatistischGueltigbisDatum]

from [RADaR_Sandbox].[BL_VERTRAG].[Fakt_Bestand] as [$Table]
group by [StatistischGueltigabDatum],
[StatistischGueltigbisDatum]
) AS [t5]


LEFT OUTER JOIN

(
select [$Table].[Datum] as [Datum],
[$Table].[JahrMonat] as [JahrMonat]

from [BLAllgemein].[Datum] as [$Table]
WHERE Jahr = 2021
) AS [t4] on 
(
[t5].[StatistischGueltigabDatum] <= [t4].[Datum] and 
[t5].[StatistischGueltigbisDatum] > [t4].[Datum]
)
)

GROUP BY [t4].[Datum] 
order by [t4].[Datum]

The latter query takes only a fraction of a second and produces exactly the same result as the long query generated by Power BI.

Can I do anything (in the dax query, the data model or direclty in the database) so that PowerBI will find a more efficient way to calculate the desired result?

Any help is appreciated!

[IMett]

Hi @amitchandak , thank you for your suggestion.
Unfortunately this wasn't a solution yet.
All of the queries still translate to the same long T-SQL query. While the userelationship version is somewhat faster than the other 2, it does not output the same results, in fact the result table is empty.
The problem is probably, that the relationship filters [Datum] = [StatistischGueltigBisDatum] for every [Datum] in 2021. However, most of the StatistischGueltigBisDatum values are far after 2021, so that no matches are found.

What I actually need is the condition FactTable[StatistischGueltigAbDatum] <= Calender[Date] < [FactTable[StatistischGueltigBisDatum]

[amitchandak]

@IMett , If you need between for StatistischGueltigbisDatum , then create an inactive relationship  and use userelationship

__AnzVerträgeBestand =
VAR _Date =
MAX ( 'BLAllgemein Datum'[Datum] )
VAR _result =
CALCULATE (
SUM ( 'BL_VERTRAG FAKT_BESTAND Table'[AnzahlLaufenderVertrag] ),
'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigabDatum] <= _Date,
'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigbisDatum] > _Date,
userelationship ( 'BLAllgemein Datum'[Datum] , 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigBisDatum] )
)
RETURN
_result

or try Cross filter

__AnzVerträgeBestand =
VAR _Date =
MAX ( 'BLAllgemein Datum'[Datum] )
VAR _result =
CALCULATE (
SUM ( 'BL_VERTRAG FAKT_BESTAND Table'[AnzahlLaufenderVertrag] ),
'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigabDatum] <= _Date,
'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigbisDatum] > _Date,
crossfilter ( 'BLAllgemein Datum'[Datum] , 'BL_VERTRAG FAKT_BESTAND Table'[StatistischGueltigAbDatum], none )
)
RETURN
_result