473,779 Members | 2,023 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Oct 10 '06 #1
1 1421

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
Oct 13 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1577
by: expecthealth | last post by:
JOB DESCRIPTION Data Warehouse Architect (Permanent, Full-Time) ______________________________________________________________________ _________ Position Description: The Data Warehouse Architect is responsible for design, specifications, development, testing and deployment for Oracle data warehouse functionality, reports, extracts and interfaces. Leads
14
3444
by: Jeff Boyer | last post by:
Hello everyone, I have recently developed a web application across my clients intranet. We used ASP with Interdev 6.0 and SQL server as the backend. They have now come to me asking me to develop some reports that can be accessed via a drill down within the app. They don't have Crystal Reports so this is not an option. Can anyone suggest an alternative to using crystal reports to publish dynamic reports via the web and ASP?
7
3577
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowing that to the backwoods manager who knows little of technology that new innovative names for old concepts would help to sale their products. I mean...
7
2241
by: Will | last post by:
On the subject of Data Warehouses, Data Cubes & OLAP…. I would like to speak frankly about Data Warehouses, Data Cubes and OLAP (on-line analytical processing). Has it dawned on anyone else that these buzz words were created by some geek who decided to take a stab at marketing? Knowing that to the backwoods manager who knows little of technology that new innovative names for old concepts would help to sale their products. I mean...
2
3021
by: Steve_CA | last post by:
Hello all, I just started a new job this week and they complain about the length of time it takes to load data into their data warehouse, which they do once a month. From what I can gather, they rebuild the indexes before the insert with an 80% Fillfactor, then insert the data (with the indexes enabled), then rebuild the indexes with a 100% Fillfactor.
0
1465
by: DB2 DBA wanted | last post by:
Maines Paper & Food Service is a $2 billion/yr food distribution company with 9 distribution centers in the US. We are currently interviewing for the position detailed below. Relocation to Binghamton, NY will be necessary. If interested, please forward a resume to Bill.Kimler@maines.net ========================================================== Job Title: Database Architect
2
4357
by: phantom | last post by:
Hi all. We have a production data source running postgres for reporting/DSS we are thinking on building a warehouse, wherein data would be transferred from postgres to the warehouse. The datawarehouse would be implemented in oracle as the plans stand. I am thereby looking for a solution to relay data between the two, although the techdocs do mention a way to generate interbase sql and carry out the migration, not sure if that is the...
17
2649
by: IanIpp | last post by:
We have a 3 month old quad processor/dual core server running SQL Server 2005 and already it is getting close to hitting the CPU wall. An 8 way CPU box is prohibitively expensive and out of the question. I am looking desperately for a way to TRULY scale out SQL server...in the same way that IIS can be scaled out via App Center. The "in the box" solution for SQL Server 2005 scaling out is the DMV. Unfortunately this solution makes...
1
6302
by: russzee | last post by:
Hello, Can I import an OLTP (Reltional DB) as a Data Source into SQL Server Analysis Services 2005 and then use the Cube Wizard and the new Data Source View feature to create the OLAP model ? Or do I have to first design an OLAP Data Warehouse with a Star Schema and then import this DW as a Data Source into my Analysis Services Project.
0
9632
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10302
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10071
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8958
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7478
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6723
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5501
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4036
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3631
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.