Microsoft’s SQL Server Parallel Data Warehouse (PDW) appliance was introduced with SQL Server 2008 R2 and is Microsoft’s first massively parallel processing (MPP) offering. Although PDW is built upon the SQL Server platform, it is a completely different product. As the name suggests, MPP uses multiple servers working as one system, called an appliance, to achieve much greater performance and scan rates than in traditional SMP systems. SMP refers to symmetric multi-processing; most database systems, such as all other versions of SQL Server, are SMP.

To obtain a better understanding of the difference between SMP and MPP systems, let’s examine a common analogy. Imagine you are handed a shuffled deck of 52 playing cards and asked to retrieve all of the Queens. Even at your fastest, it would take you several seconds to retrieve the requested cards. Let’s now take that same deck of 52 cards and divide it across ten people. No matter how fast you are, these ten people working together can retrieve all of the Queens much faster than you can by yourself.

As you may have inferred, you represent the SMP system, and the ten people represent the MPP system. This divide-and-conquer strategy is why MPP appliances are particularly well suited for high-volume, scan-intensive data warehousing environments, especially ones that need to scale to hundreds of terabytes of storage. There are other MPP appliance vendors available besides Microsoft; however, close integration with the Microsoft business intelligence stack (SQL Server, Integration Services, Analysis Services, and Reporting Services) and a compelling cost-per-terabyte make PDW a natural progression for organizations needing to take their SQL Server data warehouse to the next level. In this chapter, we will walk through how to load data into the PDW appliance using Integration Services. But first we will need to discuss some of the differences between PDW and SMP SQL Server.

PDW is built upon the SQL Server platform but has an architecture entirely its own. While the details of this architecture could easily consume a book in its own right, we will only cover the most pertinent parts to ensure you have the foundation necessary for loading data.

Before we proceed, it is important to note that only SQL Server 2008 R2 Business Intelligence Studio (BIDS) was supported at the time of writing for the creation of Integration Services packages to load data into PDW. Thus, this chapter will depart from the rest of the book in that it uses BIDS for its screenshots. Also, an actual PDW appliance is required to execute the samples in this chapter. For those who do not have a PDW appliance, there is still much information that can be gathered from this chapter regarding PDW architecture and loading best practices.

PDW Architecture Overview

Each PDW appliance has one control rack and one or more data racks. The control rack contains the following nodes:

• Control Node – Perhaps the most critical node in the appliance, the Control Node is responsible for managing the workload, creating distributed query plans, orchestrating data loads, and monitoring system operations.
• Management Node – Among other administrative functions, the management node is responsible for authentication, software updates, and system monitoring.
• Backup Node – As the name suggests, this is where backups are stored.
• Landing Zone Node – This is the node that is most relevant to this chapter. The Landing Zone node is the only node in the entire appliance that is accessible for loading data.

Figure 8-1 depicts a single data-rack PDW configuration. Each PDW appliance is comprised of one or more data racks but only a single control rack. In every data rack are ten Compute Nodes, plus one hot-spare standby Compute Node for high availability.

At the core of PDW is the concept of “shared-nothing” architecture, where a single logical table is broken up into numerous smaller pieces. The exact number of pieces depends on the number of data racks you have. Each of these smaller pieces is then stored on a separate Compute Node. Within a single Compute Node, each data piece is then split across 8 distributions. Furthermore, each of these 8 distributions has its own dedicated CPU, memory, and LUNs (hence the term “shared-nothing”). There are numerous benefits that a shared-nothing architecture enables, such as more linear scalability. But perhaps PDW’s greatest power is its ability to scan data at incredible speeds.

Let’s do some math. Assume we have a PDW appliance with one data rack, and we need to store a table with one billion rows. Each Compute Node will store 1/10^th of the rows, and each Compute Node will split its data across 8 distributions. Thus, the one billion row table will be split into 80 pieces (10 Compute Nodes x 8 distributions each). That means each distribution will store 12,500,000 rows.

But what does this mean from the end-user’s standpoint? Let’s look at a hypothetical situation. You are a user who needs to submit a query that joins two tables together: a Sales table with one billion rows, and a Customer table with 50 million rows. And, as luck would have it, there are no indexes available that will cover your query. This means you will need to scan, or read, every row in each table.

In an SMP system – where memory, storage, and CPU are shared – this query could take hours or days to run. On some systems, it might not even be feasible to attempt this query, depending on factors such as the server hardware and the amount of activity on the server. Suffice it to say, the query will take a considerable amount of time to return and will most likely have a negative impact on other activity on the server.

