I am looking for optimized solution for the below problem -
I have a cube which contains Date dimension which has one hierarchy month->quarter->year.
I have created a cube, all good. Now my ssrs report requires all my dimension customer sales attributes along with the calculations like last month profit, current month profit, last 6 months profit (listing every month), last month transactions, last year profit vs current year profit.
As i have created a hierarchy all my calucations for every month and every year and every quarter already pre-calculated in the CUBE. Now to retrieve the above listed calculations, what is the best way to do these time calculations? Where to put these calculations in CUBE or in the SSRS DATASET?
Could you please suggest some good approach and how to do it?
Although it's certainly possible - if you're comfortable with MDX - to write your query to get all these calculations into your dataset, it's usually better for reusablity across many reports to put these types of calculations in the cube. That way, other tools can also use the time calculations if you decide to use Excel or a 3rd party application.
You can use the Business Intelligence Wizard from the Calculations tab to do this - here's an article for SSAS 2005 that's still applicable to later versions: http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf
And here's another approach to do something similar: http://www.bidn.com/articles/mdx-and-dmx/169/mdx-time-calculations-done-the-right-way
UPDATE: For named sets to handle date ranges like the last 6 months from now, see this article: http://my.opera.com/duncans/blog/using-custom-sets-in-analysis-services-to-deliver-special-date-ranges-to-end-use