Microsoft Analytics Platform System Reviews

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and support?

How would you rate customer service and support?

How was the initial setup?

What about the implementation team?

What was our ROI?

What's my experience with pricing, setup cost, and licensing?

What other advice do I have?

Which deployment model are you using for this solution?

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How was the initial setup?

What about the implementation team?

What's my experience with pricing, setup cost, and licensing?

What other advice do I have?

Which deployment model are you using for this solution?

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and technical support?

Which solution did I use previously and why did I switch?

How was the initial setup?

What about the implementation team?

What other advice do I have?

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

How are customer service and technical support?

How was the initial setup?

What other advice do I have?

Which deployment model are you using for this solution?

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How are customer service and technical support?

Which solution did I use previously and why did I switch?

How was the initial setup?

What's my experience with pricing, setup cost, and licensing?

Which other solutions did I evaluate?

What other advice do I have?

Which deployment model are you using for this solution?

What is our primary use case?

What is most valuable?

What needs improvement?

For how long have I used the solution?

What do I think about the stability of the solution?

What do I think about the scalability of the solution?

How was the initial setup?

What about the implementation team?

What other advice do I have?

Which deployment model are you using for this solution?

What is our primary use case?

How has it helped my organization?

What is most valuable?

What needs improvement?

Originally published at https://www.linkedin.com/pulse/microsoft-analytics...

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.

Originally published at www.linkedin.com/pulse/microsoft-aps-hub-spoke-architecture-stephen-c-folkerts

Using Microsoft APS to Build a Hub and Spoke EDW Architecture

Scalable, cost-effective Enterprise Data Warehouse (EDW)
implementation is an elusive goal for many large organizations. Three
common approaches include centralized EDW or the ‘monolithic’ approach.
Decentralized collections of data marts, and attempts at Hub and Spoke
architectures that combine the two. Microsoft APS combines the best
features of both EDW and decentralized data marts.

Centralized EDW

The monolithic approach has become more common in large EDW
installations. However, centralized EDW tends to be extremely expensive
and very inflexible. As a result, business units become frustrated at
the inability of the EDW to meet their needs within a reasonable cost
and timeframe.

Decentralized EDW & Data Marts

A divide-and-conquer strategy is a natural and effective approach to
addressing large-scale problems such as creating an integrated,
enterprise-wide data warehouse. Decentralized EDW architectures align
well with this approach and fit the many compartmentalized demands
presented by large organizations. Decentralized data marts are more
responsive to business unit needs, but often result in many versions of
the same data that are very difficult to keep consistent across the
enterprise. Each approach, centralized or decentralized, tends to
evolve or degenerate into the other, but neither is a tenable long-term
solution for most large organizations.

Microsoft APS & Hub and Spoke Architecture

A Microsoft APS appliance enables a true ‘hub and spoke’
architecture, where the centrally managed ‘hub’ contains detailed
enterprise data, and departments or business units use ‘spokes’ to
exchange data with the hub according to their unique schemas. This
architecture can exist across many connected SQL Server MPP databases.

Microsoft expands the Hub and Spoke concept to include not only MPP
appliances but also standard symmetric multi-processing (SMP) instances
of SQL Server and SQL Server Analysis Services, (SSAS), allowing either
to be viewed as nodes within a grid. The result is a highly flexible,
affordable, and scalable platform that makes large-scale Hub and Spoke
EDW architectures a practical reality. They combine the benefits of
central control and governance with the agility of decentralized data
marts, but without the inherent delivery pains, headaches, risks, and
costs associated with previous strategies.

Hub and Spoke Architectures

Hub and Spoke architectures match the structure of most large
enterprises by offering a combination of a centralized EDW and a set of
dependent data marts. The EDW hub allows the entire enterprise to set
and enforce common standards while answering questions that cut across
business units. The data mart spokes allow business units to meet their
own needs quickly and at relatively low cost while still conforming to
the needs of the overall enterprise.

The Hub and Spoke architecture allows business units to set their own
budgets and priorities, while contributing as necessary to the central
EDW. This close fit between the architecture of the business and the
architecture of the DW platform means Hub and Spoke systems are widely
regarded as the best overall approach. In practice, Hub and Spoke
systems have been notoriously difficult to implement.

Distributing data from a centralized EDW reliably and quickly enough
to meet the needs of the business units is a big challenge in the face
of growing data volumes. To try to compensate for this, complex and
cumbersome ETL processes are developed to transfer data, between the hub
and spokes, resulting in high maintenance costs and an inability to
change with the business. In general, efforts to build a Hub and Spoke
architecture have quickly degenerated into a set of siloed data marts
after being torn apart by conflicting business units and requirements.

One response to the difficulties of building a Hub and Spoke
architecture has been to simply centralize everything onto one
monolithic EDW. A centralized EDW platform quickly becomes overloaded
with conflicting use cases. Solving any one problem requires evaluation
of all existing dependencies, which drives rigid change control
processes and ultimately impacts cost and time-to-delivery for
projects. And if virtualized data marts are used, all the queries and
I/O, execute physically in the hub. Business units become frustrated by
the inability of IT to quickly meet new requirements with the central
EDW and start building their own independent physical data marts as a
result.

