Microsoft Analytics Platform System Review

APS combines both Microsoft PDW and unstructured Hadoop analytical capabilities in a single, easy-to-manage EDW appliance.

Originally published at

In April 2014, Microsoft announced the future vision for their data and analytics platforms. Microsoft Parallel Data Warehouse (PDW) was rebranded as Microsoft Analytics Platform System (APS) with additional appliance component offerings. APS combines MPP SQL Server data warehouse with HDInsight, Microsoft’s 100% Apache Hadoop component directly into the appliance. APS is a big data analytics appliance capable of analyzing data of any type, structured or unstructured, and of any size.

Microsoft ASP integrates data from SQL Server PDW with unstructured big data from Hadoop through the PolyBase data querying technology. Polybase gives APS a huge advantage over the competition because it you to talk with big data in the regular T-SQL language you already use and understand.

With APS, NoSQL doesn’t replace relational databases. Structured and unstructured data technologies complement one another, and queries can be executed across both universes.

By integrating Hadoop into the same rack as the relational data warehouse, organizations can save on consulting, development and configuration costs for Hadoop with an integrated appliance.

Why Microsoft APS is an EDW Game Changer

While ASP provides many innovations and improvements, four stand out as strategic game changes CIOs should consider when evaluating data warehouse and analytics strategies. SQL Server PDW, HDInsight, Polybase and xVelocity Columnstores as a Platform for Data Mining and Analysis.

By combining both Microsoft Parallel Data Warehouse (PDW) and unstructured Hadoop analytical capabilities in a single, easy-to-manage EDW appliance, Microsoft APS is well positioned to help organizations use information to enhance their competitive position.

Microsoft Parallel Data Warehouse (PDW)

Microsoft SQL Server Parallel Data Warehouse (PDW) and xVelocity Columnstores are covered in my article Microsoft Parallel Data Warehouse (PDW).

HDInsight & Hortonworks

HDInsight is Microsoft’s 100% Apache Hadoop distribution based on Hortonworks Data Platform. HDInsight is the phoenix that emerged from the ashes of Dryad. Dryad was Microsoft’s own proprietary and competing version of Hadoop that Microsoft tinkered with for 5 years before abandoning it.

PolyBase & Big Data Hadoop Integration

It’s not enough to store data in Hadoop. Businesses today need to figure out how they can analyze Hadoop data fast and seamlessly in order to make more informed business decisions. Unstructured and high volume data are the two fastest growing types of enterprise data.

Organizations are using Apache Hadoop to store process non-relational data from sources like blogs, clickstream data that is generated at a rapid rate, social sentiment data with different schemas customer feedback, sensor data, or telemetry data feeds. Most of this data is not suitable for relational database management systems and often ends up isolated from business users because it is not integrated with data in the traditional data warehouse.

Technologies, like Hadoop are generally used, but implementing Hadoop with traditional data warehouse and business intelligence platforms pose new challenges. Hadoop is both open source, Java-based and manages non-relational data across many nodes. It’s easy to add data to Hadoop, but not so quick to extract and analyze it. The idea is that, if the data is there, it may take a while to retrieve it, but at least the data is stored somewhere in the system. MapReduce doesn't have to be implemented in Java, however.

Big Data is not only about figuring out how to store, manage, and analyze data from non-relational sources, but also about mashing together various non-relational data with an organization’s relational data to gain business insight. See my articles on Big Data Data Lakes & Don’t Drown in the Data and 360 Degree View & Unifying Enterprise Data in a Sea.

PolyBase is the Microsoft APS query tool that enables you to easily query PDW and HDInsight data using T-SQL, without investing in Hadoop-based skills or training.

Microsoft PolyBase is a fundamental breakthrough on the data processing engine which enables integrated query across Hadoop and relational data. PolyBase opens up a whole new world of data analysis and integration possibilities. This integration allows organizations to merge large volumes of non-relational data stored within Hadoop with their traditional enterprise data. Customers can continue to use their existing analytics tool set to analyze their organization’s big data.

Without manual intervention, PolyBase Query Processor can accept a standard SQL query and join tables from a relational source with tables from a Hadoop source to return a combined result seamlessly to the user. Queries that run too slow in Hadoop can now run quickly in PDW, data mining queries can combine Hadoop and PDW data, Hadoop data can be stored as relational data in PDW, and query results can be stored back to Hadoop.

By using the power of Microsoft APS to run queries on Hadoop data in HDInsight, it is now possible to do more in-depth data mining, reporting, and analysis without acquiring the skills to run MapReduce queries in Hadoop. PolyBase gives you the flexibility to structure the Hadoop data you need, when you need it, as it’s brought into PDW for fast analysis. You can seamlessly select from both Hadoop data in HDInsight and PDW data in the same query, and join data from both data sources. To satisfy a query, PolyBase transfers data quickly and directly between PDW’s Compute Nodes and Hadoop’s Data Nodes.

APS uses external tables to point to data stored in text files on a Hadoop HDFS cluster. Once an external table is created, the table can be used in a select statement in the same manner as a PDW table. PolyBase uses a single Transact-SQL query interface to leverage PDW and Hadoop, so you don’t need to learn a host of new skills to run MapReduce queries in Hadoop. PolyBase hides all the complexity of using Hadoop so most business users do not need to know anything about Hadoop.

PolyBase uses ‘predicate pushdown’ to Hadoop that generates map-reduce jobs behind the scenes to do the work on the Hadoop side instead of distributed query data movement when necessary.

With PolyBase, organizations can take advantage of flexible hybrid Hadoop solutions and query across Hortonworks, Cloudera, and even into the cloud with Microsoft Azure HDInsight. PolyBase is only available in Microsoft APS and is not available in SQL Server SMP at this time.

Integration with Business Intelligence Tools

APS has deep integration with Microsoft’s BI tools and other leading non-Microsoft tools, making it simple to use the BI tools you are familiar with to perform analysis. APS’s deep integration with Business Intelligence (BI) tools makes APS a comprehensive platform for building end-to-end data mining and analysis solutions. APS integrates with the Microsoft BI Stack including Reporting Services, Analysis Services, PowerPivot for Excel, and PowerView. But, APS also integrates with a growing list of leading non-Microsoft BI platforms, such as Business Objects, Cognos, SAP Data Integrator, Tableau, MicroStrategy, QlikView, Oracle Business Intelligence, and TIBCO Spotfire.

Easy to Use & Manage

APS is designed for simplicity. The complexity is already engineered into the appliance so that you don’t have to handle the details. The appliance arrives with the hardware and software already configured and installed. PDW handles all the plug and play details of distributing the data across the appliance nodes, performs all the extra steps required to process queries in parallel, and manages the low-level hardware and software configuration settings. No tuning is required because the appliance is already built and tuned to balance CPU, memory, I/O, storage, network, and other resources.

Minimal Learning Curve

EPS has a minimal learning curve. There’s no need to hire new talent in order to move from SQL Server SMP to SQL Server PDW and EPS. DBAs who already know T-SQL can easily transfer their SQL Server SMP knowledge to PDW. Some T-SQL query statements are added or extended to accommodate the MPP architecture. There’s less DBA maintenance. You don’t need to create indexes besides a clustered columnstore index. DBAs can spend more of their time as architects and not baby sitters. In my opinion, the alignment of APS with existing IT skills may be its biggest competitive advantage.

The appliance model is key to getting great performance. Tuning a large database using traditional approaches is extremely difficult and requires highly skilled DBAs. One of the main problems with the SMP model, is the difficulty of understanding and tuning the interface between the DBMS software and the underlying OS and hardware platform. With SMP, there are a plethora of tuning parameters and options for the DBA and OS administrator to setup. In the appliance model, the entire software and hardware stack from SQL to storage is automatically controlled. As a result, virtually all the complexity is removed.

Manageable Costs

Microsoft APS has manageable costs. APS has lower price/terabyte over other companies by a significant margin. About 2x lower than Teradata, Oracle, Greenplum and others. It’s worth noting that Microsoft’s offering is cheaper than the competition not because of lower quality or missing capabilities, but because of a different business strategy. The strategy of commoditizing markets and then selling higher volumes to make up for lower margins. Given that SQL Server is one of the most popular enterprise databases on the planet, and APS falls under the SQL umbrella, it has enough of a relative advantage that it could easily become the biggest Big Data appliance player of all.

Microsoft APS & Hub and Spoke Architecture

See my article Microsoft APS & Hub and Spoke Architecture about using Microsoft APS to Build a Hub and Spoke EDW Architecture.

These views are my own and may not necessarily reflect those of my current or previous employers.

**Disclosure: I am a real user, and this review is based on my own experience and opinions.
More Microsoft Analytics Platform System reviews from users
...who compared it with Oracle Big Data Appliance
Find out what your peers are saying about Microsoft, Teradata, Oracle and others in Data Warehouse. Updated: June 2021.
510,534 professionals have used our research since 2012.
Add a Comment

author avatarit_user841746 (Application Developer at a tech services company with 10,001+ employees)
Real User


Muchas Gracias Mi Amigo! You make learning so effortless. Anyone can follow you and I would not mind following you to the moon coz I know you are like my north star.

As I need to apply color in Bar graph based on below mentioned condition,
bar BOP = blue
- bar In = red
- bar Out = green
- bar EOP = Green if EOPBOP
Note : BOP, EOP, IN and OUT are values of one 'Trend' column.
On X Axis we need to take 'Trend' column and on Y Axis we need to take uniquecount(Customer Number) column.

But great job man, do keep posted with the new updates.


author avatarit_user845028 (Staff Software Engineer at a tech services company with 10,001+ employees)
Real User

Hi Man,

Nice to be visiting your blog again, it has been months for me. Well this article that i’ve been waited for so long.

Currently, we are manually starting up our Interior and Exterior servers and I have the task of scripting that startup in Red Hat Linux 5/6/7 and then being able to validate that startup.
Is there documentation available for this, or has someone created a method and could direct me to a way to handle this task?
I have basic knowledge of TIBCO, including some installation, but my primary job is supporting the entire infrastructure TIBCO lives in not the day to day operations. So I may have missed some areas in the documentation that are pertinent to my question.
I have to assume that whatever tasks happen in the GUI have underlying code or scripts that could be deployed in an automated fashion.
THANK YOU!! This saved my butt today,
I’m immensely grateful.


author avatarit_user852315 (Senior Technical Recruiter at a training & coaching company with 201-500 employees)
Real User

Hi Bru,

Smokin hot stuff! You’ve trimmed my dim. I feel as bright and fresh as your prolific website and blogs!

I have an event that comes from EMS Queue , lets say Student. I have two rules, A and B. Lets say A has no condition, but B has condition student_event.age>10. A does not consume the event but B rule has student_event.consume(). Student event has TTL as 10 mins.
But nice Article Mate! Great Information! Keep up the good work!

Merci Beaucoup,