OBIEE Moving period to date aggregations

When using the OBIEE todate functionality the BI-server starts at the first entry next higher dimensional level. => TODATE(FACT, TIME.MONTH) means that it will start aggregating from the first month it will find on the TIME.YEAR level.

Recently on of out customers wanted a moving todate column, Starting 21 days ago and going on for the next 7 days after CURRENT_DATE. We couldn’t use a MSUM since we didn’t have an entry for each date and the total reporting period was greater then the aggregation period.

We fixed by creating a extra column on the calendar view on the database indentifying the period.

CASE WHEN DAY_DATE <SYSDATE –21 then ‘00-PREVIOUS’ ELSE CASE WHEN DAY_DATE > SYSDATE + 7 THEN ‘02-NEXT’ ELSE ‘01-CURRENT’ END END AS P21MINT7PLUSPERIOD

This will give ‘00-PREVIOUS’, ‘01-CURRENT’ or ‘02-NEXT’ for P21MINT7PLUSPERIOD. It’s important that you created this in the physical layer. The TODATE function doesn’t like dynamical column in the Business Model Layer.

Next when created an alternative hierarchy: TIME.TOTAL => TIME.P21MINT7PLUSPERIOD => TIME.DAY. Now we could use the TODATE function like TODATE(FACT, P21MINT7PLUSPERIOD) Which gives us a daily moving period.

Till Next Time