Aperture Data Studio user guide

If you have any issues or questions, try troubleshooting or contact support.

Find out about main features and benefits of Aperture Data Studio.

To get started, see an overview of the functionality or try out use cases.

1. Upload your data using the Data Explorer. You can either:
   • upload your source files: right-click on My files > Upload file or
   • connect to your database using a JDBC driver: Data sources > Click here to create a new data source.
     
     
2. Preview, configure and tag your data.
   

   Preview the first 1,000 rows and check that your data is in order. If required, make changes: right-click on your file/data source and select Preview and configure.
   You can move, rename and exclude column headings and change character sets/delimiters/quotation marks. Click Apply in the top-right corner when you’re happy with the changes. The file will now appear as 'Configured'.

3. Load and explore your data.
   Right-click on it and select View data. The previewed file will now appear as Loaded. To profile the data once the file is loaded, click View Profile/Outliers. It will appear as Profiled.

   Larger files might take a while to load. A percentage progress bar will be shown over the file name in the explorer view (on the left-hand side).

   You can now understand your data: filter, sort, view the outliers and discover inconsistencies. Ensure the loading has completed before you start manipulating the data.
   
   
4. Create workflows.
   In the Workflow Designer, click Create a new workflow, name it and click Submit. Add your loaded file(s) and open the Workflow steps tab to manipulate your data as required in order to:
   
   • create a new and improved version of your data,
   • structure and prepare it for migration,
   • validate against user defined rules,
   • find matching/duplicated values,
   • cleanse records and more.
     
     
5. Report/export your data.
   In the Workflow Designer, you can create a .pdf report for each workflow: click Report in the top menu. 
   You can also save the workflow output as a .csv, .txt, .xls, .xml, .dat, or a .json file by clicking Execute in the top menu. To re-use a workflow, save it as a .wfl file by clicking Export in the top menu.

Data Explorer allows you to manage your data sources, prepare and configure your data.

This menu lists all your data sources: both text files and database connections.

My files contains files uploaded by you only; Data sources lists all the available connected/configured database connections.

To upload/connect to your data, do one of the following:

• drag and drop files into the window or
• go to My files > Click here to upload a file or
• click upload in the top menu or
• connect to your database using a JDBC driver: Data sources > Click here to create a new data source.

The supported file formats are:

• .csv
• .txt
• .xls
• .xlsx
• .sas7dbat
• .psv
• .json
• .metro2
• .dat 

Find out about using Metro 2 and JSON files.

The supported database drivers/services are:

• JDBC
• Apache Hadoop HDFS. Find out how to configure Hadoop HDFS.
• Amazon S3
• Microsoft Azure Blob Storage 

You can create JDBC connections for the following databases:

• Amazon Redshift
• Apache Cassandra
• Apache Hive which supports:
  • Apache Hadoop Hive
  • HortonWorks
  • Cloudera CDH Hive
  • Amazon EMR Hive
  • IBM BigInsights Hive
  • MapR Hive
  • Pivotal HD Hive
• DB2
• Greenplum which supports:
  • Pivotal Greenplum
  • Pivotal HAWQ
• IBM Informix
• MongoDB
• MySQL
• Oracle Database
• Oracle Service Cloud
• PostgreSQL
• Progress OpenEdge
• Salesforce which supports:
  • Salesforce.com
  • Veeva CRM
  • Force.com Applications
  • Financial Force
• Spark SQL
• Microsoft SQL which supports:
  • Microsoft SQL Server
  • Microsoft SQL Azure
• SAP Sybase

Find out how to add a custom JDBC driver. 

Data Studio supports files that contain data not displayed in tabular form (Metro 2 and JSON). You can also use the Data Studio SDK to create your own file parsers.

Simply upload the file as usual and the inbuilt file parser will automatically parse (i.e. ‘read’) the file.

Identifier field

Where required, the field used as the identifier for each unique record can be configured in the Data Explorer: right-click on the file and select Preview and configure. The Id field name in the Data tab has to match one of the column names displayed in the preview. This field will be used by the parser as the identifier field (the default is Data Studio Link which we recommend to leave unchanged).

The parsing process

Metro 2 files are parsed in a way such that each base record exists as a row in a resulting master table. Any segments associated with base records are arranged in a resulting sub-table named after that segment. The sub-tables contain the identifier column so that they can be linked back to the master record. Many segment records can, therefore, reference the same base record.

JSON files with records containing arrays of elements will have these arrays broken out into resulting sub-tables which are linked to the associated master record by the identifier field.

Once uploaded, use Data Explorer to view the data. Right-click on the loaded file and select View data. This will show the parent/base table and all the sub-tables/segments. Similarly, you can profile and view outliers for either the parent table or sub-tables.

If the file can’t be parsed for any reason, it will have the Error status. Right-click and select View data to see the details for the error.

Each table will also be available as a separate output node when a Metro 2 or JSON file is used in a workflow. Each output node represents a different table and you can preview the data in each by clicking on Show data.

To combine the sub-tables with the parent/base table we recommend using the Join step.

For each data source, the administrator can specify individual login credentials for each user/user group to ensure that restricted data is not accessible to anyone without the appropriate permissions.

To create and assign access credentials:

