DataPrep Elements

DataPrep elements in the Process Designer tool are used for executing data preparation operations upon process inputs. These elements consist of Workspace and DataPrep Export.

Note: A Hub element can be added between a Workspace element and a DataPrep Export element to process multiple input files individually or by group on a workspace.

The table below specifies functionality of the DataPrep group elements.

Element Name

Description

Properties and Comments

Workspace

Performs operations upon process inputs.

Name — name of the graphic element.
Workspace Path — path to the required input file location.

Actions to select the workspace path:

1. Click the Browse button to open the Browse dialog.
2. Select among server library.
3. If you have slow data source connections, you can select the Delay data source connections validation check box. In this case, you can cancel the validation of Data source connections when loading workspace by clicking the Cancel button in the Properties panel. You can also cancel the validation of Data source connections when opening a process which contains a workspace.
4. Click OK.
NOTE: The following alert displays when you change the workspace path of an existing workspace element: Load Plan should be reselected and reapplied in DataPrep Export/Verify Workspace settings because another workspace was specified. (It is impractical to assume that the Load Plan is still valid, given the Workspace has changed, thus, requiring this step to confirm/refresh accordingly).

Validate Workspace — Click this button to validate the workspace manually. The result of workspace validation shows the following status:

Validation successful
Validation failed
Validation is not completed

Load Plans — the list of available export load plans, including:

- Provider type. This shows the type of provider.
- File Name. If your Workspace input is a file of a certain format, you can substitute it with the file from the process input. Do the following. For the PDF Report, Text Report and Delimited Text file formats add a File input item to your visual process design form. For Microsoft Access and Microsoft Excel formats add a Database input item. Connect this item with your Workspace element. Set up the Input path for your File input or Database input. Then in the File Name drop-down list for your load plan select a File input or a Database input option.
NOTE: When the Database Input option is selected, the table name automatically changes to the table name from the database input. The table name may be changed manually.

NOTE: Database Input is required for Excel formats and must include the extension even with wildcards.
NOTE: Additional options are available for Excel Worksheet Design. They are detailed here.
- Source. If the workspace input is a database, then this field displays the database name. If the source type is SQL Server, ODBC, or OLE DB, there is the possibility to rewrite/edit the Database Data Source. To do so, click the Edit Table Info button, and then edit the Connection String, Table, etc. If the plugin is Google Analytics, the settings can be changed by clicking the Edit Table Info button. In the Google Analytics window, edit the Service Account Email, Profile ID, Start Date, End Date, etc. If the plugin is Oracle, edit the settings by clicking the Edit Table Info button. In the Oracle window, edit the connection type and settings (including host name, port, service name, user ID, password, etc.). Tables and views, as well as queries, can also be edited from this window.
NOTE: Additional options for SQL Server, OLE DB, and Oracle source types are provided below.
- Change History — additional operations to be executed with the input. Click the Add Change List button and select a change list from the Server Library to add to the workspace. You can also add new item to Server Library via the Add Change List dialog box. Upon selecting a change list, you will see a Change History list. You can add multiple change lists via the little Add Change List button on the right, or remove all change lists via the Reset Change History button. You can also disable certain changes to your workspace by clicking the respective Undo button (all the following change items will be disabled as well). Click the Redo button to enable the changes back. Non-applicable changes appear as disabled and accompany with a warning sign. You can remove such changes via the Delete button.
NOTE: When the load plan comes with a change list, this change list is labeled as “Default.” All new change lists added to the workspace’s change history are labeled according to their names in the Server Library.

When starting a process via manifest, the existing change list in the process can be overwritten with a change list from the Server Library.

DataPrep Export

Exports data from data preparation workspace.

Name — name of the graphic element.
Load Plan to be Exported — select a load plan for exporting.

The user can select several load plans for the export by ticking their corresponding checkboxes. To export all load plans, select the All Load Plans check box.

Import Export from Workspace — allows you to import an export previously saved with the connected workspace.

Click this button to open the Import Export Definition dialog box. In the Export Name dropdown menu, select the workspace export you want to import. The corresponding details of the export (e.g., load plan, export type) are displayed. Click OK to proceed. The settings of the selected export are then applied to the DataPrep Export item settings.

