It has been a while since I lasted looked at what Microsoft is offering for Business Intelligence so I was pleased to be able to attend the Senturus webinar last week, Microsoft for BI and DW. Microsoft BI encompasses an array of products in four distinct categories: Power BI, Azure, Microsoft Office and SQL Server.
Power BI is the Microsoft SaaS tool for creating dashboards, reports and visualizations. The interface is accessible through the web browser and it is mobile friendly. It is free to start using it and there are several pre-built dashboards for popular SaaS applications such as Salesforce, Quickbooks Online and Marketo. Power BI can also query data from on premise databases, spreadsheets stored in Microsoft One Drive and Azure.
There are several Azure products to enable enterprise data in the cloud. For developers, supported languages included C# and .Net (of course) and in some cases Java and Python. Licensing is a subscription fee based on usage.
HD Insight provides managed Hadoop clusters. It is an easy way to set up and scale Hadoop. The most popular Hadoop tools are included: Map Reduce, Pig, Hive, HBase, Storm and Spark.
Data Factory is ETL in the cloud. It can integrate data from other cloud providers and from databases hosted in private clouds and enterprise data centers.
Relational Database Service is SQL Server in the cloud. The advantage of Azure is that the host server does not have to be maintained. This offering is targeting SaaS application developer who need a scalable and high performance relational database back end.
SQL Data Warehouse is still in preview mode. It is Microsoft’s answer to Amazon Redshift, providing a massive scalable database for structured and unstructured data. It offers many but not all of the features of SQL Server. For example, primary and foreign keys are not supported.
Stream Analytics is the Azure service for real time data processing. This service is intended for Internet of Things (IoT) application developers so it is less interesting from a BI perspective.
Azure Machine Learning is the predictive analytics cloud platform for running data experiments and deploying predictive models as REST APIs. Python and R are supported as development languages.
Microsoft Office
photo credit: Introducing Microsoft Windows 95 via photopin (license)
Excel remains a key piece of the Microsoft BI stack. Microsoft continues to leverage the popularity of its flagship Office line of software to push into the enterprise BI space. There are a number of add-ins available to turn Excel into a powerful self-service BI tool.
PowerPivot – An add-in to turn Excel into a self-service data integration and reporting tool. It works by building a data model on the fly, then storing the data in memory like a columnar database. This allows a user to work with a large amount of data in Excel (anecdotally, tens of millions of records is possible).
PowerQuery – This add-in allows Excel to connect to cloud data sources that can be accessed via a URL. Some data transformations are permitted.
PowerView – Excel add-in to browse multidimensional cube data in Microsoft SQL Server Analysis Services (MSAS).
PowerMap – Excel add-in for plotting geo data and creating maps.
Data Mining – Adds to Excel the tools required for building and testing models against SQL Server data.
There are two other parts of Office on the periphery of the BI stack: SharePoint, which from can serve as a portal or repository for storing and organizing report output. The venerable Access database still exists, which can connect to almost any other relational database, effectively serving as a front end or integration point for larger data sources.
This is the oldest part of the Microsoft BI stack but there are a few new interesting items.
SQL Server – The all purpose relational database. It is proven and mature technology, well suited for data warehouse work.
SQL Server Analysis Services (MSAS) – This is OLAP functionality for SQL Server data, used to create a multidimensional cube structure. What is new in MSAS is tabular modeling which builds an in-memory, columnar database structure for high performance reporting. Tabular and multidimensional models are complementary solutions each with their own optimal use cases.
SQL Server Integration Services (MSIS) – The Microsoft ETL solution which supports most data sources (not only Microsoft data sources.)
SQL Server Reporting Services (MSRS) – A set of tools for developing and deploying reports.
Master Data Services – The MDM (Master Data Management) tool for implementing data standards and enabling data governance.
Data Quality Services – Tools for cleansing and enhancing data.
Commentary
As per a long standing Microsoft tradition, Excel remains a key piece of their BI stack. This is great for power users who are comfortable with self-service BI. This might not be right for organizations that are no longer interested in supporting powerful Windows desktop PCs or in environments where reports need to be delivered to users with low technical skill levels. There also has to be an acceptance that data will continue to live in Excel spreadsheets.
The other dominant theme is the Azure cloud platform. Microsoft is pushing their products in this direction and there are some compelling benefits of a cloud BI/DW deployment: reduced infrastructure costs (less CapEx), faster deployment to dispersed users, scalability and easier long term maintenance.
Like Excel, cloud based BI it is not always the right choice for every project. The following factors must be weighed carefully before deciding on a cloud option: the volume of data already inside the firewall, available bandwidth, security requirements and the complexity of cloud interoperability. At first glance it looks very inexpensive to get started with Azure but the cost increases quickly once you get into the hundreds of users and terabytes of data.
Microsoft offers a modern and comprehensive BI stack, especially for organizations already invested in the Microsoft ecosystem (Windows Servers, Microsoft SQL Server, Office, etc. ). Azure is a serious enterprise ready cloud option for Business Intelligence and Data Warehouse infrastructure which makes the Microsoft BI stack even more attractive.