DM(X)

Predixion Insight 1.2 shipped today, with quite a few new features: PMML support, data profiling, data normalization, Visual Macros for all the tools, VBA API integration  and many others.

The web site has a new look, our Support forums are now up and running  — feel free to post your questions, for Predixion Insight or in general, for Data Mining in Excel.

Predixion Insight has now a splash screen with links to Video Tutorials, our online documentation and a rich sample dataset.

One feature that I love in this release is the Data Profiling feature. With a couple of clicks, this feature will compute common statistics for all columns in your PowerPivot or Excel dataset, and render a report allowing you to explore these statistics.

to try it out, go to Insight Analytics \ Explore Data and select Profile Data.

Like all other Predixion Insight tasks, this allows you to select an Excel range or table or a Power Pivot table as your data source for profiling.

The second pane of the wizard allows one to select the type of statistics to be collected: Basic or Advanced. Advanced is a bit slower (think milliseconds to seconds), but so much more useful.

The Data Profiling result is rendered in a new Excel sheet, with a few sections:

Data Summary

the first section, Data Summary, tells you what kind of columns were detected in your data and it looks like this:

CustomerID is identified as a likely key (a unique row identifier), so statistics will not be collected for this column

Continuous statistics

This section contains information about numeric columns. Here are the profile columns generated under Advanced analysis:

- #  - the ordinal of the column in the source table. Useful if, after  sorting by any other measure, one wants to restore the table to the original layout

- Column Name

- “Looks Like” –  the result of the Predixion Insight heuristic analysis of the column. A column may be regarded as Interval (a range of values), Multinomial (a few distinct value, hence categorical or discrete), Binomial (2 values) or Constant (a single value)

- Count of Blanks, Minimum, Maximum, Mean, Sample Variance, Standard Deviation, Range, Kurtosis, Skewness, Standard Error of Mean, Approximate Median, Mode, the 95% confidence interval (assuming normal distribution). If you feel rusty about any of these metrics, just click on the column name and your browser will give you a friendly refresher

Discrete Statistics

Ordinal, Column Name, “Looks Like” and Count of Blanks appear again, just like in Continuous Statistics. The number of distinct states is added for discrete columns.

Other columns:

- Top 80% states – number of distinct states covering 80% of the data

- The Top 3 most/least popular  values, with their respective counts

The report can be filtered by column name. You may notice that certain columns appear both in the Continuous and the Discrete section of the report. Such columns may have a numeric type but a distribution which suggests they should be regarded as categorical. The Data Profile report should help you decide whether to use Classify or Estimate for such columns. (If in doubt, this may help: No model for Constant, Estimate for Interval, Classify for all others)

Correlation/Covariance

For numeric columns, here are the correlation and covariance matrices. While not very visible in this dataset, the correlation matrix uses a heat map from 1 (green, perfect positive correlation) to –1 (red, perfect negative correlation). White cells show no correlation (a value of 0). The Correlation matrix can be sorted by correlation for each individual column – sorting it by Column Index, #, will restore the shape of the matrix

Tags: Cloud, DMX by bogdan
1 Comment »

The point: I am proud to invite you to enjoy our new product, Predixion Insight. It is a cloud-based predictive analytics service that can be accessed from Excel 2007 or 2010 (32 or 64 bit) and allows you to  perform advanced analytics on your Excel or PowerPivot data.  To install the Predixion  Insight client, just go to https://www.predixionsoftware.com/products , download Predixion Insight for Excel and subscribe to our Free Beta Trial.

The rest of this post is just me bragging about how cool Predixion Insight is and how one can have fun with it.

A few years ago, I was working with my adviser on a paper trying to offer biochemists a predictive tool for the bioactivity of HIV-1 protease inhibitors. I am a bit ashamed to admit this, but to this day an “HIV-1 protease inhibitor” is, for me (just like the day I first met one), mainly a vector of 4 numbers in one dataset and 25 numbers in another.

In analyzing those datasets, I used a few different tools, including the Microsoft data mining add-ins. Excel, empowered by the data mining add-ins, was by far the best data playground for me at the time (naturally, no trace of bias here!).  What made this experience memorable is that, after working as a developer on those add-ins for a few years, I was using them as a user and not for a test or demo nor  on a customer’s dataset.

Today, I played a bit with those datasets and tried to replicate that work using Excel 2010, PowerPivot and Predixion Insight. I tried to use the combo as a real user, not as a developer, and see how I can solve a specific problem.

Here are a few things that marked the experience. I will try to blog about each individual feature in more detail in the upcoming weeks, so here is just an enumeration

- Asynchronous task execution. I can launch a task (say, a neural network processing task) and then, while the clouds are crunching the 25-dimensional protease inhibitors, I can schedule additional tasks or use excel. Even better, the processing results are cached in the cloud and available for retrieval when the  job completes, from wherever I log in. Naturally, result sets can be retrieved multiple times

- Power Pivot integration: on one hand, it is nice to know that you can analyze more than 1M rows. However, I found sampling pretty useful in solving this issue, at least for modeling. I found more interesting the fact that I can synthesize very rich datasets using power pivot. Some things are just native there (cross table RELATED calls, aggregations). Others are added by Predixion Insight (outlier and missing values handling, binning of numeric data). All these transformations are wonderfully refreshable and can be nicely applied to new data.

