Again a post inspired by the BOBJ board which talks about a generic requirement whereby making it easy for adhoc users to perform relative time period based analysis as easy as possible. The users should be able to select the date using a prompt or otherwise and ask for values for either the selected period or some other reference period such as Current Year Last Week or Prior Year by simply selecting a different measures named as such from the Universe.

This is actually quite simple once you get around to designing it. All we need to do is make sure we run multiple queries once for each time period and then join the queries at runtime in the BI Server. WEBI provides such functionality in the form of contexts. For example if we define the current period and YAGO (year ago) in 2 separate contexts, then when we pull Sales and YAGO sales in the same report BI Server will automatically run 2 queries and join the results. Thats what I want to leverage.

My example shows how to setup 2 measures, Sales and YAGO Sales. However this can be done for any number of measures as well as any number of reference periods as the process is quite simple. So here is what I have done.

1. For each relative time period we need to define an alias of the fact table in the universe. For example I need current period and YAGO therefore I have 2 fact tables Sales and YAGO Sales.

2. It is also very important to have a proper dates/calendar table that you can use for your time based calculations. In my schema it is the DATES_TABLE with a structure as shown below.

3. Now join the 2 facts to the dates table. The main fact table for current period will be joined normally to the dates table, however the YAGO sales will be joined to the dates table with a lag.

Ofcourse you would also need to define all the other joins between the YAGO_SALES fact and the other dimension tables for completeness. Now that the 2 join conditions have been defined as you can define the measures from each of these tables. When user say runs the query for October 2010 then Sales revenue will be the value for Oct-2010, however YAGO will be Oct-2009 since YAGO sales will join with the date table with a 1 year lag due to our join condition.

Now in order to force the 2 queries we need to define the contexts 1 for each fact table. This will resolve the loops as well as force the BI Server to issue 2 separate queries to the database.

Now in the query as will see the BI Server will issue 2 queries one for each context and combine them in the result set.

The beauty of this approach is that it is automatically level based. If the user selects a date in the prompt the results will be for same date last year, for month it will be same month LY, for Qtr will be same Qtr last year and for year will be last full year.

You can use the same mechanism to define last week or last month.