(First off, sorry Mike, I'm hoping this will break my writer's block...)
On Friday I was asked to look at a report that wasn't returning all of the data. Sample:
Year/Month Total Sales Total Sales (YAGO)
------------------------------------------
01/31/2013 $1,000,000 $900,000
03/31/2013 $950,000
For reference, YAGO is "Year Ago."
Notice anything funny there?
Yeah, February is missing. The (OBIEE) report has a filter on Jan, Feb and Mar of 2013. But it wasn't showing up. I confirmed via manual SQL (hah!) that there was (YAGO) data in there for February. Any ideas?
I immediately suspected one of two things:
- If the Date (month) dimension had a "year ago" column it was wrong.
- The join in OBIEE was doing it wrong.
I checked the date dimension first. It was fine. It didn't even have a YAGO column, so nothing to see there. I looked at the join between the date dimension and the fact table...
(YEAR ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" ) - 1 ) * 10000
+ MONTH ("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE" ) * 100
+ CASE WHEN DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE") = 29 THEN 28 ELSE
DayOfMonth("DW".""."DW"."My_Month_Dim"."MONTHEND_DATE") END
= "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"I want to tear my eyes out when I see stuff like that. I don't even want to know what it does. * 1000? * 100? Shoot me.
OK, so the MONTH_DIM_KEY is in the YYYYMMDD format. MONTHEND_DATE is a date data-type that corresponds to the last day of the month. For February 2013, it's 20130228, For February 2012, it should be 20120229. <<< Leap Year!!! I'm going to make a wild guess and say that the formula up there isn't working. How to test it though? That's logical SQL (OBIEE), it doesn't run in the database. I just ran the report and grabbed the SQL submitted to the database. This is what it looked like:
AND ( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 +
TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 +
CASE
WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
THEN 28
ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
END = MONTH_DIM_KEY
AND( MONTHEND_DATE IN( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ), TO_DATE(
'2013-02-28', 'YYYY-MM-DD' ), TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) ) ) Eyes are burning again. This is also the "prettified" SQL after I hit Ctrl + F7 in SQL Developer. The very
first thing I do with OBIEE generated SQL.
One part of that wouldn't be so bad, but it's three formulas adding up to some mysterious number (presumably the last day of the month, for the previous year, in YYYYMMDD format). So I moved all those formulas up into the SELECT part of the statement. Let's see what they are doing.
SELECT
( TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'yyyy' ), '9999' ) - 1 ) * 10000 part_1,
TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'MM' ), '99' ) * 100 part_2,
CASE
WHEN TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' ) = 29
THEN 28
ELSE TO_NUMBER( TO_CHAR( MONTHEND_DATE, 'dd' ), '99' )
END part_3
FROM my_month_dim
WHERE MONTHEND_DATE IN ( TO_DATE( '2013-01-31', 'YYYY-MM-DD' ),
TO_DATE( '2013-02-28', 'YYYY-MM-DD' ),
TO_DATE( '2013-03-31', 'YYYY-MM-DD' ) )That resulted in this:
PART_1 PART_2 PART_3
20120000 100 31
20120000 200 28
20120000 300 31
So PART_3 is definitely incorrect. Am I going to bother to figure out why? I have some serious issues inside of my brain which simply do not allow me to do date math. I avoid it at all costs...instead choosing to use whatever the system provides me.
One of my favorites, especially when dealing with leap years, is
ADD_MONTHS.
If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month
That's why. Add -12 months to February 28, 2013. You don't get back February 28, 2012, you get back the 29th, as it should be. Do the same thing starting with February 29th, 2012. Add 12 months, subtract 12 months. It's always right. Yay for someone figuring this out so I don't ever have to do so.
Sadly, OBIEE doesn't have the equivalent of ADD_MONTHS (or LAST_DAY), you have to build out a string and then concatenate it all together, not pleasant. So I cheated, I used
EVALUATE. Here's my working solution.
TO_NUMBER( TO_CHAR( ADD_MONTHS( MONTHEND_DATE, -12 ), 'YYYYMMDD' ) ) = DW_MONTH_DIM_KEY
Oops, that's the physical SQL. How about the OBIEE SQL:
CAST( EVALUATE( 'TO_NUMBER( TO_CHAR( ADD_MONTHS( %1, %2 ), %3 ) )',
"EDW".""."DW"."Dim_DW_MONTH_DIM_CBM"."MONTHEND_DATE",
-12, 'YYYYMMDD' ) AS INTEGER ) = "DW".""."DW"."My_Fact_Table"."MONTH_DIM_KEY"
