Steps: Create a Dataset with External Data (SFTP Server)

Security: Prism Datasets: Create
domain in the Prism Analytics functional area.

Workday recommends that you use tables to ingest data into Prism Analytics instead of base datasets, to take advantage of the many performance and feature benefits.

You can create a base dataset using external data by transferring data from an SFTP server. You might want to create a base dataset that gets its data from an external server when the server regularly collects or adds new data. You configure how often the dataset gets new data from the server.

If you're new to Workday, you don't have access to create or edit base datasets.

For integration runs that transfer data from the SFTP server to succeed:

The number of files must be less than 5,000.

The time to transfer the data must be less than 5 hours.

Each file from the server should be less than 1 GB compressed (less than 10 GB uncompressed approximately).

Access the

Data Catalog

report.

Select

Create

from SFTP

On the

Create Dataset Retrieval - Configure File Retrieval

task, you configure how to import the data from the SFTP server.

In the

Files

section, enter the filename or a filename pattern that represents 1 or more files.

The filename is case-sensitive. You can use the asterisk (*) and question mark (?) characters as wild cards to specify a filename pattern. Use the asterisk (*) to specify zero or more characters, and use the question mark (?) to specify exactly 1 character.

In the

Transport

section, specify how to connect to the SFTP server:

Option	Description
SFTP Address	Use this format: sftp://`domain_name` or sftp://`IP_address` To specify a port number, add it to the end of the domain name or IP address. If you don't specify a port number, Workday uses port 22.
Directory	(Optional) The directory on the server that contains the files. Directory names are case-sensitive. Include a leading slash (/) only for a full path, not a relative path.
Use Temp File	Writes the imported data to a temporary file in Workday with a randomly generated name. After the data import is complete, Workday automatically renames the file to the correct name. You might want to enable this option if the data import takes a very long time and might not finish before the next scheduled time to import data from the same server.
Authentication Method and Details	Select the type of security authentication that the SFTP server uses: User Name / Password . SSH Authentication . This option uses secure shell key authentication using X.509 certificates.

(Optional) In the

File Utilities

section, Consider these options:

Option	Description
Delete After Retrieval	Deletes the files on the SFTP server after the data is imported into the dataset. If Workday is unable to delete the files from the SFTP server, the data retrieval fails.
Decompress	Don't enable this option for datasets. You can transfer files that are compressed or not. For compressed files, Workday only supports gzip compression.
Decrypt Using	If you want to decrypt the imported files using Pretty Good Privacy (PGP), select a PGP Private Key Pair.

(Optional) In the

Environment Restrictions

section, at the

Restricted To

prompt, select the environment in which you want to use the settings defined in the

Transport

section.

If you leave this option empty, Workday applies the transport settings to each environment in which the dataset integration runs. When a dataset integration runs in a particular environment, such as Implementation or Production, the transport settings only work if the

Restricted To

option matches the current environment. When the current environment and the configured environment don’t match, the dataset integration fails and retrieves no files from the SFTP server. You might want to restrict the transport settings to a particular environment to avoid inadvertently transferring test data to a non-test endpoint.

Example: You create the dataset in an Implementation environment and select Implementation in

Restricted To

. Later, you migrate this dataset to a Production environment and the next time the dataset integration runs, the integration fails. To ensure that the dataset integration runs successfully in the Production environment, edit the dataset integration details and either clear the

Restricted To

option or change it to Production.

On the

Create Dataset Retrieval - Schedule Request Type

task, in

Run Frequency

, specify how often to import data from the SFTP server.

If you're importing the data once in the future or on a schedule, specify the criteria for either on the

Create Dataset Retrieval - Schedule Integration

task.

After the dataset is created, you can run the integration to bring in data to the dataset on an ad hoc basis. From the related actions menu of the

View Dataset Details

report, select

Dataset

Run Integration Now

You can't bring data into the same dataset at times that overlap with each other.

Specify a unique name for the dataset.

The dataset name is what displays in the Data Catalog. You can change the name when you create, edit, or copy the dataset.

(Optional) Change the dataset API name. Workday automatically selects an API name based on the dataset name you enter, modifying it to make it meet the name requirements. You can’t change this name after you finish creating the dataset.

(Optional) Create or edit 1 or more tags to organize the dataset in the Data Catalog.

(Optional) Add a description to help others understand the data in this dataset. You can change the description when you edit the dataset.

Select how you want to update the data in the dataset when it receives new data from an integration run.

Option	Description
Replace	Workday deletes the existing data in the dataset and replaces it with the data it imports from the SFTP server.
Append	Workday keeps the existing data in the dataset and adds to it the new data it imports from the SFTP server. Workday imports all data in all files during every integration run. Append mode is different than incrementally updating data in a dataset. Whether the data in the dataset gets updated incrementally depends on if the SFTP server contains only incremental updates since the last integration run. The file schema must meet these requirements: All files in every integration run must use the same parsing options (including the header row configuration) that were used during the first integration run. The fields must be in the same order in all files in every integration run. If the schema in a subsequent integration run contains new fields, the new fields must be located at the end of all previous fields. If the file schema in a subsequent integration run deletes one or more fields, the deleted fields must be at the end. Ensure that if the schema deletes fields, no future schema adds new fields, otherwise the integration run will fail. To ensure that all future integrations run successfully, always keep existing fields in the schema and only add new fields. If necessary, you can include empty (NULL) values in existing fields. All files in a single integration must use the same schema.

Option

Description

Replace

Workday deletes the existing data in the dataset and replaces it with the data it imports from the SFTP server.

Append

Workday keeps the existing data in the dataset and adds to it the new data it imports from the SFTP server.

Workday imports all data in all files during every integration run. Append mode is different than incrementally updating data in a dataset. Whether the data in the dataset gets updated incrementally depends on if the SFTP server contains only incremental updates since the last integration run.

The file schema must meet these requirements:

All files in every integration run must use the same parsing options (including the header row configuration) that were used during the first integration run.

The fields must be in the same order in all files in every integration run.

If the schema in a subsequent integration run contains new fields, the new fields must be located at the end of all previous fields.

If the file schema in a subsequent integration run deletes one or more fields, the deleted fields must be at the end.

Ensure that if the schema deletes fields, no future schema adds new fields, otherwise the integration run will fail. To ensure that all future integrations run successfully, always keep existing fields in the schema and only add new fields. If necessary, you can include empty (NULL) values in existing fields.

All files in a single integration must use the same schema.

An integration fails when the schema of the new data doesn't contain a field that currently exists in the dataset, and the removed field is used in a stage in the dataset. Example: If the dataset includes a Manage Fields stage and the integration brings in data that is missing a field in the dataset, the integration fails. That's because the Manage Fields stage works on every field in the dataset.

Click

Save

Workday creates the dataset, but it has no data until Workday imports the data and fields from the SFTP server during the first integration run. Depending on when you scheduled the data to import, the dataset might be empty for some time. Workday also adds 2 fields that provide information about each integration run. See Reference: WPA_ Fields.

(Optional) Change the name of your integration schedule. See Manage Dataset Integration Schedules.

Access the

Data Catalog

report, right-click the dataset you just created, and select

Edit

Configure how to parse the data in the files from the SFTP server.

See Parse External Data in a Dataset.

(Optional) Add a Stage to a Dataset.

You can add only some stage types to base datasets.

(Optional) Add a Prism Calculated Field to a Dataset.

You can add a Prism calculated field to any stage.

Concept: Dataset Workspace