Process the data

Collect statistical and informative summaries about the data ..

Processing and Profiling the 'Patient' Table

After successfully ingesting the metadata from our database schemas, our next step focuses on the data - 'Patient' table. This phase is essential for evaluating the data's structure, quality, and integrity to ensure our database's efficiency and effectiveness. Although currently limited to database metadata, Data Profiling will offer deeper insights, which we will explore later in this workshop.

Processing the data

Accessing Your Catalog

To access your catalog, please follow these steps:

1. Open Google Chrome web browser. and click on the bookmark, or

   Navigate to: https://pdc.pentaho.example/

2. Enter the following email and password, then click Sign In.

Username	[e-mail]
Password	Welcome123!

Security Advisory: Handling Login Credentials

For enhanced security, it is strongly recommended that users avoid saving their login details directly in web browsers. Browsers may inadvertently autofill these credentials in unrelated fields, posing a security risk.

Best Practice

• Disable Autofill: To mitigate potential risks, users should disable the autofill functionality for login credentials in their browser settings. This preventive measure ensures that sensitive information is not unintentionally exposed or misused.

---

Process the Data

1. Select 'Data Canvas' from the left menu option.

2. Click the checkbox to select the 'synthea' schema.

Process synthea schema

For optimal performance, keep your selection within a practical limit. Processing a very large number of tables, like 100,000, can drastically reduce speed. The default settings on the Configure Data Profiling page generally fulfill most needs and are recommended.

1. Click 'Process'.

In the process of managing both structured and unstructured data, two critical steps stand out: Metadata Ingest and Data Profiling. This distinction is essential for ensuring data quality and accessibility.

1. Metadata Ingest

Ingest Metadata

Metadata ingest is a foundational process in data management within a Data Catalog. It involves the automatic collection of metadata — the data about data — from a database schema / file / object. This step is crucial for understanding and organizing the data, making it easily accessible for further analysis and data profiling.

1. Navigate to the metadata ingest section of your Data Catalog tool and initiate the process by clicking the Start button.

Metadata Ingest

1. Users can select specific tables or datasets for metadata ingestion. For example, if you are interested in patient information, you might expand the 'patients' table and opt for relevant fields such as 'passport'.

2. After starting the ingest process, monitor its progress on the Manage Workers page. This page provides real-time updates on the ingestion task.

Metadata Ingest - Worker

1. Next -> 2. Data Profiling

2. Data Profiling

Data Profiling: An Essential Step in Data Analytics

Before diving into data analytics within a Data Catalog, it's critical to ensure your data has been properly profiled. Data profiling is not just a step but a prerequisite. Without a valid data profile, proceeding to data identification tasks is premature.

What is Data Profiling?

Data profiling involves a detailed examination of your dataset, focusing on specific data objects. During this process, you collect valuable statistics and summaries that provide insights into the nature of your data. The beauty of data profiling is its immediacy. As soon as data from a column, table, or schema is processed, the results are ready for review.

When a data source schema is initially ingested, you're limited to the database metadata. Data profiling enriches this by uncovering additional layers of information, making it an indispensable tool in the data analyst's toolkit.

1. Click the Data Profiling tile.

Data Profiling

1. Enable the option: 'Extract samples'

• Extract Samples: This feature profiles a subset of data to ascertain its characteristics. It employs predefined Dictionaries & Patterns for a comprehensive analysis, streamlining the profiling process.

• Skip Recent (days): Avoids re-profiling tables that were profiled within a specified period, enhancing efficiency by focusing on older data. Setting this to 7 means tables profiled in the last week are not re-examined.

Data Profiling - patients

1. Click: 'Start'.

You can view the status of 'Profiling' on the Manage Workers page.

Data Profiling - Worker

The dataset has now been scanned which returns the metadata properties along with some additional details.

The next stage is to explore the data to confirm the details.

---

Explore the Data

2.1 Resource

The 'top level' is the connection resource: postgresql:synthea

1. Highlight the resource: postgresql:synthea

The Summary tab displays the connection properties

Resource Summary

1. Select Details option.

The Details tab provides a overview of the Schema name, the number of Tables & Columns.

Schema Details

1. Next -> 2.2 Schema

2.2 Schema

Contains detailed information about child resources. You can view the items available in the selected resource, along with some additional information. The information varies based on the resource selected.

For example, if you select a data source, you can view available items like schema for structured data and folders for file systems. In addition, you can also get the number of tables and columns, including associated tags. For schemas, you can get additional information like row count and the last profiled date and time.

The bussines_apps_db database can contain a number of Schemas. In PostgreSQL, a schema is a named collection of tables, views, functions, constraints, indexes, sequences etc.

1. Highlight the synthea schema -> Details tab.

Note: The 'Synthea' datasource has alreaddy been profiled.

1. Next -> 2.3 Table

2.3 Table

A 'table' is a collection of documents. A document contains fields which are analogous to columns in a relational database.

1. Highlight the synthea patients -> Details tab.

patients 'table'

Note: The 'Synthea' datasource has also been profiled. The Data Identification policy has resulted in some columns being tagged as being Personal & Sensitive data.

1. Next -> 2.4 Column

2.4 Column

A document is going to be comprised of a number of fields, which can be analogous to a database column.

1. Expand the 'patients' table and select: 'passport'.

2. Once the profiling has completed, you can review the collected metadata.

For instance, selecting the 'passport' field will present various statistical insights, such as Null Count, Cardinality, and Min/Max Width, aiding in understanding the data's characteristics.

Statistics - passport

The following table identifies the key details available in the Statistics pane when you select a column in a table to view:

Feature	Description
Null Count	Number of entries that are null.
Cardinality	The number of unique values in a field, where a low cardinality number indicates many repeated values.
HLL	An estimate of cardinality of the data, with a roughly ~2% margin of error.
Blank Count	The number of entries that are blank.
Min Width	The minimum number of character count in a value in the column.
Max Width	The maximum number of character count in a value in the column.
Avg Width	The average number of character count in a value in the column.

1. Scroll down to Data Patterns:

Data patterns - passport

1. Next -> 3. Data Identification

---

Viewing Data Patterns

Data Patterns provide insights into the frequency and distribution of data when examining a specific column, including any random values present. Names and values within this view are limited to 200 characters for readability.

Access to this feature requires the appropriate permissions, enabled through the Sample Data Access setting in your user role. Users with administrative rights have the capability to adjust these permissions as needed.

This functionality is essential for understanding sample-profiled resources and accessing various resource-level details efficiently.

This feature is explored in more detail in: Data Patterns

3. Data Identification

Data Identification - Data Quality

Pentaho Data Catalog uses data identification methods called dictionaries and data patterns to help you identify data.

Dictionaries

Dictionaries are lists of words that may occur in your data that can be used for column data matching. Typically, dictionary words are those that you could not write a pattern to be able to match, such as country codes.There are two kinds of dictionaries, system defined and user defined. System defined, or built-in dictionaries come with Data Catalog and user-defined dictionaries are created by Data Catalog users.You can add a user defined dictionary in three ways:

• by importing a CSV file zipped with a JSON file containing the definition of the directory.

• by uploading a CSV file and creating the JSON file to define the dictionary by entering information in the UI.

• by selecting a profiled column of data from any JDBC table or file.

Data patterns

Data patterns are also used to identify data, by discovering patterns in the data that match a regular expression.

If data profiling is not done, Data Catalog highlights as 'Required'.

To illustrate, let's apply a data identification policy to the patients 'table'. This will apply identify and tag various columns in the table, this helps:

• to quickly locate relevant information, saving time and effort when exploring vast data repositories.

• link related data across different sections of the taxonomy.

• derive maximum value from information assets by understanding their context and purpose.

1. Click the Data Identification tile.

Data Identification

1. Click 'Select Methods'.

Data Identification - Select methods

Applying Data Dictionary + Pattern Analysis = Policy

1. Select the following Data Dictionaries & Data Patterns:

Method	Data Dictionaries	Pattern Analysis
USA_SSN		Social Security Number
Country Codes	Country Names
Country Names	Country Names
DoB		Date of Birth
USA States	States in USA

Data Identification - Policy

1. Click Start.

2. Track the Job in the Workers.

Workers - Data Identification Job

1. In Data Canvas, check that the sensitive data in the Synthea -> 'patients' table has now been identified - tagged as PII & Sensitive.

Tags - PII & Sensitive Data