In one of our subject areas, we recently changed the physical source from one system to another. No big deal right?
One of my colleagues figured out this neat little trick using Fragmentation content on the Content table of a logical table source. Basically, if the date was yesterday or greater, use Table_A, if not, use Table_B. Worked like a charm.
With the change to the physical source, it no longer worked. WTH?
I suspected the Fragmentation content because the data types changed, well, one was no longer the driver, if that makes sense. See, we had to do this work-around because the original source system wasn't designed with date date types. We had to pass a string in the YYYY-MM-DD format.
With the new source, that was resolved. We now had a date data type, awesome.
Not so awesome, because it broke. By broke I mean all the facts were doubling for everything but the current day (today).
If you're wondering, here is the logic in the Fragmentation content section for Table_A:
"BMM"."Dim - Date"."YYYY-MM-DD Column" >= VALUEOF( "INIT_BLOCK"."YYYY-MM-DD" )For Table B:
"BMM"."Dim - Date"."YYYY-MM-DD Column" < VALUEOF( "INIT_BLOCK"."YYYY-MM-DD" )The only thing different is the operator.
Since we now had a true date data type, I tried to use that, which is how I came across the 14006 Unsupported Predicate issue. You see, now I didn't have to use a repository variable, I could simply use OBIEE date functions. I came up with this to mimic the same behavior:
"BMM"."Dim - Date"."Date (Data Type!)" < CAST( TIMESTAMPADD( SQL_TSI_DAY, -1, CURRENT_TIMESTAMP ) AS DATE )Of course I changed the operator for each LTS. Save RPD, Transaction Update Failed. Check Consistency,
It's a valid formula. Works in Answers (or whatever it's called these days). Perhaps someone with far more experience can spot the mistake.