2.90:Database Export

From Grooper Wiki

Database Export is an Activity used to export extracted data to a SQL or ODBC compliant database.

Database Export is one of the main ways to Deliver data Collected in Grooper.


A completed Content Model and accompanying Batch for what will be built can be found here. It is not required to download to understand this article, but can be helpful because it can be used to follow along with the content of this article. This file was exported from and meant for use in Grooper 2.9

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. Grooper's Database Export activity is the mechanism by which this delivery is performed. Database Export uses a configured Data Connection to establish a link to (Microsoft SQL Server or ODBC-compliant) tables in a database and intelligently populate said tables.

Database export is the chief delivery device for "collection" elements. These are Data Elements for which there will be a collection, or "set", of values. Examples of these are Data Columns found within Data Tables, or Data Fields located in Data Sections with the Scope property set to MultiInstance.

Two key distinctions about Grooper's Database Export activity are its ability to take full advantage of its sophisticated hierarchical data modeling to flatten complex/inherited data structures, and the ease of delivery to multiple tables at once.

How To

Following is an example of how to setup the Database Export activity. In this example are a couple of different document formats whose data will be collected by a single Content Model and used to help illustrate the two key distinctions mentioned above. The first document shows the flattening of inherited data, while the second is used to give an avenue to export to multiple tables at once.

! Some of the tabs in this tutorial are longer than the others. Please scroll to the bottom of each step's tab before going to the step.

Understanding the Forms

The thing to understand about the document displayed on the right is that there is a combination of single instance data (single fields of information) married with multi-instance data (table displaying a dynamic amount of rows), i.e. - a one-to-many relationship. The single instance data (Last Name, First Name, Employee ID), 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. While the single instance data is only collected once, it will be reported many times. This document is meant to show flattening inherited data structures.



The second document is collecting only information from the main table, but there is Personally Identifiable Information (PII) in the table that should be protected. As a result, it will export its information to two tables (with the assumption that the second table is "protected".) This document is meant to demonstrate how to export to multiple tables via one step in a Batch Process.

Back to top to continue to next tab

Understanding the Content Model

The Content Model extracting the data for these documents is fairly straight forward. There are two Document Types, each with their own Data Model.

The first Document Type's Data Model is the one representing the one-to-many relationship. Notice for the fields represented once in the document there are Data Fields. For the tabular data, a Data Table was established.

The second Document Type's Data Model is using one table extractor to collect all the data, but reporting it to two different tables.

It should be noted that the documents in the accompanying Batches had their Document Type assigned manually. The Content Model is not performing any classification.

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 the Extract activity is successfully run against a properly classified document (you can at the very least manually assign a Document Type) it creates Index Data and marries it to the document via a JSON file called Grooper.DocumentData.json. A couple of ways to verify its existence are as follows.

  1. First, navigate to the Document (or Folder) object in the node tree; not the Batch, not the Batch Folder, not the Page, but specifically the Document object. This is where the information lives.
  2. From there, click on the Index Data tab.
  3. After doing so you can see data displayed. Were the document classified, but not successfully extracted, the data structure would be present, but the fields empty.



  1. Another means of verifying is to again, click on the Document object in the node tree.
  2. Click the Advanced tab.
  3. Click the Files tab.
  4. In the List View you should see the Grooper.DocumentData.json file.
  5. 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

Configuring a Data Connection

In order for the Database Export activity to run, it needs an established connection to a database and subsequent table(s). Grooper can connect to an existing database and import references to its tables, or easily enough, you can have Grooper create the database FOR you AND create tables based on data structures present in Grooper!
The following setup will have Grooper create the database and its tables.
It is worth noting that this article cannot tell you specifics about permissions in your environment. The configuration for this article is using Microsoft SQL Server, not ODBC, and has given the active Active Directory user full DB Admin privileges to the SQL environment.

  1. Expand the node tree to the Infrastructure folder and in the Data Connections folder Add > Data Connection...
  2. In the Add New Data Connection window give the object a name and click OK
  3. As mentioned above, the configuration settings listed here will be specific to this environment:
    • Set the Provider property is to SqlServer
    • The Server Name property should be the SQL instance. In this case, the hostname of the computer is enough because the SQL environment was established with a default SQL instance, therefore no named instance. Were there a named SQL instance it would something like OKCSPRKI01\MSSQLSERVER
    • Because Grooper will create the database, the Database Name property will be left blank for now.
    • The User Name and Password properties are left blank here, which passes the current logged in AD user's credentials to SQL.



With a connection to the SQL environment established, we can make Grooper create our database for us.

  1. Click the Create Database button
  2. In the Create Database window, give the database a name, and click the OK button.
  3. The Connection Settings and Database Name properties will be updated to reflect the new database information entered.



