Re: Designing a Reporting Database

Thanks for the help BP...I had no idea the can of worms I'd be opening! :) First of all, the datasource I am asking about here is SQL Server 2000. We are planning on moving to Oracle 9i after this version of the product. Please don't ask me about why we're doing that. For know I am merely concerned with SQL Server 2000.

> 1. There seems to be a contradiction between "the system contains about 100
> tables" and "how to setup the database portion" ... if the tables already
> exist, what is it you are setting up?
What I meant was the the core data tables have been created ("about 100") and I am now looking at an efficeint way of reporting off of these tables...be it views, other tables, etc.

> 2. Is this system supposed to do both OLTP and DSS / OLAP, or is it strictly
> DSS / OLAP?

We are not looking at OLAP yet. It is a possibility for the future but at this time it is not.

> 3. If it is both OLTP and DSS / OLAP, is it an option to split the two
> functions into separate servers, and perhaps have replication of some other
> process keep them in sync?
>
> Just a comment ... excluding indexed views, well-written views by themselves
> have no impact on performance. SQL Server takes the view definition and
> substitutes it into the query before creating a query plan.
>
> 4. What version of SQL Server are you using? If it's SQL Server 2000, are
> you familiar with indexed views?
That was something I was wondering about. I was fairly certain the indexed views were not in SQL Server 7.0. I didn't know if they existed in 2000. If they do, I believe you have already answered my question.

Please let me know though about any ways you may have done this in the past. Thanks.

>
> There are probably a load of other pertinent questions, but these are the
> first few off the top of my head.
>
> If you respond to this, please don't just answer with Yes / No type
> responses, but please supply some "meat" also. If you want us to spend time
> answering, then please spend time providing sufficient background.
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "Todd Rubel" <trubel_at_oakgroup.com> wrote in message
> news:d272bee7.0105231000.48bf2ea3_at_posting.google.com...
> > I need to create several reports for a new system I'm implementing.
> > The system contains about 100 tables and I need to create the most
> > effecient reporting system possible.
> >
> > I'm looking for advice on how to setup the database portion of this
> > application. I was thinking about using views but I am afraid of the
> > inefficiency. Next I was thinking about using separate tables written
> > with lots of triggers.
> >
> > Any advice from projects you've worked on or ideas you've heard would
> > be greatly appreciated. Thanks.
Received on Sun Jul 22 2001 - 01:25:57 CEST