Re: On the subject of Data Warehouses, Data Cubes & OLAP....

"Bob Badour" <bbadour_at_golden.net> wrote:
>"DataMan" <dataman_at_ev1.net> wrote in message
>news:vpdppi7ccdn0f1_at_corp.supernews.com...
>>
>> "Bob Badour" <bbadour_at_golden.net> wrote:
>> >"DataMan" <dataman_at_ev1.net> wrote in message
>> >news:vpdad0mimma52e_at_corp.supernews.com...
>> >>
>> >> Most OLTP systems do support the information requested. It's just
 very
 difficult
>> >> to retrieve.
>> >
>> >That is a remarkable and very interesting assertion. What can you offer
 to
>> >support such a remarkable assertion?
>> >
>> The support information or difficult to retrieve statement?
>
>I do not find the "support information" assertion remarkable, and I doubt
>anyone would. What can you offer to support your remarkable assertion that
>information is difficult to retrieve?
>
I have no idea what you find remarkable. The world is full of remarkable people all finding different things remarkable. I find it remarkable that you find no value in dimensional data warehouses. As your opinion on this matter is already formed, and many books on the subject have unsuccessfully shown you the value of data warehouses, I don't believe I can sway your opinion on the subject. If you would like some recommendations on books that cover the material in depth, I would be happy to provide some.

>
>> >> Additionally when the business requires enterprise level reporting
>> >> across the various functional applications, a warehouse becomes a
 necessity.
>> >> You know the scene, large company with 10 different order entry systems
>> >> for each functional area of the company (i.e. cost center). One for
 small
>> >> biz, one for consumer, one for z product, one for y product, etc.
 And
 of
>> >> course there was never any upfront effort by a data management
 organization
>> >> to ensure consistent representation of enterprise level entities.
 So
 you
>> >> end up with a hodge podge set of data that must be abstracted to make
 consistent
>> >> within a DW environment. And since you're already there go ahead and
 put
>> >> it in a dimensional format so somebody can understand it and easily
 pull
>> >> data/reports.
>> >
>> >What exactly is a dimensional format? How does it differ from >relational
>> representation?
>> They are both relational. Assuming both the application database and
 the
>> data warehouse are both stored in relational databases. Was your question
>> directed to differences between a data warehouse database (let's say OLAP
>> for convenience) and an application database (let's say OLTP for
 convenience)?
>> The primary difference is purpose. OLTP must support consistent real
 time
>> data. OLAP only need support historical reporting.
>
>So then, OLAP is just another word for "snapshot" as in "a derived stored
>relation" ?
>

I'm sorry, I don't understand your meaning in "a derived stored relation".  I was trying to illustrate one of the basic differences between an application database and a dimensional data warehouse. And that difference was that the application database is constantly changing over time as the business data changes. OTOH, in the data warehouse the data is never updated. So a report run in a data warehouse today will be the tomorrow and the month after that.

>
>> >How or why can it improve understanding? How have you measured >this
 improved
>> understanding?
>> I didn't say that it does.
>
>"And since you're already there go ahead and put it in a dimensional format
>so somebody can understand it and easily pull data/reports."
>
>It sounds to me like you did. If you did not say that, what the hell did
 you
>intend to say?
>

Hmmm, I guess I did say that. Sorry I was busy at work and responded too quickly to the post. A fully normalized model for a large application can have 100's of entities. A dimensional model is built to support specific reporting and consists of a fact table and its related dimension tables.  Occasionally, a dimension will also have a relationship to another table resulting in the snowflake term. Certainly, a fact table in conjunction with its dimensions is more comprehensible than a large normalized application database. I would offer that the comprehension is solely related to the business reporting and not the business rules stored in the application data structure. Thank you for allowing me to clarify that statement. My purpose was not to sell data warehouses or dimensional models. It was just a comment to the complexity inherent in some of today's applications and their corresponding data structures.
>
>> >What features does it provide that facilitate reporting?
>> >
>> The data structure allows for easier retrieval.
>
>Since the logical data structure is a relation in both cases, I fail to
 see
>how it alters anything with respect to retrieval. Perhaps you would like
 to
>clarify your remarkable statement?
>
>

Again I don't understand your statement "the logical data structure is a relation in both cases". The data structure of a dimensional model is different than the corresponding application data source(s) structure. Received on Thu Oct 23 2003 - 04:07:55 CEST