[UPHPU] Star and Snowflake Schemas
alvaro at epliant.com
Tue Feb 13 01:26:22 MST 2007
Daniel C. wrote:
> Just wondering if anyone here has experience implementing star or
> snowflake schemas in an RDBMS, and (assuming someone has), whether
> you'd be willing to tell me a bit about them. I understand the
> problems they're designed to solve, but I haven't been able to find a
> good example of an implementation of either schema.
I'm no expert, but this is what i know.
They're used for reporting and business intelligence because of how easy
is to query and aggregate data.
A star schema is composed of a fact table, with dimensions tables around it.
A good example would be a fact table of "number of requests" and
dimension tables of "request types", "time"
- TypeID (FK)
- TimeID (FK)
- Number of requests
- Name (Inbound, Outbound, etc)
With this setup it's easy to retrieve the total number of requests of
type "Outbound" on "Fridays", or total requests with code "23" on "February"
You can also add dimensions like "recipients" or "responses" and now it
start to look more like a star.
A star schema is very denormalized, to the point that it might take too
much space. If that's the case you might start to normalize some of the
dimension tables, thus making it look more like a snowflake.
More information about the UPHPU