1. In Data Explorer, click on the required data source (e.g. Salesforce).
2. Click Credentials and then Add credentials.
3. In the Add Credential dialog, specify the name for this credential. We recommend using a descriptive name as this will be used when assigning the credential to users/user groups.
4. Enter the username and a password.
5. Click Edit to specify the table(s) that user(s) with this credential will have access to. 
6. Click Apply to save changes.
7. Go back to the previous screen - click Credentials.
8. To apply the newly created credential to individual users, click Users; to apply to a group of users, click Teams. 
9. Right-click on the required user/team and click Add access.
10. Specify the credential and click Apply.

To remove access from the user/team, simply right-click on them and select Remove access.

To ensure your data has been interpreted correctly and is displayed as expected, you can preview it: right-click on your source data and select Preview and configure. A preview of the first 1,000 rows will be shown. 

You can move, rename and exclude column headings and change character sets/delimiters/quotation marks. 

To configure how the data should be interpreted, use the Data tab.

Depending on the file type, you can specify various settings. For example, for .csv files:

• Character set - the character set for the data.
• Language and region - the formatting region (e.g. whether to use UK or US format for dates).
• End of line delimiter - the sequence of characters denoting the end of the line.
• Delimiter - the delimiter to be used for separating fields in this file.
• Quote - the delimiter to be used when loading quoted values from delimited files.
• End of line precedence -  enable to force line endings to take precedence over text delimiters.
• Headings on first line - include or exclude column headings in the first row.
• Profile after load - enable to automatically profile data as soon as it's fully loaded in Data Studio.

The Headings tab allows you to manipulate the column headings: you can rename, re-order, remove and exclude them. You can also tag data.

Alternatively, modify columns by right-clicking on the column header, selecting the required option and clicking Apply in the top-right corner to save your changes. 

Standardize data

You can standardize your data easily before loading it into Data Studio: right-click on the column header and select Edit.

You can now apply one or more standardisation options: 

• Auto-parse integers: convert numeric values with no fractional part to integers. We don’t recommend disabling this option if the column values are integer numbers.
• Auto-parse decimals: convert numeric values with fractional parts to decimals. We don’t recommend disabling this option if the column values are decimal numbers.
• Auto-parse dates: parse dates based on the language for the file.
• Auto-parse scientific numbers: convert values in scientific notation (e.g. ‘1E47’) into numbers.
• Treat blanks as null: we recommend to have this option enabled to treat all blank values in the column as empty/null.
• Remove leading blanks: we recommend to have this option enabled to remove the leading white space from the column values.
• Remove trailing blanks: remove the trailing white space from the column values.

  Note that if the Treat blanks as null option is turned off, and both Remove leading blanks and Remove trailing blanks are on, Data Studio will ensure that at least a single space is retained for entirely blank values.

If you want to keep the null and blank values separate, we recommend that you turn off Remove leading blanks and Remove trailing blanks. Otherwise, Data Studio will not load values with varying spaces and will not treat them as different values.

By default, all of the standardization options above are enabled (except for Auto-parse scientific numbers). To change the enabled/disabled options for all files:

1. Go to Configuration > All server settings.
2. Find Default standardization rules.
3. Enable/disable options as required and click Apply. 

To rearrange columns, you can simply drag and drop the column headings. Click Apply in the top-right corner when you’re happy with the changes. The file will now appear as 'Configured'.

To revert all the changes made to your data:

• click Cancel in the top menu to remove changes made since the last configuration or
• right-click on the file and select Reset configuration when not previewing the file to completely reset the configuration. 

Data Studio can use machine learning algorithms to automatically tag columns. Data tags allow you to enrich source data by specifying additional information about what each column contains. These are then used to improve the user experience by providing sensible defaults and suggestions. Tags are used in various workflow steps such as Find duplicates and Validate addresses.

Data tags appear next to the column name, so if you're not very familiar with your data, they provide an overview at a glance.

There are two types of data tags:

• System defined (cannot be modified, used to drive system behavior)
• User defined

Tags in Data Studio are hierarchical: a parent tag (e.g. address) can have multiple child ones (e.g. street, city). 

While you can't modify system defined tags, you can create a child tag (e.g. PO box) and assign it to the system one (e.g. Address). Once created, it will appear in the User defined list under that system parent tag.

To create new and manage existing tags, go to Glossary > Data tags. To add a new one, click Create a new data tag.

The best way to manually tag data is before it has been loaded, in preview:

1. Go to Data Explorer.
2. Right-click on the required file and select Preview and configure.
3. Open the Headings tab to see all your column headings.
4. Click Multi select and choose one or more headings of the columns you want to tag (e.g. City, Country).
5. Right-click and select Tag columns.
6. In the Tag column dialog, click Edit.
7. Apply the necessary tag(s) (e.g. Address) and click away for changes to be saved.
   The Selected tab will show all the tags applied to this column.
8. Click Tag to save changes.

If you tag columns in a workflow step, the tags will be applied to that column in all the subsequent steps for that workflow only. 

Columns can be automatically tagged by allowing the system to recognize the type and meaning of data from its trained knowledge base.

For example, Data Studio can automatically detect columns containing names, addresses, and other customer-related data within a file. You can also easily train the system to recognize types of data that are specific to your organization (or not yet included in Data Studio’s knowledge base).

One of the benefits of having your data tagged is to allow the Workflow Designer to apply intelligent defaults in your workflow steps, significantly speeding up workflow creation.

Automated tagging is done by processing the data as it's loaded into the system. The processing is performed one column at a time by comparing the patterns of its values with known patterns (fingerprints). This comparison results in a similarity score on a scale of 0-100, and there is a configurable threshold to determine how similar the data must be to the fingerprint in order for a tag to be applied automatically.

For some tags, you may want the threshold to be high for a positive result, particularly where the values have similarity to fingerprints for other tags. For others, where the data is typically unique to that tag, you may prefer to set the threshold lower. For example, columns containing country names tend to have a very high similarity score because there's a relatively small number of countries in the world. Conversely, columns containing surnames tend to have a lower similarity score due to the much larger number of surnames in most datasets. Therefore, it's appropriate to specify a higher similarity threshold for a country and lower threshold for the surname column.

If you find that Data Studio is making mistakes when automatically tagging data, it's likely to be caused by a threshold that's set at the wrong level for the type of data you're dealing with.

Both system and user defined tags can be found in Glossary > Data tags.

To adjust the threshold, right click on the tag and select Edit to adjust this to be between 1 and 100. You can also Delete user training data to remove all the fingerprint files associated with the tag.

You can also exclude the tag from auto tagging by right-clicking on it and selecting Exclude from auto data tagging.

To enable auto tagging:

1. Go to Data Explorer.
2. Right-click on your data file and select Preview and configure.
3. In the Data tab, select Auto tag data on next load.
   Enabling this will force the data to be re-read and tags automatically applied to the columns when it's next viewed. Once viewed, this option will be automatically set back to disabled.
4. Click Apply to save changes.

If you have manually applied tags to your data, you have the option to train the system so that when it receives similar data in the future, it can automatically apply the same tags. To train Data Studio’s data tagger:

1. Go to Data Explorer.
2. Right-click on your data file and select Preview and configure.
3. Open the Headings tab.
4. Right-click on the column you want to use and select Tag column.
5. Open the Data tab and enable the Train auto tagging on next load.
   Enabling this option means that the next time you view the data, it will be re-read from the source and used to train the system. Once viewed, this option will be automatically set back to disabled.
6. Click Apply to save changes. When the process is completed, the new fingerprint files will be created and the rejection threshold might need to be adjusted.

You can also specify the Number Of Rows Used As Data Tagging Training Data – the number of rows that will be included in generating fingerprint files: go to Configuration > Loading.

View data and transform it as required. 

Once the data is loaded you can: 

• Configure and transform your data.
• Save as workflow: saves your transformation/configuration as a workflow in 'My workflows' list in the Workflow Designer.
• Download as .CSV: saves your transformed data as a .csv file in your browser’s default download location. Note that you can hide this option by going to Configuration > System > and disabling Enable download as .CSV.

Transform, manipulate, analyze and export your data with re-usable graphical workflows. 

If you have issues or questions with workflows, try the troubleshooting.

This menu lists all your data sources both raw files and database connections.

My files contains files available to you only; Data sources lists all the available database connections.

Transforming your data is easy with Aperture Data Studio. Visual workflows are built using a variety of drag-and-drop steps.

When creating/modifying workflows, the top menu allows you to:

• create a .pdf report on each workflow by clicking Report,
• re-use a workflow by saving it as a .wfl file: click Export,
• save the workflow output as a .csv, .txt, .xls, .xlsx, .xml, .dat or .json file by clicking Execute (single output only),
• auto-arrange the workflow steps by clicking Arrange,
• change the cursor to be able to select multiple steps by clicking Select. To switch back, click Move.

By default, the auto-connect for the workflow steps is enabled, allowing two steps with input/output nodes to be automatically linked when moved close to each other. You can disable this by clicking Disable snap; click Enable snap to turn the auto-connect back on.

To import one or more workflows (.wfl), click  in the top menu.

This step allows you to see data quality trends over time. 

It works best when combined with the Use snapshot range step, using snapshots taken from the Validate step (and connected to the Results for analysis node). 

In the step dialog, specify the following:

• Category - the column that determines the version of results (e.g. execution time)
• Series - the column that determines the data series (e.g. rule name)
• Metric - the column containing the names of the metrics (e.g. processed rows)
• Value - the column containing the values for the metrics (e.g. 90)
• Metric (for display) - which metric (e.g. passed rows) you wish to view across each Category and Series. Options will be populated from the Metric column. This affects both the data and chart outputs.
• All (Max Count) - the maximum number of snapshots you would like to be displayed within your specified snapshot range. You can set '0' indicating 'All' (to return all available snapshots), as well as numbers greater than 0.  
• Any (Max age) - the maximum number of days in which you'd like to data to be captured.
• Seconds (How often to sample) - how often during each day within your snapshot range you would like data to be captured. You can choose Seconds, Minutes, Hours, Days, Months, or Years. If more than one snapshot is available for a given sample size (e.g. day, hour, etc), the most recent snapshot in each sample will be returned.

There are two options for displaying the trend results:

• Evenly space chart categories - ensures the time points are displayed evenly along the x-axis (horizontal) of your results chart. This is useful if you have captured data at various times during your snapshot range and would prefer to see a more easy-to-read chart.
• Show data - view as a grid with a visual indication (red or green triangle) for each value of the selected metric
• Show chart - creates a line graph showing changes over time of the selected metric, for each series

You can also use this step to create regular reports, providing insight into the quality of your data.

The branch step allows you to duplicate data from one input so that it can be used for further workflow steps.

The rows from the source data will be duplicated (branched) into two outputs. To create more than two duplicates of the data, you can link several branch steps.

Click Show data in the step dialog to view the results.

The chart step allows you to create interactive charts of your data at any point in your workflow.

Including this step within the workflow will not affect the data connected to it. Connect this step to one input to create various types of charts. Click Bar chart (2D) in the step dialog to change the type of chart.

To add more columns to the chart (up to 10), click Add data series column. To further customize the chart, click Additional options.

Click Show chart in the step dialog to view your data as an interactive chart. Click View data to also show the data alongside the chart.

The export step allows you to export the workflow's output data to a file, DBMS or Azure service.

Each workflow can contain multiple export steps, so you can export data at any point. Each export step can be configured individually. 

Click Settings in the step dialog to specify where and how the data will be exported.

Exporting to a file

• Select File as the Workflow output type.
• Specify the Export File Type (.csv, .txt, .xls or .xlsx, .xml, .dat or .json)
• Choose the Data store type - the export location: user's or global (server's) export directory. Note that pre v1.4 workflows will continue to be exported to the server's export directory.
• Enter the name of the file in Server filename
• Enable Append date/time to filename to automatically add this information to the name of the file
• Enable Overwrite server file to allow a file with the same name to be exported more than once (the latest copy will replace the previous one). If this option is disabled, you will get an error when trying to export to the same file name more than once.

• Specify the Character set (most sets are supported)

Advanced settings

• Column name style determines how the column names will be displayed in the export file:
  • Display name - the column name as it appears in Data Studio
  • External name - the column name as it appears in the source file
  • Alias - the column's alternate name
  • Name - the column name
  • Camel case - each word or abbreviation in the middle of the phrase will begin with a capital letter (with no spaces or punctuation)
  • Title case - the first letter of every word will be capitalized
  • Humanised - the first word will be capitalized and underscores turned into spaces
  • Database style - the column name will be in upper case and underscores will be used to separate words
  • Lower case - the column name will be  in lower case
  • Upper case - the column name will be in upper case
  • Don't export headers - the column names will be excluded from the exported file
• End of line delimiter -  specify the end of a line delimiter:
  • Windows (CR+LF) - carriage return + line feed
  • UNIX (LF) - line feed only
  • Macintosh (CR) - carriage return only
  • Custom - define your own control

Exporting to a DBMS

• Select DBMS as the Workflow output type.
• Choose a DBMS from the list of available ones
• Enter the New Table name or select an existing table
• Mode determines how the data will be inserted into the target system:
  • Insert - rows are inserted into the table. The equivalent SQL statement is:
    “INSERT INTO tablename (column1, …) VALUES (value1, …)”
  • Update - rows are updated. The mapping dialog will show which source columns are mapped to which target columns and which ones have been specified as keys. You can change the mapping and keys at this stage. The keys referred to are the columns that will appear in the equivalent SQL WHERE clause:
    “UPDATE tablename SET (column1=value1, …) WHERE (columnName=value, … )”
  • Delete - rows are deleted. The keys are the column names used in the SQL WHERE clause: “DELETE FROM tablename WHERE columnName=value, … “ 
  • Insert or update (if insert fails) - if a row insert fails (there is a key collision), then the statement will be retried as an update.
  • Update or insert (if update fails) - if the update fails (no record found with the given key), then the statement will be retried as an insert.
  • Create table - selecting this option will automatically create a table in the database. You will have to specify a name for the new table which will be validated against the data source. 

Advanced settings

• Commit:
  • At the End - Any errors encountered during the export will result in the termination of execution and no changes will be applied to the target.
  • ln batches - You may specify how many statements (i.e. the batch size - max is 1,000) can execute prior to a commit. Any statements issued after the last commit will be lost if there are any errors.
  • Restartable - This is similar to commit in batches; however, all statements that will be executed have been written to a transaction log file first. If there are any errors during the execution that are not data related (e.g. a network error or a power outage) then you may restart the export and it will resume statement execution from the last commit onwards.
• Error action - specify what will happen when an error occurs during the export:
  • Abort - the export will fail when an error occurs;
  • Abort at End - the export will complete and report on any errors in the log file;
  • Abort After Percent - you can specify a percentage threshold for allowed errors: if (error count/rowCount)*100 > percentage then export will fail;
  • Abort After Count - you can specify an absolute error threshold: if the number of errors exceeds this count then export will fail;
  • Ignore - errors will be logged but ignored: the export will complete regardless. 
• Precheck - enable for data to be checked before starting the export. Use this together with the Error action (above). The following checks will be carried out for every cell: 
  • if primary key(s) have been defined for the target - check that the data in the primary key column(s) is unique and not null;
  • if a target column is not nullable - check that the data is not null;
  • check that the source data type is compatible with the target column’s data type;
  • for numeric data - check that the target column’s scale and precision are large enough;
  • for alphanumeric data - check that the target column's width is large enough.
• Truncate - if Mode is Insert, you may truncate the remote table before applying any inserts.
• Pre SQL - you can enter a SQL script that will be executed before to the export. The SQL may contain any valid statements and will be executed as is. Note that you have to include a COMMIT statement at the end of your statement.
• Post SQL - you can enter a SQL script that will be executed after the export. The SQL may contain any valid statements and will be executed as is. Note that you have to include a COMMIT statement at the end of your statement.

Exporting to Azure

• Select Azure as the Workflow output type.
• Choose a DBMS from the list of available ones
• Specify the Export File Type (.csv, .txt, .xls or .xlsx, .xml, .dat or .json)
• Enter the name of the file in Server filename
• Enable Append date/time to filename to automatically add this information to the name of the file
• Enable Overwrite server file to allow a file with the same name to be exported more than once (the latest copy will replace the previous one). If this option is disabled, you will get an error when trying to export to the same file name more than once.
  

  To execute workflows with multiple outputs, the export settings defined in each export step have to be used.

• Specify the Character set (most sets are supported)

Advanced settings

• Column name style determines how the column names will be displayed in the export file:
  • Display name - the column name as it appears in Data Studio
  • External name - the column name as it appears in the source file
  • Alias - the column's alternate name
  • Name - the column name
  • Camel case - each word or abbreviation in the middle of the phrase will begin with a capital letter (with no spaces or punctuation)
  • Title case - the first letter of every word will be capitalized
  • Humanised - the first word will be capitalized and underscores turned into spaces
  • Database style - the column name will be in upper case and underscores will be used to separate words
  • Lower case - the column name will be  in lower case
  • Upper case - the column name will be in upper case
  • Don't export headers - the column names will be excluded from the exported file
• End of line delimiter -  specify the end of a line delimiter:
  • Windows (CR+LF) - carriage return + line feed
  • UNIX (LF) - line feed only
  • Macintosh (CR) - carriage return only
  • Custom - define your own control

The filter step allows you to filter data using a variety of functions.

See Function editor for more details.

Find out how to use, configure and troubleshoot this step.

This step allows you to kick off custom events at any point in your workflow.

Tick the Only fire if input has rows checkbox for the step to be kicked off only when the input has data.

Select the required Custom event then specify values for the available event data items (the event data name will be displayed).

For example, you want to kick off an event when the Validate step has failures. Select the ‘Validation failed’ custom event (see the example in Custom events section above) and specify the reason for the failure in the ‘Reason’ event data item. This reason will now be available as an event data item when creating/editing notifications.

Another example is using a Filter step to identify records that you have particular interest in. The Fire event step can then follow the Filter step but only fire when the Filter step is producing records.

Grouping allows you to group values from one input into one or more columns and aggregate the remaining columns, if required.

Click Grouping in the step dialog then drag and drop the required columns up, into the Grouped list. Remove columns from this list by simply dragging them down.

Click  to apply the changes and Show data in the step dialog to view the results.

You also configure grouped columns and use aggregate functions on the grouped values when in Show data view: right-click on the grouped column heading and select Add aggregate. In the dialog that appears, specify the name/description for the new column, the source column to be used for aggregation and the required function.

Find out how to use this step.

The Join step allows you to join two inputs into one based on one or more columns from each input.

To specify the columns you want to join, click Left Columns or Right Columns. 

• Exact match: rows will be joined if key values on the left-hand side and right-hand side are exactly the same, including data type and casing.
• Ignore datatype: rows will be joined if keys can be standardized to the same value. A key with the alphanumeric value '1' will be joined with a key that is the integer value 1.
• Case-insensitive: rows will be joined if keys match with casing ignored. A key with value 'ABC' will be joined to a key with value 'abc'.

Click on the Venn diagram to specify the type of join you want to apply:

• Inner (yellow segment of the Venn) - returns all rows where the join condition is met (keys match)
• Left outer (yellow and green segments) - returns all rows from the left-hand table and only the rows from the right-hand table where the join condition is met (keys match)
• Left outer without intersection (green segment) - a variant on the basic left outer join: returns only rows from the left-hand table that don't meet the join condition. No rows from the right-hand table are returned.
• Right outer (yellow and blue segments) - returns all rows from the right-hand table and only the rows from the left-hand table where the join condition is met (keys match)
• Right outer without intersection (blue segment only) -  a variant on the basic right outer join: returns only rows from the right-hand table that don't meet the join condition. No rows from the left-hand table are returned.
• Full outer (all segments of the Venn)- returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contains null values. 
• Full outer without intersection (green and blue segments) - returns all rows in both the left and right tables, excluding rows where the join condition is met. 
• Cross join or Cartesian product (no segments of the Venn) - returns all rows from the left table. Each row from the left table is combined with all rows from the right table. 
  
  

Use the All matches menus to specify different combinations of join. The top left menu is for the top join table and the bottom right one for the second join table.

• All matches - perform a standard join
• Singletons only - will return the first match of a 1 to many match AND any matches that occur only once (this is a normal relational join with duplicates removed)
• Duplicates only - will return all the matches of things that are 1 to many only (this is a normal relational join excluding things that match only once)

In the join dialog, click Show data to view the results of the join. Specify which values from the joined columns to use in any following steps: all the matches, only duplicates or only singletons.

Click  to see the suggested joins. Only exact matches from the two sources will be suggested. Click on the suggested join to apply it. 

The multi view step is a very useful step for viewing your data side by side (horizontally or vertically).

You can link this step to 2-3 data sources to view and compare the data. By default, the horizontal view will be selected. To change to the vertical view, click Horizontal split.

Click Show view to display the results. For more complex views, you can also link this step to another multi-view step.

This step allows you to profile data at any point in a workflow. We examine each value and identify various attributes (such as type, min/max values, count) and determine its uniqueness and completeness.

Click on Profiled columns to select the column(s) you want to profile. In the Profiled columns list on the left, select the required ones and click . To see the results, click Show data in the step dialog. 

You can now export or take a snapshot of your profiled data to track data uniqueness over time. 

This step allows you to create a sample of the data the step is connected to. You can specify the following sampling settings:

• Start at row -  the row number from which to start loading
• Sample one row in every - specify the frequency of rows to sample
• Sample randomly - enable to create a random sample
• Limit rows - enable to specify a Row limit that will be used for the sample

To use this step, you have to have at least one JavaScript available in the script library: Glossary > Scripts.

This will allow you to use the script as a workflow step to transform your data.

Click Show data in the step dialog to view the results. The output can then be used as input to any further step(s).

To use this step, you have to have at least one R script available in the script library: Glossary > Scripts.

This will allow you to use the script as a workflow step to transform your data.

Click Show data in the step dialog to view the results. 

This step allows you to sort data in ascending/descending order.

Click Sorting in the step, select the required columns and click   to save changes.

To change the sort order, click on the column in the Sorted list.

A snapshot is a versioned copy of data taken at a certain point in time (and saved internally in Data Studio as a table with any transformations). Each snapshot is automatically assigned a unique name which you can modify.

Once created, a snapshot will appear in the list of Available data sources and can be used in any workflow. 

Take snapshot step
This step will create a snapshot (once the workflow is executed). Each time you execute a workflow with this step, a new version of the snapshot will be created.

If you want to limit the number of snapshots that are created per each step, go to Additional options and turn on Enable version limit. You will then be able to specify how many versions of that particular snapshot that you want to store.

Use latest snapshot step
This step allows you to use the latest available snapshot. First, choose the workflow it belongs to then select the required snapshot.

