Advice on Data Warehouse & Web Reporting

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.
Hope I haven't upset anyone by crossposting the question - I'm just
after a balanced view before I start work & the queries fit with a few
different ng's.
TIA
Steve
I think that AS is more important; more critical-- than RS.
there are other tools like RS on the market.
but AS leads the market by a wide margin.
Does that mean it's EASY? no. Does it mean it's SIMPLE? no.
I would reccomend taking a month off of work; immersing yourself in
SSAS and coming back to work to scrap all your existing DB work.
10 million relational developers CAN be wrong and they are.
It's better to build a solution for non technical people-- SSAS is best
utilized using OWC - Office Web Components- and non-technical people...
All of your relational mess just sounds overly complicated.
-Aaron
C4rtm4N wrote:
> 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.
> Hope I haven't upset anyone by crossposting the question - I'm just
> after a balanced view before I start work & the queries fit with a few
> different ng's.
> TIA
> Steve