A Dataset stores the schema (column layout) of the data that will be loaded, including the parsing options, settings, data tags and data batch details.
You don't have to store data within Data Studio. You can create an empty Dataset with column headers and load data in batches. This lets you re-use the same Dataset by replacing data batches whenever needed.
Data within Datasets is stored in one or more batches. Each batch has an ID, date, timestamp and row count.
When creating or editing a Dataset, choose whether it will contain one or more batches. Choose one of the following:
A multi-batch Dataset allows you track data vintage and trend changes over time. It also allows older data to be automatically deleted.
You can view the batch details within a Dataset from the Datasets list screen using Actions > Show batches.
The Source step in a Workflow provides an option to select All batches (default), last N batches, Batches created in the latest time period or to take a sample batch over a time period.
To delete manually, go to the batch details within a Dataset in the Datasets list using Actions > Show batches. Select one or more batches then Delete and confirm deletion.
Datasets also have an Allow automatic batch deletion setting. Checking this provides the Delete batches on completion option in the Source step using this Dataset. When the Workflow is executed, it can be set to automatically delete any batches of data that are processed successfully, which will ensure that no batch of data is processed through a Workflow twice.
Each multi-batch Dataset has an optional limit on the number of batches it can have. When new data is loaded into that Dataset, if the specified number of batches would exceed the limit, the oldest batch will be automatically deleted. If this setting is left blank, no automatic deletion of batches will occur.
For compliance or housekeeping reasons, we recommend to automatically delete old data. The Automatically delete older batches after N days setting is checked daily for every Dataset that has this setting enabled and delete any batches that are older than today minus the specified number of days.
To create, go to Datasets and click Add Dataset. This will launch a wizard to guide you through the process.
First, choose to either Upload file from a server import directory or connect to an External system such as a cloud file store or a database.
Data Studio supports most common file formats:.csv, .txt, .xls, .xlsx, .json, .psv, .sas7bdat, .dat, .metro2, as well as encrypted files or password protected files with these extensions: .aes, .enc, .gpg, .pgp, .zip, .xls, .xlsx.
The metadata for fixed width files (.dat or .txt) is loaded in a .csv file. The following columns are accepted in this csv file. The order of columns do not matter, as long as the headers are labelled correctly.
Col name | Required? | Description |
---|---|---|
COLUMN | Required | The name of the column. Headers in the data file are not used. |
START_POSITION | Required | Start position of the column. 1 indexed, i.e. the first character in the file is position 1. |
END_POSITION | Required | End position of the column. This is the last character included in the column. An END_POSITION of 12 will include the character at position 12. |
LENGTH | Required | Length of the column. This is needed for validation. |
TYPE | Required | The data type of the column. ALPHANUMERIC, NUMERIC or DATE. |
SUMMARY | Optional | Column description. |
TAGS | Optional | List of Data tags to be assigned to the column. Multiple values must be surrounded with double quote and separated with comma. E.g. "Name, Surname". |
Data Studio will analyze the file and automatically determine the best parser options to extract the data from the file, but these can be manually amended.
File parser is auto-selected based on the file extension, but a different parser can be selected from the list.
Character set is auto-selected by comparing first 64k characters against a universal library.
Language and region is auto-selected to match the host machine OS locale, unless overridden in the Data Studio installation config files.
The setting controls how date and numeric values are parsed on load (if automatically detecting data types). For example:
Delimiter and Quote character are auto-selected from the first 20 rows of data, but can be selected from the list or a custom value entered.
Import data from row can be used with .csv, .psv, and .txt delimited files to specify a number of rows to skip.
Use first row for column names - is selected by default, unselecting will name columns 'Column 1, Column 2, etc.'
Parse newline characters within quoted sections as new rows - always terminate row at an end of line character. By default newline characters within Quote characters, i.e. paragraphs of text, will be incorporated into the cell data.
Column datatype options:
Valid Input | Comments |
---|---|
02-Jan-1970 | Date elements separated by hyphen, shortened month name. |
04-January-1970 | Date elements separated by hyphen, using full month name. |
15.Dec.1970 | Date elements separated by period. |
15/Dec/1970 | Date elements separated by forward slash. |
01-01-20 | For 2-digit years the watershed will determine the century. Using default watershed, this will be parsed to 2020. |
01-01-80 | Will be parsed to 1980 using the default watershed. |
1970-12-15 | Year precedence. |
12/15/2020 | Month precedence. The locale (language and region) is used to set the month precedence setting in the parsing rules which determines the possible expected formats. So 10/28/2020 will not parse with a UK locale (but will for US) because 28 is an invalid month value, and there would be no US locale parsing as a fallback. |
Valid Input | Comments |
---|---|
01-01-1970T12:34:56 | Basic date/time without millis, using T time designator |
01-01-1970 12:34:56 | Basic date/time without millis |
01-01-1970T12:34 | Basic date/time without seconds, using T time designator |
01-01-1970 12:34 | Basic date/time without seconds |
01-01-1970 1234 | Hour and minute without separator. Becomes 01/01/1970 12:34 |
01-01-1970 234 | Hour and minute without separator. Becomes 01/01/1970 02:34 |
01-01-1970T12:34:56.987 | With milliseconds |
01-01-1970T12:34:56.9 | Becomes 01/01/1970 12:34:56.900 |
01-01-1970T123456.987 | Without time separators. Becomes 01/01/1970 12:34:56.987 |
01-01-1970T10:00:00Z | With timezone Zulu |
01-01-1970 10:00:00+01:00 | Becomes 01/01/1970 09:00:00 |
01-01-1970 10:00:00+01:00[Europe/Paris] | Becomes 01/01/1970 09:00:00 |
01-01-1970T10:00:00-01:00 | Becomes 01/01/1970 11:00:00 |
02-Jan-1990 12:01:02Z[UTC] | Becomes 02/01/1990 12:01:02 |
Valid Input | Comments |
---|---|
1:02:03.004 | Time with milliseconds |
1:02:03 | Time with hours, minutes and seconds |
10:02 | Time with hours and minutes |
Some database systems and file formats can store time zone-aware date and time values.
In the following CSV example, row 1 is a timestamp with a timezone +3 hrs. Row 2 is in UTC. Data Studio is not timezone aware, and when timestamps are loaded into Data Studio, values are converted into UTC.
row | timestamp in CSV file | timestamp loaded into Data Studio |
---|---|---|
1 | 01/01/2013 01:00:00+0300 | 31-Dec-2012 22:00:00 |
2 | 01/01/2013 01:00:00 | 01-Jan-2013 01:00:00 |
A watershed (also known as a century cutoff) is used to calculate the century that any two digit year is in. The default is a floating watershed of 50 years. This can be configured in Settings > Data handling with the option to use a fixed or floating watershed with configurable values for both.
Once loaded, a preview is shown containing the first few rows of data, allowing you to check the file/table and settings are correct before proceeding. For multiple tables or files like Excel containing more than one sheet, the first few rows of each table are shown in the preview.
Each column in a Dataset can have a summary description and Data tags assigned. The Optional setting, if selected, will allow data to be loaded in future without this column being included.
An existing Dataset’s column annotations can be changed by selecting Actions > Annotate columns.
Data tags are labels shown next to column headers. As well as making it easier to find the correct column, they help improve the user experience by providing relevant defaults and suggestions in Workflow steps.
We recommend to Auto tag your data, which will analyze the data and automatically apply data tags.
The Datatype of a column can have a value of Alphanumeric, Date, Numeric or Unknown. This can change how file values are automatically parsed and help users to quickly identify and find the correct column in future.
The first time data is loaded into a Dataset, the datatypes are suggested by looking at the first 20 lines of data, but these can be updated during creation or in future from the Actions > Annotate columns page.
If required, there are a number of conversion Functions that can be applied as part of a Workflow to ensure all values in a column are of the same type.
Datasets have a Name, Summary, Description and an External Label that can be updated from the Actions > Edit details page.
Other settings on this page:
To reduce disk space usage (by up to 80%) Data Studio will automatically compress your data as it's being loaded in, but compression may impact Workflow performance. The default compression applied can be updated in Settings > Performance. Each Dataset can have a different compression-level from the default. Any change of compression will be applied to data loaded from that point onwards.
The Dataset batch settings will determine if data will overwrite or be added to any existing data.
To load a new version of a file into a file Dataset:
To load new data into an external system Dataset:
A Dataset cannot be shared with another Space, so the data will only be visible to users with access to that Space.
However, a Dataset can be the source of one or more Views or Charts which can then be shared with other Spaces.