Use snapshot range step
This step allows you to use several snapshots at a time (it's the union of all the snapshots in the range). If your data changes over time, you can compare the current version to the previously created snapshots. To choose the required snapshot range, find the workflow it belongs to then select the snapshot range.

Click Show data to see the details of all your snapshots in one table.

Find out how to configure an ODBC connection.

Splicing allows you to combine two inputs into one by presenting the values next to each other.

Data in the columns is not combined or merged - the duplicated values are renamed with an appended numeric value. The output – combined values from two sources – can then be used as input to any further step(s).

Click Show data in the step dialog to view the results.

The split step allows you to apply a true/false filter to one input and split the resulting data into two outputs based on these values.

To create a filter, click Filter in the step dialog and Create in the left-hand menu. Specify the necessary filters and click Apply to save the changes.

Click Show passing/Failing rows to view the values that passed/failed the filter. The passing and failing results can be individually linked to any further step(s). 

The union step allows you to combine two inputs into one output by presenting the values on top of each other. The source columns with matching headings are combined.

The output – combined values from two sources – can then be used as input to any further step(s).

Click Show data in the step dialog to view the results.

The validation step allows you to identify data that matches the specified rule(s). The results will either be true or false.

To perform validation, you have to set validation rule(s):

• select from the list of suggested rules (picked up from the input columns) or
• click Configure rules then add a new rule in the step dialog.

You will be taken to the Function editor, allowing you to define the required rule(s). Once created, apply the rule(s) using the Edit validation configuration dialog.

Click View or sort rows to preview the data that will be validated.

To view validation results, click to see the required data: passing, failing and ignored rows. Each of these results can be further analyzed by linking them to other workflow steps.

To view information on each row, click Show data. You can right-click on a failing row and select Reasons for failed rules to see even further detail.

Click Results by rule to view the overall summary of validation results. 

Find out how to use, configure and troubleshoot this step.

Use this step to validate emails based on the format or domain address.

The two validation types are available:

• Format check - whether the email is in a valid format (a syntax check against the RegEx values as defined in the Glossary)
• Domain level - whether the email domain is valid/exists or not (a DNS server check to see if the domain exists and is an email server)

You can customize the way emails are validated:

• specify your company's DNS servers: go to Configuration > Validate emails > DNS servers.

  We use a Domain Name System (DNS) lookup to check whether the email’s domain exists. If not specified, the DNS server will default to Google (8.8.8.8) which may be blocked by your firewall. If blocked, use nslookup to find a DNS server that can be used on your network and add it by clicking Add a new DNS server.

• define email address format: go to Glossary > Constants > Email Expressions > Email Address

By default, the validation results are cached with results refreshed every 30 days. To change this, go to Configuration > Step settings > Email step > Email validation cache validity.

Use this step to quickly validate global phone numbers.  

Once connected to a data source, you have to specify the following:

• Select a phone column - specify the column containing the phone numbers you want to validate
• Select country - pick the country to use for phone validation. You can either select a single country or pick a column with country data. For the latter, please ensure that the country names adhere to ISO 3166-1 alpha-2, ISO 3166-1 alpha-3 or ISO3166 country name standards.
• Select validation rule - choose the validation rule(s) that will be applied:
  • Valid phone number: shows True for phone numbers that have been successfully validated against the selected country and False otherwise.
  • Valid phone number for the region: shows True for phone numbers that have been successfully validated against the region of the selected country and False otherwise.
  • Possible phone number: shows True for possible phone numbers that have been successfully validated against the selected country and False otherwise.
  • Invalid phone number: shows True for phone numbers that have been unsuccessfully validated against the selected country and False otherwise.
  • Invalid phone number for the region: shows True for phone numbers that have been unsuccessfully validated against the region of the selected country and False otherwise.
  • Not possible phone number: shows True for not possible phone numbers that have been successfully validated against the selected country and False otherwise.

Click Show data to see the validation results. The following columns will be appended to your data:

• Validation results – shows the result of the validation rule (one column per each applied rule)
• Phone Country - shows the country for the validated phone number.
• Phone Number Type – shows the phone type (e.g. mobile or fixed line).

This tab lists all the outputs in your workflow, providing a list view which is useful for larger/complex workflows. To quickly find a specific output, right-click on it and select Locate.

Workflows can be shared with other users connected to the same instance of Data Studio. If you share a workflow, other users will be able to access and modify it.

To share a workflow, go to Workflow Designer, select the required one and click Share.

All the shared workflows available to you will appear in the Shared workflows list.

To revert access to a workflow, select it and click Unshare. Note that administrators are able to unshare any workflow.

A workflow can be executed on an ad-hoc basis or configured to be executed in the future (and at defined intervals, if required).

Go to Workflow Designer and click Execute on the workflow you want to execute now or later.

In the Schedule workflow execution dialog specify when and how often you want to execute the workflow and whether to export the output table.  

When scheduling the workflow to be executed later, use the End time type to specify when the End action will be applied. This is the action that will be performed after the workflow is executed. The options are:

• None – no action performed after execution
• Kill – if the end time has been specified and the execution hasn’t yet finished, workflow execution will be stopped
• Reschedule – when the process completes or the end time has been reached, the execution will be rescheduled to run again according to the specified time intervals

You can also choose to export the workflow as a file or a script.

Refreshing data sources for workflow execution

When the workflow is re-executed, the data that was most recently read form the data source will be used.

To force all data sources used in a workflow to be automatically refreshed before each execution, the following conditions have to be met:

• The refresh setting is enabled: select the required workflow and click Edit details. Enable Refresh on execution and click Apply.
• If the data source is a file, it has to be modified more recently than the last time the data was read.
• No other workflow that would cause the data source to be refreshed is being executed. This prevents two workflows attempting to refresh the same data source concurrently.

By default, the refresh setting is disabled for all workflows. To enable it for all new workflows, go to Configuration > All server settings and enable Default workflow refresh on execution.

Define your business terminology, create data tags/scripts and other objects used by workflow steps. You can also view and edit the blocking keys and rulesets for the Find duplicates step.

Constants
The constants area shows the existing constant libraries. You can view the standard business constants installed with Data Studio or create your own. The business constants supplied are regular expressions that can be used in transformations to verify the format of commonly found data fields. You can also create and define your own business constant libraries to use in transformations or with custom steps.

Custom events

Data tags
You can create new and manage existing data tags using Glossary.

Experian Match blocking keys
This contains the default blocking keys used by the Find duplicates step. You can't change the default set but administrators can create their own keys to customize the behavior of the step.

Experian Match rulesets
This contains the default rulesets used by the Find duplicates step. You can't change the default ones but administrators can create their own rulesets to customize the behavior of the step. 

Notifications

Scripts
The scripts section allows you to define scripts that can be used by the Script steps in a workflow. Scripts have to be added to a script library.

To add a script library, click Create a new script library, enter a name of the library, a description (optional) and click Okay. Note that script libraries can be deleted only when they are empty.

All scripts have to be added to an existing library. To add a new script, click on the required library then click Create a new script. Scripts require a unique name, the area of use, indication of whether they can output logging to a file, the language type and the script code. The script definition can optionally also have parameters to change the script’s behavior.

You can receive email notifications when a particular system or custom event occurs in Data Studio (e.g. a workflow has completed).

To create a notification, go to Glossary > Notifications and click Create a new notification.

• Name - enter a descriptive name for the notification.
• Description - add an optional description.
• Event - select an event from a list of system and your custom events that has to occur for this notification to be triggered.
• Trigger conditions - you can provide specific event data values that will have to be matched for this notification to be triggered. 
• Notification type - the action that will occur when this notification is triggered. Currently, only email notifications are available.
• Recipient - the email address of the recipient(s). To use multiple addresses, separate them with a space, comma or semicolon.
• Email subject/Email content - the subject/body of the email. You can insert data item values using the @ symbol followed by the data item name. For example, use @WORKFLOW_NAME to insert the name of the workflow. Note that the list of available data items will depend on the selected event. 
• Owner/team - specify who will be able to edit the event (in addition to the administrator).

Administrators can configure the SMTP server settings by going to Configuration > All server settings > SMTP Properties.

Once set up, click Test SMTP Configuration (in All server settings) to test the connection.

Events are occurrences/triggers in Data Studio, such as a workflow completing. Each event contains event information - event data items (e.g. the workflow name).

You can set up email notifications to be sent to you/other recipients when certain events have been published.

There are two types of events:

• System
• Custom

System events

These are events that occur in Data Studio. The following system events (and the related data items) are available:

Custom events

These are events that you can create depending on your business requirements. In addition to the event data items you've created, the following items will be available for all custom events:

To create a custom event, go to Glossary > Custom events and click Create a new custom event.

• Name - enter a descriptive name for the event.
• Description - add an optional description.
• Event data - you can create data items relevant to this event whose values will be supplied when the event is triggered. The Fire event step is configured to populate these data items when the event is fired. Each data item is identified by its name (the description is for your reference only).
• Owner/team - specify who will be able to edit the event (in addition to the administrator).

For example, if you want to create a custom event that is fired when the Validate step has failures, you could create a custom event with the name ‘Validation failed’ and an event data item named ‘Reason’. Once used in the Fire event step, different values for the event data item (Reason) can be provided.

To publish a custom event, use the Fire event step.

Manage users, configure workflow steps and specify product settings.  

System settings

These allow you to customize Data Studio. You can specify:

• All server settings
• Communication
• Data handling
• Loading
• Security
• Storage
• System

Find out about how to change the database location or add a SSL certificate.

Step settings

Configure certain workflow steps and their behavior.

• Enable steps
• Find duplicates
• Validate addresses
• Validate emails

All users in Data Studio have to have a role assigned to them.

The following roles are available:

• Consumer
• Designer and developer
• Administrator

To create a new user:

1. Go to Configuration and click Users.
2. Click Create a new user.
3. You have to enter the following:
   - Username (can contain alphanumeric characters and underscores only)
   - Display name (no restrictions)
   - Role (choose from one of the three roles)
   - Enabled (specify whether this user is active, i.e. able to use Data Studio)

You can also group users for easier management by assigning them to teams. Users with the Administrator role will be automatically assigned to the Administrators team, all other users to the Initial group. You can modify this as required. 

Authentication mechanism
This is the method of authentication that will be used. The following options are available:

• Aperture Data Studio internal – use this for users requiring access to Data Studio only, without any additional authentication.
• LDAP and MS Active Directory (AD) server – use this to allow users to log in with their LAN IDs (fill in the LDAP username field). If this option is not available, go to Configuration > All server settings and enable LDAP properties. Find out how to configure LDAP.

To manage user details, go to Configuration > Users. Right-click on the required user to edit their details/change password or disable them.

View logs, manage scheduled and running jobs.

Audits 
A list of all audit events such as user logins, viewed data and workflow operations (create, delete, share). 

Jobs
A list of running, queued and completed jobs (e.g. workflow executions).

Log messages
A list of detailed system logs. To download logs as a .txt file, click on your username in the top menu and select Download log files. Note that this option is available to administrators only.

Processes 
A list of temporary processes that may run to support various one-off system operations.

Repository objects
A list of all repository objects (e.g. users, business constants, data tags, regular expressions, data sources).

Restart log 
A list of various checks that the system will run periodically (e.g checking disk space).

Services
A list of permanent processes that support various system operations (e.g job scheduling, housekeeping, loading, alert notification).

Sessions
A list of user and server sessions.

Snapshots
A list of all snapshots. This includes information such as when snapshots were created and how much disk space is currently used. Also, administrators have the option to delete individual snapshots.

Storage
A list of the key sub-directories that form the database.

System
Various statistics relating to the running of the system (e.g. number of CPUs, free memory, network information).

Tasks
A list of running and completed data operation tasks (e.g. load, profile, find duplicates).

Data Studio allows you to audit (record and report) user and system activities.

Enabling and tuning
Auditing can produce lengthy logs, not all of which may be essential to your requirements, so to help tailor and target your auditing, you can enable auditing based on specific events.

You can view and change audit options in Configuration > Security > Audit types.

Once an audit is enabled, every time the event occurs, an entry is created in the audit log, detailing the cause, user information and when it happened.

Audit events

• Create a workflow: tracks when a workflow is created.
• Data exported: tracks when data is exported, either via a workflow step or any other method.
• Data viewed: tracks all attempts to view data in a drilldown.
• Delete workflow: tracks when a workflow is deleted.
• Edit workflow design: tracks when a workflow’s design is changed.
• Edit workflow details: tracks when a workflow’s name or description is changed.
• Execute Workflow: tracks when a workflow is executed.
• Repository change: tracks when a repository object is created, edited or deleted. This includes tracking changes to users, datastores, entities, glossary objects and more.
• Script execution: tracks when a script is executed.
• User access: tracks user login, logout and login failure events.
• Workflow shared: tracks when a workflow is shared or unshared.

Reporting and monitoring
The events generated by active audits are visible in Monitoring > Audits.

The resulting view shows all the events generated by Data Studio with the most recent events at the top. The view can be sorted, filtered, or exported to a .csv file. This view will also update itself periodically and display any events that have happened in the meantime.

When you right-click on a column heading and select Add transformation, you will be taken to the transformation function editor. 

The left-hand menu contains two tabs: one for transformation functions and the other for connections. 

You can either scroll down to find the required function or use the search box to find it then drag and drop in the area on the right.

Example - remove special characters from phone numbers:

1. Go to Data Explorer > Sample data source.
2. Drag and drop the Customer V1 file into the workspace.
3. Drag and drop the Transform step and connect it to Customer V1.
4. In the Transform step, click View Data.
5. Right-click on the Telephone column heading and select Add transformation.
6. In the search field on the left type in Remove noise and click on the function when it’s shown. It will appear in the right-hand side (workspace).
7. Tick the following boxes: Remove vowels and Remove whitespace.
8. Click Apply in the top-right corner. You will be taken back to the file view. The  icon will appear next to the Telephone header indicating that values in this column have been transformed.

Expression preview

When creating/editing a function, the expression preview in the bottom-left corner allows you to see how the data will be transformed if you save your changes. If you're working on a workflow that contains a step that hasn't been executed, you have to click Execute to see the preview of the data. 

You can also see how a transformation will behave by using sample/dummy data: click Single at the bottom and enter the values you want to test.

To revert the transformation, right-click on the column header and select Remove transformation.

You can either continue building on this transformation using the Workflow Designer by clicking Save as workflow or save the data as a .csv file by clicking Download as.CSV.

Troubleshoot issues and find answers.