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:vpgha67etvtd6f_at_corp.supernews.com...
>>
>> "Bob Badour" <bbadour_at_golden.net> wrote:
>> >"DataMan" <dataman_at_ev1.net> wrote in message
>> >news:vpfms541cpv8ec_at_corp.supernews.com...
>> >>
>> >> "Bob Badour" <bbadour_at_golden.net> wrote:
>> >> >"DataMan" <dataman_at_ev1.net> wrote in message
>> >> >news:vpedvrc3kbf74_at_corp.supernews.com...
>> >> >>
>> >> >> "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.
>> >> >
>> >> >I already told you what I found remarkable. Can you offer any support
 for
>> >> >your remarkable assertion?
>> >> >
>> >> >
>> >> >> 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.
>> >> >
>> >> >Did I say that?
>> >> >
>> >>
>> >> No, but since you make assumptions, I thought I would too.
>> >
>> >I suppose we all make assumptions, and I try to make mine explicit. To
 which
>> >assumptions do you refer?
>> >
>> >
>> >> >> 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.
>> >> >
>> >> >Did I say that?
>> >> >
>> >>
>> >> ibid.
>> >
>> >Again, to which assumptions do you refer?
>> >
>> >
>> >> >> If you would like some recommendations on books that cover
>> >> >> the material in depth, I would be happy to provide some.
>> >> >
>> >> >That seems a very long way of saying you have nothing to offer to
 support
>> >> >your remarkable assertion. Apparently, you simply assumed it was true
>> >> >because you heard it somewhere, and now you are out of your depth.
>> >> >
>> >>
>> >> That's quite possible. But this is fun, so I continue. Please
 reference
>> >> the attached data model. This is the standard movies data model
 supplied
>> >> by CA when purchasing ERwin. If I wanted a report that provided the
 amount
>> >> of rentals customer's are making using electronic payments by store,
 the
>> >> resulting sql would be more complex than a dimensional model built
 around
>> >> a transaction fact table with associated dimensions for customer,
 store,
>> >> etc.
>> >
>> >I don't see any attachments. Regardless, how does the requirement exceed
 the
>> >capabilities and features of a snapshot?
>> >
>>
>> What does this question have to do with the my previous point?
>
>If a simple snapshot meets the reporting requirement, why would anyone need
>or want anything else? What drives the need for a different model or stars
>or snowflakes or dimension tables or fact tables or anything else for that
>matter? The relevance seems very clear to me.
>

Perhaps if that were you're original point. But it wasn't. You asked for examples of how it would be more difficult to retrieve data out of a normalized database compared to a dimensional model. I offered an example to which you replied "If a simple snapshot meets the reporting requirement, why would anyone need
 or want anything else? What drives the need for a different model or stars or snowflakes or dimension tables or fact tables or anything else for that matter?" Which just reinforces my original assumption on this whole thread.  But to address your second question, a simple snapshot doesn't meet the requirement all the time. In most cases your sourcing data from many different applications all with their own unique rules and constraints.

>
>> >> >> >> >> 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".
>> >> >
>> >> >What part do you not understand? Do you know what a relation is? Do
 you
 know
>> >> >the difference between something that is stored versus something that
 is
 not
>> >> >stored? Do you know what it means to derive something?
>> >> >
>> >>
>> >> The derived stored relation part. Yes. Yes. Yes.
>> >
>> >If you know what a relation is and you know what a derived relation is
 and
>> >you know what a stored relation is, what causes you trouble with
>> >understanding a derived stored relation? It all seems quite clear to
 me.
>> >
>>
>> Great. What's your point then?
>
>See my original question: Does that mean OLAP is just another word meaning
>"snapshot" ?
>

No. Facts can be events not just transactional data. For example, a company wants to track counts of installs and disconnects for their products, services, and customers. Every time a product is installed an install event is added to the fact for the product.

>
>> >> >> I was trying to illustrate one of the basic differences between
 an
 application
>> >> >> database and a dimensional data warehouse.
>> >> >
>> >> >You appear to have regurgitated meaningless pap. If you were
 illustrating
>> >> >anything, you could offer support for your assertions.
>> >>
>> >> I thought it was meaningful and remarkable. Oh well. I guess in a
 nutshell,
>> >> the only support I can offer is empirical. Most of the business
 community
>> >> I have dealt with have difficulty navigating and understanding what
 is
 shown
>> >> in complex, highly normalized data models.
>> >
>> >Why would anyone navigate something that does not require >navigation?
 The
>> idea seems somewhat absurd to me.
>>
>> And to alot of other people. Some people figured out how to get around
 all
>> the navigation.
>
>What navigation?
>
>
>> >Where can I find the raw data for your
>> >empirical measurements? Can you email them to me?
>>
>> Isn't that an oxymoron?
>
>No. Why would it be? Valid empiricism requires measurement. I suggest you
>read _How we know what isn't so_ by Thomas Gilovich.
>

Thanks, I'll look it up. My empiricism is invalid.

