Designing, Developing, and Deploying the World’s First Parallel Data Warehouse Customer Solution

On November 9th, 2010 Ted Kummert announced to the world the immediate availability of SQL Server Parallel Data Warehouse (PDW). Since that time, myself and our larger firm BI Voyage (BIV) have been working on multiple Parallel Data Warehouse customer deployments including Hadoop clusters. Our firm’s work with the appliance begins with the world’s fourth largest equities exchange by notional value, Direct Edge. Working together with Direct Edge, we determined that Parallel Data Warehouse was the best platform for the firm’s large volumes of data required for analysis. Direct Edge became the first non-Microsoft buyer of Parallel Data Warehouse. Present day, several leading enterprises have purchased Parallel Data Warehouse appliances including our own firm’s purchase of a half-rack PDW for training and development. In the remainder of this post I will discuss our firm’s experiences with Parallel Data Warehouse and append a link to the Direct Edge PDW Case Study.

Designing Customer Solutions 

When you are the lead architect for a Parallel Data Warehouse project the most important thing you can do is read the technical materials (including SQL Server Parallel Data Warehouse Books Online which ships with the unit) and gain as much experience as you can prior to the engagement. I see both sides of the training discussion, which is that PDW is drastically different from SQL Server SMP and thus requires substantial training for existing SQL Server professionals. One the one hand – MPP technology is quite different in that you have a new paradigm to design for performance by distributing (and replicating) your tables. There is also the notion of Distributed-SQL (D-SQL) and distributed query plans. D-SQL queries and plans involve two levels of operations: compute node-level SMP plans and appliance-level data movement plans. Certainly understanding how the distributed plan interfaces with the traditional SMP compute node plan is important and likewise understanding the key data movement operations such as shuffle and partition moves is good to know as well. However, beyond those key deltas, if you are experienced with SQL Server SMP today for data warehousing you should be just fine. Without question, one of Microsoft’s core design goals for the appliance is to enable a seamless transition of technical expertise from SQL Server SMP to MPP (as easy as they can make it).

Once you are confident designing solutions for the appliance you should begin a detailed design phase (even if you are using SCRUM project management methodologies). The good news here is it takes a few weeks from the time of order for you or your customer to receive delivery of the appliance from the hardware vendor so you have some time to design and plan. You need to design (at least to a high-level), what the distributed schema is going to look like. Which tables are going to be distributed and which tables will be replicated? Do you want to have data management capabilities via table partitioning? How will you partition the tables? Should you employ non-clustered indexes (NCIs are used much less in PDW environments)?

The data loading infrastructure and frameworks require design – how will your solution accommodate historical one-time as well as ongoing incremental data loading?  Are you going to leverage ETL or ELT processes? And do not forget about the security architecture of the solution. Who will connect to the appliance? Under what security context and permissions should these people (or services) have when connecting? Do you want a self-service report author to have the ability to drop the data warehouse?

There is also the notion of creating MPP Views which face the BI tools and components such as OLAP cubes and in-memory tabular models. It is common practice to create views to mask some of the data warehouse internal complexities away from the BI components and self-service report authors. And for those self-service to PDW scenarios trust me when I say you want to restrict how many rows folks can download into those self-service tools – in PDW environments we are generally working with hundreds of millions and even billions of records (On the BIV half rack unit we have a table with over 70 Billion records).

Before I leave the design realm there is that of OLAP Cubes and Tabular Model specific design. If you are going to be deploying these artifacts with PDW you want to ensure you read up on early best-practices and lessons learned as there are many inherit tuning options and best practices that have been discovered. Some of these items apply to design of OLAP cubes for example such as the selection of optimal storage modes.

Developing Customer Solutions

Probably the most import thing to mention under development is ‘pre-bake’ your sample datasets. We want to work small datasets so our development tasks are that much faster and easier to accomplish. Consider using a representative dataset such as X percentage of production data volumes.

It is a good time to mention appliance updates (AUs) as well. With each new AU Microsoft releases we gain new features. The new features span a variety of appliance aspects, however the development domain obtains many new updates with each AU including collations, data types, and new D-SQL constructs. So it is a good idea to double check that your PDW development unit is on the latest-and-greatest service pack available.

