Microsoft goes All-In on Analytics

It has been a busy few months in the data analytics space, especially at Microsoft.  To say the least.  Next year will be even busier, when many of these solutions converge further.

Microsoft announced General Availability of PowerBI, and brings many of its cloud features to the desktop.

Jen Underwood reveals the splendor in PowerBI Desktop.  PowerBI Desktop can be published to a Pyramid Analytics server for on-prem solutions.  PowerBI isn't just in the cloud anymore.

Microsoft acquires Datazen, and some multidimensional dashboards come out of a DataZen server in SQL 2016.

SQL 2016 with R & JSON integration.  Hold up, Revolution Analytics is coming to SQL?  JSON in SQL?  No more FOR XML madness?

New JSON support in T-SQL, the SQL Server query language, includes OPENJSON to parse JSON text and return tabular data, JSON_VALUE and JSON_QUERY for querying JSON data, and ISJSON for validating JSON-formatted text.

The syntax for the R external functions seems suspiciously familiar to what Apache Hive provides..

PIP INSTALL JUPYTER

ML Blog Team talks about Jupyter Notebooks for Azure ML Studio and Nitin Mehotra gets us started with Zeppelin Notebooks and Apache Spark on Azure HDInsight.

Michael Erasmus gives us an interesting mashup of Python and SQL running in a Jupyter notebook.
http://michaelerasm.us/redshift-and-ipython-bffs/ 

What's nice about this is the inline SQL statement, effectively removing the need for quoting & appending multi-line SQL together.  What else is useful is the ability to capture the results of the SQL query as a data frame for analysis and visualization.

A similar method might be used with MDX queries and Analysis Services OLAP cubes.
http://bkanuka.com/articles/python-connect-to-olap/

Perhaps if you have some web services connected to Excel Services or PowerPivot, this method could get really useful for exposing your data.  I would imagine writing some DAX queries nicely formatted in Jupyter python and returning data frames to R would be of interest for a couple people... well maybe just me. ;)

Speaking of DAX...

Kasper de Jonge writes about SQL 2016 includes over 50 new DAX functions
http://blogs.msdn.com/b/analysisservices/archive/2015/09/02/what-s-new-in-microsoft-sql-server-analysis-services-tabular-models-in-sql-server-2016-ctp-2-3.aspx

Some of the cool ones are Natural Inner Join,

CONCATENATEX(Employees, [FirstName] & “ “ & [LastName], “,”)
Returns "Alan Brewer, Michael Blythe"

SUBSTITUTEWITHINDEX (Left Semi Join in PowerPivot?)

ADDMISSINGITEMS (What do you mean we have no sales for March in Tuvalu? We need to see a March column for Tuvalu on the report!)

Only 46 functions left to check out...