In PDW, these kinds of queries often return in minutes – and a well-designed schema can even execute this query in seconds. This is because the hardware is optimized for scans; PDW expects to scan every row in the table. Remember how we said that every distribution has its own dedicated CPU, memory, and storage? Well, when you submit the query to join one billion rows to 50 million rows, each distribution is performing a scan on its own Sales table of 12,500,000 rows and Customer table with 625,000 rows. Not nearly as intimidating, is it? The data is then sent back to the Control Node across an ultra-fast Dual-Infiniband channel to consolidate the results and return the data to the end user. It is this divide-and-conquer strategy that results in PDW significantly outperforming SMP systems.

PDW is also able to load data very efficiently. As previously mentioned, data is brought into the appliance through the Landing Zone. Each Compute Node then uses a hashing algorithm to determine where to store the data – down to the individual distribution and associated LUNs. A relatively small amount of overhead is associated with this process. Because this overhead is incurred on every single load, transactional load patterns (i.e., singleton inserts) should be avoided. PDW performs at its best when data is loaded in large, incremental batches – you will see much better performance loading 10 files with 100,000 rows each than loading 1,000,000 rows individually, but you will see the best performance loading one file with 1,000,000 rows.

Data can be imported from numerous platforms, including from Oracle, SQL Server, MySQL, and flat files. There are two primary methods of loading data into the PDW appliance: DWLoader and Integration Services. We will briefly discuss when to use DWLoader versus Integration Services. After that, we will actually walk through an example of loading data from SQL Server using Integration Services.

DWLoader vs. Integration Services

DWLoader is a command-line utility that ships with PDW. Those familiar with SQL Server BCP (bulk copy program) will have an easy time learning DWLoader, as both utilities share a very similar syntax. One very common pattern for loading data into PDW from SQL Server is to:

1. Export data from SQL Server to a flat file using BCP
2. Relocate the data file to the Landing Zone
3. Import the data file to PDW using DWLoader

This is a very efficient method for loading data, and it is very easy to generate scripts for table DDL, BCP commands, and DWLoader commands. For this reason, you may want to consider DWLoader for performing initial and incremental loading of the large quantity of small dimensional tables that often exist in data warehouses. Doing so can greatly speed up data warehouse migration. This same load pattern can also be used with flat files generated from any system, not just SQL Server.

For your larger tables, you may instead want to consider Integration Services. Integration Services offers greater functionality and arguably more end-to-end convenience. This is because Integration Services is able to connect directly to the data source and load the data into the PDW appliance without having to stop at a file share. Integration Services can also perform transformations in flight, which DWLoader does not support.

It’s important to note that each Data Flow within Integration Services is single-threaded and can bottleneck on IO. Typically, a single-threaded Integration Services package will perform up to ten times slower than DWLoader. However, a multi-threaded Integration Services package – similar to the one we will create shortly – can mitigate that limitation. For large tables requiring data type conversions, an Integration Services package with 10 parallel Data Flows provides the best of both worlds: similar performance to DWLoader and all the advanced functionality that Integration Services offers.

A number of variables should be considered when deciding whether to use DWLoader or Integration Services. In addition to table size, the network speed and table design can have an impact. At the end of the day, most PDW implementations will use a combination of both tools. The best idea is to test the performance of each method in your environment and use the tool that makes the most sense for each table.

ETL vs. ELT

Many Integration Services packages are designed using an Extract, Transform, and Load (ETL) process. This is a practical model that strives to lessen the impact of moving data on the source and destination servers – which are traditionally more resource-constrained – by placing the burden of data filtering, cleansing, and other such activities on the (arguably more easy-to-scale) ETL server. Extract, Load, and Transform (ELT) processes, in contrast, place the burden on the destination server.

While both models have their place and while PDW can support both models, ELT clearly performs better with PDW from both a technical and business perspective. On the technical side, PDW is able to utilize its massively parallel processing power to more efficiently load and transform large volumes of data. From the business aspect, having more data co-located allows more meaningful data to be gleaned during the transformation process. Organizations with MPP systems often find that the ability to co-locate and transform large quantities of disparate data allows them to make the leap from reactive data marts (How much of this product did we sell?) to predictive data modeling (How can we sell more of this product?).