The primary development tools you will be using in developing PDW solutions is Nexus Query Chameleon (PDW V1 Query Tool), the PDW Admin Console (monitor and diagnose query performance), and the SQL Server SMP BI developer tools (BIDS or SSDT). You can also leverage SQL Server SMP’s T-SQL with PDW by creating a linked server to the Parallel Data Warehouse appliance. The primary development objects available to you in PDW V1 are tables, views, and stored procedures.

Next up in PDE development is Create Table As Select (CTAS), get to know this syntax, you’re going to be using it…a lot. CTAS is essentially the SQL Server SMP equivalent of SELECT INTO. It is a fully parallelized SELECT that results in the creation of a new user table. You can write CTAS UPDATEs (matching on conditions), CTAS DELETEs (filtering out records via a SELECT), just about any set-based operation you can think of can be accomplished using CTAS. CTAS is often also used to redistribute an existing distributed table by a new distribution column.

Deploying Customer Solutions

When you are ready to deploy a Parallel Data Warehouse database and potentially accompanying Microsoft business intelligence artifacts you will want to consider a few things. First and foremost, are the development and production appliances physically the same? Some of our customers have purchased the optional Parallel Data Warehouse development unit which is a smaller appliance configuration designed for development and test. In other customer scenarios we build and productionize on the same physical appliance when a test unit is not available. All of the changes related to physical appliance settings apply here including ensuring the production appliance is on the network, latest appliance updates applied, and updating your BI artifact’s connection strings to point to the correct appliance control node clustered IP address for connectivity using either the Data Direct or Tabular Data Stream protocols.

Second is source-control. You can use most any source control system such as Visual Studio Team System or Subversion to check in your PDW’s .sql files. These files may contain Distributed-SQL DDL, DML, or both. Also, do not forget about your BI artifacts such as Reporting Services reports and OLAP Cubes or Tabular Models.

Third is that of historical data loading. As part of your ELT design and development phases you should have a data loading solution in place that facilitates the massive historical load that first must take place. Generally speaking you will want to leverage dwloader to obtain the fast parallel bulk load performance (1 TB/hour). dwloader requires incoming (schema-matching) data files including support for gzip (compressed) files. You will want to ensure all of these files are ready for load and ideally they will be located in a directory on the appliance’s landing zone.

Fourth are optional subscribing SQL Server SMP spokes. You can optionally choose to design a hub-and-spoke architecture (for example to reuse an existing SQL Server Fact Track investment) whereby traditional SQL Server relational instances receive fresh copies of datasets by using the appliance’s feature called Remote Table Copy (RTC). RTC provides a high-speed export of appliance data, but you need to ensure the spokes are attached to the appliance’s internal Infiniband network. The logic behind choosing such a design is you may want to offload some of the query workload to downstream spokes. One example is an accounting department that might constantly query a certain dataset throughout the business day and you wish to offload those queries to a separate server thus freeing up the Parallel Data Warehouse for more ad-hoc (and possibly of greater importance) requests.

Fifth is that of MPP backup plans. Once you have the customer’s solution deployed and are actively loading data you will want to design and put into place a regular backup plan just like you would with regular SQL Server SMP. PDW supports full and differential backups. A typical configuration we use is weekly full and daily differentials. PDW backups are automatically compressed and backup the entire appliance database (across all nodes). You can (and we also recommend) backing up the PDW’s Master database every so often as well which contains appliance metadata such as user logins. The biggest thing to be aware of when planning for PDW backups is that in order to restore a backup, you must restore it to an appliance with the same or more number of compute nodes. For example, if you elect to purchase two Parallel Data Warehouse units for disaster recovery purposes, you will need to ensure that both appliances have a matching number of compute nodes for cross-backup/restore operations.

The sixth item under MPP deployment is that of monitoring the appliance. Out-of-the-box you have access to the PDW Admin Console as well as the underlying system and dynamic management views that are accessible via a SQL prompt. You can optionally (and we recommend to our customers) downloading the SQL Server Parallel Data Warehouse Management Pack and installing it into Systems Center Operation Manager (SCOM) 2007 R2 instance. You can also (and we often do) develop additional custom monitoring reports using Reporting Services querying the system and dynamic views in the appliance and then deploying those items to a SharePoint farm for regular consumption.

