Jamie's Junk

Happy New Year with a big bang!

MSDNArchive — Fri, 01 Jan 2010 20:05:00 GMT

It's been over 10 years that I've been working on data mining for SQL Server and the new year brings big changes for me. It's been a very exciting time for me to learn this technology and industry, and with the new year, I'm leaving Microsoft to become...(read more)

The amazing flexibility of DMX Table Valued Parameters

MSDNArchive — Fri, 25 Sep 2009 01:58:57 GMT

When most people think of “Table Valued Parameters” they think of “ possibly the most anticipated T-SQL feature of SQL Server 2008 .” However, little may you know, that the Data Mining team added table valued parameters and table valued functions...(read more)

More Data Mining in the Cloud?

MSDNArchive — Tue, 12 May 2009 22:46:00 GMT

Since we released the Tech Preview of the Cloud Data Mining Addins last year, I've heard about and talked with Michael Zeller of Zementis. Zementis has been very active in shaping the PMML (Predictive Modeling Markup Language) standard for representing...(read more)

New Cloud Data Mining Addin Usability Feature (well kind of)

MSDNArchive — Fri, 08 May 2009 02:11:00 GMT

Bogdan did an awesome job putting together the modified Data Mining Addin for the cloud. We saw quite a few downloads of his new addin, but surprisingly and unfortunately, didn't see a lot of usage! Strange? Maybe. Bogdan thought about the issue for a...(read more)

In Germany this April?

MSDNArchive — Thu, 05 Feb 2009 21:26:06 GMT

If so – check out the European PASS Conference , if not – maybe you should be! This conference will have a great BI focus including a pre-conference session on using SQL Server BI tools to monitor SQL Server BI tools and at least two data mining sessions...(read more)

Querying the Dependency Net

MSDNArchive — Sat, 13 Dec 2008 00:42:22 GMT

OK, ok, so just yesterday I posted that it was easy to determine what queries were being used by the browsers to get the data underlying the view. Of course it’s easy to get them, but without a teensy weensy bit of documentation, it’s not necessarily...(read more)

Data Mining in SQL Server 2008 Book Review

MSDNArchive — Thu, 11 Dec 2008 21:12:45 GMT

I just read a great review of our book from Richard Lees in Australia. Richard is one of the early adopters of Analysis Service and Data Mining, so he has a lot of experience in this area (we actually reference some of his samples in the book!)...(read more)

Querying like the Data Mining viewers do

MSDNArchive — Thu, 11 Dec 2008 02:11:57 GMT

It happens all the time. You see some cool user interface trick and think “how can I do that?” Or, maybe more likely, you think “gee, that’s useful, where’s that documented?” In any case, if you have ever wondered about how the DM viewers...(read more)

Those kids won't eat anything!

MSDNArchive — Fri, 21 Nov 2008 00:15:53 GMT

I did my BI Power Hour demo at PASS 2008 yesterday and it featured my twin boys Bowen and Logan.

Logan (right) has an ASD (Autism Spectrum Disorder) that limits his diet (no milk products, gluten, or soy) and Bowen has some sensory issues, but that doesn't explain their extreme pickiness with food. For example, I made a rice-yogurt-blueberry smoothie for Logan and he just looked at it and said "yucky!". Ugh.

So, I decided to make a worksheet listing foods they these kids will eat and won't eat. I used attributes of Color, Type, and Processed, along with a column indicating whether or not they will actually eat the food. Of course, I had to answer to myself disturbing questions such as "what color are hot dogs?", but I got through it.

Then I used the Prediction Calculator from the new Table Analysis Tools Excel addin for SQL Server 2008. The Prediction Calculator creates a little widget in Excel that allows you to enter in input values and based on your costs. Running the Prediction Calculator is as simple as selecting your table, clicking the Prediction Calculator button on the Table Analyze ribbon, and then choosing the column and value you want to predict.

There's actually a little more work to do after you run the tool, and that is specifying your costs and profits. Your costs are the cost you incur for getting the answer wrong, and a profit is the profit you make when you get the answer right. The grid below is in Prediction Calculator Report that is created after running the tool. In this case, I figured that if I guessed that my kids would eat some food and was wrong, it would cost me the 5 bucks for the food which would be wasted, therefore I set the False Positive Cost to 5. Furthermore, I figured that if I guessed correctly that they wouldn't eat a food, I saved the money and the 5 bucks would still be in my pocket, so I set the True Negative Profit to 5 as well.

Doing so, gave me a profit chart that looks like below - which is a problem. Basically, what a "always rising" chart says is that you should always say "no" to achieve the highest profit - which makes sense since I can only lose money by saying "yes" and only gain money by saying "no". Essentially my laptop analysis tells me that my kids are simply too picky and I should just make them starve! Hah! My laptop apparently has never had kids!

Anyway, I decided that there's some nominal value for my kids eating, so I changed the parameters a bit. I figured that my kids complaining that I didn't get a food that they wanted causes me the psychological cost of 1 dollar (or maybe the real cost of going back to the store of a dollar, however you want to see it), and I set the False Negative Cost to 1. Also I decided the value of my kids not getting a sugar imbalance and (literally) bouncing off the walls is a "peace of mind" profit of a dollar, so I set the True Positive Profit to 1 as well. This gives me a better behaved profit chart with a peak like below.

Once I've set my costs, I can go to the Prediction Calculator sheet that was created and select my inputs and see if my kids will actually eat the food. In this case, "Yellow, unprocessed, grains and nuts" doesn't exceed the threshold of 642, so the answer is no. Yay! I saved 5 bucks because my kids won't eat corn :(.

Of course, this isn't very useful for me in this format - it's not like I'm going to lug my laptop around the grocery store plugging in values for every product I see on the shelf. So to get around this I use the new, experimental Cloud Data Mining Service. The web interface contains many of the Table Analysis Tools, including the Prediction Calculator, and you can access data from CSV files, SQL Data Services, or another way which is not entirely obvious and not documented by simply pasting your data from Excel to the web.

Once you've pasted your data, I run the Prediction Calculator just like I did in Excel. There's one small difference, however, in the result is that I have the HTML fragment for the calculator itself. Therefore I can make my own web site with the calculator embedded inside.

Once I have my website (which in this case is at

http://www.sqlserverdatamining.com/PASS2008BIPowerHourDemo.htm) I can access the Prediction Calculator from any web-enabled device - like my phone, which I can carry around the grocery store and determine that my kids will eat .... brown.....processed.....meat.... oh yay....

Time's running out for your free data mining book!

MSDNArchive — Thu, 20 Nov 2008 22:52:00 GMT

The survey is now closed. Thank you.

Tomorrow is the last day you can fill out this survey for a chance to win one of ten copies of Data Mining with SQL Server 2008. I used the the Data Exploration tool in the SQL Server 2008 Data Mining Client for Excel, and saw that it takes most people less than 15 minutes to fill it out.

(time to take survey in seconds)

Regarding the book, just today I received my sample copies and I was surprised at how much bigger it is than the 2005 book! It rounds out at 636 pages - I remember last version we were running up against publisher defined page limits and we cut back material to make it fit. This time, we just wrote what needed to be written and the publisher agreed to let us, the authors, make the decisions on how long the book should be. I'm really happy about how the text turned out this time - we still don't have a review on Amazon, so hopefully the public will agree!