I'm about to embark on re-writing a database & bespoke web reporting application for our call centre & would like a little advice please.
Currently the database has 10 tables containing summaried (<=1 record per staff member per day) data from different legacy systems, populated by DTS. There is an 11th table that has staff data in which is used to link the others together as many have different primary keys. After the data has been linked together an aggregated table (1 record per person per day) is created once a day.
Currently our intranet site is configured to run a number of stored procedures that return KPI data from the aggregated table into datasets which are then rendered in the form of datagrids. Users are either allowed to specify the parameters for these stored procedures or they are pre-determined for them depending on who they are (eg agents in the call centre all see a MTD report for themselves only).
The aim of the re-write is to
(a) cut down on admin when KPI definitions change
(b) make the setup much more generic so that it could be transported to other areas of the business or even to different companies with minimum rework
(c) upgrade from SQL 2000 to SQL 2005
(d) tidy the webpages a little & maybe add some gauge type controls
I'm unsure about 2 things -
(1) Should I totally re-design things & use Analysis Services instead or would I find no benefit as everyone is only given one view of the truth (ie no slicing & dicing depending upon preference)? I know very little about this service so it would be a challenge & from what I've read I'm not so sure whether it would be appropriate for all of the staff querying the database constantly anyway(there are over 500 of them & currently the stored procedures use nested temp tables to calculate everything that needs to be shown on the webpages). I guess that I couldn't fill a datagrid with their data using this method either but I'm sure that someone will be able to keep me right.
(2) Should I dump the datagrids in favour of Reporting Services? This was originally not used as our IT department could get it installed properly on the SQL 2000 server & the datagrid solution was found to be both adequate & easy to setup. We have Crystal Reports in the company also but licence costs are likely to be a problem.
I've cross posted this message to the .Net and SQL Programming groups as it's a bit of both & I'd like a balanced view on things before I start work.
TIA
Steve
Currently the database has 10 tables containing summaried (<=1 record per staff member per day) data from different legacy systems, populated by DTS. There is an 11th table that has staff data in which is used to link the others together as many have different primary keys. After the data has been linked together an aggregated table (1 record per person per day) is created once a day.
Currently our intranet site is configured to run a number of stored procedures that return KPI data from the aggregated table into datasets which are then rendered in the form of datagrids. Users are either allowed to specify the parameters for these stored procedures or they are pre-determined for them depending on who they are (eg agents in the call centre all see a MTD report for themselves only).
The aim of the re-write is to
(a) cut down on admin when KPI definitions change
(b) make the setup much more generic so that it could be transported to other areas of the business or even to different companies with minimum rework
(c) upgrade from SQL 2000 to SQL 2005
(d) tidy the webpages a little & maybe add some gauge type controls
I'm unsure about 2 things -
(1) Should I totally re-design things & use Analysis Services instead or would I find no benefit as everyone is only given one view of the truth (ie no slicing & dicing depending upon preference)? I know very little about this service so it would be a challenge & from what I've read I'm not so sure whether it would be appropriate for all of the staff querying the database constantly anyway(there are over 500 of them & currently the stored procedures use nested temp tables to calculate everything that needs to be shown on the webpages). I guess that I couldn't fill a datagrid with their data using this method either but I'm sure that someone will be able to keep me right.
(2) Should I dump the datagrids in favour of Reporting Services? This was originally not used as our IT department could get it installed properly on the SQL 2000 server & the datagrid solution was found to be both adequate & easy to setup. We have Crystal Reports in the company also but licence costs are likely to be a problem.
I've cross posted this message to the .Net and SQL Programming groups as it's a bit of both & I'd like a balanced view on things before I start work.
TIA
Steve