Applying MS Clustering Algorithm for grouping time series

Discussion in 'microsoft.public.sqlserver.datamining' started by Yuelin Liang, Apr 6, 2005.

  1. Yuelin Liang

    Yuelin Liang Guest

    Hi All

    I have a database table containing time series data,
    which are one year's speed measurements for a road section.

    The first column is the datetime of the measurment,
    and the second column is the speed value. Values were
    measured every 5 minutes. So the data are like the following:

    01/01/2003 0:00 28
    01/01/2003 0:05 37
    01/01/2003 0:10 31

    31/12/2003 23:50 60
    31/12/2003 23:55 39

    What I need to do is to group the speed values according to the
    time dimension. E.g. all the working days have similar speeds,
    all the weekends have similar speeds, etc.

    Can I use SQL Server 2000 data mining to do this task? How to
    tackle the problem in general? Or any tutorials for doing this?

    Many thanks

    Yuelin Liang, Apr 6, 2005
    1. Advertisements

  2. I would start off with a model like this:

    CREATE MINING MODEL SpeedClustering
    MeasurementTime DATE KEY,
    [Hour of Day] LONG CONTINUOUS,
    [Day of Week] TEXT DISCRETE
    ) USING Microsoft_Clustering

    You will obviously have to create the hour of day and day of week variables.
    You might consider adding additional flags like "Holiday" and "School Day"
    which may impact traffic as well.

    You may have to eliminate any measurements of 0 which are coincidental, e.g.
    at night, 0 may mean no traffic, whereas at 4 PM, 0 means you're unlucky.
    Jamie MacLennan \(MS\), Apr 7, 2005
    1. Advertisements

  3. Yuelin Liang

    Yuelin Liang Guest

    Thank you very much, Jamie.

    I will try that.


    Yuelin Liang, Apr 8, 2005
  4. You're welcome - I would like to hear how it works out. SQL 2005 has a
    suite of cluster viewers that would be helpful in understanding the results
    if that's available to you (i.e. you have an MSDN subscription). Otherwise,
    you should download the resource kit from

    Which has a cluster viewer created by Angoss that displays cluster profiles.


    -Jamie MacLennan
    SQL Server Data Mining
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Jamie MacLennan \(MS\), Apr 8, 2005
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.