# accumulated sums for reporting

## 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 : 2012-11-27

Age : 41

Location : Chile

Similar topics

» Cenotaph Vandal Jailed

» how to generate xslt reports in testng?

» Beginner Selinium Webdriver

» How to use ANT build to create HTML reports in Eclispe

» which defect tracking tool using in selenium?

» how to generate xslt reports in testng?

» Beginner Selinium Webdriver

» How to use ANT build to create HTML reports in Eclispe

» which defect tracking tool using in selenium?

Page

**1**of**1****Permissions in this forum:**

**cannot**reply to topics in this forum