2021:Data Export (Export Definition)

Data Export is one of the Export Types available when configuring an Export Behavior. It exports extracted document data over a Data Connection, allowing users to export data to a SQL or ODBC compliant database.

About

You may download and import the file below into your own Grooper environment (version 2021). This contains Batches with the example document(s) and a Content Model discussed in this article [[]]

About

The most important goal of Grooper is to deliver accurate data to line of business systems that allow the information to be integrated into impactful business decisioning. Tables in databases remain, to this day, one of the main vessels by which this information is stored. Data Export is one of the main ways to deliver data collected in Grooper.

There are three important things to understand when using and configuring Data Export to export data to a database:

• The Export activity.
• Data Elements
• Data Connections

The Export Activity

Grooper's Export activity is the mechanism by which Grooper-processed document content is delivered to an external storage platform. Export configurations are defined by adding Export Type definitions to Export Behaviors. Data Export is the Export Type designed to export Batch Folder document data collected by the Extract activity to a Microsoft SQL Server or ODBC-compliant database server.

For more information on configuring Export Behaviors, please visit the full Export activity article.

Data Elements

Data Export is the chief delivery device for "collection" elements. Data is collected in Grooper by executing the Extract activity, extracting values from a Batch Folder according to its classified Document Type's Data Model.

A Data Model in Grooper is a digital representation of document data targeted for extraction, defining the data structure for a Content Type in a Content Model. Data Models are objects comprised of Data Element objects, including:

• Data Fields used to target single field values on a document.
• Data Tables and their child Data Columns used to target tabular data on a document.
• Data Sections used to divide a document into sections to simplify extraction logic and/or target repeating sections of extractable Data Elements on a single document.

With Data Models and their child Data Elements configured, Grooper collects values using the Extract activity.

Depending on the Content Type hierarchy in a Content Model and/or Data Element hierarchy in a Data Model, there will be a collection, or "set", of values for varying data scope of a fully extracted Data Model's hierarchy. That may be the full data scope of the Data Model, including any inherited Data Elements inherited from parent Data Models. It may be a narrower scope of Data Elements like a child Data Section comprised of its own child Data Fields.

Understanding this will be important as Data Export has the ability to take full advantage of Grooper's hierarchical data modeling to flatten complex and inherited data structures. Understanding Data Element hierarchy and scope will also be critical when exporting data from a single document to multiple different database tables to ensure the right data exports to the right places.

Data Connections

Data Export uses a configured Data Connection object to establish a link to SQL or ODBC compliant database tables in a database and intelligently populate said tables. Once this connection is established, collected Data Elements can be mapped to corresponding column locations in one or multiple database tables. Much of Data Export's configuration is assigning these data mappings. The Data Connection presents these mappable data endpoints to Grooper as well as allowing data content to flow from Grooper to the database table when the Export activity processes each Batch Folder in a Batch.

Furthermore, not only can Grooper connect to existing databases using a Data Connection, but it can create whole new databases as well as database tables once a connection to the database server is established.

We discuss how to create Data Connections, add a new database from a Data Connection, and add a new database table from a Data Connection in the #Configuring a Data Connection tutorial below.

How To

In the following tutorials, we discuss an an example of how to setup an Export Behavior, using Data Export. In this example are a couple of different document formats whose data will be collected by a single Content Model. This will help illustrate two key distinctions mentioned above. The first document (the "Employee Report) will demonstrate the flattening of inherited data. The second (the "Personnel Information Report") will give us an avenue to export to multiple tables at once. The example documents have already been classified and their data extracted. Before we get into Data Export configuration specifics, there's a few things we need to understand about "the story so far" The forms used in this example and how they are formatted. The Content Model used in this example, its Document Types and how their Data Models represent the data we want to collect. The extracted index data, which will be exported to a database.

Understanding the FormsUnderstanding the Content ModelVerifying Index Data

Understanding the Forms

Document 1: Employee Report The thing to understand about this document is some of its data share a "one-to-many" relationship. Some of the data is described as "single instance" data. These are individual fields like "Employee Last Name", "Employee First Name" and "Employee ID". For each document, there is only one value for each of these fields. These values are only listed once, and hence only collected once during extraction. Some of the data, however, is described as "multi-instance" data. The "Earnings" table displays a dynamic amount of rows, for which there may be a varying number of data for its columns ("Code Desc", "MTD", "QTD", "YTD") depending on how many rows are in the table. There are multiple instances of the "YTD" value for the whole table (and therefore the whole document). The single instance data, as a result of only being listed once on the document, will only be collected once, but needs to be married to each row of information from the table, in one way or another. The "one" "Employee ID" value, for example, pertains to the "many" different table rows. This document is meant to show how to flatten data structures. While the single instance data is only collected once, it will be reported many times upon exporting to a database table.

Document 2: Personnel Information Report The second document is essentially one big table of personnel information (name, address, email, phone number and the like). While we ultimately want to collect data from all rows in this table, there are potentially two sets of information here. Some of it is generic personnel information, but some of it is "personally identifiable information" or PII. This information should be protected for legal reasons. As a result, we will export collected data to two database tables (with the assumption that the second table is "protected".) This document is meant to demonstrate how to export to multiple tables via one Export Behavior. ⚠ DISCLAIMER: The author of this article is not a lawyer. For educational purposes we divided "Personnel Information" into two sets: "non-PII" and "PII" •  non-PII: Employee ID, Phone Number, E-mail address, IP Address, Gender, ZIP •  PII: First Name, Last Name, SSN, Street Number, Street Name, City, State. DO NOT TAKE THIS TO MEAN THIS INFORMATION IS OR IS NOT PERSONALLY IDENTIFIABLE INFORMATION. This division was done purely for educational purposes to demonstrate a concept. PII gets tricky in the real world. For example, an IP Address would not normally qualify as PII by itself, but could when combined with other personal information. Please consult your own legal department to determine what data you're collecting is PII and should be protected more securely.

Back to top to continue to next tab

Understanding the Content Model

The Content Model provided for this tutorial is named "Example Model - Data Export". This Content Model is designed to extract the data for these two different kinds of documents, each represented by its own Document Type.

The Employee Report Document Type The "Employee Report" Document Type' represents our "Employee Report" document. This is the one we will use to demonstrate flattening a data structure that shares a one-to-many relationship on the document. It has its own child Data Model with Data Elements already configured for extraction. For the individual fields, represented once in the document, there are three corresponding Data Field elements: "Last Name" "First Name" "Employee ID" For the tabular data, a Data Table named "Earnings" is established, with four Data Column elements, corresponding to the columns on the document: "Code Desc" "MTD" "QTD" "YTD"

The Personnel Info Report Document Type The "Personnel Info Report" Document Type represents our "personnel information" documents. This is the one we will use to demonstrate exporting multiple table structures to multiple databases from a single document. It too has its own child Data Model with Data Elements already configured for extraction. The "non-PII" Data Table will extract data from each table row on the document for non-protected personnel information, as described by its child Data Column elements: "Employee ID" "Phone Number" "EMail" "Gender" "Zip" The "PII" Data Table will extract data from each table row on the document for protected personnel information, as described by its child Data Column elements: "Employee ID" "First Name" "Last Name" "SSN" "Street Number" "Street Name" "City" "State" This set-up uses a single table extractor to collect each row on the document, but reports it to two different Data Table objects in Grooper (the "non-PII" Data Table and the "PII" Data Table). This will ultimately allow us to parse data from these columns, and place the PII related information into a separate database.

Verifying Index Data

Before the Database Export activity can send data, it must have data!

It's easy to get in the habit of testing extraction on a Data Field or a Data Model and feel good about the results, but it must be understood that the information displayed when doing so is in memory, or temporary. When testing a Data Export configuration, it's a good idea to ensure extracted data is actually present for document Batch Folders whose data you want to export.

When the Extract activity runs, it executes all extraction logic for the Data Model tied to a Batch Folder's classified Document Type. For each Batch Folder document, it creates "Index Data" and marries it to the Batch Folder via a JSON file called Grooper.DocumentData.json.

A couple of ways to verify its existence are as follows:

Option 1 First, navigate to a document Batch Folder object in the node tree. Not the Batch, not the root Batch Folder, not a Page object, but specifically a document Batch Folder object. Here, we have the first Batch Folder in the root folder of our Batch selected. This is where the extracted index data information lives. From there, click on the "Index Data" tab. After doing so you can see the extracted data displayed.

FYI Were the document classified (the Batch Folder assigned a Document Type), but not successfully extracted (the Extract activity not applied), the data structure would be present, but the fields empty. At that point the Batch Folder would have the Document Type's Data Model associated to it, but would not have applied its extraction logic to collect values (That's what the Extract activity is for).

Option 2 Another means of verifying is to actually view the file created by the Extract activity and stored in the Grooper repository's file store location. Again, click on a document Batch Folder object in the node tree. Click the "Advanced" tab. Click the "Files" tab. In the List View panel you should see a file named Grooper.DocumentData.json. This is the file the Extract activity generates when it processes a Batch Folder. It serializes all the extracted Data Elements' values for the document's Data Model. When you click on that file, you should see the stored JSON information of the indexed data displayed in the viewer below.

Back to top to continue to next tab