Under the Hood: Insight from the Ironworks Technology Group

Parallel Data Warehouse Version 2 and Polybase; Seamless querying of structured and unstructured data

Last November (2012) Microsoft introduced the second version of its massively parallel processing (MPP) architecture called Parallel Data Warehouse. There are some distinct differences between version 1 and version 2. Two of the biggest differences are:

1) PDW ships installed on a Dell appliance.

2) The inclusion of Polybase.

 PDW V2 is built using Dell’s Power Edge 12^th generation servers. Some of the advantages that are included in this construction are:

• Lower price point (backup and landing zone servers not longer required)
• Increased CPU power (16 vs V1′s 12 cores per compute node)
• Increased Memory (256 gb vs V1′s 96 gb per server)
• Increased storage/scalability (support for up to 5 Petabytes of disk space vs V1′s 610Tb)
• Infiniband Data Movement Speeds: FDR (14x bandwidth) vs V1′s QDR (4x bandwidth)
• Microsoft V2 enhancements include “direct attached” SAS JBOD storage, Hyper V, Mirroring, and Server 2012 Storage Spaces

Putting aside the advantages related to the hardware, this article aims to focus on Polybase which has quickly become a big part of Microsoft’s Big Data strategy.

Polybase is Microsoft’s entry to the cross platform query tool which enables users to write T-SQL code to simultaneously query relational and unstructured data. By allowing users to query in the familiar T-SQL code, Polybase eliminates the need to learn a new query language (MapReduce) and allows for the combination of the different data stores at the query level. The cross platform querying allows you to leave Hadoop data in its unstructured state and thus eliminates the need to populate a Data Warehouse with Hadoop data.

All Hadoop data has some inherent structure and Polybase analyzes this structure and brings the schema of the HDFS cluster into the metadata of the relational database which allows the Hadoop/unstructured data to be treated as a local table. There are a few preliminary steps that need to be performed, such as registering the Hadoop cluster (you can register many clusters as this is not a one-to-one relationship) and setting the file format for the cluster, however these are one time workloads that when completed enable you to create what is essentially a view over the HDFS data which is accessible to the relational query engine.

Polybase introduces the CTAS functionality, ‘Create Table as Select’, and when used in conjunction with the EXTERNAL keyword when querying unstructured data, allows the relational engine to retrieve the data from the nodes in the Hadoop cluster and store it as a temp and/or permanent table in the relational database. Not only can you do simple selects, you will be able to perform JOIN’s and Group By’s on the Hadoop data.

Pulling the data from the HDFS to a table in the relational database isn’t always the optimal plan but all this is done in parallel which allows for better performance and gains in processing. Also, as I’ll explain shortly, Phase 2 of Polybase will eliminate the need to store unstructured data in the relational db.

The result of the combining of traditional and non-traditional data sources, as seen in the below illustration, allows unified access with Microsoft’s BI tools such as Power Pivot and Power View with Excel. This simplifies the access to unstructured data and helps remove the complexity associated with ‘Big Data’.

  All of the above functionality will be present in the initial version of Polybase that ships with PDW v2.

As many might be aware, Polybase isn’t the first tool to allow querying of structured and unstructured data and what will truly distinguish Polybase from other tools such as Sqoop is the functionality that will be available in Phase 2.

When Phase 2 ships the query optimizer will be able make a cost-based decision, when referencing data in an HDFS, to determine whether it should transform the query into a MapReduce job to be performed on the Hadoop cluster or if it should just process using the SQL server instances on the PDW. Also, the optimizer will have the ability to move the workload of a query involving only PDW data to the Hadoop cluster. This intelligence within the optimizer will allow it to split the workload between the 2 platforms and thus leverage the true capabilities of the Hadoop cluster. All these decisions are of course “behind the scenes” and are totally transparent to the end user.

Ideally, getting the data into the hands (and on the screens) of the folks that know what to do with it is where the power of Business Intelligence truly lies and this is a big step in bringing that closer to reality. The next step in this march: shipping Polybase with other SQL Server versions as opposed to solely PDW. We’ll just have to wait and see what Polybase Phase 3 brings.

An Introduction to Big Data

What is Big Data and why do we need it

