Recordings for 24 Hours of PASS: Business Analytics - 2014

If you would like to take a peek about the coming PASS Business Analyics Conference in San Jose, CA, May 7-9, 2014;

If you would like to learn more about how to use Power Query, PowerPivot, Power View, and Power Map to discover and demonstrate business insights;

If you would like to learn something about big data or predictive analysis/modeling;

here is a valuable resource that you should not miss,

Recordings for 24 Hours of PASS: Business Analytics - 2014

Hidden Gem for Data Analysis: Data Profiling Task

Data analysis and cleansing are the most time consuming parts when you want to load source data, or prepare sampling data for data mining. You can write TSQL to find out distinct values or lengths of a column that are existing in the testing data. However, when you have millions of rows or more than thirty columns for analysis, TSQL scripts might take a whole day to get the answer. Since SQL Server 2008,  SSIS has Data Profiling task that can conveniently help analyze the content and structure of data, and uncover patterns, inconsistencies, anomalies and even redundancies. Today we will give a brief demonstration about this hidden gem for data analysis.

How to view the result by Data Profiler Viewer?

Data Profiler Viewer is used to view the generated profiler by data profiling task in SSIS. You create a new package and drag “Data Profiling Task” to the control flow pane as shown.

Double click the task, a new Data Profiling Task Editor window pops up and then click “Open Profile Viewer” button.

Click the open button at the upper left corner and locate the output profile file in xml format.

Types of profiles and statistics for analysis

Then you can explore the profile. Below only show four kinds of profiler but you can have eight kinds of profiles to request:

1. Column Length Distribution
2. Column Null Ratio
3. Column Pattern
4. Column Statistics including min, max, mean, standard deviation for each column.
5. Column Value Distribution including values, counts, percentages.
6. Functional Dependency: fully or partially dependent of other column to check redundancy.
7. Candidate Key: check which column might be a good candidate for a primary key or business key.
8. Value Inclusion: check whether all values in a column exists in another table. It can be dimensions or lookup table.
   

Demo

Let us take a look at the Column Value Distribution Profile first. Click each column on the top pane and then the lower pane will display Value, Count, Percentage for that column. You can sort by value, counts, or percentage by clicking the dropdown arrow at the right side of item. For this column, you can easily tell the top three kinds of type codes are M, P, R.

Note that it won’t be an exhausted list for all the values. Here is an example for drug code. It only listed three values in the lower pane but number of distinct values is 48720. So if the variation for column values is a lot but for some values, there are only a tiny percentage records, the profile won’t display those small percentage.

In summary, data profiles generated by Data Profiling Task of SSIS is very convenient to help you understand data, find patterns, derive data rules, detect the outliners of columns for data cleansing. You can even perform data profiling to test the foreign key relationship. If you would like to find out how to set up each kinds of profile requests, Jamie Thomson’s series of SSIS: Data Profiling Task are an awesome reference.

Reference: TechNet’s Data Profile Viewer

How to: Use Check Constraints to Enforce Business Rules

As a developer, you may often find that it is essential to ensure data integrity before the data get into database. Otherwise, you will face a cleanup work or the situation of “trash in, trash out” afterwards. Today we will address how to enforce business rules by using check constraints on columns.

Here's an example of a Member table with name and phone number as columns. Let us check constraint on the phone number column. Constraint will suggest that phone numbers should follow the format of xxx-xxx-xxxx.

IF OBJECT_ID('[dbo].[Member]') IS NOT NULL
    DROP TABLE [dbo].[Member]
    CREATE TABLE [dbo].[Member](
    MEMBERID INT IDENTITY NOT NULL,
    [Name] varchar(25) NOT NULL,
    [PhoneNumber] varchar(12) NULL
    )

---- INSERT RECORDS
Insert INTO [dbo].[Member]    Values 
    ('Susan', '697-555-0142'),
    ('Kevin', '819-555-0175')

If you check the table, you can see it already has two records.

SELECT *
FROM  [dbo].[Member]

Add a CHECK CONSTRAINT

Then we add a CHECK CONSTRAINT to help check the format of phone numbers.

ALTER TABLE [dbo].[Member]
ADD CONSTRAINT CK_Member_Phone
CHECK ([PhoneNumber] LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
GO

If any of existing records in tables violate the constraint to be added, it will pop up messages like this:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint “CK_Member_Phone”. The conflict occurred in database "xxxx", table "dbo.Member", column 'PhoneNumber'.

 

Test a CHECK CONSTRAINT

Next, you can test the check constraint by trying to insert a record that has a phone number not following the format defined in the constraint CK_Member_Phone.

Insert INTO [dbo].[Member]    Values 
    ('Charles', '212-555-0187'),
    ('Chris', '612555-0100')

An error message will be returned like the following one:
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Member_Phone". The conflict occurred in database "DemoSQL2012", table "dbo.Member", column 'PhoneNumber'.
The statement has been terminated.