3 Terabyte data warehouse system requirement

d wrote:
 > I expect a 3 terabyte warehouse would need no less than
....
 > SAN storage for data, logs, backups

I would expect SANs to be exceptionally unsuited for
datawarehousing.

Stephan
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

 > What do you think?

hmmm, I think it depends on:
- data model
- database server features - parallelism, partitioning,
clustering, materialized views with query rewrite, etc
- query frequency
- query resource impact
- query performance goals
- data rolloff/archival requirements
- data load frequency, impact
- aggregation frequency, design

There are so many variables, that I almost always prefer to build a
series of prototypes, and target a very scalable solution: that way
you can initially go with cheaper hardware, and lower dbms cost, but
can increase incrementally as usuage increases.

Also, any time you combine the warehouse with the mart you're asking
for trouble. My preference is to keep them separate - the warehouse
becomes the simplest component in the architecture and it solves a ton
of problems. It also means that you can quickly recreate a mart with
new rules over a weekend, can support multiple marts on smaller servers
so that different departments have greater control over their
performance, can have smaller/simpler marts for crystal & excel, maybe
an all-summary mart for some other application, etc, etc.

buck
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

Nigel wrote:
 > As this app uses Analysis Services, most of these questions are not
 > applicable. I assume all the queries would be against MOLAP cubes
which,
 > in most cases, will respond very quickly.

hmm, I've never heard of a 3 tb molap cube, let alone it responding
very quickly.
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

wrote in message

  >> What do you think?
 >
 > hmmm, I think it depends on:
 > - data model
 > - database server features - parallelism, partitioning,
 > clustering, materialized views with query rewrite, etc
 > - query frequency
 > - query resource impact
 > - query performance goals
 > - data rolloff/archival requirements
 > - data load frequency, impact
 > - aggregation frequency, design
 >
 > There are so many variables, that I almost always prefer to build a
 > series of prototypes, and target a very scalable solution: that way
 > you can initially go with cheaper hardware, and lower dbms cost, but
 > can increase incrementally as usuage increases.
 >
 > Also, any time you combine the warehouse with the mart you're asking
 > for trouble. My preference is to keep them separate - the warehouse
 > becomes the simplest component in the architecture and it solves a ton
 > of problems. It also means that you can quickly recreate a mart with
 > new rules over a weekend, can support multiple marts on smaller
 > servers so that different departments have greater control over their
 > performance, can have smaller/simpler marts for crystal & excel, maybe
 > an all-summary mart for some other application, etc, etc.

As this app uses Analysis Services, most of these questions are not
applicable. I assume all the queries would be against MOLAP cubes which,
in most cases, will respond very quickly.
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

 > Analysis Services stores data a lot more efficiently than relational
databases
do, even if you load all the row-level detail into the cube, which is
 > unlikely

But I think that's related to the questions that need to be asked: will
the queries be heavily-hitting the details? How much will the data
churn? 10 million rows / day? etc, etc.

At least before you spend several million in hardware. Again, a series
of prototypes would be invaluable to eliminating risks stemming from
these unknowns, or possibly-changing answers.

buck
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

wrote in message

 > Nigel wrote:
  >> As this app uses Analysis Services, most of these questions are not
  >> applicable. I assume all the queries would be against MOLAP cubes
  >> which, in most cases, will respond very quickly.
 >
 > hmm, I've never heard of a 3 tb molap cube, let alone it responding
 > very quickly.

The data would get a lot more compact with MOLAP storage -- typically
one could expect between a four- and ten-fold contraction. Analysis
Services stores data a lot more efficiently than relational databases
do, even if you load all the row-level detail into the cube, which is
unlikely. You can always drill down to detail level when it's needed,
which probably isn't very often.

And I assume the data would be loaded into multiple smaller cubes and
partitions, rather than one humongous hypercube which would be very slow
to build/update.
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

wrote in message

  >> Analysis Services stores data a lot more efficiently than relational
 > databases
 > do, even if you load all the row-level detail into the cube, which is
  >> unlikely
 >
 > But I think that's related to the questions that need to be asked:
 > will the queries be heavily-hitting the details? How much will the
 > data churn? 10 million rows / day? etc, etc.
 >
 > At least before you spend several million in hardware. Again, a
 > series of prototypes would be invaluable to eliminating risks
 > stemming from these unknowns, or possibly-changing answers.

Yes, I agree; it's just that you ask a different set of questions if
it's a MOLAP solution, and test different scenarios. You certainly don't
breeze into an app of this size with as little information as the OP
provided.
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

Nigel Pendse wrote:

  > > hmm, I've never heard of a 3 tb molap cube, let alone it responding
  > > very quickly.
 >
 > The data would get a lot more compact with MOLAP storage -- typically
 > one could expect between a four- and ten-fold contraction.

The problem is that the contraction is very dependent on the dimensional
model. And a 300 GByte MOLAP cube still won't fit in ram on a current
machine.

Stephan
 >> Stay informed about: 3 Terabyte data warehouse system requirement 

"Stephan Eggermont" wrote in message



   >>> hmm, I've never heard of a 3 tb molap cube, let alone it responding
   >>> very quickly.
  >>
  >> The data would get a lot more compact with MOLAP storage -- typically
  >> one could expect between a four- and ten-fold contraction.
 >
 > The problem is that the contraction is very dependent on the
 > dimensional model. And a 300 GByte MOLAP cube still won't fit in ram
 > on a current machine.

Most MOLAP cubes don't fit in RAM -- why should this be a problem with
Analysis Services? Only a few OLAP servers, like TM1 and Alea, require
OLAP cubes to fit into RAM. Analysis Services cubes can be far larger
than the available RAM.
 >> Stay informed about: 3 Terabyte data warehouse system requirement