Friday, January 15, 2010

Pivot/Unpivot or Transpose in SQL 2005 and SSIS 2005

Lately I’ve been working on a project to bring a suite of Excel Reports to the new age, via SSIS and SSRS. (yeah, boring stuff, not mentioning the times spent to understand the user actions, VBA macros, cell formulae etc, such a pain!!!). Anyway, one of the existing manual actions from the users of the Excel which I need to emulate in the new system is the transpose copy paste action. At last, something that I haven’t done before and it’s interesting to me, in a sense.

After some research, there’s actually quite a lot of way to achieve the result, either using SSIS Data Flow task of Pivot/Unpivot, or just pure Transact-SQL approach.

The SSIS Method

Can’t explain better than what this blog has already mentioned. It involves a clever use of both Pivot and Unpivot Data Flow tasks. The example given provides the basic concept for me to tackle my slightly complex scenario.

Transact-SQL Method

Again, no point for me to reinvent the wheel. This blog has given the best options available. On top of that, MSDN also provide good examples on how to use the PIVOT and UNPIVOT command.

My Implementation

After the research, time to get the job done. So here’s I need to do:

I have an Excel table that looks like this:

Date: 15/1/2010

Category

Product A

Product B

Product C

Product D

In

Out

In

Out

In

Out

In

Out

Category 1

Category 2

Category 3

I need to transform it into this first:

Date

Product Name

Category

Balance

15/1/2010

Product A

Category 1

{In-Out}

15/1/2010

Product B

Category 2

{In-Out}

15/1/2010

Product C

Category 3

{In-Out}

Then into these tables for SSRS consumption:

Product A

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

Product B

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

Product C

Date

Category 1

Category 2

Category 3

15/1/2010

{Balance Value 1}

{Balance Value 2}

{Balance Value 3}

I decided to use both SSIS and Transact-SQL PIVOT for my solution. Reason is to avoid having to keep multiple Product tables. In the example shown above, I would need 3 tables for each product. Imagine having more than that…

Part A: SSIS Unpivot

First off, here’s my SSIS Data Flow:

1. Because I can’t Unpivot both [In] and [Out] columns at the same time (SSIS limitation), I have to Unpivot them separately, then join them back later through the Merge Join task.

2. Merge Join task requires all the data flow coming into it sorted with the same key(s). It will sort of being smart to detect if the data flow reaching it is sorted or not. If it is not, then it will not run and you'll get an error. This is typically solved by using the Sort task first, but since I know my data from the Excel is sorted, I avoid using the performance taxing Sort task. Instead, I set the Unpivot task’s output properties of IsSorted to True, then, in the Unpivot Output columns, set the SortKeyPosition of the sorted column to 1. (If more columns were used to sort, set the other columns with SortKeyPosition=2 or 3 or so forth). Screenshot below shows the Avanced Editor of the Unpivot task, where I set the IsSorted property.

3. The resultant join will have some invalid rows, due to merge columns in the Excel. Therefore, I’ve used the Conditional Split task (Get valid data) to exclude the unwanted columns.

4. The rest are simple stuff of adding the derived Date column, calculations of {In}-{Out} and data conversion, before loading into Production table.

Part B: SQL PIVOT

Next is the PIVOT SQL command (following the sample in MSDN)

SELECT Date, [Category 1], [Category 2], [Category 3] FROM

(

SELECT Date, Category, Balance

FROM Table01

WHERE ProductName = ‘Product A’

AND Date = ‘20100115’

) AS SourceTable

PIVOT

(

AVG(Balance)

FOR Category

IN ([Category 1], [Category 2], [Category 3])

) AS PivotTable;

So it’s done. Probably not the best apparoch, but hey, it works anyway J.

Setting up SharePoint 2010 development environment on my Windows 7 laptop

SharePoint 2010 is now well into the Beta 2 phase. As a consultant and developer for SharePoint solutions, it’s time for me to dive in! To do that well, I need to have a development environment to play around on top of reading blogs and PDC materials.

Given the choice, I would love to setup the a virtual box on my work laptop, but the hardware specs of 4GB and 2.0GHz seems to be at the border line of usable performance. Besides, I don’t want to reinstall my newly imaged Windows 7 workstation laptop to Windows 2008 R2 for Hyper-V. I’m not gonna buy VMWare either. I also tried Sun’s VirtualBox which support x64 guest OS, but it’s really really slow…

That leaves me no choice but to install it on the host machine itself, since SharePoint 2010 now support installation on Windows 7 and Vista, or, more or less. I says so because it’s not so straight forward as installing it on Windows Server 2008. However, it’s much more easier than doing the same for SharePoint 2007.

Being a late starter, plenty of resources already available on the Internet. Many people hit into some problems getting it to work on Windows 7. Some straight forward, some not. Here’s the steps I’ve taken to complete the installation until Site Collection creation is working:

  1. Followed the steps in Setting Up the Development Environment for SharePoint Server. (Why can’t it be easier?)
  2. As pointed out in the SharePoint Team Blog, applied the Hotfix KB976462.
  3. Had a UserProfile Exception, due to TimoutException. This blog helped me out, which is to delete some cached ASP.NET files in C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Temporary ASP.NET Files\d31735f8a1b34f3a8263707181c7e298\5902e8c4\679035c5\.
  4. Some other errors that I also encountered during the configuration wizard process are:

a. Exception: Microsoft.SharePoint.SPException: User cannot be found.

b. Unable to create a Service Connection Point in the current Active Directory domain. Verify that the SharePoint container exists in the current domain and that you have rights to write to it.

These errors were probably resolved by applying the patch in #2 or the steps in #3.

So now I got a working test bed to try out those exciting features that I’ve read for months!