Re: Star vs Snowflake: Looking for good reading material

David Cressey <david_at_dcressey.com> wrote in message news:gEYP5.15014$q4.253093_at_petpeeve.ziplink.net...
>
> The real question is, "what do you gain by snowflaking?" Space? Time?
> Simplicity?
> The "acid" test?

Duplication, i.e., loss of normalization, but I suspect this is my ignorance talking.

I can see snowflakes being used to build the dimension tables as views (or calculated and stored as tables). As long as the dimension tables are not the "root", then I'm comfortable.

I'm considering an application which has a data mart component. I agree with the star for the mart. The question is: "Can I use the dimension tables for major portions of my working app", or should I keep these dimensions normalized. With a snowflake, I can have the app and mart share a single dbase (no real concerns about massive scaling up and needing a separate query server as yet).

> Kimball addresses space and time resource issues better than I can. The
> "acid"
> test should not apply here, unless you are loading your warehouse in or
 near
> real time.

Agreed. I have an app with a small portion of user interaction, but a big portion
of the app is a batch generation of data which should be moved directly to the
data mart.

> So I'm left with simplicity. Simplicity is somewhat subjective, but
 here's
> my take:
> I learned star schema design as an alternative to normalized design, but
 I
> didn't
> really internalize it until I got exposed to something else. That
 something
> else is
> dimensional modeling.

Understood. Takes a little while, doesn't it? I've have normalization for so long its become natural. Now things are temporarily a little unnatural.

> My recommendation is that you read, very carefully, the chapters about
> "An introductory course n dimensional modeling" and "a graduate course
> in dimensional modeling". (sorry if I got the chapter headings wrong.
 I
> don't have the book in front of me). And, as you read, keep in mind that
> modeling is not the same as design.

The 2nd time read is going much better. I also found a couple of backup articles with a google search today. Not as much as I would have expected, and nothing that really is a slam dunk in favor of a strict star. But its helping.

> Having said that, there are plenty of circumstances, other than
 warehouses
> and data marts,where normalization is still the best design principle,
> and star schema would be at best a distraction, and at worst a serious
> obstacle to quality.
>
I'm finding it a fascinating enlargement of my perspective. Thanks for the input. Received on Tue Nov 14 2000 - 05:49:24 CET