Cumulative measures

Hi all,

I'm currently working on a project that requires many cumulative measures, including cumulative averages.

My question is:

Where would be best to calculate my cumulatives, In the database itself using SQL or within the cube?

I started with the cube approach, however, when creating cumulative averages I get told I cannot use the aggregate function with calculated measures.

My other option would be to create the averages within the SQL, and then apply cumulatively within the cube.

Any insight would be much appreciated.

Thanks.

Hi Orrill,

"the cube" is very vague, but generally I would not put cumulative logic into either SQL data or a cube. It is usually too cumbersome and restrictive when done that way to be of use.

Most query tools can handle cumulative logic, e.g.:
- SSRS: RunningValue function
- Excel Pivot Tables on an SSAS cube: Show Values as ... Running Total
- SQL Server 2012+ : SUM (x) OVER ( ORDER BY y)

All of these techniques give the data analyst control over the cumulative calc rules, which will always be a more flexible and practical solution than anything you pre-build. They also typically involve much less design and processing effort.

Good luck!
Mike