Export Type — the type of export file. Further settings depend on the type of file and are described as advanced options below.
Export Destination — path to required export file.

Note: You may also use the naming macros to name the exported files and destination folder. To do this, click the Insert Macro icon ( Insert Macro icon ) and select the naming macros from the drop-down list.

NOTE: The Input File Name macro is available to specify one or more input file names. If there are several inputs in a Load Plan (Join/Union), the names of the export files will contain a combination of these input files. When the Input files are processed by group, the Input File Name macro will only hold the first file name.

Export Options — allows you to specify the filter, summary, and sort settings for the export

Filtering:

Select filter: From the dropdown list, choose the filter you want to apply to the export. When All filters is selected, you will be asked to specify the Automatic Naming setting:

By Files: produces a file for each filter
By Tables: produces a table for each filter

Select a summary:

NOTE: This option is available for the following file types: CSV, Microsoft Excel, Microsoft Access, XML, JSON, Tableau, SAS Transport Format, and Qlik.

Summary: From the dropdown list, choose the summary to apply to the export. Selecting All summaries will let you specify the Automatic Naming setting:

By files — when exporting multiple summaries, a file is exported for each summary, and the files are named according to the summary names.
By tables — when exporting multiple summaries, a table is produced in the export file for each summary.

Measure: Allows you to specify the measure to apply to the summary selected. This is activated when the summary has a defined column key. From the dropdown list, you can also choose All, by key to include all measures grouped by key or All, by measure to include all measures grouped by measure.
Split: Check Each distinct value of the first key makes a separate file or table to split the summary export according to each value change in the first key (i.e., the leftmost one). The Automatic Naming setting can then be modified:

By Files: produces a file for each distinct first key
By Tables: produces a table for each distinct first

Drill level: Set the drill level to apply to the summary being exported. Drilling up and drilling down is to collapse and expand a summary data set, respectively. A lower drill level (e.g., Level 1) results in fewer columns in the exported table.

Sorting

Select Order: From the dropdown list, choose the sort to apply to the export.

NOTE: When the selected table has a row limit applied, you can proceed with the export by exporting either all rows or limited rows. Specifically, the following options are available:

Export all rows: the export file will contain all the rows from the workspace
Export limited rows (Limit: X number of rows): the export file will contain only a limited number of rows, as set in Data Prep Studio.

The option to export either all rows or limited rows is only available when one load plan is selected. It cannot be modified when multiple load plans are selected for export.

Advanced options for Workspace element

Additional options for excel worksheet design

When dealing with multiple inputs, the Parse inputs for sheet names option becomes available. Select the checkbox if you want to parse the inputs for specific sheets, and then click Apply to…

When selected, changes can be applied to the following:

All worksheets
First sheet
Last sheet
Selected Sheet by Name

When not selected, changes can be applied to the following:

Selected Sheet by Name
Selected Sheet by Number

When dealing with only one input, modifications can be applied to the following by clicking Apply to…:

All worksheets
First sheet
Last sheet
Selected Sheet by Name

Additional options for SQL Server, OLE DB, and Oracle source types

In the column list in the SQL Server/OLE DB/Oracle dialog box, select the column to use.

Select from the following options:

Aggregate: Select the check box if the column data should be aggregated (i.e., grouped together) and then select the aggregation method to apply from the drop-down menu. The following aggregation methods are available:

Sum: Applies to numeric data; returns the total value of all values in the column
Count: Applies to numeric data and dates; returns the number of entries in the column
Min: Applies to numeric data and dates; returns the lowest value in the column
Max: Applies to numeric data and dates; returns the highest value in the column
Group by: Applies to numeric data, dates, and text; returns grouped values and is often used with functions such as SUM and COUNT.

Apply Filter: Select the check box if the column data should be filtered and then select the filter method to apply from the drop-down menu. Enter the filter value to use in the corresponding Filter Value box.

The following filter methods are available:

Equal to: Applies to numeric data, dates, and text; returns all values that contain the specified filter value
Not Equal To: Applies to numeric data, dates, and text; returns all values except those that contain the specified filter value
Greater Than: Applies to numeric data and dates; returns all values that are greater than the specified filter value
Greater Than Equal: Applies to numeric data and dates; returns all values that are greater than or equal to the specified value
Less Than: Applies to numeric data and dates; returns all values that are less than the specified filter value
Less Than Equal: Applies to numeric data and dates; returns all values that are less than or equal to the specified filter value
Contains: Applies to text; returns all values that contain the specified filter value
Starts With: Applies to text; returns all values that start with the specified filter value
Ends With: Applies to text; returns all values that end with the specified filter value