This Data Connection is not complete until a table references has been imported (this particular Data Connection will end up with three table references). A database has been created and is connected to, and Grooper did the work for us there, so let's keep that up to get tables made. Three different tables need to be created and references imported, due to the two different Document Types and the subsequent Data Models (the second Document Type containing a Data Model with two Data Tables). The following process will describe importing the first table, and will simply need to be repeated for the second and third tables (of which will not consist of the same inheritance notions discussed on step 5 because the scope considered for the Database Export 02 Document Type has Data Tables and no Data Fields.)

  1. Click the Create Table button.
  2. Click the Content Type property and click the drop-down arrow to get a mini node tree view.
  3. Select the first Content Type. In the case of this exmaple, it will be the Document Type named Database Export 01.
  2. Becaues the Database Export 01 Content Type was selected, the following Data Element Scope property will have a drop down of which will contain Data Elements relative to this particular Content Type.
  3. Selecting the Earnings Data Table will set the baseline structure of the table to be created, and it will inherit upward.
    • Think back to the notion of the one-to-many relationship. This table can have a dynamic number of rows and the Data Columns are capable of capturing and reporting back unlimited instances of data, hence multiple rows. The Data Fields within this scope, however, are only capable of capturing and returning a single piece of data. But, given the nature of inheretence, the table that will be created will make columns not just for the Data Columns of the Data Table, but for each of the Data Fields contained within this scope.
    • Were there Data Fields further up the inheretence tree, say at the base Data Model of the main Content Model the table would also attempt to inheret those as well.
  2. The Table Name property is arbitrary, but by default is a concatenation of the strings passed by the Content Type and Data Element Scope properties.M
  3. Click the Execute button to close this window and continue the process forward...
  2. Here's where Grooper is doing the hard work for us. Notice in the Review/Edit SQL Statement window the SQL Statement required to create our table is already written for us.
  3. Click the Run SQL Statement button to create the table.
  2. With the table made, the Database Tables List View will have an entry in it.
    • It will initially display with a red dot (as seen in the image), but will change to a green dot when you successfully complete the reference import.
  3. The Table Columns List View will display the data structure of the table, while the Data Preview List View will display data within the table.
    • Because this table was just created, it will not have any data to display.
  4. Clicking the Import Table Reference button will ...
    • Be warned, the Drop Table button is a powerful one in that it will delete the entire table from SQL. Use this button with EXTREME caution.
  5. ...create a Database Table object in the node tree.
    • You cannot use the Drop Table button unless you delete the Database Table object from the node tree.

Back to top to continue to next tab

Configuring Database Export Activity

Data collected. Connection to database established. Now it's time to configure the activity that will send the data to its final destination.

  1. Select a Batch Process
  2. Click the Add Step... button.
  3. Set the Activity Type property to Database Export
  4. Click the drop-down menu of the Database Connection property and select the Data Connection object to be used.
  2. Click the drop-down menu of the Content Type property and select the Content Type that contains the scope of the information you want to deliver. In this configuration it is the Database Export 01 Document Type.
  2. Click the Export Mappings property then its ellipsis button to open the Export Mapping Collection Editor window.
  3. Click the Add button to add an entry to the List View below.
  4. Click the item added to the List View to reveal its properties.
  5. Select the Database Table property and in its drop-down menu select the Database Table object (the imported table reference that is a child of the Data Connection ojbect that was previously established) that represents the scope of the data you wish to send. In this case it is Database_Export_01_Earnings".
  6. Select the Data Element Scope property and in its drop-down menu select the object that represents the scope to which you wish the map to the previously selected table. In this case it is the Earnings Data Table.
    • Selecting this Data Table as the scope will not only allow mapping to its Data Columns, but all Data Fields up its logical heirarchical tree path.
  2. Select the Columns Mappings property then its ellipsis button to open the Column Mappings window.
  3. The column on the left represents the data elements of the scope selected, in this case the Earnings Data Table and its inherited Data Fields. The column on the right represents the columns of information from the Database Table object created and imported previously. Because the table in the database was created by Grooper based on this schema, the column names should match. One by one (or use the Auto Map... object command) match these columns up.


The Database Export activity that was just established is specific to the table configured because it is contained within the narrow bounds of that particular Content Type. Two other tables need to be mapped however. To do this, because they exist within the bounds of a different Content Type, another Database Export activity needs to be established.
The good news is that the remaining two tables are both relative to the same Content Type. Considering this, both tales can be mapped within on Database Export activity. The process for doing so is exactly like what was just done, with the only difference being you can add another entry to the Export Mapping Collection Editor window.
This concept is novel and is extrememly useful as you work with more and more complex data structures.

Back to top to continue to next tab

Processing and Viewing Output Data

With everything configured, you can now execute the activities and view the results.

  1. Make sure the Batch Process is expanded in the node tree, and select the Batch Process Step object.
  2. Selecting the Batch Process Step object will give access to the Unattended Activity Tester tab.
  3. Select a Batch.
  2. Click the Process All button to bring up the Process All window.
  3. In the Process All window you can set the amount of threads you want to apply to the processing of this activity now.
    • Note that this setting is temporary for running this particular activity this one time.
  4. Click the Start button to run the activity.
    • Because two Batch Process Steps were made to accommodate the two Content Types, this process should be repeated for the other Database Export activity on the other Batch.



With the activities successfully run, over in SQL Management Studio the output can be seen.

Version History

Prior to Grooper 2.9 a different Batch Process Step was needed for every unique table export. The addition of the Export Mappings Collection Editor allows multiple tables that belong to the same Content Type to be exported in one Batch Process Step.

Retrieved from "https://wiki.grooper.com/index.php?title=2.90:Database_Export&oldid=17163"