Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence

I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.

Data Warehousing Tip: Using T-SQL vs SSIS for Slowly Changing Dimension (SCD) Types 1 and 2

Here’s an example of using T-SQL to process a Slowly Changing Type 1&2 Dimension . Note:  SSIS has an SCD transform ,however, it does not process large dimensional datasets very quickly.
…eh, I’m putting it too nicely, the SSIS SCD transform is painfully slow!  This T-SQL performs 20X quicker than…

Read more

Data Warehousing Tip: T-SQL Script to Create DEFAULT Constraints by Data Type


There is an ongoing discussion/debate regarding the handling of NULLS in a data warehouse, however, the following article by Michelle Poolet summarizes it beautifully – “consider the data warehouse business requirements” (see http://sqlmag.com/database-administration/null-data-warehouse).


Beyond consideration, there are some data types that should get the DEFAULT treatment (bit, char, nchar,… Read more

Parallel Data Warehouse (PDW) Tip: Useful Appliance Queries

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

Data Warehousing Tip: Surrogate Key Generation

If IDENTITY insert is disabled or simply not available in a particular database or a database architecture, 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 [SomeDB]

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

SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

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 How-To: Simple Method to Pass MDX Multi-Select Parameters to MDX Datasets

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

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

Parallel Data Warehouse (PDW) Tip: Proof of Concept (POC) Guidelines

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

Visio 2010 Workaround: Application Hangs when opening ‘Database Model Diagram’ template

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

Parallel Data Warehouse (PDW) Performance Tip: CTAS vs INSERT SELECT

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

Parallel Data Warehouse (PDW) How-To: Using BACKUP and RESTORE DATABASE on PDW

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

Parallel Data Warehouse (PDW) How-To: Check Database Size with DBCC_pdw_showspaceused

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

Parallel Data Warehouse (PDW) How-To: Avoid ShuffleMove and PartitionMove Operations

There are a couple process hungry operations that can be avoided when developing or migrating T-SQL queries into Microsoft’s Parallel Data Warehouse (PDW) environment. With proper planning and attention to detail, one can side-step two operations more commonly known as the Data Movement Service (DMS) ShuffleMove and PartitionMove operations.  To… Read more

Parallel Data Warehouse (PDW) Tip: Usage of SYSDATETIME() vs GETDATE()

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

SQL SERVER - CTRL+SHIFT+] Shortcut to Select Code Between Two Parenthesis

Reblogged from Journey to SQL Authority with Pinal Dave:

Every weekend brings creative ideas and accidents brings best unknown secrets in front of us. Just a day while working with complex SQL Server code in SSMS I came across very interesting shortcut which I have never used before and instantly…

Read more

Parallel Data Warehouse (PDW) Basics: CREATE TABLE and CTAS Syntax

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

Dynamic Multi-Threaded SSIS using C#

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

Parallel Data Warehouse (PDW) Lesson 1: – Basic Architecture Overview

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

SSIS How-To: Reuse Existing Connection Manager to Execute SQL in Script Task

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