The seventh and final item is ensuring proper appliance security. The appliance supports SQL Server authentication and entails logins and roles (Server and Database). Ideally you designed the security infrastructure as part of the design phase of work and you just need to instantiate it at this point. Be sure to setup service accounts for appliance access as well such as Reporting Services for example.

Migrating a SQL Server SMP Database

There are a few methods available to you for migration (forklift) of a traditional SQL Server SMP database to SQL Server Parallel Data Warehouse. There is the old reliable method of manually creating PDW DDL and then using a variety of tools including SSIS and PDW’s dwloader to historically load data into the new MPP schema. This manual migration process involves converting incompatible data types, using PDW’s specific DDL syntax, choosing distributed versus replication for table storage, and the selection of distribution, index, and partition columns and values. Our firm has created a utility to automatically migrate databases from SQL Server SMP to SQL Server Parallel Data Warehouse, you can find more information on the tool as well as a short video clip of its usage at http://www.bivoyage.com/pdw_migration.html (we use the utility to assist our customer’s projects and do not sell the tool directly).

The Future of Parallel Data Warehouse: Microsoft’s Big Data Appliance

With the announcements made by Microsoft at PASS Summit 2012 the future of Parallel Data Warehouse is clear. Dr. David DeWitt of the Microsoft Gray Systems Lab provided invaluable details behind Project Polybase, his detailed presentation can be found here. Microsoft intends to unify the two diverse worlds of Hadoop and Massively Parallel Processing database management systems. Project Polybase will facilitate the parallel query execution of data persisted in Parallel Data Warehouse as well as Hadoop distributed file systems (HDFS). In Dr. David DeWitt’s presentation he discusses three phases of increasing PDW and HDFS parallel query integration.

Project Polybase will make its debut (Phase 1) in Parallel Data Warehouse’s initial V2 release. In addition, Microsoft (in partnership with Hortonworks) is actively building HDInsight. HDInsight is an Apache compatible Hadoop distribution that Microsoft is making available on Windows Server and Windows Azure platforms. Project Polybase is slated to work with all Apache compatible Hadoop distributions. While Project Polybase is getting much warranted attention don’t forget that Parallel Data Warehouse V2 will also include native support for columnstore indexes which can dramatically increase data warehouse query performance.

Parallel Data Warehouse’s hardware vendors (HP and DELL) are also continuing to include faster hardware. Some of the key hardware components for Parallel Data Warehouse appliances include infiniband fabric, CPU cores (per compute node), and memory (per compute node). With increased available hardware, columnstore index support, and parallel query execution across HDFS and MPP persisted datasets Parallel Data Warehouse V2 is slated to become Microsoft’s Big Data Appliance.

Download the Case Study

You can download the Direct Edge Parallel Data Warehouse Case Study at http://www.bivoyage.com/pdw_contact.html.

Data Modeling for PDW Best Practices

Team BIV has worked on numerous PDW deployments and certainly if any aspect of DW/BI is considered an art it is that of Data Modeling. We’ve witnessed painful Distributed-Sql query plans and then had the “ah hah” moment thereafter. As a result, our team has formulated a core set of Data Modeling Key Principals for PDW and these principals apply to both 3NF and Dimensional data models:

  • Minimize Data Movement
  • Avoid Data Distribution Skew
  • Use Non-Clustered Indexes Sparingly
  • Maintain Fresh Query Statistics(AU3)

Minimize Data Movement

This principal is by far the most important. The goal is to ensure common query and JOIN patterns do not require the movement of data prior to returning each compute node’s results up to the control node (Localized Queries also known as Aggregate/JOIN compatibility). The data movement that can occur (if needed) is facilitated by PDW’s Data Movement Services (DMS). Before I go any further let me help you by providing a few facts about data movement in the appliance (this is from PDW BOL):

  • When joining replicated to replicated tables no data movement occurs
  • When joining distributed to distributed tables data movement can occur
  • When joining distributed to replicated tables data movement can occur

