Random Thoughts

Views on life

Archive for the ‘Prior Period’ Category

Is it my time yet ?

Posted by Hemanta Banerjee on November 11, 2010


Over the last couple of weeks I have came across several posts in the BOB Board that revolve around time based analysis. Since the questions seem keep repeating it makes it an ideal candidate for a blog posting. Most of the analysis that I run into involve either analysis the most current data i.e. current day, current week, or current month. In fact most of the standard reports are probably built with that as the default selection parameter. And also in most of the cases this data is being compared with some other period like either last quarter or last year.

In my previous posts I have covered 2 very key topics

  • Period to date analysis – Examples would be YTD or MTD type of analysis which I have covered here
  • Prior Period Analysis – Covered here.

In this post I will cover how to make date selections easier for users, especially in scenarios where they want to analyse the most recent period. You might ask why all of this work when I can select dates using the filter criteria in WEBI. The answer is usability. As you can see below it is much simpler to select “Current Year” or “Last Week” from the prompt selection rather than having to go through a set of dates.

image

So how can we design something like this. It is quite simple actually. First off I define a derived table with the set of pre-defined date ranges that I want to make available for the users.

image

The code for the derived table is actually quite simple. For example in my case I have used the MAX function to determine the current date based on the dates in the dimension table.

Select 1 AS ITEM_INDEX, ‘All Days’ as DATE_RANGE, min(DATE) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select 2 AS ITEM_INDEX, ‘Today’ as DATE_RANGE, max(DATE) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select 3 AS ITEM_INDEX, ‘Last Week’ as DATE_RANGE, dateadd(dd,-7, max(DATE)) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select 4 AS ITEM_INDEX,’Current Month’ as DATE_RANGE, cast(CAST(datepart(yyyy,max(DATE)) as varchar(10)) + ‘-‘ + CAST(datepart(mm,max(DATE)) as varchar(10)) + ‘-01’ as DATETIME) as DATE_RANGE_MIN, max(DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select 5 AS ITEM_INDEX,’Current Year’ as DATE_RANGE, cast(CAST(datepart(yyyy,max(DATE)) as varchar(10)) + ‘-01-01’ as DATETIME) as DATE_RANGE_MIN, max(DATES_TABLE.DATE) as DATE_RANGE_MAX from DATES_TABLE
union
Select 6 AS ITEM_INDEX,’Current Qtr’ as DATE_RANGE, ‘DATE_RANGE_MIN’ =
    case
        when datepart(qq,max(DATES_TABLE.DATE)) = 1 then cast(cast(datepart(yyyy,max(DATES_TABLE.DATE)) as varchar(10))+ ‘-01-01’ as datetime)
        when datepart(qq,max(DATES_TABLE.DATE)) = 2 then cast(cast(datepart(yyyy,max(DATES_TABLE.DATE)) as varchar(10))+ ‘-04-01’ as datetime)
        when datepart(qq,max(DATES_TABLE.DATE)) = 3 then cast(cast(datepart(yyyy,max(DATES_TABLE.DATE)) as varchar(10))+ ‘-07-01’ as datetime)
        When datepart(qq,max(DATES_TABLE.DATE)) = 4 then cast(cast(datepart(yyyy,max(DATES_TABLE.DATE)) as varchar(10))+ ‘-10-01’ as datetime)
        else cast(‘1900-01-01’ as datetime)
        end,
max(DATES_TABLE.DATE) as DATE_RANGE_MAX from DATES_TABLE

My code assumes that the dates dimension table is updated and contains only the valid dates. If that’s not the case then you would need to use either a system function like GetDate() to get the current date or use some form of control table for the current date information. This has been explained quite well by Dave in his blog.

This derived table has been joined to the fact table using a between clause as shown below.

image

I also need to define the contexts to resolve the loops created by the joins.

image

Now we are ready to add the "DATE_RANGE” column to the universe. In my specific example I have defined the object as a hidden object in the universe and defined a filter called DATE_RANGE with a @prompt as shown below. This is to make it easy to use. I do not want to clutter up the time hierarchy with unnecessary objects. However I want to give the flexibility to the users to easily pick a date range for their analysis.

image

DATE_RANGE.DATE_RANGE = case when @Prompt(‘Select Date Range for Analysis:’,’A’,’Date Range\Date Range’,mono,free) = ‘*’ then ‘All Days’ else @Prompt(‘Select Date Range for Analysis:’,’A’,’Date Range\Date Range’,mono,free) end

The prompt condition allows the user to either pick ‘*’ meaning all dates, or pick some other date range for analysis. Using the approach above ensures that during adhoc analysis the user has to drag the date range to the query filter and they will be prompted with a set of pre-defined filter conditions to restrict the data.

image

I have also gone ahead and defined another condition object called “Custom Date Range” that shows the calendar to the user and allows the user to pick any date range from a standard calendar. The custom date range prompts the user for a start and end date and filters the data based on the user selection.

image

DATES_TABLE.DATE >= @Prompt(‘Select Start date:’,’D’,’Period\Date’,Mono,free,not_persistent,{‘2001/01/01’}) AND DATES_TABLE.DATE <= @Prompt(‘Select End date:’,’D’,’Period\Date’,Mono,free,not_persistent)

So in summary using some of the techniques given here as well in the other posts around time slicing, you can implement quite sophisticated and flexible time based analysis. To access the other articles in the series click on the links below.

  • Period to date analysis – Examples would be YTD or MTD type of analysis which I have covered here
  • Prior Period Analysis – Covered here.
Advertisements

Posted in BusinessObjects, Prior Period, Time Sliced | Tagged: , , , , , | Leave a Comment »

How to do Year Ago or Prior period type comparison in WEBI

Posted by Hemanta Banerjee on November 3, 2010


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. That’s 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.

image

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.

image

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.

image image

 

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.

image

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.

image

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.

image

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.

Posted in BusinessObjects, Prior Period, WEBI | Tagged: , , , , , , , | 2 Comments »

 
%d bloggers like this: