SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Silvia Cobialca / A Neural Network in SQL Server / Latest Posts

RE: A Neural Network in SQL Server

Silvia Cobialca — Mon, 14 Nov 2011 12:45:05 GMT

Thanks Dave for the ellaboration of all your experiences with a subject similar to mine. I also did it just for fun and let me tell you it certainly was a lot of fun and experience with coding.I do have still some pending stuff left on this one and that is a generalization of the model I did. Have it on standby due to time constraints but will revisit this one in the near future

RE: A Neural Network in SQL Server

dray — Thu, 10 Nov 2011 12:23:24 GMT

I've lurked too much - gleaned much from SQLCentral but pretty much keep quite. I was searching for Neural Nets (NN) using a SQL system for main processing and was pleasantly surprised.Loved the article, sorry to resurrect a thread here, but I've been working on a neural net in sql, but using a simple feed forward where layer 0 is the input and layer n+1 is the output. The whole network could be expressed in 2 tables, one for the neurons, the other for the links between neurons. The Neuron table keeps state from the last feed-forward iteration, a sigmoid shape bias, and a threshold. The NeuralLink table links the neurons and holds a weight per neuron.I was trying to think in sets when constructing the model. Each neuron will know the layer it is in and by anchoring on the neurons from N[sub]1[/sub] to N[sub]x[/sub], each set of connections are called in and the connected neuron weights are sum'd and saved per neuron.The problem is - I've so far been unable to unravel the loop to support a recursive CTE because a summation will need to occur before moving onto the next layer. I have to use a while loop to iterate through the layers. (from 1 to x - forward fashion). Essentially, sequential processing must occur at the time being.The back propagation routine for learning is just done in reverse, N[sub]x[/sub] to N[sub]1[/sub]The training data and corresponding expected correct responses can be added into 2 tables and the relational nature immediately allows a pathway for easy training using sets.This system may work surprisingly well. Usually the layers aren't the issue, its the sheer number of interconnections. If I can take advantage of proper indexing and set based procedures, the SQL solution with large numbers of neurons may end up working because SQL is tuned to working with large amounts of relational data better than my C# code. The Network and the processing code looks a lot less complex, since most of the programming is spent re-building RI into the objects needed to support NN (Neural Nets). I've attempted to make the Neural Net a data processing set system.To make use of it, update and populate the input layer and call the stored procedure telling it what neural net you want to process. Read output from the last layer in the network for your answers.Due to the rational layout, several neural networks could be created sharing the sample inputs and outputs. Support to deposit the output data per network with epoch encoding would allow for several networks to process the same input data, potentially allowing for some interesting parallelism.A Neural Net on SQL would still be quite intensive - albeit predicatively spikey. Its been a fun project. Getting the back-prob to work without a functional calculus background has been tricky. I work for a soil and water testing lab, and develop databases, web services, intra-business programs (LIMS , analysis, reporting, etc) and other software development. I've made for the company a multiple pH probe system that collects acquired data and determines when the probes are stable and controls other auto-processing aspects. I have 120 million or so datapoints collected (ph behaviors) and would like to see if I can unleash a neural net on this data and see what kind of stuff would come up.Some thoughts would be see if there could be a minimal number of samples to predict the end point pH, or determine when something is sampled wrong etc.Mainly its to expand more useless knowledge :DInteresting to see others messing around with NN in SQL.Thanks,Dave

RE: A Neural Network in SQL Server

timo-947179 — Thu, 03 Dec 2009 06:42:19 GMT

Very good article, thank you!About the classification matrix: am I reading it wrong or is there an error in the explanation?[quote]Also, there were 243 cases when the network predicted that it was going to rain and it didn't rain (a false negative)[/quote]Shouldn't it be:Also, there were 243 cases when the network predicted that it was NOT going to rain and it DID rain (a false negative)

RE: A Neural Network in SQL Server

Phil Paxton — Thu, 05 Nov 2009 08:13:21 GMT

Excellent article. It's a nice break from "standard" languages, even though that's how I get the most money.There's a quote from Celko which (paraphrased) to be good with SQL is to forget how to program.I figure that's almost like what XSLT 1.0 (which I miss because it would separate the men from the boys) was for a lot of people: you can't look at it procedurally.Is there a better version of the "Analysis Services" diagram?I cranked the resolution to practically nothing and used my reading glasses to no joy.

RE: A Neural Network in SQL Server

dalewilbanks — Thu, 05 Nov 2009 07:45:04 GMT

I Love C, C#, but often write > 50% of application code in SQL, because it is the fastest way to manipulate data, period. If the data can be calculated within the database using SQL, rather than having to retrieve it, you have a magnitude less of network I/O, which is typically a large bottleneck for an application.'If' you are going to persist your Neural Net state in the database with every calculation, using SQL might be the fastest NeuralNet implementation, due to minimal network I/O. Not saying that is the best approach, just saying it should be considered.

RE: A Neural Network in SQL Server

