Parameter / Incremental Refresh data type conversions

[rehpyc]

With some of our source systems, fact tables are clustered off either a date or datetime field. For both parameter and incremental refresh usage, the date value is being casted as a datetime2, which ultimately causes SQL Server to not use the clustered index (implicit data type conversion, leading to a full table scan off a predicate filter vs. index seek).

With parameter usage, is there a way to convert things to standard datetime vs. datetime2 to avoid the implicit conversion of the table field?

Though I've read a few tutorials regarding the ability to use a function w/ incremental refresh set up in Power BI Desktop data sets, to cast the datetime values of the RangeStart & RangeEnd to a date (or int), it seems the web service throws errors when attempting this for a data flow or new data mart. Is this a known deficiency?

[bsenden]

Did you manage to find something that avoids the implicit conversion from Power BI? We are facing the same issue

[otravers]

Do you actually care about the extra precision provided by datetime2, or do you even care about the time part of the date time at all, for BI purposes? For Power BI consumption purposes, I like to use SQL views with datetime columns where I've dropped the time value (i.e. it's all 00:00:00) and if need hours or even minutes, those can be stored in separate integer columns. That way I don't increase cardinality more than needed, and Power BI incremental refreshes are happy with a datetime column to work with.

Just because you use Power Query doesn't mean that you can't push back some data prep to the source SQL server when you control it.