How to work with YTD measures within OLAP

[blom0344]

Can OLAP handle something like YTD measures?

We are used to create complex reports with BI tools that show measures for fiscal years, months and comparitive YTD (year-to-date) figures.

The crowd is used to reports incorporating (Example):

Sales Current Year
Sales Last Year
Sales Last Year (YTD)
Sales Last month (1M)
Sales Equivalent to Last month last year (13M)

Can we handle this with SQL server / DTS?

Ties Blom
Information analyst

 

[johnherman]

Totals like this are built-in with Cognos Power Play cubes when built with the Cognos Transformer tool. There are also similar calculated fields available in Analysis Services. I cannot comment on any other OLAP tools due to lack of in-depth knowledge.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[blom0344]

Actually we are going the OLAP way to complement our use of Business Objects (which is deemed to expensive for our salesreps) The idea is to create cubes with Analysis Services and use a third party tool to create offline cubes for distribution.

http://www.localcubetask.com/

I'm a 100% SQL guy, so almost anything in this field is new to me.

We will be using Excel to create pivottables, but it seems to me we'll have to settle for a lot of tabs to show all data

Ties Blom
Information analyst

 

[rpdeb]

You can create or write various YTD/MTD or periodtodate function using analysis services MDX functions.
This functions can be expanded for rolling periods, parellalperiods calculation usually needed in reports. Third party tools like proclarity and panorama provides very good support for such needs.

Functions like lag(), lead() allows you to add members of period/time dimension in sum-range function. You should be little careful in keeping mdx calculated measures sime in case of large data set/cubes.

Analysis services supported functions are
MTD, YTD, Periodstodate, parellalPeriod, Sum() with lag and lead, etc

Good luck.

Rabi