Cumulative measures

Go down

Cumulative measures

Post  Orrill on Thu Nov 21, 2013 5:51 am

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.



Posts : 1
Join date : 2013-11-21

View user profile

Back to top Go down

Re: Cumulative measures

Post  Mike Honey on Thu Nov 21, 2013 7:33 pm

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 Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile

Back to top Go down

Back to top

- Similar topics

Permissions in this forum:
You cannot reply to topics in this forum