Posted by David Portas on 03/14/06 01:14

Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> > Now here is a more interesting slant on the key problem. When does a
> > metric become a kludge?
>
> When the kludge is not a metric.
>
> Of course, two hits from the same IP address in the same ms is a little
> funny - but so are two hits 10 ms apart, but your model does not account
> for that. No_of_hits is just a mechanism you've added to solve the
> problem with collisions on a key values that represents a contiguous
> spectrum.
>
> Take another example. Some process in laboratory or whereever generates
> lots of measurements data, in rates of microseconds. We don't use datetime
> to store the value, obviously, but would it be right to assume discrete
> steps of microseconds? Maybe, but what if the frequency is somewhat
> uneven?. We can get two values registered for the same millisecond, but
> we know that they are nevertheless apart. And here we are not talking
> number of hits, but some value - presumably floating-point.
>
> What I am saying is that you cannot use en entity that is a continuous
> spectrum as a key. Key values must be discrete. Of, course, in a
> digital computer, everything is discrete - but that only means that
> two analogue values can get the same representation.

See Kimball on slowly changing dimensions. Or Date, Darwen and
Lorentzos on Temporal Data and The Relational Model. Time-variant data
is commonplace and all the techniques I know of expect the time
dimension to be part of the key. Kimball actually asserts that we
should choose fixed, granular periods for facts - although I don't
think he develops a proper argument to support that. So this is
interesting but given that we have widely implemented and proven
industry standard solutions for these problems I don't think you should
dismiss them without hard evidence that you can better them.

> And this is just one example where the real world does not have that
> fine key the relational model wants. Again, think customers.

Been there. Done that.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]