SSAS 2008R2 - Creating "Year over Year Growth %" or "Year To Date"

[DimFact]

Is there any way to create time intelligence, e.g. year to date, without using built in time intelligence tools?

[John Simon]

Absolutely. But you need to learn MDX.

In my current project we have created a date utility dimension with WTD, MTD, YTD, YTD Growth etc.

See here: http://www.ssas-info.com/analysis-services-articles/59-time-dimension/371-multiple-time-utility-dimensions-by-chris-webb

[Mike Honey]

Personally I prefer to calculate those attributes during ETL and refresh or update extra attributes on my "Dates" dimension. So for a "Year to Date" attribute you might assign values of "Current YTD", "Prior YTD" or "Other" to each date row. I find this is the most useful as it can feed SQL queries/reports as well as the cube. It's also the easiest to test (much as I love MDX).

[Boyan Penev]

Mike,

I disagree.

A scenario:

We are currently in Oct 2010, or in the 4th Calendar Quarter. If the user wants to see the current QTD your preference would be great. However, what happens when they want to check the previous QTD and compare to the current one, or to the one before that? How can we also compare the current QTD to last year same QTD, or last year's 4th QTD vs two years ago 4th QTD? What if users flick between different dates in the current month - say a KPI which is built on top of a QTD calc and they want to see how they go against the targets?? Etc, etc, etc..

In my opinion, creating an additional column for "time intelligence" attributes is not feasible as it is too limiting. Performance-wise, if we have troubles with some calculations (either SQL or MDX), then yes - possibly materialise, but I wouldn't imagine getting many of those with well-written code.

SSAS in particular is very efficient with time intelligence calcs. You have an array of in-built functions making coding the measures/utility dimensions a breeze (e.g. YTD, QTD, MTD, WTD, ParallelPeriod, ClosingPeriod, etc...).

----

DimFact,

Absolutely. I would say that you should actually avoid using the Time Intelligence wizard. Have a look at the article John has posted and also at the MSDN MDX Function Reference where you can find all MDX functions related to date (not grouped, though). I am sure that if you search for "time intelligence functions ssas", "mdx date and time functions", or similar there will be plently of references on how to build them in MDX. There are some books which you can get which also show you how to build such calcs...In general, it should not be too challenging if you stick with one utility dimension, or even easier - if yo ubuild them as calculated measures.

[Mike Honey]

Hi bpenev,

Thanks for your input. I should point out that my solution can be extended to cover multiple e.g Current QTD, Previous QTD, Previous Previous QTD etc etc. Each row in a "Dates" dimension could be assigned to one of those values as appropriate, stored in a single attribute. That design pattern can be repeated to cover the various time intelligence requirements (e.g. YTD, Current Month vs Prior Months etc) - typically there is a finite list.

Fundamentally I think the difference is how you define the meaning of "To-Date". Using the MDX approach, YTD, QTD etc are relative to whichever date the user or query author has selected, and the date range included is dependant on that selection. I think that for 99% of use cases this flexibility is not required; the business user expects "YTD" to have one meaning where "To Date" means today, i.e. on 4 Oct 11, YTD always means 1 Jan 11 to 4 Oct 11. I think the MDX approach also leads to problems with typical query tools (e.g. Excel, SSRS) as the "Current Date" needs to be reselected by the user every day that the report is run.

I'd also prefer to centralise the "well-written code" as much as possible (i.e. in the ETL layer), and avoid query/report/cube developers needing to repeat or reinvent calculations - this often leads to inconsistent results. With my approach, the cube developer just needs to present the extra attributes in the "Dates" dimension.

Mike

[Boyan Penev]

Hi Mike,

Let me focus on each of your paragraphs spearately:

"typically there is a finite list"

If you inspect the scenarios above you'll see that this "finite list" is finite as in the type of time intelligence functions, but not in the combinations of data points which may need to be analysed (e.g. when the user changes to a previous period).

"Each row in a "Dates" dimension could be assigned to one of those values as appropriate, stored in a single attribute."

You mean a single attribute per calc - e.g. you'd still have two columns if you want o implement QTD and MTD, where you put 1 or 0 depending on whether the particular date falls within those periods, right? Therefore, you'd need to change your ETL to implement additional attributes..and when we speak about Previous Previous Previous QTD thing start getting a bit messy.

"I think the MDX approach also leads to problems with typical query tools (e.g. Excel, SSRS) as the "Current Date" needs to be reselected by the user every day that the report is run."

This is incorrect - reports in SSRS can be written to default to the current date. Same goes for Excel analytics - you can have a default member other than All in your date dimension in SSAS which will be selected "by default" if this is the intended behavior. Let me also make the point that Excel+SSAS users typically easily "get" the point that all they see is relative to the period they are selecting - it is only intuitive that when a user changes the date, or month, they get to see data relevant to that date, or month.

"I'd also prefer to centralise the "well-written code" as much as possible (i.e. in the ETL layer), and avoid query/report/cube developers needing to repeat or reinvent calculations - this often leads to inconsistent results. With my approach, the cube developer just needs to present the extra attributes in the "Dates" dimension."