Deciding on an ELT strategy does not necessarily mean your Integration Services package will not have to perform any transformations, however. In fact, many Integration Services packages may require transformations of some sort to convert data types. Table 8-1 illustrates the data types supported in PDW and the equivalent Integration Services data types.

Also, it is worth noting that PDW does not currently support the following data types at the time of this writing:

• DT_DBTIMESTAMPOFFSET
• DT_DBTIME2
• DT_GUID
• DT_IMAGE
• DT_NTEXT
• DT_TEXT

Any of these unsupported data types will need to be converted to a compatible data type using the Data Conversion transformation. We will walk through how to perform such a transformation in just a moment.

Installing the PDW Destination Adapter

As we have previously discussed, data is loaded into the PDW through the Landing Zone. All Integration Services packages will either be run from the Landing Zone node or, preferably, from a non-appliance server with access to the Landing Zone. The 32-bit destination adapter is required for Integration Services and should be installed on the server running the packages. If you are using a 64-bit machine, you will need to install both the 32-bit and 64-bit adapters. The Windows Installer packages are accessible from C:PDWINSTmediamsi on both the Management node and the Landing Zone node, or from the network share at \ < Landing Zone IP Address > edistr.

Once the PDW destination adapter has been installed, you will need to add it to the Integration Services Toolbox. Let’s walk through how to do this now. Start a new Integration Services Project and name it PDW_Example. After the project loads, select Tools from the main menu, and then navigate to Choose Toolbox Items, as illustrated in Figure 8-2.

Once the Choose Toolbox Items modal appears, click on the SSIS Data Flow Items tab. Navigate down to SQL Server PDW Destination and check the box to the left of the adapter, as illustrated in Figure 8-3. Click OK.

The PDW Destination Adapter is now installed. Let’s move on to setting up our data source.

The Data Source

In preparation for moving data from SQL Server to PDW, we need to create a database in SQL Server and populate it with some test data. Execute the T-SQL code in Listing 8-1 from SQL Server Management Studio (SSMS) to create a new database called PDW_Source_Example.

Listing 8-1. Example of T-SQL Code to Create a SQL Server Database

USE [master];GO
/* Create a database to experiment with */
CREATE DATABASE [PDW_Source_Example]
	ON PRIMARY
	(NAME = N'PDW_Source_Example' ,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAPDW_Source_Example.mdf'
 ,	SIZE = 1024MB
 ,	MAXSIZE = UNLIMITED
 ,	FILEGROWTH = 1024MB
	)
	LOG ON
	(
 NAME = N'PDW_Source_Example_log'
 ,	FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAPDW_Source_Example_log.ldf'
 ,	SIZE = 256MB
 ,	MAXSIZE = UNLIMITED
 ,	FILEGROWTH = 256MB
	);
GO

Please note that your database file path may vary depending on the details of your particular installation.

Now let’s create a table and populate it with some data. As we discussed before, Integration Services works best with large tables that can be multi-threaded. One good example of this is a Sales Fact table that is partitioned by year. Listing 8-2 will provide the T-SQL code to create the table and partitioning dependencies.

Listing 8-2. Example of T-SQL Code to Create a Partitioned Table in SQL Server

USE PDW_Source_Example;
GO

/* Create your partition function */
CREATE PARTITION FUNCTION example_yearlyDateRange_pf
(DATETIME) AS RANGE RIGHT
FOR VALUES('2010-01-01', '2011-01-01', '2012-01-01'),
GO

/* Associate your partition function with a partition scheme */
CREATE PARTITION SCHEME example_yearlyDateRange_psAS PARTITION
example_yearlyDateRange_pf ALL TO([Primary]);
GO

/* Create a partitioned fact table to experiment with */
CREATE TABLE PDW_Source_Example.dbo.FactSales (
   orderID      INT IDENTITY(1,1)
   , orderDate  DATETIME
   , customerID INT 
   , webID      UNIQUEIDENTIFIER DEFAULT (NEWID())
   CONSTRAINT PK_FactSales
      PRIMARY KEY CLUSTERED
     ( 
           orderDate 
          , orderID
     )
) ON example_yearlyDateRange_ps(orderDate);

Next, we need to generate data using the T-SQL in Listing 8-3. This is the data we will be loading into PDW.

Listing 8-3. Example of T-SQL Code to Populate a Table with Sample Data

/* Declare variables and initialize with an arbitrary date */
DECLARE @startDate DATETIME = '2010-01-01';

