Over the last couple of weeks I came across several posts in the BOB Board that revolve around time based analysis. Since the questions keep repeating it makes it an ideal candidate for a blog posting. Most of the analysis that I run into involve either, the most current data i.e. current day, current week, or current month. In fact most of the standard reports are probably built with 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 two key topics.
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 Weekfrom the prompt selection rather than having to go through a set of dates.
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.
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
This derived table has been joined to the fact table using a between clause as shown below.
I also need to define the contexts to resolve the loops created by the joins.
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.
DATE_RANGE.DATE_RANGE = case when @Prompt('Select Date Range for Analysis:','A','Date RangeDate Range',mono,free) = '*' then 'All Days' else @Prompt('Select Date Range for Analysis:','A','Date RangeDate 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.
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.
DATES_TABLE.DATE >= @Prompt('Select Start date:','D','PeriodDate',Mono,free,not_persistent,{'2001/01/01'}) AND DATES_TABLE.DATE <= @Prompt('Select End date:','D','PeriodDate',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.