Advanced options for DataPrep Export element

Additional options for CSV file typeS

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting existing data.

Delimiter sets the separator symbol between columns. The delimiter characters available are comma, semicolon, tab, pipe, and space. Choose Other to specify a different character.
Qualifier sets the wrapper symbol type.
Choose the Encoding Type to apply to the CSV file. The following options are available:

Code Page
UTF-8
UTF-16LE
UTF-16BE

Set the Code Page to apply to the CSV file. The following options are available:

OEM United States
Greek (DOS)
Baltic (DOS)
Western European (DOS)
Central European (DOS)
OEM Cyrillic
Turkish (DOS)
Portuguese (DOS)
Icelandic (DOS)
French Canadian (DOS)
Nordic (DOS)

Cyrillic (DOS)
Greek, Modern (DOS)
Japanese (Shift-JIS)
Central European (Windows)
Cyrillic (Windows)
Western European (Windows)
Greek (Windows)
Turkish (Windows)
Baltic (Windows)
Cyrillic (KOI8-R)
Cyrillic (KOI8-U)

Western European (ISO)
Central European (ISO)
Latin 3 (ISO)
Baltic (ISO)
Cyrillic (ISO)
Greek (ISO)
Turkish (ISO)
Estonian (ISO)
Latin 9 (ISO)
Japanese (JIS)
Japanese (EUC)

Select the Include Column Header checkbox to include headers in your file.
Select the Append DOS end-of-life character (x1A) checkbox to add a DOS EOF character to the export file.
Select the Legacy compatibility mode – Ignore format customizations during export checkbox if you want to use the Monarch Classic style formatting instead of the column format customizations during the export of numeric and date columns.

NOTES

When this option is selected, numeric values are exported using the current system’s localized decimal separator. For date values, the following options are available:

- yyyy-mm-dd

- yyyymmdd

- Use current Windows Regional Settings (based on the settings of the server where the host is installed)

For Monarch Server, this would typically be based on the Service Account for the Monarch Server Agent Service localization specifically (or, if running Monarch Server in console mode, the User Account context in which the console is running).

ADditional options for fixed text file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting existing data.

Click Column sizes to adjust the widths of the columns in your file.

additional options for Altair panopticon file types

In the When output file exists dropdown menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting existing data.

Enter your Table Name.

NOTE: Click the Existing Tables button ( ) to display the names of the tables in the export file when exporting to a file that already exists. Select the desired table name.

When Add data is selected in the When output file exists dropdown menu, the When table exists dropdown menu becomes available with the following options:

select Overwrite to substitute the data table,
select Append to append records to the existing table,
select Update to update existing rows,
select Update and append to update existing rows and append new ones,
select Skip to stop the export operation from overwriting existing data.

additional options for microsoft excel file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting existing data.

Enter your Table Name.

NOTE: Click the Existing Tables button () to display the names of the tables in the export file when exporting to a file that already exists. Select the desired table name.

When Add data is selected in the When output file exists dropdown menu, the When table exists dropdown menu becomes available with the following options:

select Overwrite to substitute the data table,
select Append to append records to the existing table,
select Skip to stop the export operation from overwriting existing data.

Select the Suppress column header row checkbox if you prefer to hide the column headers in the exported Excel file.
When Append is selected, select the Do not match column names checkbox if you want the columns from potentially different tables to be left aligned regardless of column names or data types.
Click the Advanced Excel Export Options button to apply the following settings:

For Table Exports:

Digital Signature: Select this checkbox to apply a digital signature to the exported Excel file. By adding a digital signature, you establish the authenticity, integrity, and non repudiation of the export output. Select or change the certificate to be used by clicking the Certificate button. In the Select Certificate dialog box, specify the certificate source by choosing between Store and File in the dropdown menu.

- Store: A list of the certificates currently available on your Certificate Store is displayed. Select a certificate from the list and click OK.

