Database Design - newbie question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nuala - 17 May 2005 18:25 GMT

Hi ,

First of all my apologies for this very basic question but I can n't find a
definite answers to this question.

I'm new to data warehousing and have started reading "The Data Warehouse
Lifecycle Toolkit" by Kimbal (and others). From it I understood that the
database in your application (normalised) is very differenent to the one in
your warehouse (denormalised). But from reading about SQL Server analysis
server it does n't seem to matter what you use as your source as you can
create your cubes from an application database design.

Is this right? are there any drawbacks to this. It would mean that we won't
need another table design for our warehouse database.

Thanks,

Signature

Nuala

Reply to this Message

JT - 17 May 2005 19:17 GMT

What differentiates the data warehouse design from the online transactional
database is the following:
1. To optimize the OLTP database, historical records are often migrated
to the warehouse, and it is from here that trend reports / analysis are
generated. Also, the indexing on the warehouse is optimized for analysis
purposes. The warehouse typically has much more indexing.
2. The warehouse databases are typically located on a different server
that is used exclusively for reporting and analysis. This reduces the load
on the OLTP server.
3. To improve the performace of analysis, data in the warehouse may be
stored in multiple levels of summarization, and most analysis may only be
derived from the smaller summarized datasets.

So the idea of data warehousing is that you perform your analysis against
summarized and highly indexed data instead of at the transactional level.

> Hi ,
>
[quoted text clipped - 12 lines]
>
> Thanks,

Reply to this Message

anders t - 17 May 2005 22:45 GMT

Quoting JT in microsoft.public.sqlserver.datawarehouse:

>So the idea of data warehousing is that you perform your analysis against
>summarized and highly indexed data instead of at the transactional level.

Recommended reading (since I've just read it... :) )

"The impact of alternative diagrams on the accuracy of recall:
A comparison of star-schema diagrams and entity-relationship diagrams"

Corral et al., ' www.sciencedirect.com ' .

Signature

All that we see, or seem,
is but a dream, within a dream,
installed by the Machine

Reply to this Message