Prev: Kuljetusalan ongelmista
Next: Transact-SQL for Restore Filelistonly using a variable
From: Karol R on 21 Jun 2008 14:04
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


From: Plamen Ratchev on 21 Jun 2008 15:52
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/prodtechnol/sql/2005/dwsqlsy.mspx
http://www.devx.com/dbzone/Article/21410/1954

HTH,

Plamen Ratchev
http://www.SQLStudio.com

From: Dan Guzman on 22 Jun 2008 09:34
> 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/library/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/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

SQL 2008 RC0 samples:

http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=14274

--
Hope this helps.

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

"Karol R" <karol.rolski(a)vp.pl> wrote in message
news:g3jfr3$ko$1(a)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
>
>

From: Karol R on 22 Jun 2008 14:30
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


From: Erland Sommarskog on 22 Jun 2008 14:30
Karol R (karol.rolski(a)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, esquel(a)sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 |  Next  |  Last
Pages: 1 2
Prev: Kuljetusalan ongelmista
Next: Transact-SQL for Restore Filelistonly using a variable