/* Perform an iterative insert into the FactSales table */
WHILE @startDate < GETDATE()
BEGIN

      INSERT INTO PDW_Source_Example.dbo.FactSales
      (orderDate, customerID)
      SELECT @startDate
            , DATEPART(WEEK, @startDate) + DATEPART(HOUR, @startDate);
      /* Increment the date value by hour; for more test data,
      replace HOUR with MINUTE or SECOND */
      SET @startDate = DATEADD(HOUR, 1, @startDate);
END;

This script will generate roughly twenty thousand rows in the FactSales table, although you can easily increase the number of rows generated by replacing HOUR in the DATEADD statement with MINUTE or even SECOND.

Now that we have a data source to work with, we are ready to start working on our Integration Services package.

The Data Flow

We are now going to configure the Data Flow that will move data from SQL Server to PDW. We will first create a connection to our data source via an OLE DB Source. We will transform the UNIQUEIDENTIFIER to a Unicode string (DT_WSTR) using a Data Conversion. We will then configure our PDW Destination Adapter to load data into the PDW appliance. Lastly, we will multi-thread the package to improve load performance.

One easy way to multi-thread is to create multiple Data Flows that execute in parallel for the same table. You can have up to 10 simultaneous loads – 10 Data Flows – for a table. However, you want to be careful not to cause too much contention. You can limit contention by querying on the clustered index or, if you have SQL Server Enterprise Edition, separating the loads by partition. This latter method is preferable and is the approach our example will use.

/* Retrieve sales for 2010 */
     SELECT
     orderID
     , orderDate
     , customerID
     , webID
     FROM PDW_Source_Example.dbo.FactSales
     WHERE orderDate = = '2010-01-01'
          AND orderDate < '2011-01-01';

CREATE DATABASE StageDB_Example
WITH
(
                    AUTOGROW           = ON
                , REPLICATED_SIZE    = 1 GB
                , DISTRIBUTED_SIZE = 5 GB
                , LOG_SIZE = 1 GB
);

/* Estimate compression ratio */
EXECUTE
sp_estimate_data_compression_savings
'dbo', 'FactSales', NULL, NULL, 'PAGE';

CREATE DATABASE PDW_Destination_Example
WITH
     REPLICATED_SIZE = 1 GB
         , DISTRIBUTED_SIZE = 5 GB
         , LOG_SIZE = 1 GB
);
CREATE TABLE PDW_Destination_Example.dbo.FactSales
(
            orderID
            INT
            , orderDate DATETIME
            , customerID INT
            , webID CHAR(38)
)
WITH
(
            CLUSTERED INDEX (orderDate)
            , DISTRIBUTION = HASH (orderID)
);

Multi-Threading

We have completed our Data Flow for 2010, but we still need to create identical Data Flows for 2011 and 2012. We can do this easily by using copy and paste.

First, click on the Control Flow tab and rename the first Data Flow “SalesMart 2010.” Then, copy and paste “SalesMart 2010” and rename it “SalesMart 2011.”

Double-click on “SalesMart 2011” to return to the Data Flow designer, then double-click on the OLE DB Source. Replace the SQL Command with the code in Listing 8-8.

Listing 8-8. SQL Command for 2011 data

/* Retrieve sales for 2011 */
SELECT
     orderID
     , orderDate
     , customerID
     , webID
FROM PDW_Source_Example.dbo.FactSales
WHERE orderDate = = '2011-01-01'
    AND orderDate < '2012-01-01';

Return to the Control Flow tab and copy the “SalesMart 2010” Data Flow again. Rename it “Sales Mart 2012.” Using the code in Listing 8-9, replace the SQL Command in the OLE DB Source.

Listing 8-9. SQL Command for 2012 Data

/* Retrieve sales for 2012 */
SELECT
         orderID
     , orderDate
     , customerID
     , webID
FROM PDW_Source_Example.dbo.FactSales
WHERE orderDate = = '2012-01-01'
     AND orderDate < '2013-01-01';

We are now ready to execute the package! Press F5 or navigate to Debug Start Debugging. Your successfully executed package should look similar to Figure 8-14.

Summary

We’ve covered a lot of material in this chapter. You have learned about the architecture of Microsoft SQL Server Parallel Data Warehouse (PDW) and some of the differences between SMP and MPP systems. You have learned about different loading methods and discussed how to improve load performance. You have also discovered some best practices along the way. Lastly, you walked through a step-by-step exercise to load data from SQL Server into PDW.