Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
BruceT
Yak Posting Veteran
78 Posts |
 Posted - 2011-08-26 : 16:14:09
|
| Hi all,Given transaction tables with several million rows holding insurance charges, payments, adjustments etc. The requirement is a managers screen that summarizes the various transactions on user selected criteria. Say monthly and /or for particular insurers and /or particular departments etc. Requirements are not completely firmed up yet, but it will be along those lines. The manager(s) would probably want update to date totals on a daily basis.Does anyone have any input on whether a summary table(s)/view(s) would be preferable given it/they would need to be maintained/queried regularly or queries summarizing the detail transactions on the fly as requested by the user?I lean toward queries against the detail as I hate essentially storing data twice and maintaining summary totals, but was interested in your input. Also, performance is going to be a very important constraint on this.Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-27 : 04:55:50
|
| are you planning to implement it in same db where you'll be storing application data? Ideally we design an OLAP system for handling reporting/analytic requirements as long as its quite large. In such cases, depending on how complex your calculation/aggregation are we may choose to design aggregation tables which will be populated by means of overnight jobs. One thing to keep in mind is volatility of data if you're implementing it in OLTP itself and also up to what level you want data to be up to date. The on the fly queries are fine as long as response times for them are low and also amount of data is low.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-08-29 : 14:42:26
|
| Thanks for the responses. Yeah, I thought about warehousing it, but the data is going to be live. Meaning services waiting to be charged, charges waiting to be billed, charges billed and awaiting payment, payments needing to be applied, open receivables by month, by department, by staff, revenues by the same, etc.In a nut shell, it can be historical data, but is going to include live data as well. Looks like they want 3 types of dashboard data, historical totals, current totals, and future projections based on historical trends (including current totals).Thanks again |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-30 : 09:49:44
|
| for services waiting to be charged, charges waiting to be billed etc upto what level of accuracy you're looking at? for all the other cases (dashboard data, historical totals, current totals, and future projections based on historical trends ) I feel like a OLAP system should suffice------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
BruceT
Yak Posting Veteran
78 Posts |
Posted - 2011-08-30 : 11:46:52
|
quote:
Originally posted by visakh16
for services waiting to be charged, charges waiting to be billed etc upto what level of accuracy you're looking at? ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
As up to date as end of day yesterday.Thanks visakh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-30 : 12:43:07
|
| then OLAP should be enough for you------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Summary table(s) or not?