Predixion 2.0 @ SQL PASS 2011
If you happen to be at the SQL PASS conference this week in Seattle, you will find us presenting the new version of the Predixion Insight package. We added rich collaborative features and completely new visualizations.
Entries Tagged as 'Excel Add-ins'
Data Mining for the Cloud (or how I spent my summer)
This week, at the KDD (Knowledge Discovery and Data Mining) conference, we (as in Microsoft SQL Server Data Mining team) presented the Table Analysis Tools for the Cloud, a preview for a technology that enables anybody to play with some of the Microsoft’s data mining tools, without any bulky downloads and with zero configuration effort.
Around May this year I practically entered some sort of sabbatical: 3 months to work on an incubation project of my choice (yes, the Microsoft SQL Server organization does this kind of things! if it sounds appealing, check out our recruiting site or, even better, contact directly our SQL Server Data Mining recruiter, Melsa Clarke - melsac AT microsoft DOT com). With some help from Jamie, various management levels and some nice guys in the SQL Server Data Services team, I gathered the infrastructure for a Software as a Service incubation and set up a web incarnation of the Table Analysis Tools add-in for Excel.
Now it is up and running. The entry page is at sqlserverdatamining.com/cloud, so if I got you bored already and you don’t want to read the rest of this stuff, go ahead and browse that page.
What it is
TAT Cloud is a set of canned data mining tasks that you can use without having SQL Server installed on your machine. It consists of encapsulations of some common data mining problems, such as detecting key influencers, forecasting, generating predictive scorecards or doing market basket analysis. The tasks can be executed directly from your browser: just go to the web page, upload your data (in CSV) format and run a tool from the toolbar. Even better, the tasks can be executed directly from Excel. For this, however, you will need to have Excel 2007 and install an add-in which can be downloaded from here.
All tasks work on a table (Excel table or a table in CSV format that you upload to the web interface). All tasks produce reports that can be used to learn more about the analyzed data.
Here is a complete list of features:
- - Analyze Key Influencers: it detects the columns that impact your target column. It presents a report of those values in other columns that correlate strongly with values in your target column.
- - Detect Categories (clustering, for data miners) — identifies groups of table rows that share similar characteristics. A categories report is generated, which details the characteristics of each category
- - Fill From Example — to some extent, similar to Excel’s Autofill feature: it learns from a few examples and extends the learned patterns to the remaining rows in the table
- - Forecasting — analyzes vertical series of numeric data, detects periodicity, trends and correlations between series and produces a forecast for those series
- - Highlight Exceptions — finds the interesting (or unusual, or out-of-ordinary) rows in your table
- - Scenario Analysis — What-If and Goal-Seek tools based on a probabilistic model built on top of your data.
- - Prediction Calculator — a tool for generating prediction scorecards
- - Market Basket Analysis — analyzes transaction tables to identify groups of items that appear together in transactions
All features work from Excel. However, not all the features are implemented in the web interface. They will show up eventually!
A really nice presentation of how to use the tools was written by Brent Ozar on SQLServerPedia. Thanks, Brent!
How it works
Your data (CSV file or Excel spreadsheet) is uploaded to the web service site. There, Analysis Services crunches it and produces the reports you get either in the browser or in a different spreadsheet. The data and the mining model used for analyzing it are deleted immediately after processing. If something bad happens and your session does not conclude successfully (fancy wording for "if it crashes") then both data and models are removed automatically after 15-20 minutes.
You will notice in the Excel add-in (as well as in the web interface) a strange IP address, 131.107.181.99 — this is IP of the service. It will be changed to something cleaner very soon.
Excel uses HTTPS to connect to the service, in an effort to protect your data. However, you should not use this technology on sensitive data.
What it is not
This is not an official shipping Microsoft technology. It is actually less than even a beta. It may crash, it may produce incorrect results, it may be shot down at any time. BTW, I would appreciate if you posted note here or on the Microsoft data mining forums if this happens (particularly about crashes, I should probably know if it gets shut down)!
What next
Well, try the tools: sqlserverdatamining.com/cloud . I recommend downloading the Excel 2007 add-in, rather than using the web application, as it is more functional. Post any questions you might have on the Microsoft data mining forums. And check out this blog periodically for announcements (typically new additions to the web interface) or for more details on how this stuff works
Video Tutorials for Business Intelligence with Excel 2007
MyVBProf.com hosts a set of great video tutorials for performing business intelligence tasks with Excel 2007. Bill Burrows starts with the installation of pre-requisites. He even shows how to make sure that Excel .Net Programmability Support is available! Wow!!
Bill then follows up with a set of tutorials on pivot tables and wraps up with videos on using the Table Analysis Tools from the SQL Server Data Mining add-ins for Excel. His approach is accessible, his style is captivating, his examples are meaningful and very well explained.
Therefore:
1. If you have SQL Server 2005 or 2008 CTP these tutorials give you a great start for BI using Excel and Analysis Service.
2. If you do not have SQL Server, get a free evaluation from http://www.microsoft.com/sql/downloads/trial-software.mspx, then go back to the previous point
3. You don’t have SQL Server, don’t want to try it out, don’t care about the data mining part — fine, just watch some of the tutorials to see what you miss!
Excel data mining cell functions
In the previous post I presented a not-so-documented cell function installed in Excel by the data mining add-ins. Jamie also gave some cool demos featuring these functions. To complete this topic, here is a full description of the cell functions and how they can be used.
The Excel Data Mining Client add-in includes 3 cell functions: DMPREDICT, DMPREDICTTABLEROW and DMCONTENTQUERY. These functions are included in the add-ins for SQL Server 2005, but not documented or supported. However, they will be both documented and supported in the add-ins for SQL Server 2008.
To use these functions, you will need a mining model available on the server. For the examples below, I created a Microsoft Decision Trees mining model, built by running the Classify task in the DM Client add-in over the sample Table Analysis Tools Sample spreadsheet in the sample workbook installed by the add-ins.
Regression accuracy: Excel’s regression vs. the SSDM algorithm
A recent post on the MSDN Forums raised an interesting issue: Excel Data Analysis’s Linear Regression and SSDM were returning different results. Specifically, the SSDM results were much worse.
The issue turned out to be a data modeling issue (columns were not mapped properly). However, during the investigation I had to compare Excel’s linear regression with the SSDM regression algorithm(s). Thought this might be interesting, so here is one way to compare the results from the two implementations.
I started with some simple (X,Y) data (available for download as a CSV file). First step - run Excel’s Data Analysis regression tool. The results are displayed typically in a separate spreadsheet, and the interesting part is the Coefficients sections:
Therefore, the Excel regression formula is
Y = 2.37486095661847*X -0.310344827586214
Next thing — apply Excel’s regression coefficients to the existing data. I did this by adding a column in my data spreadsheet and populating it with a formula:
Next thing, I created a Microsoft Linear Regression mining model on the same data. There is a variety of ways to do this, such as exporting data to a table, connecting directly to the Excel spreadsheet or, the simplest way, by using the Excel add-ins.
To get the model’s predictions in Excel, I used one of the functions exposed by the Excel add-ins, DMPREDICT. If you do not have the add-ins you can always execute a prediction query in SQL Server Management Studio or BI Dev Studio.
However, with the add-ins’s function, getting the prediction results is really easy:
