Forum Discussion
Incremental Refresh from TE3 Gets Nebulous Type Conversion Error
This verifies what the problem is. By running this simple DAX query in TE3, I can reproduce the exact same error I was getting:
So, the answer here is, when employing this DAX-based dimDate table and refreshing an "empty" model (metadata-data only / freshly-deployed), to be sure to:- refresh a partition that you are sure has data in the source, first, before the other partitions
- include some error-trapping code in the DAX that accounts for instances where it might return an empty string (BLANK())
So, since I posted this, I have gotten the model to refresh. Strangely, the thing that "unstuck" it was to refresh a full yearly or quarterly partition, before refreshing any of the more recent daily partitions. These daily partitions didn't have any data in the source, which I think might be what caused the issue. And I don't think it was the actual partition refresh that was failing but the post-refresh calculation that occurs. You see, my dimDate table is DAX-based and it looks at the fact tables to determine the data ranges it needs to have.
------------------------------------------------------------
--
-- Configuration
--
------------------------------------------------------------
--All the information needed to understand this table can be found here:
--https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/
--and here: https://github.com/sql-bi/DaxDateTemplate
------------------------------------------------------------
VAR TodayReference = TODAY ()
VAR CalendarFirstDate =
DATEVALUE (
FORMAT (
MIN (
'factPayments'[DATE_WID]
),
"####-##-##"
)
)
VAR CalendarLastDate =
DATEVALUE (
FORMAT (
MAX (
'factPayments'[DATE_WID]
),
"####-##-##"
)
)
VAR FirstYear = YEAR ( CalendarFirstDate )
VAR LastYear = YEAR ( CalendarLastDate )
VAR FiscalCalendarFirstMonth = 1 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month)
So, I think when those daily partitions try to refresh, and there is no data in them, it makes the post-refresh calculation associated with dimDate fail. Which fails the whole partition refresh.
I've said it many times and I'll say it again. Creating calendar tables in DAX or Power Query is futile. Those tables are immutable and should be sourced further upstream. You should also never combine Incremental Refresh with Auto Date/Time - exactly to avoid these kind of post refresh computations and to avoid refreshes of related partitions.
Well, I vehemently disagree. I know that a DAX-based calendar table can cause some issues sometimes, but the juice is worth the squeeze (IMHO).
- they are light-weight (data as code), they are dynamic (always the exact records that you need, not some SQL-based giant that needs stored procs to update it every couple years)
- their logic is included in source control with their associated model (not in the source control for the data warehouse)
- their logic can quickly and easily be changed by the BI professional with no arguing or waiting on data engineers to add / fix things
- that logic / features can differ from model to model (a table in the DW can't unless you give it a hundred columns)
- having the calendar logic contained within the Power BI environment alongside other model logic can simplify management and troubleshooting, as everything is managed in a unified manner.
But I certainly respect your opinion and your approach.
Can you tell me what you meant with "with Auto Date/Time"? I don't have any Auto Date/Time columns in this model (or any model ever, for that matter). 😀
That's good. You can check the partitions. If you see any rogue ones you know that Auto Date/Time is enabled.
This verifies what the problem is. By running this simple DAX query in TE3, I can reproduce the exact same error I was getting:
So, the answer here is, when employing this DAX-based dimDate table and refreshing an "empty" model (metadata-data only / freshly-deployed), to be sure to:- refresh a partition that you are sure has data in the source, first, before the other partitions
- include some error-trapping code in the DAX that accounts for instances where it might return an empty string (BLANK())
Would you have had this issue with an externally sourced Calendar table ? 🙂
