This post contains the following queries that support development and operational tasks within a Microsoft Parallel Data Warehousing (PDW) Appliance environment.

-Create Statistics
-Query Execution
-Query Runtimes
-Database Metadata
-Appliance Health Status
-DMS Errors
-Volume Size

These queries utilize system views found in the sys database and expose information on… Read more

Parallel Data Warehouse Tip (PDW): Surrogate Key Generation
IDENTITY is not supported on PDW so you’ll want to do a table based surrogate key assignment or use INSERT w ROW_NUMBER() with the OVER clause. I prefer using ROW_NUMBER().

USE [PDWDB]

CREATE TABLE [dbo].[testTgtTable](
[srgKey] [int] NULL,
[colA] [nchar](10) NULL,
[colB]… Read more

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

1. Create Named… Read more

SSRS Tip: Simplest Method to Pass MDX Multi-Select Parameters to MDX Datasets

I recently ran across a poorly covered topic in regards SSRS development using MDX parameters and datasets.  After some trail and error and visiting a number of online resources, I found the approach below to be the most… Read more

HP Announcement: PDW V2 Appliance Features

New features for the HP’s Enterprise Data Warehouse (EDW) V2 appliance for SQL Server PDW have been announced!

Highlights:

• 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…

Read more

As with any other project our objective is to drive PDW POC success!  To clear and cultivate the playing field for successful POC engagements, we must, at a minimum, consider the following guidelines.

Guideline 1: Identify Existing Roadblocks and De-scope
Major POC challenges can manifest themselves in many different forms… Read more

I received the following error today when attempting to use Visio’s ‘Database Model Diagram’ template under category ‘Software and Database’.

The program VISIO.EXE version 14.0.6122.5000 stopped interacting with Windows and was closed. To see if more information about the problem is available, check the problem history in the Action Center…

Read more

When loading tables in Microsoft’s Parallel Data Warehouse (PDW) via T-SQL, one will soon encounter the need to load data into a new table. Whether the developer needs to perform an initial data load of table or even go through the PDW-specific steps to perform a ‘trunc and load’ of… Read more

Before we get to the backup and restore syntax, its worth noting that the Parallel Data Warehouse (PDW) appliance architecture offers an environment that greatly enhances backup times (due to dedicated storage and network interfaces, see the following post for more information - http://saldeloera.wordpress.com/2012/07/09/lesson-1-of-parallel-data-warehouse-basic-architecture-overview/).  Summarized below, the observable backup… Read more

There is a way to view the size of a particular PDW database using the  DBCC_pdw_showspaceused command via the NEXUS query editor.  Executing the steps below will allow the developer to view sizes (in Mb) for the following columns:

Reserved_Space
Data_Space
Index_Space
Unused_Space
PDW_Node_ID

Follow the steps below in NEXUS… Read more

There are a few basic pitfalls that can be avoided when developing or migrating T-SQL queries into Microsoft’s Parallel Data Warehouse (PDW) environment. Like anything else, with proper planning and attention to detail, one can side-step two significant PDW perils more commonly known (or much less commonly known) as the… Read more

Usage of GETDATE() dates back to the earliest versions of SQL server and has become the primary blade in our SQL swiss army knife when we want to generate the current system date and time. Currently, there is something missing in the current release of Microsoft’s Parallel Data Warehouse (PDW)… Read more

Read more

After forgetting the syntax for temp tables a couple of times, I decided to write a brief overview of the table creation syntax for Microsoft’s Parallel Data Warehouse (PDW) architecture.

One thing worth noting up-front, this post does not include considerations that should be made in determinining whether a table… Read more

Here is a snippet of code that will assist in the dynamic ‘spin-up’ of duplicate SSIS packages.  To put it simply, I used this approach in a C# service that monitored a nightly FTP file drop folder and inserted records into a queue table (one record per file) for SSIS… Read more

I’ll begin my coverage of Microsoft’s Parallel Data Warehouse (PDW) architecture / appliance with a brief overview of the Massively Parallel Processing (MPP) architecture and the specific hardware components of the PDW appliance.

For starters, think of PDW as a single, pre-configured server and network environment, comprised… Read more

There’s a myriad of examples out there on how to execute a sql task via OLEDB connections and C# in a SSIS Script Task.   However, I have yet to find a best practice solution that reuses an existing connection manager to properly establish, execute the SQL script (either dynamic or… Read more