# Connecting to Workday Data Connect
<a name="connecting-workday-data-connect"></a>

You can create a data connection to Workday Data Connect to query Workday Data Lake tables from notebooks and Visual ETL in Amazon SageMaker Unified Studio. The connection uses the Workday Data Connect Iceberg REST catalog endpoint. Workday Data Lake stores Workday data as Apache Iceberg tables and refreshes them from your Workday tenant on a schedule. The data stays in Workday, and Amazon SageMaker Unified Studio reads it in place without copying it. Because Workday Data Lake refreshes on a schedule, Workday Data Connect returns data as of the most recent refresh. For queries that need the current data in your Workday tenant, use Workday Live Data Query instead.

## Prerequisites
<a name="workday-data-connect-prerequisites"></a>

For more information about setting up Workday Data Lake, see [Get Started with Workday Data Lake](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/workday-data-lake.html) in the Workday documentation. Before you create the connection, complete the following in your Workday tenant:
+ Set up an Integration System User (ISU) for Data Lake. For more information, see [Set Up Integration System Users for Data Lake](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/set-up-isus-for-data-lake.html) in the Workday documentation.
+ Register an API client for the ISU with the JWT Bearer Grant client grant type and an x509 public certificate. Keep the matching private key in PEM format, and note the token endpoint and client ID. For more information, see [Register API Client for Data Lake (JWT Bearer Grant)](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/register-api-client-for-data-lake--jwt-bearer-gran.html) in the Workday documentation.
+ Add the tables and columns that you want to query to your Data Lake. For more information, see [Add Tables and Columns to Data Lake](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/add-tables-and-fields-to-data-lake.html) in the Workday documentation.
+ Create a Data Lake access role that assigns the ISU and the tables it can read. For more information, see [Configure Data Lake Access Roles](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/configure-data-lake-access-roles.html) in the Workday documentation.

**Important**  
The ISU must be assigned to a Data Lake access role that includes the tables that you want to read. Without it, you can create the connection, but listing or reading the catalog fails with an `Access denied` error.

**Note**  
The connection supports OAuth 2.0 with the JWT bearer grant type. The refresh token grant type isn't supported for connections that you create in Amazon SageMaker Unified Studio.

## Steps to connect to Workday Data Connect from Amazon SageMaker Unified Studio
<a name="steps-to-connect-to-workday-data-connect"></a>

1. In the navigation pane, choose **Connections**.

1. Choose **Create connection**.

1. In the gallery that opens, select **Workday Data Connect**.

1. For **Name**, enter a descriptive name for your connection. Optionally, enter a **Description**.

1. Configure the connection properties:

   1. **Instance URL** - The Workday Data Connect catalog server URL, in the format `https://catalog.{{data-center-code}}.myworkday.com`, where {{data-center-code}} is your Workday environment prefix, such as `wd00`.

   1. **Tenant ID** - Your Workday tenant ID. For example, `aws_tenant1`.

   1. **Source catalog names** - Enter `workday`, and then choose **Add**.

1. For **Authentication**, the credential type is **OAuth 2.0**. Provide the following:

   1. **Token URL** - The Workday Data Connect token URL, in the format `https://{{host}}/ccx/oauth2/{{tenant}}/token`.

   1. **Client ID** - The client ID of the API client registered for the ISU. For example, `MThmMWI3YWEtZDZjMS00MzRmLTliZWBeMzg4ZjI2NmI1MjFl4`.

   1. **OAuth scopes** - Optional. The OAuth scopes to request with the access token, as a space-separated list.

1. For **Credential source**, select one of the following:

   1. **JWT bearer** - Provide the credentials that Amazon SageMaker Unified Studio uses to sign a JWT for the ISU. Your credentials are stored in AWS Secrets Manager.

      1. **Workday integration system user** - The ISU user name. For example, `demo-isu`.

      1. **Private key PEM** - The PEM private key that matches the x509 certificate registered for the API client, including the `BEGIN PRIVATE KEY` and `END PRIVATE KEY` lines.

   1. **AWS Secrets Manager** - Enter the Amazon Resource Name (ARN) of a secret in AWS Secrets Manager that holds the credentials. The secret must contain the ISU user name in the `USERNAME` key and the private key in the `PRIVATE_KEY_PEM` key. You might need an administrator to create a secret.

1. Choose **Create connection**.

## Query Workday tables from a notebook
<a name="query-workday-tables-from-a-notebook"></a>

After you create the connection, you can query Workday tables from a notebook in your project with Spark SQL. Workday tables use three-part names in the format `workday.<namespace>.<table>`. The `workday_core` namespace contains Workday business object tables. The `join_workday_core` namespace contains joiner tables that flatten array relationships.

1. In the navigation pane, choose **Notebooks**, and then create or open a notebook.

1. In the **Compute environment** panel, under **Spark**, for **Connection**, choose the AWS Glue Spark connection (for example, `default.spark`).

1. Add a Python cell, and then run Spark SQL queries against the Workday catalog.

The following example lists namespaces and tables, and reads a table:

```
spark.sql("SHOW NAMESPACES IN workday").show(truncate=False)
spark.sql("SHOW TABLES IN workday.workday_core").show(truncate=False)
spark.sql("SELECT * FROM workday.workday_core.campaign LIMIT 10").show(truncate=False)
```

The following example summarizes compensation pay ranges by currency and frequency:

```
spark.sql("""
    SELECT pay_range_currency_id,
           pay_range_frequency_name,
           COUNT(*) AS range_count,
           ROUND(AVG(pay_range_minimum), 2) AS avg_minimum,
           ROUND(AVG(pay_range_maximum), 2) AS avg_maximum
    FROM workday.workday_core.compensation_pay_range
    WHERE pay_range_maximum IS NOT NULL
    GROUP BY pay_range_currency_id, pay_range_frequency_name
    ORDER BY range_count DESC
""").show(truncate=False)
```

Workday stores multi-instance relationships as array columns of IDs. To join on an array column, use `explode`, and then join each ID to the related table:

```
spark.sql("""
    SELECT e.pay_range,
           child.display_id AS related_range,
           child.pay_range_minimum,
           child.pay_range_maximum
    FROM (
        SELECT display_id AS pay_range,
               explode(compensation_pay_ranges) AS related_id
        FROM workday.workday_core.compensation_pay_range
    ) e
    JOIN workday.workday_core.compensation_pay_range child
        ON child.id = e.related_id
    LIMIT 20
""").show(truncate=False)
```

You can also join Workday tables with other data in the same notebook. Examples include tables in your lakehouse and DataFrames that you create in Spark. For more information about Workday query patterns, see [Query Workday Data Lake](https://doc.workday.com/admin-guide/en-us/workday-data-cloud/data-out/workday-data-lake/query-workday-data-lake.html) in the Workday documentation.

**Note**  
Workday refreshes Data Lake tables on a schedule, so query results can lag behind your Workday tenant. For queries that need the current data in your tenant, use Workday Live Data Query.

## Troubleshooting
<a name="troubleshooting-workday-data-connect"></a>

**Listing or reading the Workday catalog fails with an `Access denied` error**  
The ISU used by the connection isn't assigned to a Data Lake access role, or the role doesn't include the table. A Workday administrator must add the ISU and the tables to a Data Lake access role in the Data Lake Admin Console. Creating the connection succeeds even when the ISU has no Data Lake access, because the connection only obtains an access token.

**A table is listed, but its columns are missing or the table isn't found**  
Only the tables and columns that your Workday administrator added to the Data Lake are available. New tables in the Workday catalog aren't added automatically. Ask your Workday administrator to add the table and columns. Also check that you use the three-part name `workday.<namespace>.<table>`.

**The connection fails with an authentication error**  
Check that the client ID and ISU user name are correct, and that the private key matches the x509 certificate registered for the API client in Workday. The private key must be in PEM format with its line breaks.