- File: Navigate to the folder containing your certificate file and then select it. Enter the certificate password in the field provided. Click the Get Certificate Information button to display the details of the certificate.

Add AutoFilter drop down lists to column labels in the Table: Select this checkbox to enable Excel’s AutoFilter feature so that it is ready and active upon opening the exported Excel file.
Include Pivot Table sheet: Select this checkbox to modify the pivot settings. Click the Pivot Table button to display the Pivot Table Settings dialog box:
Fields tab: Use this tab to set the desired pivot field filters, columns, rows, and values.
Options tab: Use this tab to specify the Pivot Table name, corresponding action if the Pivot Table exists, style, and grand total parameters.

For Summary Exports:

Digital Signature: Select this checkbox to apply a digital signature to the exported Excel file. By adding a digital signature, you establish the authenticity, integrity, and non repudiation of the export output. Select or change the certificate to be used by clicking the Certificate button. In the Select Certificate dialog box, specify the certificate source by choosing between Store and File in the dropdown menu.

- Store: A list of the certificates currently available on your Certificate Store is displayed. Select a certificate from the list and click OK.

Embed the page setup header in the worksheet title: Select this checkbox to make the Summary page header (defined using the Page Setup dialog) part of title of exported Excel Worksheet.
Include Outline to enable Drill Up/Down in Excel: Select this checkbox to include an outline in the exported Excel worksheet. The outline will allow you to drill up and down in Excel.
Include Formulas to facilitate editing in Excel: Select this checkbox if you want Automator to attempt to translate subtotal and grand total calculations into Excel formulas in the exported Excel file.
Use conditional formatting if required conditions are fewer than (?): This option implements Conditional Formatting Rules in the exported Excel file as long as the number of rules would not exceed the specified amount. The Conditional Formatting feature in Excel provides a mechanism to modify the display attributes of a cell based upon the value of that cell. Automator leverages this feature by creating conditional formats from the rules specified on the Coloring and Limits dialog of a measure's properties. Depending upon the rules defined for each measure, up to 3 conditional formatting rules can be generated for each value of an exported measure.

Select the Export All checkbox if you want to export all columns; otherwise, click Column Mapping to map the export columns to the destination columns.

Additional options for Microsoft Access file types

In the When output file exists dropdown menu, select Overwrite to substitute files with the same file names.
Enter your Table Name.

NOTE: Click the Existing Tables button () to display the names of the tables in the export file when exporting to a file that already exists. Select the desired table name.

When Add data is selected in the When output file exists dropdown menu, the When table exists dropdown menu becomes available with the following options:

select Overwrite to substitute the data table,
select Append to append records to the existing table,
select Update to update existing rows,
select Update and append to update existing rows and append new ones,
select Skip to stop the export operation from overwriting existing data.

Select the Export All checkbox if you want to export all columns; otherwise, click Column Mapping to map the export columns to the destination columns.
When Update or Update and append is selected from the When table exists dropdown menu,modify the following:

When update match not found: Select the Export to delimited text file check box to export the unmatched record(s) to a delimited text file when updating existing rows in a table if a matching row is not found in the destination table.

Additional options for OleDb and ODBC file types

Type in the Connection String or select it via the Data Link Wizard or Connection Definitions with all the required data.
Select the Include password checkbox to input a password.
Select the Bulk Behavior checkbox to enhance export performance.

NOTE: This option is applicable for Oracle Database and Microsoft SQL Server OLEDB providers only.

Select the Use Transaction checkbox to revert the export via rollback if an error occurs during the export.

The Use Transaction checkbox is only available when the Bulk Behavior check box is selected.
Selecting this option will affect the performance of the table export and may require a large amount of system resources, especially for the target destination. Therefore, make sure to test this option first and then make the necessary adjustments.
The transaction is valid for the export operation only and not for the overall process.

Enter your Table Name.
In the When table exists drop-down menu,

select Overwrite to substitute the data table,
select Append to append records to the existing table,
select Update to update existing rows,
select Update and append to update existing rows and append new ones.
select Skip to stop the export operation from overwriting existing data.

Select the Export All checkbox if you want to export all columns; otherwise, click Column Mapping to map the export columns to the destination columns.
When Update or Update and append is selected from the When table exists dropdown menu, modify the following:

