Sequence Clustering Algorithm

Question

This is the table that i have

StudentID  ClassID CentreID  EnrollType                  History     JoinYear        Month
 1000020    1           1             NEW Student              NULL        2005             JAN
 1000020    2           1             Current                       NuLL        2005             JAN
 1000020    3           1             Current                       NuLL        2005             JAN
  1000020    2           1             EXIT                            NuLL       2005            JAN
  1000099    4           3             NEW STudent           NULL         2007            MAR
   1000104   2           5             Internal Transfer       NULL        2009            MAR
 .................................................................................
 
I need to use SEQUENCE Clustering algorithm to capture events.  For example

STUDENT JOIN EVENT

 IF the StudentID(j-1) < > Student(j) & enroltype= "NEWSTUDENT" ----> student joined at "j"
 IF the StudentID(j-1) < > Student(j) & enroltype= "Internal Transfer" ----> student joined at "j"

Each student has multiple records .
When this above condition occurs , Few attributes like firstname, lastname  ,joinyear have to be displayed.

I am new to SSAS ....Can anyone please tel me how i could implement this using sequence clustering algorithm. All the data is coming from one single table. What would go in my nested table and case table.???? Do i have to make use MDX to solve this problem.

Answer 1

Can you clarify the question - I'm not quite understanding what you are trying to do here.

---

-- Jamie MacLennan -- SQL Server Data Mining -- http://blogs.msdn.com/jamiemac

Answer 2

Hi jamie,

Thanks for your reply.
I am trying to capture all of the following event sequences. Once I can successfully capture them, I need to generate one record for one captured event in a new table.

The following Events are of my interest.

 · Student Join Event -       

 IF the StudentID(j-1) < > Student(j) & enrolltype= "NEWSTUDENT" ----> student joined at "j"
 IF the StudentID(j-1) < > Student(j) & enrolltype= "Internal Transfer" ----> student joined at "j"


When the above pattern is observed , The following data should be generated.

For Student Join Event

• StudentID
• Year/Month
• Enrolltype.

  

Answer 3

Hi Leo,
Im just taking a guess, im dont know data mining.

Can u do a sort on StudentID, ClassID.
Then use a while to check the Student join event.

This way you coud get the required result hopefullly from a query.

---

Hope this helps !! - Sudeep | Please mark the post(s) as “Answered” that answers your query.

Answer 4

Hi sudeep,

I gave up on this and i am trying to use SSIS to accomplish the same task..Thanks for ur help

Answer 5

Hi sudeep,

I gave up on this and i am trying to use SSIS to accomplish the same task..Thanks for ur help

I would be looking to solve this relationally in the database. I do not think it is a problem that Sequence Clsutering is going to help you with to be honest. SSIS will also work for you.

Answer 6

Ya even i realized that ..Thanks anyways allan