While you would have read many definitions of Big Data, almost all industry leaders agree that Big Data differs from traditional data storage in the following 3 dimensions.

Volume of Data

As a result of the explosive growth in technology and communication, massive amount of data is being generated. This data needs to be stored and analyzed. Traditional RDBMS databases are proving to be incapable of handling large volumes of data and that’s where Big Data technologies come into play.

Velocity of Analysis

While traditional data storage and analysis technologies take hours or sometimes days to extract, transform, load, and then analyze the data, Big Data technologies have to operate at a very high speed and in some cases have to produce analysis results at real time or near real time speeds.

Variety of data

Traditional RDBMS databases have focused on storing structured data. This structured data can then be analyzed using traditional data analysis tools. However, these systems are not designed to handle and analyze unstructured data such as documents, tweets, reviews, comments, etc. To address this problem a majority of Big Data products/solutions use no-SQL databases which are designed to efficiently store and analyze unstructured data.

That brings us to the next question - why do we need Big Data technology?

The growth of internet availability has changed everything. Internet is now easily available thorough mobile devices – something that has single-handedly increased the size of internet-generated data manifolds. . The traditional methods for improving business in the market place such as market survey, fraud detection, consumer sentiment analysis, etc. have to increasingly depend on human and machine generated data mostly over the internet or telecommunications networks. The amount of data that needs to be stored and analyzed for such a solution is often in the order of Petabytes and traditional data storage and analysis technologies find it difficult to provide efficient solutions in such cases. This is just one case in point. There are many other such use cases in both the Public and Commercial organizations where Big Data offers promising solution. This has led to a Big Data revolution, albeit in an early adoption stage. 

Common Data Storage and Data Engineering methods for Big Data

Big Data solutions are typically based on implementation of models which include massive parallel processing on huge datasets across a large number of computers or nodes. An example of such a model is MapReduce.

MapReduce is a framework for processing parallel problems across huge datasets using a large number of computers (nodes), collectively referred to as a cluster (if all nodes are on the same local network and use similar hardware) or a grid (if the nodes are shared across geographically and administratively distributed systems, and use more heterogeneous hardware). Computational processing can occur on data stored either in a file system (unstructured) or in a database (structured).

"Map" step: The master node takes the input, divides it into smaller sub-problems, and distributes them to worker nodes. A worker node may do this again in turn, leading to a multi-level tree structure. The worker node processes the smaller problem, and passes the answer back to its master node.

"Reduce" step: The master node then collects the answers to all the sub-problems and combines them in some way to form the output – the answer to the problem it was originally trying to solve.

MapReduce allows for distributed processing of the map and reduction operations. . It can be applied to significantly larger datasets than "commodity" servers can handle. A large server farm can use MapReduce to sort a petabyte of data in only a few hours. On the storage side, a file system is used which can store redundant data in multiple computers with efficient storage and retrieval of data. An example of such a file system is Hadoop Distributed File System (HDFS).

HDFS is a distributed file system designed to run on commodity hardware. It has many similarities with existing distributed file systems. However, the differences from other distributed file systems are significant. HDFS is highly fault-tolerant and is designed to be deployed on low-cost hardware. HDFS provides high throughput access to application data and is suitable for applications that have large data sets.

HDFS has a master/slave architecture. An HDFS cluster consists of a single NameNode, a master server that manages the file system namespace and regulates access to files by clients. In addition, there are a number of DataNodes, which manage storage attached to the nodes that they run on. HDFS allows user data to be stored in files. Internally, a file is split into one or more blocks and these blocks are stored in a set of DataNodes. HDFS is built using Java; any machine that supports Java can run the NameNode or the DataNode software.. A typical deployment has a dedicated machine that runs only the NameNode software. Each of the other machines in the cluster runs one instance of the DataNode software.

Big Data Market Size and Vendor Revenues

The Big Data market is on the verge of a rapid growth spurt that will see it top the $50 billion mark worldwide within the next five years.

As of early 2012, the Big Data market stands at just over $5 billion based on related software, hardware, and services revenue. Increased interest in and awareness of the power of Big Data and related analytic capabilities to gain competitive advantage and to improve operational efficiencies, coupled with developments in the technologies and services that make Big Data a practical reality, will result in a super-charged CAGR of 58% between now and 2017.

