Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

A Neural Network in SQL Server Expand / Collapse
Author
Message
Posted Tuesday, October 27, 2009 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 7, 2011 3:12 PM
Points: 10, Visits: 24
For me, this is exactly why I read the articles posted almost every day, this is the exactly the type of discussions one needs to understand the various facets of what will work under a variety of circumstances
THANKS!
Post #809399
Posted Tuesday, October 27, 2009 9:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 2, 2012 8:33 AM
Points: 65, Visits: 58
I have a newbie question: SSAS has a neural net algorithm. So, obviously it can be produced in SQL Server. How did your results differ from running the SSAS Neural Net algorithm?
Post #809404
Posted Tuesday, October 27, 2009 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:11 AM
Points: 6, Visits: 109
When comparing the classification matrices of both NN, mine and SSAS, the global error was quite similar (mine was a little higher but not much). The intention is to show these results when advancing in a coming article
Thanks for the comments!

Silvia
Post #809464
Posted Tuesday, October 27, 2009 11:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 5, 2009 1:16 PM
Points: 2, Visits: 9
As we know, there are a variety of Neural Network tools out there - and of course some are better than others. One issue with NNs is that by nature, they are a "black box" technology - its not an easy task to conjure causal factors out of a proprietary tool - whether the output be on target or way off the mark. By capturing how the training (and later, validation) goes in a localized and "query able" data model, the door opens to using this potentially valuable metadata in a variety of creative ways in a much broader data model if the results warrant, not to mention being able to benchmark your own implementation in comparison with other implementations you might try. Looking forward to your next post, Sylvia!
Post #809472
Posted Tuesday, October 27, 2009 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 4:36 AM
Points: 33, Visits: 412
With all iterative linear algebra code, the primary considerations are speed and numerical stability. Code maintainability can also be an issue.

What benefits are there on these fronts to doing the NN trick in sql?
Post #809476
Posted Tuesday, October 27, 2009 11:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 21, 2014 4:36 AM
Points: 33, Visits: 412
sknox (10/27/2009)On a serious note, sometimes programming isn't about performance, or even about successfully accomplishing a task, but about learning.


"Learning" also encompasses choosing a more appropriate platform/language to implement desired functionality over a lesser platform/language for that task.

Asking what benefits/detriments a particular choice of platform/language has for a given task is perfectly appropriate, even in a learning context.
Post #809478
Posted Tuesday, October 27, 2009 12:24 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:19 AM
Points: 338, Visits: 1,421
sknox (10/27/2009)
jacroberts (10/27/2009)
I can see that you can do this using SQL server but I fail to see any benefit to using SQL Server rather than wirting some code using a normal computer language like C.
Surely it is much slower using SQL Server?


First, I take offense at your characterization of SQL as not a "normal computer language" -- unless, of course, you mean that it's above normal. Second, I laugh at your characterization of C as normal in any way.

On a serious note, sometimes programming isn't about performance, or even about successfully accomplishing a task, but about learning. From the introduction to the article, it's clear that Silvia wasn't trying to create the most efficient or useful neural network, but more interested in how two programming concepts (a relational database and a neural network) might be brought together.

Also, bear in mind that many organizations can justify a SQL programmer where they can't justify a .NET or C programmer -- and they may benefit greatly from a neural network in SQL.


Does anyone think SQL is a normal programming language??? It's non-procedural for a start. You seem to be very odd, being able to be offended by statements about SQL! And 'normal' doesn't mean good it was used as standard or more normally used for this type of application.

C, and it's variants C++ and C# are probably the most widely used language in the world with everything from operating systems to applications written in it and even, dare I say, SQL Server which is probably written in C++.
Post #809518
Posted Tuesday, October 27, 2009 2:02 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:42 AM
Points: 1,322, Visits: 793
Nicely done and interesting article. I had actually considered writing a similar article, but you have beaten me to the punch :)

I like your use of Analysis Services as a feature selection tool. This is a good way for someone interested in the concepts to get a jump start. I had a little difficulty interpreting your ER diagram as some of the lines are crossing. Would it be possible to see a picture of a cleaned-up version? David Hay has some excellent guidelines for diagramming conventions here: http://www.essentialstrategies.com/publications/modeling/makingrd.htm

For those asking about performance/appropriateness of the implementation, my experience is that there are some types of predictive analytics processing that benefit greatly (both from performance and code maintenance standpoints) via implementation within SQL. Without seeing the full implementation, I'm not sure if this is such a case, but it's a good idea to think in this direction.

TroyK



Post #809567
Posted Wednesday, October 28, 2009 4:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 5, 2009 10:04 AM
Points: 2, Visits: 6
Interesting post.

Just a few suggestions:

NNs works better, as someone said, with real numbers not with integers. NNs is a non-linear interpolation so the result will be spreaded in the range for that variable you want to predice.
If you force the result to integer or worst to binary, you lost the richness of the prediction and obtain ambigous data. In the case you've presented the NN can give you back the PoP value in the range 0% and 100% exactly as a professional weather forecasting system could do.
So NNs are not well suitable, due their intrinsic nature, for discrete systems.

The input data are the most important thing, NNs learn only from them and if the information are not representive enough of the system, you will never obtain good results.
In other words the input data have to be wide enough to cover all the situation in that the system could work.
If your NN gives you bad results you should look at the input data as the first thing, and ask if the data give to the NN all the info it needs as quantity of different "situations" and if some input was not lost.
When you try to model real system as you did, the input problem is often challenging and is "the problem".
For example I think that the data of the region are not enough and data from neighboring regions could be have an influence on the next day's Pop (because the clouds are moving).
Or again, 5 years of data is a too small set.

But your approach is interesting and SQL server could be an useful to prototype a NN prediction system and then when well tested, develop an external software to achieve better performance.

Giuseppe
Post #809799
Posted Wednesday, October 28, 2009 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 7:11 AM
Points: 6, Visits: 109
Giuseppe-
You have very good points there, and it's right exactly what I experienced while working with the program. I didn't use an integer for PoP, then. What I did after the program returned the value was range the results and give them a 0 or a 1 according to their values.

Silvia
Post #809851
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse