Share
Embed
Slide 1
Slide 1 text
No content
Slide 2
Slide 2 text
No content
Slide 3
Slide 3 text
No content
Slide 4
Slide 4 text
No content
Slide 5
Slide 5 text
No content
Slide 6
Slide 6 text
No content
Slide 7
Slide 7 text
• Degree of excellence exhibited by the data in relation to
the portrayal of the actual scenario.
• The state of completeness, validity, consistency,
timeliness and accuracy that makes data appropriate for a
specific use.
• The totality of features and characteristics of data that
bears on their ability to satisfy a given purpose; the sum
of the degrees of excellence for factors related to data.
• The processes and technologies involved in ensuring the
conformance of data values to business requirements and
acceptance criteria.
• Complete, standards based, consistent, accurate and time
stamped.
“Data Quality” Wikipedia, revised on December 17, 2014, http://en.wikipedia.org/wiki/Data_quality
Slide 8
Slide 8 text
No content
Slide 9
Slide 9 text
• Boring enterprisey buzz words.
• This is not a data quality strategy
discussion
• Hands-on tactical view for people
who want to know how to get
things done.
Slide 10
Slide 10 text
• Investigate what data profiling is
• Look at data profiling techniques
• Walk through some data profiling tools
• “Free Stuff”
• The stuff you can use right now.
Slide 11
Slide 11 text
Data profiling is a process whereby one
examines the data available in an existing
database or flat file and collect statistics
and information about that data.
Ed Lindsey, Three-Dimensional Analysis: Data Profiling Techniques (Data Profiling LLC, 2008), 29.
Slide 12
Slide 12 text
Because you need to:
• Transform the data
• Move the data
• Find patterns in data
• The data is unknown (to you)
• Improve data quality
• Data warehouse stuff
Slide 13
Slide 13 text
• Column Profiling
• Table Analysis
• Cross Table Analysis
• Business Rule Analysis
Slide 14
Slide 14 text
• Inferred data type
• Data types
• Minimum, mean and max values
• Nulls rule, % of nulls, the number of
nulls
• Distinct values
• Frequency distribution (counts)
• Pattern analysis
Slide 15
Slide 15 text
• Identify primary keys
• Identify candidate keys
• Verify foreign keys
• Candidates for normalization
• Duplicate rows
Slide 16
Slide 16 text
• Identifying relationships between tables
• Look for duplicate data between tables.
Slide 17
Slide 17 text
• Apply your business rules to the data.
• If you don’t know the rules of the
business you better figure it out.
Slide 18
Slide 18 text
• Evaluate the Table and Column Schema
• Get a feel of what kind of data you’ll be
dealing with.
• Check out the data types
• Take a peek at the data itself
• DMV’s are great for viewing the table
and column information
Slide 19
Slide 19 text
• Determine Min, Max and Mean for
numeric columns.
• Gives an idea about the range of the
data.
• Does the Min, Max and Mean make
sense for the data?
Slide 20
Slide 20 text
• NULL is an unknown value.
• NULL != NULL
• Can’t join on a NULL
Slide 21
Slide 21 text
• Nulls can be a problem so we need to
know what we’re dealing with.
• Nulls rule: Is it nullable?
• Number of nulls: How many nulls are
we dealing with?
• Percentage of nulls: How many nulls
compared with the number of rows?
Slide 22
Slide 22 text
• Need to look at the individual values of
a column and how frequently they
occur.
• We can do this by getting the distinct
values and counts (frequency
distribution) of a column.
Slide 23
Slide 23 text
• Determine the format that a string is in.
• Valid Phone Numbers
• 3058675309
• (305)8675309
• 305-867-5309
• (305) 867-5309
Slide 24
Slide 24 text
• We can use LIKE or PATINDEX in TSQL
• We can use Regular Expressions
against the data.
Slide 25
Slide 25 text
• Mostly important for doing wibbly,
wobbly, data warehouse-y stuff.
• Need to go though column(s) and
identify which combination of columns
would make good candidate keys
Slide 26
Slide 26 text
• Does the data in one table match the
data in another.
• What if we had a type that was missing?
• We can accomplish this by using left
joins and see what is missing.
Slide 27
Slide 27 text
• Do we have data that needs to be
normalized?
• Delimited strings
• Addresses
• Repeated data that could use it’s own
table?
Slide 28
Slide 28 text
• Because we don’t want another Clone
Wars.
• Maybe it’s valid…maybe it’s not.
• But we need to know if it’s there or not.
Slide 29
Slide 29 text
• Since SQL Server 2008 SSIS has
included the Data Profiling Task
• Connects to a database and performs
different data profiling tasks
• Outputs information in XML
• Use the Data Profile Viewer application
to view the results.
Slide 30
Slide 30 text
• Column Length Distribution
• Column Null Ratio
• Column Pattern
• Column Statistics
• Column Value Distribution
• Candidate Key
• Functional Dependency
• Value Inclusion
Slide 31
Slide 31 text
• Shows the extent that the values in one
column depends on the values in
another column(s).
• Example: Zip Codes should all
correspond to the same State.
Slide 32
Slide 32 text
• Shows if a column(s) can serve as a
foreign key between two tables.
Slide 33
Slide 33 text
• Data profiling tools in one easy to use
script.
• Table Schema
• NULL Ratios and Unique Values
• Column Statistics
• Candidate Key Check
• Column Value Distribution
Slide 34
Slide 34 text
https://github.com/Jorriss/sp_DataProfile
Slide 35
Slide 35 text
• Three-Dimensional Analysis: Data Profiling
Techniques, Ed Lindsey
• CLR Assembly RegEx Functions for SQL Server by
Example - https://www.simple-talk.com/sql/t-sql-
programming/clr-assembly-regex-functions-for-sql-
server-by-example/
• Pattern Matching (Regex) in T-SQL -
http://www.sqllion.com/2010/12/pattern-matching-
regex-in-t-sql/
• Data Profiling Task (MSDN)
http://msdn.microsoft.com/en-
us/library/bb895263.aspx
Slide 36
Slide 36 text
• Richie Rump
• @Jorriss
• http://jorriss.net
• http://dotnetmiami.com
• http://statisticsparser.com