Supercharge Your Universe with Time-Series Analysis – Part 1

To some degree or another, as we close out the year, we will all reflect on 2009 and what has been accomplished.  There are always things we might have done better, but looking at things over the time helps to put them in perspective– both in life and in business.    This is the first in a three part series.

Time Puts Everything in Perspective

When it comes to running reports and looking at your data are you leveraging time like you should?  Most organizations understand their performance month to month or year over year, but how much flexibility do you give your analysts to look at the information within the time segments they choose?  Some common time-series are:  YTD (Year to Date), MTD (Month to Date), WTD (Week to Date), but there is so much more.  As we prepare to close out 2009 and welcome in 2010, I challenge you to see if you are really using the power of time in your BI solution.

Insight Through Time

It’s important to always analyze your figures in comparison to other equivalent times, e.g. YTD with PYTD (Previous YTD).  The same with MTD and WTD, but how do you do it?  Within the Universe you can create filters.  These filters can be applied to any query.  Here is the SQL required.

YTD (Year to Date)

datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate()) and
dbo.Current_Facts.sales_date<= getdate()

PYTD (Previous Year to Date)

datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate())-1 and
datepart(dy,dbo.Current_Facts.sales_date)<=datepart(dy,getdate())

Because you can run multiple queries using BusinessObjects you can simply create a query with the YTD filter and then “duplicate” the query and replace the YTD filter with the PYTD filter.  The dimensions of the two queries will be merged automatically.  Here is the query panel:

And here are the results:

… but sometimes you don’t want to have to make two queries.  Why not just get all results for the last two years and split things up within the measure?  Well, the power of the semantic layer allows you to do that too.  I’ll explain more about that in my next post, but before I do, let me share some additional common filters you should consider using:

Last 7 Days

dbo.Current_Facts.sales_date between convert (SMALLDATETIME,
{fn CURDATE()})-7 and convert (SMALLDATETIME, {fn CURDATE()})

Last n Days

dbo.Current_Facts.sales_date between getdate()-
@Prompt('Days Ago','N',,mono,free) and getdate()

Last n Months

datediff(m, dbo.Current_Facts.sales_date,getdate()) <=
@Prompt('Months Ago','N',,mono,free) and dbo.Current_Facts.sales_date<=getdate()

Last n Months Ago Ending When

datediff(m, dbo.Current_Facts.sales_date,@Prompt('Ending Date','D',,mono,free))
<= @Prompt('Months Ago','N',,mono,free) and
dbo.Current_Facts.sales_date<=@Prompt('Ending Date','D',,mono,free)

I will post a copy of my everything-you-never-thought-you-could-do-in-a-universe universe at the end of this series, but here is a list of some of the filters that exist in my universe.

That’s a long list, eh?  I almost wish all these filters were already in the universe by default, but they aren’t, so don’t be afraid to add as many as you need.  Remember, when you add a filter to a query it affects the entire query.  Tomorrow we’ll talk about the technique of creating time specific measures…

«Good BI»