(Source: Wikibon)

Of the current market, Big Data pure-play vendors account for $468 million in revenue. Despite their relatively small percentage of current overall revenue (approximately 5%), these vendors – such as Vertica, Splunk and Cloudera -- are responsible for the vast majority of new innovations and modern approaches to data management and analytics that have emerged over the last several years and made Big Data the hottest sector in IT.

The current Big Data market leaders, by revenue, are IBM, Intel, and HP, these megavendors will face increased competition from established enterprise suppliers as well as the aforementioned Big Data pure-plays developing Big Data technologies and use cases that are driving the market. 

Total 2011 Big Data Revenue by Vendor (Top 10)
Vendor	Big Data Revenue (in $US     millions)	Total Revenue (in $US     millions)	Big Data Revenue as % of Total Revenue
IBM	$953	$106,000	1%
Intel	$765	$54,000	1%
HP	$513	$126,000	0%
Fujitsu	$285	$50,700	1%
Accenture	$273	$21,900	0%
CSC	$160	$16,200	1%
Dell	$154	$61,000	0%
Seagate	$149	$11,600	1%
EMC	$138	$19,000	1%
Teradata	$120	$2,200	5%

 

References:

• Wikibon

http://wikibon.org/wiki/v/Big_Data_Market_Size_and_Vendor_Revenues

• Hadoop

http://hadoop.apache.org/

 

Do Associations Have Any Business Intelligence?

My colleague, Rick Johnston, wrote this great BI post for Acronym, the ASAE blog for the association community. His post, Do Associations Have Any  Business Intelligence?, explores how business intelligence can help associations to become more accountable to their constituents. I couldn’t help sharing it here. For more from Rick, follow him on Twitter at @rjohnston.

 -----------------------------------------------

At a technology trends discussion on Saturday, ASAE CIO Reggie Henry asked why associations are not doing more to use their data to make just-in-time business decisions. After all, there are plenty of business intelligence (BI) tools out there, and for-profit organizations use them all the time to stay ahead of their competition and ensure profits for their owners or shareholders.

We see very few associations leveraging BI tools to make sense of the valuable data many of them are collecting. Most associations collect lots of membership data, revenue data, subscriptions data, meeting attendance data, and much more. But who is asking "What do we need to know to measure our success and adjust our operational plans in time to make a difference this year?" In most cases, the answer is nobody. But why?

My answer to Reggie's question reflects on how nonprofits are different from corporations. I contend that it doesn't have to do with size, resources, or technical ability. It has more to do with accountability. Associations have boards who care about their cause, profession, or industry but have no personal financial stake in the outcomes. Everyone tries to be nice and it's just not that important to be better than good. The status quo will suffice.

 Having over 25 years in association management and having served in several association boards, I can't remember a board meeting where someone asked the really tough questions around performance accountability. Oh, we look at financial and program reports and how we could have done better last year. We have lots of excuses (the economy, staff turnover, regulations, etc.) to explain away the high-level picture of prosperity, decline, or more often simply the status quo.

 We need to dig deeper into the data mine to get real insight into the what and why questions beneath the surface of year-end reports. To think more like a business and closely analyze their operations and reap the intelligence that will help them measure real results and make quick decisions that will have a positive impact. For example, someone should be asking:

 •Do our online promotions reach the right audiences and incent the behaviors (conference registration, advocacy actions, etc.) that we desire?

• Are membership renewal rates increasing or declining in one demographic more than another?

• What do our conference attendees value the most? What kinds of conversations are taking place?

 I believe the data is often there but leaders in many associations avoid looking at it. By that I mean asking the questions that will allow them to really understand what is happening and why it happens in time to do something about it. Business intelligence tools need not be expensive to purchase or implement. The Texas Medical Association has been using relatively simple BI tools such as Excel to ask these questions for some time. I don't know where they are today, but I'll bet their constituents are better off for their having taken accountability seriously. Can you say the same?

Understanding Big Data: the three Vs, and why do you need to deal with all three.

 Everyone knows that data is growing exponentially. The term that gets thrown around most is Big Data. However from a holistic standpoint, Big Data does not just include big volumes of data (terabytes and petabytes), but also the need for faster access to that data, as well as the need for integrating structured data with unstructured content.