When you are data modeling for the appliance keep in mind common query patterns as you want to minimize any data movement in the DSQL query plans. Another fact we’ve learned in this domain is slightly different data types involved in JOINs. If you JOIN a INT to a BIGINT in the appliance you will incur data movement as it qualifies it as an incompatible JOIN. We had a customer that needed three different very large fact tables JOINed across all of them and there was not an obvious single distribution key across all three. By performing further analysis we distributed all them on a column that at first wasn’t an obvious choice for distribution just so we incurred minimal data movement (though some still occurs depending upon the query pattern submitted).

Avoid Data Distribution Skew

When deciding on a distribution key for each distributed table ensure you select a field with high cardinality. Candidate distribution keys are ID columns and other highly unique values. If you choose a poor distribution key a disproportionate number of records will reside across the compute nodes and their corresponding distributions. Distributed queries are only as fast as the slowest compute node and distribution.

Use Non-Clustered Indexes Sparingly

By building a solid MPP data model using a combination of strategic distribution and clustered index keys the need for non-clustered indexes in MPP is minimal. However, there are a few scenarios where a non-clustered index is still of value at the cost of slower writes. Examples are commonly referenced columns in JOIN and WHERE clauses that do not serve as the distribution or clustered key.

Maintain Fresh Query Statistics

As of Appliance Update 3 the control node now uses query statistics in addition to the traditional SMP query statistics found on the compute nodes. We’ve seen how a given query will perform poorly at first only to then issue a UPDATE STATISTICS statement and the same query performs much better. The need for these to be kept fresh is required for optimal MPP query plans.

PDW: Restore Single Table

To restore a single table from a backup, you will restore a database backup to a new DB (which can be created by the DSQL RESTORE command itself).  Drop the table you want to replace. Then CTAS (Copy Table As Select) the restored table to the database where you dropped the table.

The following example is used to restore the table IDST_WIP_CUST_RATE in DDPCST to a copy of the same table from a backup.

Step 1 – select database to restore and restore it: select * from dwsys.sys.dm_pdw_online_backups order by BackUpStartDate desc returns the available databases for restore.RESTORE DATABASE  DDPTEMP FROM  DDPCST; note the name DDPTEMP, name used will need to be used in step 4 and 5.

Step 2 – Copy Create Table SQL before dropping or renaming table. In Nexus, right click on the target table that you plan to replace and select View DDL. Select the table under the appropriate database so that the DB reference will be correct as well. This will open a new window in Nexus with the below SQL. Delete the field definitions and the semicolon (highlighted in blue).

CREATE TABLE [DDPCST].[dbo].[IDST_WIP_CUST_RATE]

( [ACCTCORP] INT NULL, [HOUSE] DECIMAL(6,0) NULL, [CUST] DECIMAL(2,0) NULL, [WPCNT] VARCHAR(9) NULL, [RATESIGN] CHAR(1) NULL, [RATECD] VARCHAR(3) NULL, [SERCNT] INT NULL, [SERTY] CHAR(1) NULL, [SAMT] DECIMAL(6,2) NULL, [PRICELEVEL] INT NULL, [SOURCE_UPDATE_DATE] DATETIME NULL, [LAST_UPDATE_DATE] DATETIME NULL, [SOURCED_FROM] CHAR(1) NULL, [SOURCE_DB] CHAR(4) NULL ) WITH (CLUSTERED INDEX([ACCTCORP], [HOUSE], [CUST]), DISTRIBUTION = REPLICATE);

Once deleted you will have this: CREATE TABLE [DDPCST].[dbo].[IDST_WIP_CUST_RATE] WITH (CLUSTERED INDEX([ACCTCORP], [HOUSE], [CUST]), DISTRIBUTION = REPLICATE)

Step 3 – drop the existing table or rename it. Option 1 – drop table DROP TABLE [DDPCST].[dbo].[IDST_WIP_CUST_RATE] Option 2 – rename table USE DDPCST; RENAME OBJECT IDST_WIP_CUST_RATE TO IDST_WIP_CUST_RATE_old

