Jamie's Junk

Happy New Year with a big bang!

2010-01-01T20:05:00Z

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 the founding CTO of Predixion Software. It's too early for me to say exactly what we will be creating over there at Predixion, but it's safe to say that SQL Server Data Mining will feature prominently in our business. I don't have an "official" landing...(read more)

The amazing flexibility of DMX Table Valued Parameters

2009-09-25T01:58:57Z

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 to DMX in SQL Server 2005 . Not only has this feature been in shipping code for more than four years as of this writing, the DMX implementation is considerably easier to use as well. This blog entry is one of those that has been requested so...(read more)

More Data Mining in the Cloud?

2009-05-12T22:46:00Z

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 learned knowledge in XML format. They have a product called ADAPA that allows you to score, or predict from, arbitrary data sets using arbitrary PMML models. Recently they released a version of ADAPA for the Amazon Cloud service and it has been...(read more)

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

2009-05-08T02:11:00Z

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 while and from a few anecdotal comments determined that once installed, the addin can be pretty hard to find. So, the new usability feature is a full page writeup on how to find the addin at http://www.sqlserverdatamining.com/cloud/ once you click...(read more)

In Germany this April?

2009-02-05T21:26:06Z

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, including one from Steve Simon of State Street Corporation talking about how they personally leverage SQL Server Data Mining to manage their risk exposure. Not another talking head from the product team (aka “me”) spouting the general awesomeness...(read more)

Querying the Dependency Net

2008-12-13T00:42:22Z

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 easy to understand what the parameters mean or possibly what the results mean. For instance, take the dependency net – everybody loves the dependency net – it’s cool and shows that “high-level information” that everybody craves. For example...(read more)

Data Mining in SQL Server 2008 Book Review

2008-12-11T21:12:45Z

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!). You should check out his blog anyway, just to learn from a master and expand your BI expertise!...(read more)

Querying like the Data Mining viewers do

2008-12-11T02:11:57Z

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 get the data to display on the screen, then this is the post for you. In many cases, what is displayed in the data mining viewers is the result of built-in stored procedures which allow the processing required for the view to be done on the server...(read more)

Those kids won't eat anything!

2008-11-21T00:15:53Z

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!

2008-11-20T22:52:00Z

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!