The Three V Methodology that we embrace, is a comprehensive  strategy for addressing all aspects of Big Data, and balancing the technical aspects of all three  dimensions such that the target data architecture satisfies the client’s needs on all three:

• Volume: For scaling up to terabytes and petabytes of data, traditional databases usually do not cut it; hence we recommend the use of MapReduce technology, which can support huge volumes of data very quickly, across a distributed computing architecture. MapReduce originated at Google, and is now freely available using Hadoop and other open source implementations.

 

• Variety: In order to ingest hundreds of thousands of documents (or social media feeds or any other unstructured content), we recommend the use of Hadoop Distributed File System (HDFS), which is an open source technology that has become the de-facto standard for addressing Big Data needs.  With our deep experience in enterprise data tools from vendors such as Oracle and Microsoft, we know how best to make use of their support for Hadoop, and how to create a data architecture that combines the best technologies for handling both structured  (databases) and unstructured data (documents).

 

• Velocity: In the Big Data world, the need is not only for ingesting huge volumes of data in real time, but also extracting information from them equally fast. We use open source technologies such as MongoDB which are not SQL, but document-oriented databases. They support an innovative way of querying data, called NoSQL.  It is many times faster than the traditional SQL-based inserts and queries on the database.

 

In a cool application of the three Vs of Big Data, Google scientists are working with the Centers for Disease Control and Prevention (CDC) to track the spread of flu around the world by analyzing what people are typing in to search. Since 2010 Google also has this information on a portal: http://www.google.org/flutrends/.

Another pioneer in this space is IKANOW. Their open source platform, Infinit.e , helps leverage the technologies mentioned above to get business value from Big Data.

Conclusion: Simply focusing on one of the 3 V’s individually isn’t going to solve your Big Data problems. You need to be able to ingest (variety and velocity), scale (volume), query (velocity), and analyze (velocity) this enormous amount of data.

Next-Gen Business Intelligence: Self-Service Tools for Optimized Decision-Making

Companies of all sizes are suddenly overflowing with data that pours in from both internal and external sources. Each department within the company uses the information differently and pulls data in from a unique mix of sources. Many IT departments struggle to keep up with the increasing demand for data and insight. Decision-makers want information that is relevant to their areas of responsibility. They want dashboards that help make sense of the raw data. And they want it in real-time - not in a week or even a day.

How can companies keep up with rising expectations to deliver accurate data to the people who need it, quickly and without breaking the bank?

For companies using Microsoft's technology stack, the latest release of SQL, 2012, may be an efficient and economical solution. PowerPivot and Power View are tools that build upon the end user's familiarity with Excel and other applications to create self-service functionality.

The thoughtful and strategic implementation of a self-service business intelligence solution offers many advantages to companies. However, a rush to implement often recreates the original problems and quickly puts companies right back where they started. 

