Forum Discussion
Incremental Refresh from TE3 Gets Nebulous Type Conversion Error
- 2 years ago
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.
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())
- lbendlin2 years agoSuper User
Would you have had this issue with an externally sourced Calendar table ? 🙂