Creating Special Relative Date Categories

I need to re-create special relative date categories in SSAS similar to the functionality offered by Cognos/Powerplay/Tranformer. My problem is that we are replacing Cognos PowerPlay/Transformer with SSAS. With Cognos, you can create relative time categories very easily. The relative time categories are part of the time dimension. When setting up these special time categories, you tell Cognos how to determine the relative time based on the current date or some other calculated date. As a result, the Cognos relative time categories like YTD, MTD, Yesterday etc. are all based off this date. Every time you refresh the cube, the relative date categories change. The big difference between SSAS and Cognos is that SSAS apparently requires you to bring the date hierarchy into a row or column. Whereas, in Cognos, the relative date categories are independent.

For example, I have reports that have several relative date categories in columns like -- Yesterday, WTD, MTD, YTD, Prior YTD etc. Under these columns, I have Sales Dollars, Sales Units, Cost, etc. In rows, I have divisions and products. Cognos knows that Yesterday was January 1, and WTD represents Sunday through Tuesday etc. The relative dates are not dependent on me placing the time hierarchy on the grid.

Is it possible to duplicate this functionality in SSAS? That is, can I create calculated “relative dates” that will change based on the current date or some lag from the current date? Thus, when I add these relative dates to the report, they will always reflect an offset from the current date. It sounds like this can be done through some MDX statement based on the current date but I'm not sure how to do this. Can anyjone provide me with some guidance on this?

Thank you.

David Greenberg

First, it is correct that you will have to feed SSAS2005 with attributes for date, month, quarter and year, but it is fairly simple to this directly in your dimension table or in the datasource view for the cube.

You can use the TSQL DATEPART(), YEAR(), QUARTER()-functions for this. Have a look in Books On Line for date-functions.

When you build the time dimension in Business Intelligence Developer Studio, you are assisted with a guide that will help you will building user hierarchies in the time dimension.

After that you can either create your MDX-time calculations yourself, in the calculation tab of the cube editor, or use the Business Intelligence wizard on the time dimension to get assistance with the MDX.

Here are some useful links

http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx (a little bit advanced)

http://www.sqlmag.com/articles/index.cfm?articleid=46157&

http://www.databasejournal.com/features/article.php/3593466 . Look for William E. Pearson

HTH

Thomas Ivarsson

|||

Hey David,

I have the same problem with the relative dates, I am trying to migrate/re-create our cubes from Cognos to SSAS, when I got to the relative date part I got stuck and I couldn't figure out how to proceed.

I bought 2 books one of them is purely MDX Scripts and none of the books talk about a relative date!!!!!

Were you able to figure out how to incorporate the relative date in SSAS?

Thank you

John Ghannam

|||

Hi John,

I finally figured this out after several months of trying. I even purchased two MDX books myself. I tried a solution to code the MDX with the current date but it just didn't work properly. My solution doesn't use MDX but you can extend the functionality with MDX.

What I developed works very well in terms of query performance and flexibility. My solution requires a view over the date dimension table. Using T-SQL, I wrote a bunch of SQL statements that will return all of the relative dates used in PowerPlay Transformer like YTD, Last 12 Months, Last Month, MTD, WTD etc. All of these statements are based on supplying a starting date that is based off of getdate(). I needed to set the "current date" to be today's date minus one day since we want to track our shipments through the last completed day. The process of figuring this out was tedious but my custom solution works better and faster than using a calculated member because the data is stored in the cube rather than derived at query run-time. With this solution, I was able to figure out a complete process for converting our Cognos environment into SSAS.

The T-SQL statements to derive the relative dates are a bit tricky but you can write me at david@.appliedbusinessintelligence.com if you need help.

David Greenberg