-  Visual Macros: these are executable reports generated by Predixion Insight for Excel, reports which make repetitive tasks much easier. In brief, after going through a wizard, such as specifying which of 25 input columns should be used in a regression model, a script is generated, script which can be modified and re-executed. Also, multiple such scripts can be chained on the same worksheet and executed as a package. This way , creating 5 regression models on the same 25 columns, with different algorithm parameters, stops being boring. These macros can be used for queries as well and I intend to show some Visual Macros based application worksheets in a future post

Add the fact that I ran the experiments on a tiny laptop running just Office and you’ll see why  the 2010 version of the Excel-based advanced analytics tools is my new preferred data playground. Hence, the point at the beginning of this post. Enjoy!

Technorati Tags: Predixion,Insight,Excel,PowerPivot

Tags: DMX by bogdan
4 Comments »

Predixion is getting ready for show time. Today is the first day of the Predixion Insight invitational Beta, the first product from our company. This Beta will become public shortly (mid August) so if you are a Data Miner, a PowerPivot pro or  Excel user please keep an eye on www.predixionsoftware.com to get more information. In fact, you should probably check out that page today (if you didn’t recently) as it contains a very crisp description of the Predixion vision for removing the barriers to predictive analytics.

Ever since two years ago when I spent my summer developing a cloud prototype I got hooked on the potential of predictive analytics in the cloud. Predixion Insight is unlocking that potential. I hope and believe that you will find our product a powerful instrument for analyzing your data, anywhere, anytime. We certainly put a lot of work in making it so.

Some information about the product features is already available on Jamie’s blog . I will follow up with more posts describing in detail some product features (after the public Beta becomes available).

Tags: DMX by bogdan
1 Comment »

As of today I am working as a Principal Architect (tada!) for Predixion Software, a predictive analytics startup in Redmond, WA. So far, it’s pretty close to the Encarta definition of exhilarate! 

The company aims to bring predictive analytics technology within reach of every information worker. My past professional incarnation taught me that it may be quite a challenge to make statistics and data mining appealing and intuitive. It also taught me that this is exactly what many users actually expect from an advanced analytics solution, so I jumped at this challenge. (By the way, if you find this kind of problems interesting and appealing then check out our job posting at stackoverflow).

The fact that  Jamie MacLennan is the CTO of this company also helped. I worked for quite a few years with Jamie on Microsoft’s SQL Server Data Mining and I am really looking forward to building other cool products.

So – until Predixion has a product I will keep posting about SQL Server Data Mining but also about my trip in the startup world. Meanwhile, if you use SQL Server Data Mining, take this survey  and win a copy of Jamie and Bogdan’s book!

Tags: DMX by bogdan
4 Comments »

Today has been my last day at Microsoft. I spent more than 10 years with the company and loved every bit of these 10 years.  If not for a very promising startup, I would probably be looking forward to spending the next 10 years with Microsoft, in the same BI area that made me write this blog.  My best wishes to the company, the technology and, above all, to the wonderful people that made me love my Microsoft journey!

The next, very-soon-to-show-up-here post will discuss where I am going and what I will be doing there. Talking about future, though, is talking about work, which would spoil this weekend.

This blog will come back to life (although it might move to a different platform). The main topic remains Data Mining and predictive analytics, the core technology remains SQL Server Analysis Services.

Regards,

bogdan

PS – The Cloud Table Analysis Tools service completed its prototype mission and has been retired. Thanks a lot to all the users! More in my next post

Tags: DMX by bogdan
3 Comments »

I will try to continue the series started by Jamie, presenting the other set of queries issued by the Microsoft Association Rules viewer. Recently, a question on these queries appeared on the MSDN Data Mining Forums and the poster raised a very good point: while the stored procedures were intended as internal calls for the built-in viewers, external applications and viewers may want to employ them.

So, here is how the rest of the Association Rules viewer works.

Once the viewer is loaded, the first call is something like:

CALL System.Microsoft.AnalysisServices.System.DataMining.AssociationRules.GetStatistics(’Customers’)

The single parameter of this stored procedure is the mining model name.  The result is a one-row table containing the following columns:

[Read more →]

Tags: DMX by bogdan
3 Comments »

On Saturday, November 15th, the connection to the Table Analysis in the Cloud URL is broken. Until the problem is identified and fixed, here are some workarounds:

- For the web interface, use the http://www.sqlserverdatamining.com/cloud URL

- For the Excel add-in, please change the services connection URL. To do that, click the Connections button in the "Analyze (in the Cloud)" ribbon and change the destination URL to

http://131.107.181.101/CloudDM/TATServices/

NOTE: This temporary solution does not support SSL. Your data is transmitted in clear

I’ll post here an update as soon as the servers are up again

Tags: DMX by bogdan
1 Comment »

The “Data Mining with SQL Server 2008″ book  now has a blog. You can check it out at http://www.SqlDataMiningBook.com

Soon enough, there will be some content there:

- various data mining related postings from Jamie and me — all the product related postings on this blog will be replicated on the book’s blog . With a bit of discipline, all the postings will be tagged with the relevant chapter number

- errata (well, hopefully that won’t be the main topic of the blog )

- any new downloads or other information that may be relevant for the readers

Tags: DMX by bogdan
1 Comment »

The new version of the SQL Data Mining book is finally available, at least at Amazon. If you are currently SQL DM user, you have an opportunity to get a free autographed copy  by filling out a short survey about the way you use SQL Server Data Mining. For more details about the survey, check Jamie’s blog post.

More details about the book here: Data Mining with SQL Server 2008

Tags: DMX by bogdan
1 Comment »

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

Tags: Cloud, DMX, Excel Add-ins by bogdan
12 Comments »