Analyze

Once all the connection data has been configured and profiles have been defined, we can move on to the main functionality of Analyze; the Analyze tab. In this part of the application the user can specify analyze functions on the columns of the imported metadata and Analyze the data in the database.

At the left side of the window you see the imported tables or views. The right part shows the details and statistics of this table or view.

To analyze one or more tables or views you should press the "Run Statistics" button below on the right side of the window.

There are three icons giving you 3 options.

From left to right:

Run Statistics: The Analyse run will start.
Pause Statistics: The run will pause, a pop-up will appear. To continue press "OK".
Abort Statistics: The analyse run will abort.

To run statistics a connection to the database is required.

Saving your project

After gathering statistics on your tables, it's recommended to save your project. This is because gathering statistics generally takes some time, and in the event of closing and opening the project again this step can be skipped if the project was saved (unless gathering statistics again is desirable.)

Tables and views at the left side

On the left side of the screen the imported tables and views are shown.

From top to bottom:

A search box. This enables you to filter the tables or views in this screen. You can use % as a wildcard.
A drop-down menu containing all available schemas. This only applies if more then one schema is imported.
The Tables and views. These can be identified by the following icons:
for tables,
for views.

You can select one or more tables simultaneously to perform tasks on them at the same time.

Use the right mouse button to

Count rows. The amount will appear right from the table or view name.
Clear statistics: Clear statistics , including count, from the selected tables or views.
Clear profiles: Clear the matching profiles for the selected tables or views.

Statistics at the right side

The right part of the screen contains 3 Tabs.

Tab Statistics

This will give statistics date for the tables or views. Only selected tables or views are analysed.

Some of the columns provide characteristics of the field, they are available without running any analysis. They are based upon the imported data made available upon importing metadata.

After an analyze run the other columns are filled.

Statistics are gathered on the data in the columns.

# Nulls: The amount of NULL values in this column.
# Distinct: The number of unique values in this column
Min: Show the lowest value in this column ("A" is before "a", "-1" is before "1")
Max: Show the highest value in this column.
# Shortest: Show the shortest value in this column. For numeric also the shortest length is given where the the value 11 has the length 2 and the value -2 also has the length 2.
# Longest: Show the longest value in this column. For numeric also the shortest length is given where the the value 11 has the length 2 and the value -2 also has the length 2.
Least frequent: Show the least often occurring value in this column.
Most frequent: Show the most frequently occurring value in this column.

Filtering

If you select a table and simply press "Run Statistics" the analyze is run on the content of the whole table.

To obtain statistics on a section of your data a filter can be given.

In the center part of the window click the "Filter" tab and a text-box will open. In this text-box a SQL "where" clause can be given.

Running Statistics using a filter will analyze only the filtered data.
The resulting amount is shown in the Summary below after "Data filing".
A filter can be given for every table or view.
Filters are saved in the project
Filters are NOT removed when clearing Statistic data.
The Number of records in the left panel still shows the total number of unfiltered records in this table.

Tab Summary

The summary tab shows either one or two diagrams depending on whether the table was filtered. The data filling diagram shows the filing of this column. The other diagram is only visible for Numeric columns. It shows the number of positive, negative and "0" records in this column.

Below the diagram part you find three blocks of information: Ordering, Length, Frequency

For all three blocks you can see the most extreme results. The other tabs elaborate upon these results.

Below an example for an alphanumeric and a numeric column , both with the filter on LAST_NAME (like 'A%') (meaning the last name value must start with A):

Tab Ordering

It shows two panels. One for the Minimum values and one for the maximum values. It shows the value (from low to high and vice versa), the length and the number of occurrences.

Double-click on the record to show the found record(s). You can adjust this filter and run the query if you like to filter the data you've found further.

Tab Length

This shows two panels. One for the shortest and one for the longest values. It shows also the number of occurrences.

Double-click on the record to show the found record(s). You can adjust this filter and run the query if you like to filter the data you've found further.

Tab Frequency

In the frequency you can see which values occur most often and least often within a given table. In the Value column the actual value is shown, and in the Occurrences column the amount of times that value was encountered is shown.

Double-click on the record to show the found record(s). You can adjust this filter and run the query if you like to filter the data you've found further.

Tab Profiling

This screen shows an oversight of which tables conform to the profiles we've configured earlier.

In the example below the column EMAIL and GENDER have matching profiles. In this case this is completely to be expected, after all, when we name a database column "EMAIL", we should expect that there'll be email addresses in there.

Having abstract columns named undescriptive terms such as ZGKLZ125 or ZGKLA124 makes the use of profile rather helpful, as it removes the abstraction layer and gives us an idea of what is actually in these columns.

Tab Data

This screen gives a SQL window where you can provide a "where" clause to filter the data in the selected table. It will show the results in a grid. You can sort the grid by clicking the headers.

Only 100 records are retrieved by default. Since all data is transferred to the client raising this value may result in some performance issues.