When update match not found: Select the Export to delimited text file check box to export the unmatched record(s) to a delimited text file when updating existing rows in a table if a matching row is not found in the destination table.

Additional options for Qlik FILE TYPES

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting an existing data.

Additional options for Tableau file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names.
select Skip to stop the export operation from overwriting existing data.

Note: Export to Tableau is performed in .tde format.

To publish the Tableau export file to Tableau Server, click the Publish to Tableau Server check box under the Publish to Tableau Server options. This displays the following Tableau Server settings.

Data Source Name: The name of the Data Source on the Tableau Server.
Site: The address of the data source.
Path to TABCMD: The route to the executable file of the TableauServerTabcmd application.
Server Address: The address of the Tableau Server.
Username: The login name for the Tableau Server.
Use Password File: Allows the use of .txt file with the password.
Password: The password to the Tableau Server (if the option Use Password File is enabled, it allows to specify the path to the Password File).
Proxy Address: The address of the proxy server (in HOST:PORT format).
Publish Type: Allows to specify an action for the Data Source:

Overwrite Data Source: Overwrites the data in the Data Source with the data from the export table (date modified is changed);
Replace Data: Replaces the data in the Data Source with the data from the export table (date modified is not changed);
Append Data: Adds data from the export table to the end of the Data Source table.

Additional options for IBM Cognos Analytics file types

Enter your Table Name.
In the When output file exists dropdown menu,

select Overwrite to substitute files with the same file names,
select Skip to stop the export operation from overwriting existing data.

Type the value of the Cognos Analytics server URL to the Server Url field.

Click the Test button to get all namespaces.
Select the corresponding Namespace.
Type the correct Username and Password.
Click the Check Credentials button to validate credentials.

Note: If you want to log in anonymously, select the Is Anonymous checkbox.

Additional options for Microsoft Power BI file types

Enter your Table Name.
In the When output file exists dropdown menu,

select Overwrite to substitute files with the same file names,
select Skip to stop the export operation from overwriting existing data.

Click the Set Power BI Login Data button to open the Power BI Login form.

Note: If the data on logged in user has been already received, then the button name becomes Update Power BI Login Data.

Additional options for SAs transport format file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names.
select Skip to stop the export operation from overwriting existing data.

Enter your Table Name.

NOTE: Click the Existing Tables button () to display the names of the tables in the export file when exporting to a file that already exists. Select the desired table name. The table name is limited to 8 characters in length.

Additional options for JSON file types

In the When table exists drop-down menu,

select Overwrite to substitute files with the same file names,
select Add data to add the new data to the existing one,
select Skip to stop the export operation from overwriting an existing data.

Additional options for altair knowledge studio file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names.
select Skip to stop the export operation from overwriting existing data.

Additional options for XML file types

In the When output file exists drop-down menu,

select Overwrite to substitute files with the same file names.
select Skip to stop the export operation from overwriting existing data.

Select the Legacy compatibility mode – Ignore format customizations during export checkbox if you want to use the Monarch Classic style formatting instead of the column format customizations during the export of numeric and date columns.

NOTE: When this option is selected, numeric values are exported using the current system’s localized decimal separator. Date values are exported in “yyy-mm-ddThh:mm:ss” format. For Monarch Server, this would typically be based on the Service Account for the Monarch Server Agent Service localization specifically (or, if running Monarch Server in console mode, the User Account context in which the console is running).

Automator supports the following plug-ins

Datawatch.DataPrep.Engine

PDF Report
Text Report
Delimited Text
JSON
XML
HTML
Microsoft Access
Microsoft Excel
ODBC

Panopticon.DataDirect

DB2
Hadoop Hive
Cloudera Impala
Informix
MonogoDB
MySql
Oracle
PostgreSQL
Amazon Redshift
Salesforce
SqlServer
SybaseIQ
Teradata

Panopticon.CloudantPlugin

IBM Cloudant

Panopticon.DatawatchPlugin

Monarch Server - Report Warehouse

Panopticon.ODataPlugin

Odata

Panopticon.SplunkPlugin

Splunk

Panopticon.BusinessObjectsUniversePlugin

Business Objects Universe

Panopticon.GoogleAnalyticsPlugin

Google Analytics