7
$\begingroup$

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.

enter image description here

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.

enter image description here

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?

$\endgroup$
  • 1
    $\begingroup$ R will be integrated with SQL Server 2016. Now you have the best of both! $\endgroup$ – Michael Green Nov 20 '15 at 11:35
  • $\begingroup$ Yeah, I saw that, but you still have to upgrade or purchase 2016. Not easy to do that at the drop of a hat. $\endgroup$ – Fastidious Nov 20 '15 at 22:27
  • $\begingroup$ We're asking the same question. It appears R can already read/write from.to SQL, So I don't understand what SQL 2016 will offer. Maybe you can run R packages within SQL? $\endgroup$ – Sir Swears-a-lot Jan 21 '16 at 1:51
2
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ Thanks. Most of the data I'm analyzing is in multi-dimensional form. NoSQL solutions and other cloud based solutions are not really an option for me. Thus, it doesn't make sense to look towards Azure. However, I'll check out the blog post. Unfortunately, SSAS hasn't really improved all that much over the years and I am on 2008 R2. $\endgroup$ – Fastidious Mar 27 '15 at 11:45
  • $\begingroup$ By the way, can you add more insight on the limited statistical methods and algorithm variety? I assumed you could develop your own methods and algorithms with SSAS just like you could with R? Or do you mean the availability of those packages is not as common as the open source community? $\endgroup$ – Fastidious Mar 27 '15 at 11:51
  • $\begingroup$ @Fastidious: You're welcome. Re: your first comment (I'll address the second one in the next comment). I don't understand your rationale behind rejecting Azure ML. While I'm not a big fan of Microsoft solutions, but for those who are tied to that technology stack, Azure ML seems like a sensible (while, for some, still might not be the best) option. Azure ML has direct ties with Azure, which, being a general cloud platform, supports whatever you throw at it. Wrap whatever environment and tools you use into (or install them separately on) a virtual machine and launch it. It is not cheap, though. $\endgroup$ – Aleksandr Blekh Mar 27 '15 at 11:55
  • 1
    $\begingroup$ No worries. We do use R and feed data to it via SQL Server. I just wanted to see if we can still leverage everything in SQL too. $\endgroup$ – Fastidious Mar 27 '15 at 17:55
  • 1
    $\begingroup$ To whomever suggested removing my first bullet point: I have rejected your edit suggestion. Firstly, Microsoft doesn't own R, which is an open source project. The fact that MS acquired Revolution Analytics (RA) and integrated R, a subset of R huge ecosystem and RA's product into MS stack doesn't make R equally "comfortable" environment versus SSAS. Secondly, in any case, SSAS and other relevant MS' products are still more integrated with/into MS ecosystem for the near-term, at least. This is my opinion and I stand by it. As such, I'd like my answer to remain as is. Thank you for understanding. $\endgroup$ – Aleksandr Blekh Mar 16 '17 at 8:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.