Differences between relational DB and warehouse ?

[Karol R]

Can anyone please explain me main differences between relational DB and
warehouse (Point me to web site) ?

Apart from theoretical differences I would like to know how Warehouse DB is
updated ? If data needs to remain unchanged then what is happening for
instance if in source relational DB address for a customer will change and
record gets the update. How this is loaded to DB ?

Is there any case study of SQL S2005 implementation of Warehouse, maybe
tutorial that I can fallow ?

Thanks

Q

[Plamen Ratchev]

You probably mean difference between On-Line Transaction Processing (OLTP)
and On-Line Analytical Processing (OLAP) databases. Here are a few resources
to explain each and compare:
http://en.wikipedia.org/wiki/OLTP
http://en.wikipedia.org/wiki/Olap
http://www.gnulamp.com/oltp.html
http://www.rainmakerworks.com/pdfdocs/OLTP_vs_OLAP.pdf

Operational data from OLTP databases is
extracted/cleaned/transformed/consolidated into a data warehouse database
that is used to support OLAP. There are different processes and tools to
load OLTP data into data warehouses. The frequency of updates to the data in
the data warehouse depends on business requirements, could be nightly,
weekly, etc. More about data warehouses:
http://www.dwreview.com/DW_Overview.html

In SQL Server the main tool for loading data is Integration Services (DTS in
SQL Server 2000), and Analysis Services for OLAP.
http://www.microsoft.com/technet/pro...5/dwsqlsy.mspx
http://www.devx.com/dbzone/Article/21410/1954

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[Dan Guzman]

Apart from theoretical differences I would like to know how Warehouse DB

is updated ? If data needs to remain unchanged then what is happening for
instance if in source relational DB address for a customer will change and
record gets the update. How this is loaded to DB ?

I believe you are referring to a slowly changing dimension. SSIS includes a
transform to facilitate this. See
http://technet.microsoft.com/en-us/l.../ms141715.aspx.

Is there any case study of SQL S2005 implementation of Warehouse, maybe
tutorial that I can fallow ?

See the SQL 2005 samples on Codeplex:

http://www.codeplex.com/MSFTDBProdSa...ReleaseId=4004

SQL 2008 RC0 samples:

http://www.codeplex.com/MSFTDBProdSa...eleaseId=14274

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"Karol R" <ka**********@vp.plwrote in message
news:g3*********@news.onet.pl...

Can anyone please explain me main differences between relational DB and
warehouse (Point me to web site) ?

Apart from theoretical differences I would like to know how Warehouse DB
is updated ? If data needs to remain unchanged then what is happening for
instance if in source relational DB address for a customer will change and
record gets the update. How this is loaded to DB ?

Is there any case study of SQL S2005 implementation of Warehouse, maybe
tutorial that I can fallow ?

Thanks

Q

[Karol R]

Thanks all for your response.

I'm starting to look into those web websites.

However, just to clarify, I was asking about differences between simple
relational DB and Data Warehouse.

For instance, if someone have two SQL S2005 instances and tells me that on
the instance A he has got production database and on the instance B he has
got Data Warehouse implemented then what would be main differences between
those two ?

Some how I thought that if relational DB contain tables, relations,
procedures etc. then Data Warehouse must be something else. But it is not
right ? Both contains tables, views, triggers etc, only difference is that
against DW - BI processes can be run, because data within DW are prepare to
provide information for BI, right ? (by ‘data within DW are prepare’ I mean
extracted/cleaned/transformed/consolidated ) .

Thanks again

Q

[Erland Sommarskog]

Karol R (ka**********@vp.pl) writes:

For instance, if someone have two SQL S2005 instances and tells me that on
the instance A he has got production database and on the instance B he has
got Data Warehouse implemented then what would be main differences between
those two ?

Some how I thought that if relational DB contain tables, relations,
procedures etc. then Data Warehouse must be something else. But it is
not right ? Both contains tables, views, triggers etc, only difference
is that against DW - BI processes can be run, because data within DW are
prepare to provide information for BI, right ? (by ‘data within DW are
prepare’ I mean extracted/cleaned/transformed/consolidated ) .

The Data Warehouse could be implemented on Analysis Services, in case
you have cubes with aggregated data in it.

But the DW could also be relational database where the schema is laid
out differently to make queries easier. An OLTP database is typically
very normalised, whereas the DW database is less so.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Plamen Ratchev]

Read the links I posted and you will see the differences.

In short, a Data Warehouse is relational database too, just the data is
structured differently. Depending on the purpose, data can be summarized, or
denormalized, to accommodate reporting and analysis functionality. But
fundamentally it is the same as operational database, it has tables,
relations, etc.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

[--CELKO--]

>Can anyone please explain me main differences between relational DB and warehouse (Point me to web site)? <<

Get a copy of "Analytics and OLAP in SQL"; it is a quick overview for
the SQL programmer who has been working on OLTP applications and needs
to learn the major concepts and buzz words.

>Apart from theoretical differences I would like to know how Warehouse DB is updated? <<

In bulk, with special ETL tools if you are lucky.

>If data needs to remain unchanged then what is happening for instance if in source relational DB address for a customer will change and record gets the update. How this is loaded to DB ? <<

A warehouse holds history, not the current data. Once the data goes
into the warehouse, it stays there until it ages out, perhaps to
archives. There is also a good chance that you cannot see each
customer. They might be consolidated into higher level groups ("men,
age 50-55 years, chews tobacco" or some such thing).

>Is there any case study of SQL S2005 implementation of Warehouse, maybe tutorial that I can follow ? <<

Look for articles on the Ralph Kimball website. Frankly, SQL Server
is not the choice for a real Data Warehouse. Look at Teradata, SAND,
WX2 and other products that are built for this kind of work. Even DB2
and Oracle are also better choices. DB2, for example, has an
optimizer that detects Star schemas and generates special access
methods and joins for them.

[Plamen Ratchev]

"--CELKO--" <jc*******@earthlink.netwrote in message
news:14**********************************@z66g2000 hsc.googlegroups.com...

>
Look for articles on the Ralph Kimball website. Frankly, SQL Server
is not the choice for a real Data Warehouse. Look at Teradata, SAND,
WX2 and other products that are built for this kind of work. Even DB2
and Oracle are also better choices. DB2, for example, has an
optimizer that detects Star schemas and generates special access
methods and joins for them.

SQL Server 2008 offers Star Join query optimization too:
http://technet.microsoft.com/en-us/m...chNet.10).aspx
Plamen Ratchev
http://www.SQLStudio.com