You can define the calcs in SSAS as time utility dimension or calculated measures and report developers do not need to write them themselves. Similarly, in SQL, you can build functions which do the same.

[Mike Honey]

Hi Boyan,

This is a great discussion, I hope we can continue it in a constructive way. Let me focus on each of your replies separately.

"typically there is a finite list"

I think the "combinations of data points" that business users actually want to analyse in these scenarios is actually very small. I explained my thinking in my para above on the meaning of "To-Date" to business users.

"Each row in a "Dates" dimension could be assigned ..."

Yes I'd have two columns to support QTD and MTD, but with multiple values in each column (e.g. "Current QTD", "Previous QTD" ...). But I'm not seeing much difference between needing to change the ETL vs needing to change the SSAS calc script - they are both changes. At least you can make one change in the ETL and be confident it has flowed out in a consistent way to all reports/queries/cubes. I don't follow your point about "Previous Previous Previous QTD"? Obviously the label could be improved but implementation is just an extension of Previous QTD logic.

"... problems with typical query tools ..."
Yes each SSRS report can have expression-driven parameters to select the current date, but this leads to code duplicated across the reports. Tinkering with parameters in SSRS reports on cubes is notoriously fragile, so I try to avoid it if I can. Catering for common requirements like "Current Date actually means Last Business Day" would mean to even more complexity with this approach. I'd say this complexity is probably beyond 95% of the "Report Developer" audience.

For Excel users, I have considered and tested setting the default date. What put me off that approach is that my SSAS models often have multiple Date dimensions. If you set them all to default to a single date, it becomes quite confusing for the Excel user as there are multiple "hidden" filters affecting what they initially view.

"... centralise the "well-written code" ...

I think your response has pretty much confirmed my point - you would need two separate sections of code for SSAS and SQL (written in two different languages) to produce the same result. I also think calling SQL functions would give poor performance as most reports/queries would use that logic for filtering results. The ETL approach lets you index the results, and makes them more obvious to report/query developers.


Finally, at the risk of adding more fuel to the fire, I've realised I overlooked possibly the biggest factor in favour of the ETL approach - cubes with multiple Date dimensions. Assuming you have a single Dates Dimension feeding multiple Cube Dimensions, the MDX approach will need to duplicate the code for each Cube Dimension (AFAIK MDX syntax requires Cube Dimension references). The ETL approach provides the same "Time Intelligence" logic to all Cube Dimensions (and sql queries & reports), without duplicating any code.

Mike

[Boyan Penev]

OK, let's agree on something as this conversation is getting too long.

You prefer a limited set of functionality, which I think is not enough for the users I work with, but you have decided it is for your users; while I prefer a more felxible approach.

I prefer to maintain my calculations in the SSAS or SQL layer; while you prefer to materialise parts of them, so they are more centralised for ease of maintanance.

While I disagree on all fronts, I will keep civil as I can see that arguing is futile. Furthermore, I think we've put a decent argument in front of the forum readers and I don't think we'll get anywhere from here.

[Mike Honey]

Hi Boyan,

A great summary of a very interesting discussion.

Regards
Mike

[hang]

If cube is part of the DW delivery, I would use MDX to implement most aggregate functions, especially those performance costly ones. My concern with ETL calculating the aggregates is that it would take far more time and maintenance effort than the cube. It's important to let ETL do the work to save the reporting time, however the MDX on the cube is just as fast, if not faster. I think MDX functions would be more elegant approach for the posted problem, unless cube delivery is not in the DW plan or no one would like to touch another new language.

From consistency stand point, I would try to avoid mixing aggregate calculations in both SQL and MDX, instead I would try to leverage both languages to deliver the functions at their best strength.

However, Mike has made a valid point. It would be really powerful if TSQL can access cube aggregated data or support MDX function call directly so that the problems Mike pointed out would go away.

[aldo-k]

Hi,

I actually had problem with YTD as well. I used built in time intelligence wizard for creating YTD, QTD and MTD calculations. First problem was that I was not able to see calculated measures on Date level. So I decided to go with simple calculated member, so now I could see xTD measures as they are at the moment. Built in calculation gave me in comparison full previous month (quarter, year) and compared it to current month despite the fact that current month is only half done. So this actually does not make any sense to me nor is it useful.
Another issue was that I had to change date every day, so I decided to add default member to my date dimension. As Date dimension is role playing dimension, I could not add it to dimension, but I have to use ALTER CUBE statement. I tried to add default member to date, so it is set always to yesterday and then problems started once again. Built in time intelligence wizard created member that was not aggregatable, so default member added I tried to select full year instead of "yesterday" that was my default member and I got an error stating that there is problem with non aggregatable member that is used in calculations. So basically it did not work. I tried to change aggregatable property for that member and as a result I could select whole year instead of "yesterday", but now time intelligence calculations did not work any more.
So I decided to go only with calculated members without any scope or built in calculations. That way it might not be as clean as automatically generated code, but it works, I can add default member as I need (so no more daily date changing) and it is also easier to use on different graphs in Excel or SSRS.

[Sponsored content]