Apriori algorithm [SQL Server 2005 and ASP.NET]

Hi

I am working on academic project using SQL Server 2005 & Visual studio 2005. Using Apriori algorithm to find the association between Patient City and likely diseases.

I have created PATIENT table with Patient_Id, Patient_name, Age, City attributes and Diseases table with Disease_Id, Disease_name. Connected these two tables, MANY - MANY [M:N]. Got a third relation with Patient_Id and Disease_Id attributes.

I am just inputting some dummy data into patient table and disease tables to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.

Here are my tables with attributes:

PATIENT ( Patient_Id, Patient_name, Age, City)

Diseases(Disease_Id, Disease_name)

[M:N] Got third below third relation bcz its Many to Many relationship

PATIENT_DISEASES(Patient_Id, Disease_Id)

I do think and believe that there is an efficient way of doing , instead of usin dummy data or using this relationships. I did check Microsoft Association algorithm and realised it is not Apriori algorithm.

Could you suggest the best or efficient way of doing this using SQL Server 2005?

Your help and insight into this matter is highly appreciated.

Regards

Sukumar Raju

The Association Rules is Apriori. What kind of rules are you looking for? Disease --> Disease, City --> Disease, Age -->Disease? All of the above?|||

Thank you.

Looking for association between City --> Disease.

I am just inputting some dummy data into patient table and disease table to make Apriori algorithm work. When a new Patient City is entered into patient table, System checks Patient table for same City previously stored and using Third relation, pulls Disease that associated with the City.

I think i have to write T-SQL statements to get the above algorithm work. Is there other way to achieve?

Is there best way other than simply writing T-SQL statements to implement Apriori algorithm work! Am i going in the right way?

Your suggestion and help is appreciated

regards

Sukumar raju

|||

You need to use SQL Server Analysis Services to use the Association Rules algorithm. You will launch BI Dev Studio and create a Analysis Services project containing a data source, data source view, and data mining structure - there are UI tools to do all of this - you don't need to write any TSQL.

I recommend checking out the resources at www.sqlserverdatamining.com - in particular the tutorial to learn how to use the DM functionality in SQL Server.

Thanks

-Jamie

|||

Thank you Mr.Jammie

I would do that. Could you give any resources that are useful to achieve my objective?

regards

Sukumar Raju

|||You can try the book I wrote with Zhaohui Tang - Data Mining with SQL Server 2005 - as a starting point. It describes how to create models of the type you need.|||

Thank you!

Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can

1) Create association model using BI in SQL Server 2005

2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.

hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Shouldbe getting in couple of days.

your suggestion and help is appreciated. Please advise.

regards

Sukumar Raju

|||

Sukumar Raju wrote:

Thank you! Jamie!

Your help and information is much appreciated. Can I retrieve this particular association model on web page? i m using asp.net for my web interface. I think i can

1) Create association model using BI in SQL Server 2005

2) Access this model on web interface, such a way when i enter patient city in PATIENT table from Web interface, get a prompt message showing "more likely diseases" from Diseases table , that associated with the entered City.

hoping i m going in the right way. I have ordered the book writeen by you Data mining with SQL Server 2005. Getting late...As my project submission date is just 5 days away.

your suggestion and help is appreciated. Please advise.

regards

Sukumar Raju