Step 4 – CTAS the new table Take the SQL from Step 2, and add the bottom line ‘AS SELECT’ ensuring you use DB name of DB you restored and the same table name. CREATE TABLE [DDPCST].[dbo].[IDST_WIP_CUST_RATE] WITH (CLUSTERED INDEX([ACCTCORP], [HOUSE], [CUST]), DISTRIBUTION = REPLICATE) –above from step 2.  The below line you add. AS SELECT * FROM [DDPTEMP].[dbo].[IDST_WIP_CUST_RATE];

Execute the above statement.

Step 5 – drop the temporary database you created. DROP DATABASE DDPTEMP;

MPP-Driven Microsoft Business Intelligence: Uniting Parallel Data Warehouse with Analysis Services

With the arrival of Parallel Data Warehouse (PDW), Microsoft has a compelling Massively Parallel Processing (MPP) appliance designed for the data warehouse specific workload. Since 1996, when Microsoft acquired OLAP technology from Panorama Software, the company has offered a market-leading OnLine Analytical Processing (OLAP) product in the form of SQL Server Analysis Services. BIV has worked on multiple Microsoft Big Data projects leveraging PDW, Hadoop, and Analysis Services for the BI and analytics tiers. In the remainder of this post I will describe in detail some of the early best practices for uniting PDW with MS OLAP our team has uncovered:

  • Think MOLAP Not ROLAP: Originally, a few folks were evangelizing to leverage ROLAP with PDW which is logical (to a degree) since ROLAP would leverage the underlying MPP appliance for all queries and data. However, in reality ROLAP with PDW rarely provides the results expected. While PDW is blazingly fast for large-scan based aggregate queries, in a pivot table like environment where the user expects immediate response (< 5 seconds as a general benchmark) for all combinations of cell sets, ROLAP is not sufficient. HOLAP with PDW is not currently supported as it requires indexed views which the appliance does not yet offer.
  • Got AU3? Employ TDS: If you are working with an appliance that has Appliance Update 3 (AU3) installed you can choose between the original Data Direct drivers and protocols and the newer Tabular Data Stream (TDS) equivalents (SQL Native Client). The BIV team has witnessed a fair amount of performance improvement (Roughly 10% Increase in MOLAP Processing) by using the newer TDS via SQL Native Client provider with Analysis Services over the older Data Direct providers.
  • OLAP Referential Integrity: Analysis Services projects require Referential Integrity (RI) in order to link measure groups with dimensions. However, PDW does not currently support the notion of RI (Primary/Foreign Key relationships). The solution is to employ Analysis Services Data Source View (DSV) support of RI. In the DSV you can create primary/foreign key relationships between your relational entities.
  • Parallel Partition Processing Timeouts: PDW currently supports a maximum of 32 concurrent queries. Any queries that are submitted after the 32nd has been received are placed into a QUEUE status by the appliance. Keep this fact in mind when planning out your Analysis Services partition processing strategies. We’ve seen partition processing threads timeout by submitting too many parallel partitions for processing. You can throttle the number of parallel threads in the Analysis Services processing window as well as in XMLA.
  • Avoid Fact (Degenerate) Dimensions: Fact dimensions are the Analysis Services implementation of what the larger data warehousing industry generally refers to as Degenerate dimensions. These dimensions are grained and stored in the individual fact tables as opposed to separate dimension tables as is the case with regular and snowflaked dimensions. When designing your PDW data warehouse you should strive to avoid persisting these attributes in fact tables and relocate them to a regular dimension table if possible. This MPP modeling decision is a balance because if the fact dimension contain a very high number of unique values (which is common in fact dimensions) then it may need to be stored as a regular fact table (thus be physically distributed) in the appliance. The take away here is we’ve found these dimensions can take a long time to process as the underlying appliance has to scan and return each individual attribute member. If you can avoid these do so, if you cannot and the number of unique members is very high then it is what it is and you will incur some additional overhead when processing measure groups containing the fact dimensions.
  • Analysis Services 2012: SQL Server 2012 BI is not yet fully supported with the appliance. You can however implement a multi-hop scenario leveraging PowerPivot or Analysis Services OLAP to first create a cube from the appliance and then leverage a downstream BI Semantic Model (BISM) to support Power View reporting.

