Re: Designing a Reporting Database
Date: Sat, 21 Jul 2001 23:25:59 GMT
Message-ID: <3b0d425f_1_at_news3.prserv.net>
Todd,
When I mentioned OLAP I wasn't specifically talking about Analysis Services, although I can certainly see the confusion. OLAP applications have been implemented on relational platforms, so I was asking whether the application itself was originally OLTP, and now the same set of tables are being considered for DSS / OLAP purposes.
It this is indeed the case, then I would certainly suggest, as I mentioned in the earlier post, seriously considering not hosting both OLTP and DSS / OLAP applications on the same server in the same database.
SQL Server 2000 does indeed support indexed views.
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.0105240743.3263dfe3_at_posting.google.com...
> 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:59 CEST