Hello,

This is an opinion question i guess : i am about to write
a multitable lookup that looks at 7 differant tables
basically to think about writing a timetable lookup. There
are 2 tables of 1 to 1 and 5 of 1 to many.

The results will be called via asp.net web site; and the
number of queries will be huge. (>10k a day typically) The
data volume is fairly signifcant; typically 1million
records will be in each query.

Is it better to write a stored procedure which creates
temp tables or to move over to analysis services and have
a suitable cube of all the data. Oh and to make it
slightly worse the data is partioned accross many tables
(e.g. contacts are in 5 partioned tables) Also how hard
will it be to maintain the cube if we need to add an eigth
or ninth dimension

Hi Chris,

Do you mean that you want to create a Decision-Support system? If so,
Analysis Services can provide more flexibility and easy maintenance.

It is also relatively easy to administer cube structure. If you add new
dimensions, you need to reprocess the cube.

It is really difficult to say what is better, as it really depends on your
actual requirement. However, according to my experience, OLAP solution is
easier to maintain and change than ASP.NET solutions, when it comes to DSS
systems.


Bill Cheng
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Chris Hoare" <c.h0are@ntlworld.com>
| Sender: "Chris Hoare" <c.h0are@ntlworld.com>
| Subject: Temp Tables or Cube ?
| Date: Wed, 23 Jul 2003 03:41:49 -0700
| Lines: 19
| Message-ID: <09ad01c35107$056baef0$a601280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNRBwVr/M7zD8yOSZ6ToDU8LGwQfw==
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.datawarehouse:17770
| NNTP-Posting-Host: TK2MSFTNGXA14 10.40.1.166
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Hello,
|
| This is an opinion question i guess : i am about to write
| a multitable lookup that looks at 7 differant tables
| basically to think about writing a timetable lookup. There
| are 2 tables of 1 to 1 and 5 of 1 to many.
|
| The results will be called via asp.net web site; and the
| number of queries will be huge. (>10k a day typically) The
| data volume is fairly signifcant; typically 1million
| records will be in each query.
|
| Is it better to write a stored procedure which creates
| temp tables or to move over to analysis services and have
| a suitable cube of all the data. Oh and to make it
| slightly worse the data is partioned accross many tables
| (e.g. contacts are in 5 partioned tables) Also how hard
| will it be to maintain the cube if we need to add an eigth
| or ninth dimension
|