Hi Bob,
"A colleague has proposed using the following structure for all fact tables in our dw design"
Thank you for presenting the opportunity to emphasis again how counter productive it is to try and come up with 'one standard' for something inside a dimensional model...(or any model for that matter...)
Sophisticated designs break 'rules' in order to deliver functionality/ performance.
I can share with you that there are some very interesting things that can be done when you put unique primary keys that are integers onto the front of fact tables......for example updating the fact table goes much faster. (Updating a fact table??? Shock and horror I thought we were not supposed to do this??? Well, we do.)
Fact table to fact table joins also produce some interesting features.
Also, some times the concatenation of the dimensional keys do not provide a primary key...
As far as I am aware there is no 'one set of rules' that will always produce the best designed model.
That's what they pay DW modellers for...;-) If it was as easy as churning through a set of rules anyone could do it...
If your colleague really wants to lean dimensional modelling...point him to Ralphs books for a start, and then let him work his way along..;-)
Also, logical vs physical.....many modellers still draw the distinction in DWing...Heck NCR even sell their logical data models.
These will go the way of the dodo....a newer concept is called Physical Data Modelling and you never need to worry about logical again.....not sure if there is a book out on this yet, I learned from a practitioner in this case.
In the future, ODSs/DWs/DMs (whatever) will be built without logical models and without all the fuss and confusion and analysis paralysis of numerous people trying to come to consensus.... :-)
Best Regards
Peter Nolan
Data Warehousing Consultant
Mobile: +353 879 581 732
Homepage:
-----Original Message-----
From: owner-dwlist@datawarehousing.com
[
Sent: 27 July 2004 16:05
To: dwlist@datawarehousing.com
Subject: dwlist: Surrogate Keys on the Fact Table?
Please visit our sponsor today at
For help with list commands, send a message to <
From: "Bob Underwood" <bunderwood@kc.rr.com>
Good morning data warehouser's,
I wanted to start a thread with the community about the physical design of fact tables. We are working to reach consensus about design standards. A colleague has proposed using the following structure for all fact tables in our dw design:
1. A single-column surrogate primary key.
2. A unique key index for the concatenated foreign keys from the dimension tables. The dimension table keys are single attribute surrogates.
I'm not convinced this is the best design, especially since I can't imagine what this would be used for and personally am not an advocate of fact table to fact table joins. I'm also concerned that it may be confusing for users, although can see that I can abstract this away in a logical model.
Has anyone had any experience with this approach?
Is this primarily a logical vs. physical design issue?
Any thoughts would be appreciated.
Thanks,
-Bob Underwood
Data Warehouse Architect
----- Original Message -----
From: <modestov@clearviewbi.com>
To: <dwlist@datawarehousing.com>
Sent: Monday, July 26, 2004 2:52 PM
Subject: RE: dwlist: The possibility of complete systems
>
> provider of real-time data integration and resiliency solutions.
> Please visit our sponsor today at
> to access data warehousing white papers and best practices.
> For help with list commands, send a message
> to <
> word "help" in the body of the message.
> From: modestov@clearviewbi.com
> Apologies for intervening in such a high level discussion :)
> Kurt Gödel proved that a complete (logical) system is inconsistent (or
> contradictory). It follows that a consistent (logical) system is
incomplete.
> Most logical systems are consistent and therefore complete. However,
there
> are some that are incomplete.
> In a complete logical system the proposition "I wrote this message and
I
did
> not write this message" is true. The same will apply for the
proposition
> "relational database theory is complete and relational database theory
is
> not complete".
> Fortunately for those of us that studied Philosophy Kurt Gödel was not
a
> philosopher, he was a mathematician, specialised in mathematical
logic.
Not
> all 'mortals' can read Gödel, you have to be a mathematician and a
good
one
> to read and understand Gödel. Just in case you are wondering, I am not
> of them :), but I tried ;).
> Regards,
> Modesto
> -----Original Message-----
> From: owner-dwlist@datawarehousing.com
> [
> Sent: 23 July 2004 22:42
> To: dwlist@datawarehousing.com
> Subject: dwlist: The possibility of complete systems
data
> warehousing white papers and best practices.
> For help with list commands, send a message to
> <
the
body
> of the message.
> From: "Peter Nolan" <peter@peternolan.com>
> Hi Neil,
> "Kurt Godel proved a long time ago that no formal system is complete."
> I'd be interested in the paper or reference about this to know what he
was
> talking about...especially since it's perfectly possible to define
formal
> complete systems.....
> You see, I spent 3 months of my life doing a proof that all
mathematical
> based systems could be broken down to 15 (if memory serves me) logical
> axioms. That is, any mathematical system that was well-defined was
complete
> and could derived from these axioms. It could be done both ways. We
had to
> prove it from axioms up.....hhhhmmmmm......
> And yes, we had to prove that relational theory was also based on
> mathematical theory, and that the maths was based on the axioms so
that
> relational theory was a complete and closed expression of a system.
> Isn't it amazing what some philosophy lecturers will inflict on their
> students...;-)
> I did Epistemology and Metaphysics as well, I enjoyed the question:
> 'What is it possible to believe and still be rational'
> much more than all the logic...
> The arguments in those classes were so much more interesting!!!
> And from the look of the discussion on this list of late it seems that
many
> people believe it's possible to believe all manner of things and still
be
> rational.. ;-)
> Best Regards
> Peter Nolan
> Data Warehousing Consultant and Philosophy Major( Logic and
Metaphysics)
> ;-)