Solved

Microsoft sql server "frequency report"

Posted on 2014-07-22
4
387 Views
Last Modified: 2016-02-13
Hello there!

I've been doing a lot of "initial analysis" on data files we receive from external entities.  Part of what I do with that data is to look at it and see what we have and describe it.  

I like to work in SSMS but find myself often using other tools to run "frequencies" (e.g., SPSS or AgileDataSoftware FileQuery").  Is there a function in SSMS that would allow me to run frequencies on data without having to define each field etc?  Something I could quickly run against a table that would tell me for each field in the table the count of distinct values, count of "empty" values, min and max length, types of characters (letters, digits, whitespace, special characters), and then 10 or 20 sample values?  

The output would be best in a report format.  

I feel like I'm looking for a plugin or something but I'm not really sure.  

TIA!
0
Comment
Question by:ttist25
  • 2
  • 2
4 Comments
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 40213970
Hi
In SSIS there is a Data Profiler Task.
You can add this task to an SSIS worflow and execute it on a specified table.

It will produce an XML file which you can open with the Data Profile Viewer tool which is in the Integration Services folder in the SQL Server program group.

It can display column length, pattern, null ratio statistics and does so in a 'dashboard' type view

Please let me know if you would like more detail on this SSIS Data Profiler Task
0
 
LVL 1

Author Closing Comment

by:ttist25
ID: 40215248
Hi Barry!

Thanks for the response.  Although I don't have time to figure out how to do it at the moment, it sounds like this is what I'm looking for.  I'm not sure when I'll have the time to look into workflows and how to add tasks but I didn't want to leave you hanging while I figured that out so, I awarded the points.  

If you have time and could do a quick list of steps that'd be great but definitely not necessary as it's outside the scope of the original question.  

THanks again!
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 40219805
Hi
Please see print screen attached which depicts and existing such task in SSIS.
I would advise maybe doing a few basic SSIS tutorials first if you are not familiar with SSIS.

You add the 'Data Profiling Task' from the toolbox to the main pane and add 2 connection managers, one for the DB/table that contains your data and the other for the profiling results XML file. You can then right-click on the  'Data Profiling Task' and choose Execute
SSIS-DataProfiler.png
0
 
LVL 1

Author Comment

by:ttist25
ID: 40220438
Thanks Barry!

Have a great weekend!
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question