Supercharge Your Universe with Time-Series Analysis – Part 2

Using time is one of the most important ways to analyze information.  Each day, week, month and year is unique and can best be understood within it’s specific context.  In my last post, we showed how you can use the Semantic Layer to create time-based filters.  Today we are going to discuss Time Specific Measures.

Creating Time Specific Measures

Time Specific Measures are important because they allow us additional flexibility within WebIntelligence.  Instead of creating two queries, one for last years sales and one for this years sales, we can create one query and then use the definition within the measure objects to filter the records we need.  The following examples are for SQL Server:

YTD Sales

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<=
 getdate() then dbo.Current_Facts.store_sales else 0 end)

Previous YTD

sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)

Now I can run a report and place these two measures on the report and the measures values for Current YTD and Previous YTD are already filtered accordingly.  What’s great about these measures is that they can be exceedingly complex within the universe, but they are easy for users to implement.  They don’t need to worry about accidentally filtering out too many records because the filtering within the case statement is non-exclusive.

Try this one on for size, Year to Date Sales v. Previous Year to Date Sales as a percent.

YTD Sales vs Prev YTD Sales %

case when sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
 datepart(yyyy,getdate())-1 and datepart(dy,dbo.Current_Facts.sales_date) <=
 datepart(dy,getdate()) then dbo.Current_Facts.store_sales else 0 end)=0 then 0
else (sum(case when datepart(yyyy,dbo.Current_Facts.sales_date) =
  datepart(yyyy,getdate()) and dbo.Current_Facts.sales_date<= getdate()
then dbo.Current_Facts.store_sales else 0 end)/sum(case when
 datepart(yyyy,dbo.Current_Facts.sales_date)=datepart(yyyy,getdate())-1 and
 datepart(dy,dbo.Current_Facts.sales_date)<=datepart(dy,getdate())
then dbo.Current_Facts.store_sales else 0 end))-1 end

Now that’s some complicated SQL, and yet, because of the power of the semantic layer, all the end user consumer has to do is to drag the object onto the report.  The database will do the rest.

You can do the same on a monthly level.  Here is a list of time-based measures that I use all the time.

As you can imagine, it’s a great way to provide options to your users that they would never have dreamed possible before.  In my final post, part 3, I will provide a copy of the universe for you to download.

«Good BI»