Decentralized EDW

With a decentralized approach, which the other two tend to degenerate
into anyway, business units simply build their own independent data
marts. Although such an approach is obviously responsive to business
needs, it doesn’t allow management to answer cross-enterprise questions
easily or quickly. Keeping all copies of data across a decentralized
infrastructure current and accurate can become overwhelming. The
problem becomes worse as relatively low bandwidth data movement options
drive complex data transformations that scale poorly. And it’s very
difficult to apply any real measure of enterprise-wide standards,
controls or regulatory compliance.

Microsoft’s EDW Platform

A Microsoft APS appliance can be viewed as a highly-specialized grid
of servers being pulled together to collectively form an EDW platform.
Taking this view, it is a small step to think of PDW as both a grid of
appliances and a grid of nodes. Moving data across this grid of
appliances is incredibly efficient, since data can be moved directly
from node to node within the grid. This maximizes parallelism across the
environment and minimizes the conversion overhead associated with
export and load operations. Such a grid of appliances can be used to
implement a data warehousing Hub and Spoke architecture.

Microsoft expands the Hub and Spoke solution to include not only MPP
appliances but also standard SMP instances of SQL Server and SSAS to be
viewed as nodes within a grid. A grid of SMP databases and MPP
appliances can be used as the basis for any large-scale data warehouse
environment or architecture. However, it is particularly suitable for a
Hub and Spoke architecture.

MPP for Hub and Spoke

Microsoft PDW with high speed parallel database copy is fundamental
to solving one of the most intractable problems in large-scale data
warehousing. Building an effective, scalable, and affordable Hub and
Spoke solution. The basic idea is to take a divide-and-conquer approach
to building an EDW. This avoids performance problems due to conflicts
between queries from different business units. Provides a dedicated,
high-speed, network interconnecting all hub and spoke databases. And
business analysts view the appliance as a set of separate data marts,
but can drill into detailed data on the hub where required.

The Microsoft Hub and Spoke Solution

Imagine a fairly large MPP appliance acting as the hub for a set of
MPP appliance and SMP database data marts. The hub holds detailed data,
probably in a normalized schema, for a number of business units or the
entire enterprise. The hub is loaded in near real time or in daily
batches from source systems leveraging a preferred ETL solution. Data is
then transformed or restructured to a denormalized structure (star,
cube, etc.), as needed, and transferred to the appropriate data mart(s)
via the high speed grid for consumption by end users. If a data mart
requires data from sources that are not covered by the hub, this data is
loaded independently using standard ETL tools. However, most of the
data required (both fact and dimensions) comes from the hub.

Users connect to the independent data mart appliances as usual for
running queries. This allows each data mart to be tuned for the needs of
a particular set of users and sized to handle the required level of
performance and concurrency. While the data marts can be independently
designed to meet the needs of each business, it will be possible to
leverage existing data mart applications such as Microsoft Analysis
Services, Reporting Services, Excel, or other BI vendor products.

Bandwidth within the grid is large enough to enable the direct copy
of detailed fact data or entire data marts. This can greatly simplify
the data mart creation and update process by using a publish-subscribe
model as opposed to complex transformation logic that, coupled with
expensive export and load scenarios, creates significant challenges for
traditional federated approaches. The end result is an EDW platform
that can handle a very complex workload while being extremely scalable
at a sensible cost.

Disaster Recovery and High Availability

The Microsoft EDW platform provides the capability to set alternate
database systems within the dedicated high speed network as failover
targets. As an example, a user attempting to connect to a spoke that is
currently unavailable would automatically be redirected to an alternate
spoke specified within the standard connection protocol. This simple
approach becomes very powerful when combined with the Hub and Spoke
architecture. The high-speed and bandwidth of the grid copy facility
allows full copies of end-user data marts to be moved to multiple
spokes. This effectively recreates the end-user view of the data on
multiple spoke systems, each a valid failover option for the other in an
outage scenario.

This concept can also be leveraged across multiple data centers to
provide an effective disaster recovery architecture. Individual
appliances can be replicated on a second site and automatically kept
up-to-date. Note that not all of the appliances on a grid would need to
be replicated. In most scenarios only the hubs need to be replicated,
as spokes can be recreated from the hubs. This provides the flexibility
for each business unit to decide whether or not to provide a disaster
recovery capability, based on their own service-level agreements (SLAs).

Microsoft’s Grid-Enablement Strategy

This approach offers customers an attractive alternative to
centralized, monolithic approaches. Data marts can be tailored to meet
the individual needs of business units (both in terms of capacity and
performance). Furthermore, customers can buy into the Microsoft EDW
approach with the deployment of a few stand-alone data marts on standard
SQL Server SMP reference architectures. From this relatively low-cost
start point, you can scale into the hundreds of terabytes while
delivering manageable flexibility without sacrificing cost and
performance.

Microsoft Analytics Platform System (APS)

See my article Microsoft Analytics Platform System (APS) for a more in-depth look at Microsoft APS.

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