Connecting to Workday Data Connect

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

For more information about setting up Workday Data Lake, see Get Started with Workday Data Lake 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 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) 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 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 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

1. In the navigation pane, choose Connections.

2. Choose Create connection.

3. In the gallery that opens, select Workday Data Connect.

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

5. 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.

   2. Tenant ID - Your Workday tenant ID. For example, aws_tenant1.

   3. Source catalog names - Enter workday, and then choose Add.

6. 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.

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

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

7. 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.

      2. 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.

   2. 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.

8. Choose Create connection.

Query Workday tables from a notebook

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.

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

3. 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 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

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.