Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
Did you manage to find something that avoids the implicit conversion from Power BI? We are facing the same issue
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.