Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mikebird
Aged Yak Warrior
529 Posts |
 Posted - 2009-03-13 : 04:19:57
|
| I'm reading Ralph Kimball. ConfusingNormalization. 1NF <--> 6NFFact Table and Dimension Tables. Are these pertinent in designing a datawarehouse? I only just discovered that my reporting career (SSRS & Crystal Reports) relies on datawarehouses. I've known this since 1998, so the datawarehouse theory is not quite as new as touted.Snowflake schema? From when I first touched databases - ORACLE / SQL Server, the schemas looked like a snowflake. Sub-table structure branching from a main table. You break up a main table's columns by putting them into additional tables. That's Normalization. Is that right?In reverse, the design becomes a database as less tables - one table. I've only ever seen A3 schema sellotaped together on a huge boardroom table - 100s of tables joined, with partitioned sections for different purposes - a few tables joined for something simple, or dozens joined for something complex - a SELECT, or an UPDATE... would this a datawarehouse, all this time, which I'd be reporting on? Or a operational schema; DW is something different?What's the difference between standard operational schema and FACT / DIMENSIONAL tables? |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2009-03-13 : 09:28:28
|
quote:
What's the difference between standard operational schema and FACT / DIMENSIONAL tables?
Oh, this is a big question...I guess the difference is less obvious when you're comparing a snowflake schema to a standard relational model, but with a star schema it's pretty obvious once you look at the actual contents of the tables. Take a look at this classic example of a star schema: http://en.wikipedia.org/wiki/File:Star-schema-example.pngIt would be totally pointless to have your production database modeled like this, but for reporting purposes it's really powerful and effective. For example getting a report for units sold per month and per quarter is the exact same query with the same join condition, you just need to group by Quarter instead of Month which has already been prepopulated in the date dimension. - Lumbago |
 |
|
|
mikebird
Aged Yak Warrior
529 Posts |
Posted - 2009-03-13 : 10:58:09
|
| Top reply! Thanks! I don't think there will be any more. I'll be reading more Kimball. Looks like nice boat. We have the same hobbies, except I'm also a serious DJ. |
|
|
|
|
|
|
|
|
Datawarehouses