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
SteveI 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

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

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
SteveI 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

ADS user and sql 2005

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).
Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.
Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?
TIA
RobMore Info:
I checked the server log and the error is state 6. I found a blog on
MSN and it says state 6 is 'Attempt to use a Windows login name with
SQL Authentication'.
Right, exactly what I thought I wanted to do.
I thought that when I added a windows user to a sql servers security
and login, that windows user can access the sql server??|||You need to give this user explicit credentials, typically make hime a
member of a role which has the right you need.

In SQL 2000 you do this under security. It's quite simple.

Regards,
Henrik

*** Sent via Developersdex http://www.developersdex.com ***|||rcamarda (robc390@.hotmail.com) writes:

Quote:

Originally Posted by

I wish to use something other than sql's SA account user to connect to
my data warehouse, so I created a user in our active directory user.
Ill use dw as the new user as example.
after I created the user, dw, in ADS, I added the user via Management
Studio in SecurityLogins.
I grant ower of ads\dw to my datawarehouse.
I try to connect to the database engine using SQL Servier
Authentication, Login: ads\dw.
I get Cannot connect to xxxx, Login failed for user 'ads\dw' (Microsoft
SQL Server, Error: 18456).

Mixing apples and oranges, I see. To log into SQL Server as ADS\dw,
you need to be logged into Windows as ADS\dw. That's what integrated
security is all about. By already being authenticated by Windows,
there is no need for SQL Server to authenticate you again. But you
cannot log into SQL Server with another Windows login than the one
you are logged into Windows with. You can only log into SQL Server
with an explicit username/password with an SQL login.

Quote:

Originally Posted by

Next, I add this user to the local server's administrators group (the
server is in admin mode) and login.

And dw now has sysadmin rights in the server, unless you remove
BUILTIN\Administrators.

Quote:

Originally Posted by

Now I can connect to the database as user dw. ( i suspect the users
memebership of administrator is the reason).
I dont wish to have the dw user part of administrator, but I want it to
have control over just the datawarehouse database.
What am I doing wroing?

First descide whether it's a Windows login or an SQL Login you want.
Next grant this user access to the server and database. Next you grant
him CONTROL on the database. (You are on SQL 2005, right?)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,
Yes I am on sql 2005. I am using Cognos' ReportNet (Now Cognos8) to
connect to its Content Store, a database. I need to provide a user and
password. I thought I would set up a user on ADS and provide the
account and password.
Out of confusion/frustration/ignorance I created a local user within
SQL server and it works just fine.
(I have several SQL servers for the database, and I thought using ADS
for user logins and authentication would be better).
So, was my problem more to do with trying to connect as another windows
user with the SQL Management tool? (I did not try to configure Cognos
since I could not connect via the SQL Studio)
Thanks for your help and any other pointers
Rob