g.ciccotti — Wed, 28 Oct 2009 07:02:50 GMT

Silvia,perhaps could be more simple to predice the mm of rain really fallen in the next day.And set Pop=1 if the mm was grater than 0.Wait to read more of your work.Giuseppe

RE: A Neural Network in SQL Server

Silvia Cobialca — Wed, 28 Oct 2009 06:40:34 GMT

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

RE: A Neural Network in SQL Server

g.ciccotti — Wed, 28 Oct 2009 04:44:52 GMT

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

RE: A Neural Network in SQL Server

cs_troyk — Tue, 27 Oct 2009 14:02:30 GMT

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: [url]http://www.essentialstrategies.com/publications/modeling/makingrd.htm[/url]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

RE: A Neural Network in SQL Server

Jonathan AC Roberts — Tue, 27 Oct 2009 12:24:53 GMT

[quote][b]sknox (10/27/2009)[/b][hr][quote][b]jacroberts (10/27/2009)[/b][hr]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?[/quote]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. :-POn 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.[/quote]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++.

RE: A Neural Network in SQL Server

sherifffruitfly — Tue, 27 Oct 2009 11:11:56 GMT

[quote][b]sknox (10/27/2009)[/b]On a serious note, sometimes programming isn't about performance, or even about successfully accomplishing a task, but about learning.[/quote]"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.

RE: A Neural Network in SQL Server

sherifffruitfly — Tue, 27 Oct 2009 11:08:25 GMT

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?

RE: A Neural Network in SQL Server

Michael Martin-492204 — Tue, 27 Oct 2009 11:05:02 GMT

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!

RE: A Neural Network in SQL Server

Silvia Cobialca — Tue, 27 Oct 2009 10:48:56 GMT

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 articleThanks for the comments!Silvia

RE: A Neural Network in SQL Server

samkaplan — Tue, 27 Oct 2009 09:29:30 GMT

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?

RE: A Neural Network in SQL Server

mjwehr — Tue, 27 Oct 2009 09:26:48 GMT

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!:-D

RE: A Neural Network in SQL Server

sknox — Tue, 27 Oct 2009 09:17:50 GMT

[quote][b]jacroberts (10/27/2009)[/b][hr]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?[/quote]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. :-POn 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.

RE: A Neural Network in SQL Server

Jonathan AC Roberts — Tue, 27 Oct 2009 06:29:04 GMT

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?

RE: A Neural Network in SQL Server

Martin Vrieze — Tue, 27 Oct 2009 06:28:03 GMT

I am aware that SSAS has full blown Econometrics built into the SSAS package capable of performing more than just neural nets...we're talking a full slate of linear regression/non-linear regression/discrete choice/arima/two-stage least squares/vector auto regression/co-integration...everything you would find in SAS or SPSS.At the end of the day, you still need to get the data into the SSAS tool which is amazingly difficult...and why SQL Server is not a first choice among data analysts for handling statistical analytics.

RE: A Neural Network in SQL Server

mjwehr — Tue, 27 Oct 2009 05:55:36 GMT

Thank you for sharing! Well written and understandable for me.I am just starting to use SQL Server 2005/2008 as a tool specifically for developing data storage design to be used in predictive analysis as the end goal.I am very happy to be able to get some insight to the development this early in the journey! MJWehr

RE: A Neural Network in SQL Server

Michael Martin-492204 — Tue, 27 Oct 2009 05:07:40 GMT

Hi Silvia: Thanks very much for sharing your methodology, and am really interested in reading your next posting! I've had some success emulating NNs in Excel, but scale becomes an issue eventually. Best Wishes, Michael

RE: A Neural Network in SQL Server

Silvia Cobialca — Tue, 27 Oct 2009 05:06:47 GMT

Thanks for you comments. I will elaborate on the code and how it works in an incoming article, where I'll include the training and testing of the networkSilvia

RE: A Neural Network in SQL Server

isaac-609925 — Tue, 27 Oct 2009 04:21:22 GMT

Very ineresting, and very well explained.Looking for the next session - can you share the source ?ThanksIsaac

RE: A Neural Network in SQL Server

Mark G. — Tue, 27 Oct 2009 02:30:21 GMT

Great and unusual article related to database. Would it be possible to share the source code?Cheers

RE: A Neural Network in SQL Server

Open Minded — Mon, 26 Oct 2009 23:27:25 GMT

i had a tough time building a neural network. i used mathematical functions to 'amplify', update then recast the result prior to output. it seemed the training would never end.close to the end, i was thinking of allowing decimal values instead of integer values. i was maybe thinking, with decimal values it would look fuzzy enough to be accurate.i had moved on to another company, leaving the project and with a heavy 'agreement of silence' signed just so i could leave clean.

A Neural Network in SQL Server

Silvia Cobialca — Mon, 26 Oct 2009 23:22:11 GMT

Comments posted to this topic are about the item [B]A Neural Network in SQL Server[/B]