>
>> >> In fact, most business folks
>> >> don't care.
>> >
>> >I agree. Most of them accept meaningless pap without objection.
>> >
>> >
>> >> They just want the application to work and they want their reports.
>> >
>> >Is there supposed to be some meaningful point in the above observation?
>> >
>> >
>> >> Those same folks who previously needed the IT shop to develop and write
>> >> reports for them are now using BI tools to do their own data mining,
 data
>> >> queries against dimensional models. Why or how they can do one and
 not
 the
>> >> other is obviously a matter for debate. I see the success of the DW
 industry
>> >> and it's prevalence in corporate world and decide that people see value
 in
>> >> the technology.
>> >
>> >What technology is that? Be specific: We are all engineers here.
>>
>> Ummm, data warehousing?
>
>I am afraid you are using circular logic. You have yet to describe what
>exactly data warehousing is in any comprehensible manner as a technology.
 It
>appears you continue to repeat meaningless pap without any real
>comprehension yourself and you continue to find yourself beyond your depth.
>
That's classic.

>
>> >> >> 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.
>> >> >
>> >> >Really? Now that is a truly remarkable statement! Do you realise this
>> >>
>> >> Sweet. Now I'm making progress!
>> >>
>> >> >fundamentally alters every cosmogeny I have ever heard: "In the
 beginning
>> >> >was the data warehouse..." One immediately wants to ask whence this
 eternal
>> >> >data came, but Ockham already took care of that one.
>> >> >
>> >>
>> >> Great statement! But I had to look up Ockham. You are a scholar!
 The
 jury's
>> >> still out on the gentleman part.
>> >
>> >Definitely not a gentleman. The jury came back on that verdict ages ago.
>> >
>> >
>> >> >> So
>> >> >> a report run in a data warehouse today will be the tomorrow and
 the
 month
>> >> >> after that.
>> >> >
>> >> >What would be the point in running the same report on unchanging data?
>> >> >
>> >>
>> >> You want two copies?
>> >
>> >Okay. But wouldn't a photocopier or Kinkos be cheaper?
>> >
>> >
>> >> End of year summary?
>> >
>> >Since nothing gets updated, what would be the point?
>> >
>>
>> All 12 months in one report. Don't you get one of those from you CC
 company?
>> Do you keep it or throw it away?
>
>If the data is not going to change, why would I wait until next month for
>next month's report?
>
>
>> >> Year to date?
>> >
>> >Since nothing changes, what would the year to date show? Why wouldn't
 one
>> >just report on the data at the beginning of the year?
>> >
>>
>> How about trending? Don't folks at your company want to see how sales
 of
>> a certain product are trending on a monthly, quarterly basis?
>
>Trends imply change over time. Since according to you the data in the data
>warehouse never changes, what meaning would a trend have?
>

I'm not sure why you're having such difficulty with this concept. Take a look at the stock market and rolling averages.

>
>> This is real
>> easy to do with a dimensional model BTW. Try it in a normalized data
 model
>> (if they keep history at all).
>
>Other than stating that the data in the data warehouse never changes, you
>have still not described the difference between dimensional and normalized
>data models. Actually, you have posted a number of mutually contradictory
>statements so it is impossible to discern what the hell you are talking
>about.
>

You forgot a few:
  Their requirements to model are different   And as a result their structure is different   And supports easier retrival

>
>> >> >> >> >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.
>> >> >
>> >> >Yes, potentially hundreds of dimension tables... Does a data warehouse
>> >> >comprising multiple data marts have only a single fact table or is
 it
 not
 a
>> >> >dimensional model?
>> >> >
>> >>
>> >> I guess it's possible for a single fact table to have hundreds of
 dimensions.
>> >> I've not seen one. I usually see 5 to 15 dimensions. A dimensional
 model
>> >> supports only specific reporting. As such you would need to create
 a
 fact
>> >> for each type of reporting required.
>> >
>> >I see. We have lots of dimensions and lots of fact tables. How does one
 know
>> >which dimensions and facts to use?
>> >
>>
>> The one that you need. Is this a trick question?
>
>No. I guess the user would just use the entity the user needs. >What purpose
 does the data warehouse serve? How does it improve understanding? How have
>you measured this improved understanding? You have yet to answer any
>question with a meaningful answer.
>

I disagree. But I knew we would.

>
>> >> >> Occasionally, a dimension will also have a relationship to another
 table
>> >> >> resulting in the snowflake term.
>> >> >
>> >> >So, if we rearrange our entities into a snowflake pattern, we get
 a
 data
>> >> >warehouse?!?
>> >> >
>> >>
>> >> I'm not quite sure how you came to that conclusion. There is the
 application
>> >> database(s) and there's the data warehouse. "Snowflaking" entities
 in
 an
>> >> application database doesn't make a DW.
>> >
>> >Then what does make a DW?
>> >
>> >
>> That's really a good question. Because there are lots of people who do
 data
>> warehousing and probably really are not. Let me think about this for
 awhile
>> and see if I can come up with the characteristics of a data warehouse
 versus
>> an application database. What are the characteristics of your DW?
>
>My DW is neither more nor less than a snapshot. As such, it is neither more
>nor less than a derived stored relation. I generally ignore the meaningless
>pap.
>
>
Received on Fri Oct 24 2003 - 04:52:14 CEST