How does SQL Server Analysis Services compare to R?

Question

This may be too broad of a question with heavy opinions, but I really am finding it hard to seek information about running various algorithms using SQL Server Analysis Service Data Mining projects versus using R. This is mainly because all the data science guys I work with don't have any idea about SSAS because no one seems to use it. :)

The Database Guy

Before I start, let me clarify. I am a database guy and not a data scientist. I work with people who are data scientist who mainly use R. I assist these guys with creating large data sets where they can analyze and crunch data.

My objective here is to leverage a tool that came with SQL Server that no one is really leveraging because no one seems to have a clue about how it works in comparison to other methods and tools such as R, SAS, SSPS and so forth in my camp.

SSAS

I have never really used SQL Server Analysis Services (SSAS) outside of creating OLAP cubes. Those who know SSAS, you can also perform data mining tasks on cubes or directly on the data in SQL Server.

SSAS Data Mining comes with a range of algorithm types:

• Classification algorithms predict one or more discrete variables, based on the other attributes in the dataset.
• Regression algorithms predict one or more continuous variables, such as profit or loss, based on other attributes in the dataset.
• Segmentation algorithms divide data into groups, or clusters, of items that have similar properties.
• Association algorithms find correlations between different attributes in a dataset. The most common application of this kind of algorithm is for creating association rules, which can be used in a market basket analysis.
• Sequence analysis algorithms summarize frequent sequences or episodes in data, such as a Web path flow.

Predicting Discrete Columns

With these different algorithm options, I can start making general predictions from the data such as finding out simply who is going to buy a bike based on a predictable column, Bike Buyers, against an input column, Age. The histogram shows that the age of a person helps distinguish whether that person will purchase a bicycle.

Predicting Continuous Columns

When the Microsoft Decision Trees algorithm builds a tree based on a continuous predictable column, each node contains a regression formula. A split occurs at a point of non-linearity in the regression formula. For example, consider the following diagram.

Comparison

With some of that said, it seems I can run a range of algorithms on the data and also have various functions available to me in SSAS to run against the data. It also seems I can develop my own algorithms in Visual Studio and deploy them to SSAS (if I'm not mistaken).

So, what am I missing here in regards to languages and tools from R? Is it just that they have more flexibility to deploy and edit complex algorithms versus SSAS etc?

Answer 1

In my opinion, it seems that SSAS makes more sense for someone who:

• has significantly invested in Microsoft's technology stack and platform;
• prefer point-and-click interface (GUI) to command line;
• focus on data warehousing (OLAP cubes, etc.);
• has limited needs in terms of statistical methods and algorithms variety;
• has limited needs in cross-language integration;
• doesn't care much about openness, cross-platform integration and vendor lock-in.

You can find useful this blog post by Sami Badawi. However, note that the post is not recent, so some information might be outdated. Plus, the post contains an initial review, which might be not very accurate or comprehensive. If you're thinking about data science, while considering staying within Microsoft ecosystem, I suggest you to take a look at Microsoft's own machine learning platform Azure ML. This blog post presents a brief comparison of (early) Azure ML and SSAS.