For more information about the challenges CIOs face in managing big data, the new tools available in SQL 2012, and tips for a successful implementation, check out the new white paper co-authored by Sean Woehrle and me.

 Visualizing Business Intelligence: How Self-Service BI Tools Improve Decision Making (http://www.icfi.com/insights/white-papers/2012/visualizing-business-intelligence)

SQL Server 2012

Microsoft’s newest version of SQL Server, previously designated as Code Name Denali, won’t be released until next year, but the good folks in Redmond have made a CTP (Community Technical Preview) available for trial.  This is the third and likely last preview prior to the rumored release sometime in the first quarter of 2012.  SQL Server 2012’s enhancements are wide ranging, from new T-SQL functionality to the AlwaysOn capability that has DBA’s raving.  Most important to my role, however, are the upgrades it brings to Microsoft’s Business Intelligence platform.  This will be a two part series delving into more detail on Microsoft’s Business Intelligence Semantic Model and Project Crescent (since renamed Power View), the company’s new web based reporting tool which is a part of Reporting Services that targets the information worker audience that might have a less technical background.

Perhaps the most significant SQL Server 2012 BI enhancement revolves around the Business Intelligence Semantic Model or BISM.  Microsoft is billing the BISM in SQL Server 2012 as “one model for all end user experiences.” It allows aspects of the traditional multidimensional model to coexist with the relational model in a format called the tabular model and can be used with all client tools in the Microsoft BI stack. 

While the traditional multidimensional model, also known as a cube or UDM (Unified Dimensional Model), has many advantages, it can involve a steep initial learning curve that can deter non IT users from taking full advantage of its functionality.  The tabular model has its roots in the relational model and can be easier to comprehend for users without a BI Development background.  The tabular model can still be created by a professional BI developer using Microsoft Visual Studio (also known as BIDS or Business Intelligence Development Studio) and Analysis Services as well as by a business user with PowerPivot for excel and then shared using PowerPivot for Sharepoint.  The diagram below shows how the flexibility of the tabular model allows for an easy transition between Microsoft’s personal BI tool – (PowerPivot) and their Professional BI tools – (Analysis Services, Reporting Services) and the new reporting tool (PowerView).  The end user, depending on the client tool and the needs of the application, can choose between the multidimensional or tabular interface.

 

 

 

As with everything hailed as new or improved, there are downsides.  One potential drawback of the new tabular model is the perceived loss of processing efficiency created by the pre aggregation of data and measure creation that gives multidimensional models a performance advantage.  Microsoft has countered this by introducing a new columnstore index in the SQL Server 2012 database engine.  A columnstore index stores index data in columns, not rows.  For example, an index page would be used to store FirstName and another index page would be used to store LastName.  If a table’s columnstore index contained 4 columns and a query only needed to return 2 of those columns, then the query could be completed by only hitting the index pages with the 2 necessary columns, not every data page that stores index data as would be the case when using a non-columnstore index.  This new index in combination with the VertiPaq engine that was introduced with PowerPivot in SQL Server 2008 R2 allows for higher data compression which in turn allows exponentially more data to be stored in memory.  In memory data reduces the need for disk I/O operations and increased performance.  Of course, caution needs to be exercised as the use of VertiPaq can lead to higher memory requirements and expenses.  For larger datasets, or smaller memory capabilities, an alternative to the VertiPaq engine is the Direct Query mode where the data resides in database tables.  This is also known as passthrough mode as the query processing is passed down to the data source.  Hybrid VertiPaq/direct query modes can also be used to satisfy a solution’s specific data availability and processing needs.

There are many more features and enhancements in SQL Server 2012 but a deeper dive into those features is out of the scope of this post.  The goal of this post was that Microsoft has opened up their Business Intelligence stack to a much wider array of end users.  Allowing for the traditional multidimensional model while supplementing it with a tabular model gives businesses implementing a BI solution scalability and flexibility choices they didn’t previously have.  SQL Server 2012 shows that Microsoft has devoted significant resources to their BI tools and will continue to not only support but also grow their presence in the BI arena.

Reference:  Analysis Services – Vision & Roadmap Update by T.K. Anand; http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx

 

 

Understanding Master Data Management (MDM)

We are starting a new blog at Ironworks, in the area of Business Intelligence (BI) and Enterprise Information Management (EIM). We hope to keep this blog updated with new content at regular intervals. Our focus would be to provide you with best practices and advice on technical issues as well as introduction to new and emerging areas of BI, data warehousing and other areas of EIM.

We start off with a three part series on Master Data Management (MDM).  With the ever-increasing size and complexity of organizational data, MDM has become a top-of-the-mind concern for CIOs across the board.  Our first blog-article attempts to shed light on this important subject. We cordially invite your comments and inputs.

Thanks, and happy reading!

Deep Sogani

Practice Leader, BI & EIM, Ironworks

Understanding MDM

Part 1 of 3

Master Data Management (MDM) Defined:

MDM is the practice of integrating, cleansing and rationalizing data from multiple systems into an accurate, unified master record.  It strives to arrive at a single version of truth - an agreed-upon golden copy of data that can be used in core business activities.  MDM includes both tools and processes.

MDM in relation to Transactional Data and Reference Data:

It is worth noting that MDM pertains to the non-transactional data entities of an organization, also called reference data. However not all reference data can be called master data. MDM covers only key business entities, such as customers, products, services, locations, employees, etc.

The problem that MDM solves:

Organizational data is invariably spread across multiple databases, offline spreadsheets, and disconnected applications. This data does not conform to standards and formats, and there is usually no agreed-upon version. To address these issues organizations use MDM to build a single, accurate view of enterprise data.

So, why do you need MDM?

Data is one of the most valuable assets of an enterprise. Both IT and Business understand this.   Business people know that improved access to better quality data can help them make better business decisions, thus ensuring business growth and sustainability.  Master Data Management is about providing a single version of the truth, as opposed to having to go to multiple sources of data, which often leads to an incorrect conclusion of the truth.  Without confidence in your data, business decisions relating to product mix, target market, brand performance, pricing, customer satisfaction, and advertising etc. can’t be made with required certainty.

Benefits of MDM:

• More Revenue:
  • Valid and accurate data allows better forecasting, planning, budgeting and better-informed decisions. Better business decisions lead to more revenue.
  • MDM, when implemented for the customer domain, invariably leads to better marketing, better customer service and more effective marketing campaigns.
• Cost Reduction:
  • When data from multiple systems gets integrated, it allows all those systems to function better by avoiding data errors and by benefiting from the single version of truth arrived through MDM.
  • Manual data reconciliation is no longer needed.
• Focus:
  • MDM starts with identifying the most critical and important data for the company. This alone can help shine light within the organization on what is important.
• Cultural Benefits:
  • Identifying the critical data elements and establishing a common business terminology goes a long way in coordinating teams and establishing a framework of collaboration within the organization.
• Fraud Prevention:
  • Sarbanes-Oxley and other regulations have now increased pressure on the organization to get things right. For many companies, MDM is one of the support mechanisms for ensuring accuracy and preventing fraudulent reporting. MDM creates rules that make it difficult to have false or inaccurate data.
• Compliance
  • Implementing MDM helps organizations meet data compliance needs. For example, once MDM is implemented, an organization no longer needs personally identifiable information like SSNs to be the key identifier for entities such as patients, students, etc.  This helps protect data and maintain privacy, as well as satisfy an important compliance need.

 

Ensuring business and IT alignment is critical

Achieving this single version of the truth can only happen through a partnership between business and IT where both are equally aware of the significance of enterprise master data.  The business brings the knowledge of what is critical to business execution, while IT brings their view into all of the data sources across the enterprise and understanding of the overall enterprise data landscape. 

Steps to achieve MDM:

Schematically represented in the figure below, organizational master data is continuously improving & evolving and comprises the following steps:

• Collaborate:  The first step in the MDM process is for various stakeholders, in business & IT, to agree upon what data elements are critical to the business and therefore need to be part of the MDM initiative. Furthermore the multiple system/data owners need to collaborate to provide the data as well as to agree upon a single set of definitions, standards, and formats in which the master data would be represented.
• Integrate: involves data acquisition from multiple sources & its inclusion in a single repository – either a physical repository or a logical one (a Federated repository).
• Normalize:  bringing data under a common denominator such as standard unit of measurement and granularity, and such other steps that allow for data from multiple sources to be integrated.
• Cleanse:  cleanse, standardize and augment the data; remove duplicates, supply missing data, and handle other data quality issues.
• MASTER:  model Master Data and define relationships between master data elements. The end result of this step is achievement of master data, which of course, needs to be continuously updated and improved.

 

Master data that comes out of the MDM platform supports various inward and outward facing applications like the call center, customer service, help desk, sales, etc. It also supports enterprise reporting needs such as KPI dashboards for executives, operational reports, and others.

Data from MDM is also fed back into the source systems to improve the quality of data in the source systems themselves.

Subsequent parts of this series will elaborate on how MDM can be implemented as service to fulfill the scenarios mentioned above.

Part 2:

• MDM Implementation Styles
• Things to keep in mind while implementing MDM
• Domains closely related to MDM, such as Data Governance, CDI, etc.

Part 3:

• Data Architecture of MDM
• Implementing MDM with the Enterprise Data Warehouse (EDW)

References:

• “Open MDM Value” - A publication sponsored by Talend and Presented by Information Management.
• Master Data Management Resource Guide: http://www.mdmsource.com
• The What Why & How of Master Data Management
  • http://msdn.microsoft.com/en-us/library/bb190163.aspx