accumulated sums for reporting

View previous topic View next topic Go down

accumulated sums for reporting

Post  rodrigo.abt on Tue Nov 27, 2012 7:09 pm

Hello all,

I'm working on a project as a data mart and BI architect. The data mart will be used as an input for a series of fixed reports that will serve as a proof of concept for a future BI solution. One of the reports involves the creation of moving accumulated sums with variable ranges. Just for simplicity suppose the data comes from a fact table like this:

id, qty, period
1, 2, 201112
1, 3, 201111
1, 1, 201110
1, 5, 201108
1, 3, 201104
1, 1, 201103

Notice the gaps between periods. What I want is to compute accumulated sums for contiguous periods, accounting for gaps. For example, if the user selects 3 periods starting from 201112, the final qty sum should be 6 (sum of qty from 201112 to 201110). If a period is not reported the qty is supposed to be 0. So, I want my output to look like this:

id, analysis_period, qty_sum
1, 201112, 6
1, 201111, 4 <= 3 in 201111 + 1 in 201110 + 0 in 201109 (no data)
1, 201110, 6
1, 201109, 5 <= new row which represents qty sum between 201109 and 201107
1, 201108, 5
1, 201107, 0
1, 201106, 3
1, 201105, 4
1, 201104, 4
1, 201103, 1

In OLTP scenarios I normally solve this with a (big) query involving window functions like lead and lag and using conditional sums. My question is:

What do you think is the best data mart design strategy to deal with these kind of calculations?. I thought I could use aggregated fact tables but if I don't know beforehand the ranges involved. Another option I thought was to insert empty rows with ETL accounting for missing periods and enforce a strict row ordering. In this case you could solve the requirement just by retrieving as many contiguous rows as requested, but I don't think this could be a very reliable solution.

So far I'm considering a mixed scenario where I insert rows for missing periods and then use windowing functions without conditional sums.

I kindly apprecciate your input.



Posts : 1
Join date : 2012-11-27
Age : 41
Location : Chile

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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