Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Cumulative measures

2 posters

Go down

Cumulative measures Empty Cumulative measures

Post  Orrill 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

Back to top Go down

Cumulative measures Empty Re: Cumulative measures

Post  Mike Honey 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
Mike Honey

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

Back to top Go down

Back to top

- Similar topics

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