hi all,

I have some questions about datawarehouse systems in which I am
recently involved in a DW project...

waiting for your help

thnx!


1. when data is extracted,transformed and loaded from the OLTP what is
the data model in datawarehouse.
I mean, is the OLTP relational model preserved in DW tables.if not, how
are new DW tables related to each other.

2. is it true that you only use the star/snowflake schemas when you are
modeling a datamart from the data in the datawarehouse.

3. does any ETL process is used when the data from DW is exctracted to
logical datamarts.

Ozgur

responsible for upholding the enterprise business rules.
It's really just a data store to support reporting over
time. Whether you decide to implement a data structure
based on normalization or a data structure based on
dimensional design principles depends on all sorts of
business requirements and design considerations too detailed
for this forum.
2. Not if you subscribe to Kimball's EDW theories. Ralph
has come out with his conformed dimension approach to all
linkage across fact tables. I'm not a subscriber though. I
believe you build your warehouse in a normalized fashion and
if you want, build a highly specialized data mart from the
warehouse as you propose. There is also the concept of
operational data mart which is populated from the ODS.
3. I don't believe in logical models for data warehouses
unless you're specifically trying to build an enterprise
logical data model. Your purely a reporting data structure,
so you generally aren't implementing business rules.

You wouldn't use a data model created for OLTP for a data warehouse.
The T in ETL is Transform, so the answers to (1) are a) usually a
dimensional model and b) No and c) by the transformations you have
defined in your ETL process.

The answer to (2) is No, your datamart doesn't have to use a
star/snowflake schema. It might be a cube or it might take some other form.

Regarding (3), you wouldn't *extract* to a *logical* datamart. You'd
extract only to an actual, real datamart.

Seth Grimes

ozgurkalay (AT) gmail (DOT) com wrote:

take advantage of parallel processing.

ozgurkalay (AT) gmail (DOT) com schrieb:
quite simple - The data would grow and and compliance to legal and other
rules means the OLTP database will become unusable one day.
Reporting directly on operative databases will impact the users and
requirements for datawarehouse databases and OLTP databases are directly
contradictionary. Also data must be static - there is no real
analytics if information is not validated. (With some exceptions e.g.
real time trend analysis)

Also a datawarehouse feeds usually on more than one operative system
this leads to
*Transformation* and Transformation means to normalise the data - e.g.
provide surrogate keys to keep the data independend, to get into a same
data same meaning situation e.g a gender is stored as M/F in one source
and 0 / 1 in another.
Finally - data must be loaded in the DWH and if required aggregation
processes must run. Most and challenging part is to get the data in
fast because the loading window is sometimes quite small.

2. Not neccessary you could run a datawarehouse on any model. The reason
why most datawarehouse run on stars and snowflakes I will explain here:
A federated datawarehouse is mostly organised in subjects - what is
the purpose of a datawarehouse? Marketing, QM, Production, Process
Control, HR, Controlling - Business Processes have different requirements.

I prefer subject/ requirements and measures instead of Dimensions
and facts.
So if you analyze your requirements then you might encounter that some
requirements are common to all subjects and others are quite specific.
So you would collect all subject oriented information together.

This is one reason that will lead sooner or later to a Star Schema or
Snowflakes, Constellations or what ever names people invent to impress
people.
The other and in my opinion more important reason to create Stars and
Snowflakes (that sometimes cannot be avoided) is to minimize the number
of joins required. The star schema is just one "hop" between subject
and measure and as you probably imagine a join is like a loop. On a 3NF
Schema with zero redundancy this would produce huge physical I/O and
sloow results.

The ideal datamodel for a Datawarehouse is a flat table - completely
denormalized - all data in one table. No joins at all. Unfortunately a
conventional relational database supports quite often only 1024 columns
and the table would grow to an incredible size and so become unusable
even if you partition it.

So there is some workaround (prothesis) called RAM databases or OLAP
cubes to create some flat model in RAM. Problem 1 not persistent -
Problem 2 you cant drill down because not all your data would fit in
RAM. (This is the reason why I prefer vector databases instead of
conventional ones)


3. It depends. If you have a central datawarehouse to feed "orbital"
datamarts to relief the main datawarehouse load then not always a 2nd
ETL is necessary. Reason: as mentioned in 1. Datawarehouse data should
be normalised as soon as it leaves the operational system. Of course I
encountered several times local datamarts that were created independedly
and the subject information was differently organised so a
retransformation was necessary. Practically this happens quite often -
however - this should be avoided. This is extremely expensive and will
lead to wrong business decisions. There should be a common view and
again same term same meaning.

When I join a DWH project - The first and foremost thing to do is
what is the purpose and clarify all business terms of this subject and
collect it in a data dictionary. - then assign it to the subject
(dimensional) tables. This is the highest and the most political
challenging job. (If you want to avoid that buy a ready made schema and
deploy this one)

Lots of stuff - you can read DWH from books or at University and some
practical experience during projects got me down to earth what is done
in the world. - There is no such thing as the ideal DWH- and if you try
to design one there is time- and of course a budget restriction!


Long story - hope this helps
Klaus
klausATroehlergenriemer!NOSPAMTAG!.de

Greven / Germany