IDERA released a new free tool, SQL Data Profiler, designed to provide quick visibility into data patterns contained in SQL Server databases. Its intuitive user interface simplifies exploring patterns in data by streamlining and automating the process. Quickly view a summary of the data in selected database tables and columns, and receive recommendations based on the data in the columns.
With the free tool, profile data in tables (optionally by subsets of data types), adjust profiling thresholds to customize the analysis, display a summary of data in a selected table and its columns, receive recommendations based on data per column, and export results to plain text file format.
Connect to an instance of SQL Server, select a database, and select a table to profile the data contained in the table and its columns. Select the types of data to include in the profiling of the database table. Focus on these data types to reduce the time to analyze large data sets and the number of displayed results. For example, initially only profile core elements and subsequently investigate specific areas of interest.
Modify the settings of the profiling operation. Set the thresholds for suggesting reference tables, possible anomalies, and different data types.
Display a summary of the data contained in a selected table and each of its columns. For the table, display the number of columns and rows, the last modification date of the data and schema, and the size of the table. For each column, show the name, data type, statistics, and more for a total of 19 properties. Profile the distribution of the values in each column. For a selected column, display the list of unique values, their count, and their relative occurrence.
Display recommendations based on the data contained in a selected column. These recommendations refer to modifying the data type to accommodate the data better, handling sparse columns, migrating columns to reference or lookup tables, adding foreign key constraints, and more for a total of 37 recommendations.
Save the chosen settings and options, and the results of the profiling to a tab-delimited plain text file. Import this file into applications such as Microsoft Excel with minimal user interaction.
Download the new SQL Data Profiler today to analyze data patterns in tables and columns. Refer also to IDERA’s Community Forum for SQL Data Profiler.
You should be able to download this free tool via this hyperlink: https://www.idera.com/productssolutions/freetools/sql-data-profiler/registrationform.
Hi Peter, Where can I download this tool? When I select the download link on the profile page for the tool it simply navigates me back to the Idera blog page.
You can now download SQL Data Profiler v220.127.116.110 which should fix the issues with:
* "Profiling Failed: The profiling of table [table] of database [database] failed. Arithmetic overflow error converting expression to data type money." and
* "Profiling Failed: The profiling of table [table] of database [database] failed. The conversion of the nvarchar value of [value] overflowed an int column.".
For SQL Data Profiler, the queries require access to the system views INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLES. For every database that a connected account wants to profile, the account needs permissions granted to access these system views. By default, sysadmin has these permissions.
I tried a DB I have admin rights to and everything works fine. So I suspect there Tool is wanting higher access then what I have on the external DBs I am trying to analyze.
Just upgraded and still getting the same error that others have reported. "Index out of range ....." I tried several tables in the data base and I am getting the error for all of them.
I am also getting: "user does not have permission to perform this action"
I only have data read access to the tables. I can not do DDL or DML operations. But Data profiler should only be reading the data correct?
You can now download SQL Data Profiler v18.104.22.1680 which should fix the issue with "Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index." and "Exception in Fetching table profile for grid 'FORMAT' is not a recognized built-in function name." for SQL Server 2008 R2.
Thank you for sharing these feature requests with us. I added them to our issue tracking system, for consideration for possibly future updates.
We received the application log file and we are trying to resolve the issue.
it would be nice if I could 'drill down' into a column to see distinct values, counts;
also, nice if it could validate column values against a lookup table and identify 'fallouts' (eg table of valid state codes)
I send to dropbox the application log file.
I apologize for the inconvenience.
Can you please share with us your application log file?
We use TracerX for logging. The log files are saved to the application data folder. Please refer to the forum posts "In which folder is user application data stored?" (community.idera.com/.../23191) and "How to send log files for free tools to IDERA?" (community.idera.com/.../23175).
Hi Team at IDERA,
I can't use the new tool because always aperas this message:
Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index
This hapen in all tables.