Archive for the ‘Data Analytics’ Category

Converting Rows to Column with Segmentation

February 23, 2012

First of all, my apologies for the long time between posts.  My job (that pays me money) has kept me fairly busy over the past few months and the few that do follow this blog have been asking when I am going to do my next post… Well here it is.

Recently I found myself with the need to convert rows in a table into a single column.   My limitation was that I was only able to pull in 200 rows values per column and those values had to be comma delimited.  Meaning if I had 3118 rows in my source table and I needed to convert those 3118 rows into a single column, but could only have a maximum 200 row values in my column, my output would be 16 rows (3118/200 = 15.59 rounded up to 16).  My first inclination was to pivot the data; but that only allows me to create columns in excess of the values I want to pass into those columns.



Shade Under The Cloud

July 20, 2011

More and more companies and consumers are increasingly using use data-intensive applications driven by cloud technology that may have been previously unavailable due to overall cost and complexity.  Now, even mom and pop shops can store data, deploy complex applications using cloud technology.  They are finding that this is often the best way to provide optimal service, reduce failure points, ensure data integrity, reduce operating costs, and create a more easily managed environment.


Context Data Mining

August 31, 2010

A lot of companies are placing big bets that location services similar to Facebook Places are going to significantly change marketing, advertising and social networking in the coming years.  While some are still skeptical, I think leveraging the data mined from location services (or from a number of other places for that matter) will help in context aware applications.  Context aware applications are capable of giving us relevant information based on where we are located and/or the activity that we are performing.  The CNN money article Data mining your digital footprints speaks about two really cool “context-aware” applications worth reading.


Currency Conversions in SSIS

May 3, 2010

Capturing Real-Time Currency Conversions in SSIS

This article shows how to convert currency using SSIS. A web service task makes an external call to a web service to capture up-to-date exchange rates. The really cool part about this article is that it shows how to configure: web service tasks, recordset destinations and a for-each loop to shred a recordset.

Daily Extracts… Monthly Reports

April 14, 2010

Have you ever had to run a daily task that feeds a report which is generated at the end of every month?  If your answer is yes, you probably know that this can be either a mundane task to do manually or a logistic nightmare in planning, execution & data contention.  In SSIS, I’ll demonstrate a simple way to accomplish this task while minimizing space requirements and maintenance.  The package will determine the day of the month and make a decision on which portions of the package to execute based on that day.


Data Quality Part II

February 26, 2010

In Part I of this series, I showed how to create rules around data in a table. This post will expand on the idea of data quality and the need for data quality integration in your systems and applications. Ideally, a good data quality solution will help to reduce the amount of ETL (and associated overhead) required to incorporate data sets between applications, and will help to create data that is both efficient and valuable to your organization. (more…)

Extract Variable Records to the Same File

January 22, 2010

Hello my faithful reader(s).  I have been away for a while but do not despair:  I am back and 2010 promises some really cool posts.  Today’s post centers on extracting variable record types to a single flat file.  This applies to data warehouse type files that have  header, detail & trailer records somewhat similar to those I have seen for terradata and other warehouse applications.  There will also be a future post in this blog that will describe how to load this data type into your warehouse along with some built in intelligence.


Parent-Child records in SSIS

December 1, 2009

Converting a File with Parent-Child Records in SSIS

This article talks about converting a file with Parent-Child records using SSIS.  Parent-child files have a header record (parent) with an undetermined amount of subsequent detail records(child).  The transformation extracts non-blank records, creates a key for each record and splits that record by type and then merges the records back together to create a flattened record with a 1-to-1 relationship. 

Read the article at:

Data Quality Part – I

December 1, 2009

Since the blog is named data integrity, I figured I would look up what “data integrity” truly meant.  In searching for the definition on google, data integrity came to be defined as ‘data that is complete or whole. All characteristics of the data including business rules, rules for how pieces of data relate, dates, definitions and lineage must be correct for data to be complete’.  With that in mind, I am going to start a number of posts on Data Quality and Data Profiling over the coming weeks. 


Dynamic SQL using CHARINDEX

November 11, 2009

Recently I was called upon to create a new field from an existing field.  The specifications called for the new field to be composed of any data between two dashes.  The dashes could be at any positional value and the data between the dashes was not a consistent length.