Microsoft SQL Server Parallel Data Warehouse for Newcomers

Why am I Blogging about Microsoft SQL Server Parallel Data Warehouse?

BI Voyage (BIV) (www.bivoyage.com ) , a firm I built along with my work and business partners, has a charter of being the Premier Microsoft Appliance & Cloud Computing Platform System Integrator firm for data warehousing and business intelligence deployments. BIV has been fortunate enough to partake in several early PDW deployments working hand-in-hand with the Microsoft PDW teams. BIV is fortunate to be a leading early adopter firm of PDW including how to best design and deploy the Massively Parallel Processing (MPP) DW-Only appliance for customers.

Brief Bit of MPP History

Microsoft SQL Server Parallel Data Warehouse (PDW) is the result of the company’s prior acquisition of Datallegro in 2008. Datallegro was the invention of Stuart Frost starting back in 2003 to compete with the Netezza (now an IBM company) product line. A great blog post by Stuart regarding why he founded Datallegro can be found at http://www.beyeblogs.com/DATAllegro/archive/2008/04/who_i_am_and_why_im_here.php .

Microsoft realized it needed an offering for the Very Large Data Warehousing (VLDW) market and as a result acquired Datallegro for roughly $238 M USD. Microsoft’s official press release can be found at http://www.microsoft.com/en-us/news/press/2008/sep08/09-16DAPR.aspx. PDW was originally built using custom JAVA and the INGRES database platform. Once Microsoft acquired the company and its IP the first obvious task was to port the appliance over to the Microsoft SQL/Windows stack. Microsoft internally went to work on this migration between the 2008 and 2010 period of time.

The Microsoft PDW Teams

When you purchase a PDW you will quickly become aware of the internal Microsoft PDW teams. These teams serve different purposes but their combined charter is to ensure PDW customers are happy and successful!

  • Engineering: Enhance PDW’s software and thus it’s ever evolving features
  • Center of Excellence (Incubation): Oversee PDW deployments
  • Premier Mission Critical Support: Support PDW customers
  • Sales Team: Grow the PDW line of business thru working with prospective customers

How Do I Obtain One?

Either by way of calling Microsoft or its hardware partners directly or by leveraging a trusted PDW partner such as BIV you get introduced to a PDW SSP (member of the sales team) as well as the equivilent on the HP/Dell side for hardware. All companies including BIV recommend to prospective customers an initial Architecture Design Review (ADR) engagement that can last from .5 to 3 business days depending upon scope. The goal of the ADR is to discover your current decision support’s architecture and requirements, review Microsoft’s offerings, and to design an initial MPP/PDW architecture that meets your organization’s requirements.

Why MPP for DW?

Historically speaking Symmetric multiprocessing (SMP) has been a processor architecture for database platforms designed mostly for OLTP workloads. MPP architectures allow multiple servers to cooperate as one thus enabling distribution and parallel processing of large scan-based queries against large data volume tables. Most all DW workload centric appliances leverage MPP architecture in some form of fashion.

Under the Hood

As the figure below shows, inside of a PDW is at a minimum two physical racks: Control and Data. PDW data racks are configurable from .5 to 4 full racks depending upon your data volume requirements. There is builtin High Availability HA as well at the node-level for Control, Management, and Compute as well as individual hardware component HA (power supplies, etc.).

The Control Rack entails the following nodes:

  • Control: Runs MPP Engine + runs PDW’s Data Movement Services
  • Landing Zone: Data loading occurs on this node
  • Backup: MPP database backups reside on this node, space is configurable upon purchase
  • Management: Runs Active Directory, Patching/WSUS, Cluster Services, etc.

The Data Rack entails the following nodes:

  • Compute: Runs SQL Server SMP over 8 LUNs + runs PDW’s Data Movement Services
PDW_v1

PDW_v1

We hope you’ve enjoyed our first post on PDW here at the BIV blog!

Derek Comingore & Team BIV