Calculations from a Table vs On the fly

Question

Calculations from a Table vs On the fly

Attopeu

Ten Centuries

Points: 1308
More actions
November 1, 2011 at 8:17 am

#390735

Hi Guru,
I need to develop a new report for sales people. This report will do lots of calculations from backend stored procedure to get totals sales, bonus, compensation etc... for daily basis. What is the best option here on developing stored proc?
1. Doing calculations on the fly?
2. Create a Calculation table and store daily transactions on this tab? Then pull this already calculated data from this table.
Thanks much,
Attopeu,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

It depends on how much data you have. If it is not such a big table from which you are calculating you could do it on the fly. With the right index support it will not be that costly. You could also take a look at Computed columns and see if that will help you.

If the table is pretty big and your server is showing issues with performance, you could have a night job that aggregates data. The draw back is that you will not have the data from the current day.

My 2 cents

-Roy

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 2

Howdy,

My 2 cents is build now to scale later.

Store data on the most granular level.

Based on your requirements, cubes will be ideally suited.

Different dimensions can later be added to view sales .

Cheers,

Shanu

Jared SSC Guru Points: 61793 More actions · Answer 3

This really depends on how fast the stored proc retrieves data versus what the business expects for speed. If they want a report to run in 2 seconds, and the stored proc takes 2 minutes you have a problem. However, you also have to weigh that against you resources and availability; i.e. how often does the data change and how current does the report have to be? Several factors weigh in on how you should proceed.

Thanks,

Jared

Jared
CE - Microsoft

Attopeu Ten Centuries Points: 1308 More actions · Answer 4

Hi All,

Thanks for all your reponses. I know for sure OLAP Cube is not the solution here because we are making changes on existing reports. My very concern now is there will be lots calculations for salespeople as below:

-Weekday

-Weekend

-HolidyWeekday

-HolidayWeekend

and a few more.

For sure, the table will grow very large as well.

I am not leaning toward storing calculation in the table.

Any other advise?

Thanks,

Attopeu

Jared SSC Guru Points: 61793 More actions · Answer 5

Attopeu (11/4/2011)
Hi All,
Thanks for all your reponses. I know for sure OLAP Cube is not the solution here because we are making changes on existing reports. My very concern now is there will be lots calculations for salespeople as below:
-Weekday
-Weekend
-HolidyWeekday
-HolidayWeekend
and a few more.
For sure, the table will grow very large as well.
I am not leaning toward storing calculation in the table.
Any other advise?
Thanks,
Attopeu

Coming from a sales company,I will tell you what we did. Since it is daily you can calculate all statistics for a day and store into a table. This way, you are not calculating all at once on the fly, but each day and adding to the table. That is the safest bet. For reports that need to be updated immediately, use optimized stored procedures (i.e. test temp table vs temp variable vs cte for speed) and make sure your indexes are all created properly.

For really heavy reports, it is sometimes best to have a web/windows application do the calculations for you and return the results.

Thanks,

Jared

Jared
CE - Microsoft

shanu.hoosen SSCrazy Points: 2227 More actions · Answer 6

Another option apart from all listed above is an aggregated table. All this does is stores the calc result by the group by you select/chose.

As a rule of thumb, you aggregate up the time scale. Eg. store your data on a weekly level with all other measures and keys