accumulated sums for reporting
Page 1 of 1
accumulated sums for reporting
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.
Regards,
Rodrigo
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.
Regards,
Rodrigo
rodrigo.abt Posts : 1
Join date : 20121127
Age : 46
Location : Chile
Similar topics
» One indicator that sums up on one dimension and Averages on another
» Reporting on SCD
» Operational Reporting
» New Layer in DWH for Reporting
» Bi Temporal  As At reporting,
» Reporting on SCD
» Operational Reporting
» New Layer in DWH for Reporting
» Bi Temporal  As At reporting,
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum

