Concept: Tables
A table is a Workday Prism Analytics object that stores (materializes) data and represents it in a tabular format. A table has a user-defined schema and only contains data that's valid against the schema. The data in tables is backed by a distributed columnar data store.
You create tables to bring in data from multiple sources and store it in a central location, the Data Catalog (similar to a data warehouse). You can then join, transform, blend, and enrich table data using derived datasets based on the table. Use derived datasets to prepare data for analysis.
If you're new to Workday, you don't have access to create or edit base datasets.
Tables Compared to Base Datasets
A table is similar to a base dataset with important differences.
Concept | Tables | Base Datasets |
|---|---|---|
Schema | You define the table schema before you load data into the table. If you’re familiar with databases, this is commonly referred to as schema-on-write. You can still change the table schema, but there are requirements and limitations. Example: You can add/remove fields at any time, but if you want to change the field type, the table must be empty (zero rows). Schema changes are destructive. Example: if you remove a field, you lose all data contained in the field. | Base datasets control underlying data stored in files. Dataset schemas describe how to read (and later transform) the data stored in those files. If you’re familiar with databases, this is commonly referred to as schema-on-read. Although you can change the dataset schema at any time, you must make sure that the schema matches the data in the underlying files so that it recognizes the data correctly. If the schema doesn't match the file, then Prism sets value as null. |
Source type | Tables can accept data from any type of source at any time, such as file upload, a dataset, or REST API. Create data change tasks to load data into and change data in a table. You can use a different source type for each data change task that works on the same target table. Example: You create 2 data change tasks that use the same target table and these source types:
| A base dataset only accepts data from the same source type that you used when you created the dataset. That is, if you create a dataset using SFTP, it will only accept data from an SFTP server. |
Data validation | When you load data into a table, Workday validates the data against the defined schema. If the value for a field doesn’t match the field type or other field parameters (such as date format), then Workday marks the entire row as invalid and doesn’t include the row in the table. Instead, the row is sent to an error file that you can download. | When you publish a dataset, Workday reads the data stored on disk and validates it against the current schema of the base dataset. If the value for a field doesn’t match the field type or other field parameters (such as date format), then Workday marks that field value as NULL and includes the row in the published Prism data source. |
NULL handling | Every field allows NULL values unless you configured it as required. Workday distinguishes between NULL values and empty string values in Text fields when reading a delimited source file to load data into a table. If a delimited file contains:
| Every field allows NULL values. Workday doesn’t distinguish between NULL values and empty string values in delimited files. |
Name restrictions | Tables have 2 names:
Table fields also have a display name and an API Name. You can change the display name at any time. However, you can’t change the API name after you create the field and save the table. Display, API, and field API names must be unique and conform to the name validation rules. See Reference: Naming Guidelines for more details. | Base (and derived) datasets have 2 names:
Dataset fields only allow 1 name and you can change it at any time. But you must ensure to fix any downstream errors that might result from changing the field name. Display and API names must be unique and conform to the name validation rules. |
Deleting data | You can remove all rows (truncate) or some rows (delete) from a table. You can selectively delete rows based on a key field. You might want to do this when you append data to the table and you need to remove the rows added from a particular load. | You can remove all rows (truncate) from a base dataset. You can’t delete a subset of rows. |
Make Prism data source | Use the Enable for Analysis option when you create or edit the table schema to create a Prism data source. | Publish the dataset to create a Prism data source. |
Row count | Workday knows exactly how many rows of data exist in a table, and it displays the number of rows in the Data Catalog and on the View Table Details report. | Workday doesn’t know how many rows of data exist in a base dataset. |