Connect Amazon SageMaker Unified Studio to Microsoft Power BI – Part 1: IAM Identity Center (IDC)-based domains

Connecting Power BI to your Amazon SageMaker Unified Studio data catalogs typically required third-party bridges. These bridges added complexity and licensing costs. In this post, you create a direct connection using new authentication modes in the Amazon Athena ODBC driver, removing those dependencies entirely. If your organization uses Power BI as its business intelligence (BI) tool, your analysts can configure access to governed data in Amazon SageMaker Unified Studio without changing their tools or workflows. As an AWS alternative, Amazon Quick Sight provides serverless BI integration with Amazon SageMaker Unified Studio at pay-per-session pricing.

A previous post showed the connection method using a third-party ODBC-JDBC bridge. The Amazon Athena ODBC driver (version 2.2.0 and later) now supports Amazon SageMaker Unified Studio authentication directly, eliminating the need for customers to configure third-party bridge components previously required for this connection. This bridge also created additional components and required ongoing maintenance. The native connection simplifies the architecture by reducing these requirements.

Customer Spotlight

UC Irvine, a top-ten U.S. public research university, consolidates student data from systems across multiple departments into a single governed repository that supports reporting, research, and analytics for decision-making at the strategic, tactical, and operational levels. Many of their analysts rely on Power BI to explore and visualize this governed data.

“Our users rely on Power BI for data visualization and reporting, but connecting to governed data in AWS previously required workarounds. The ODBC connection feature gives a direct path from Power BI into our SageMaker Unified Studio projects—no bridge software, no extra licensing, just a connection string and we’re ready to go.”

— Bernadette Theologidy, Manager, Student Analytics, UC Irvine

The Athena ODBC driver introduces two new authentication modes for SageMaker Unified Studio:

1. SageMakerBrowserIdc (for IDC-based domains): The driver opens a browser window and authenticates through AWS IAM Identity Center (and your external identity provider, if configured). No local AWS credentials are needed.
2. SageMakerIam (for AWS Identity and Access Management (IAM)-based and IDC-based domains): The driver uses AWS credentials from the default credential provider chain. For this walkthrough, we use AWS IAM Identity Center to provide those credentials.

You connect Microsoft Power BI to Amazon SageMaker Unified Studio through Athena. The Athena ODBC driver supports using two connection methods that use these authentication modes:

Method 1: DSN-based (Athena Power BI connector): You configure an ODBC Data Source Name (DSN) and use the Athena connector in Power BI. This method supports DirectQuery and Import mode with both SageMakerBrowserIdc and SageMakerIam authentication.

Method 2: DSN-less (Power BI ODBC connector): You use the Power BI ODBC connector with a connection string, requiring no DSN configuration. This method supports Import mode only with SageMakerIam authentication. DirectQuery isn’t available because the Power BI ODBC connector doesn’t support it. The connection string in Power BI Desktop must match exactly the one on Power BI Service. Because the gateway runs as a Windows service without interactive browser access, both ends must use SageMakerIam.

This is Part 1 of a two-part series. This post covers IDC-based domains using both connection methods. Part 2 covers IAM-based domains.

Solution overview

In this walkthrough, you take the role of a data analyst at an energy company. You need to understand the current state and future direction of the U.S. power generation fleet using the Public Utility Data Liberation Project, available on the Registry of Open Data on AWS. Our goal is to analyze generation capacity and identify where new investment is flowing. We connect Power BI to Athena through Amazon SageMaker Unified Studio and query the EIA-860 generators dataset directly from our data catalog. The result is a single visualization that reveals the energy transition.

The following diagram illustrates the solution architecture for connecting Power BI to Amazon SageMaker Unified Studio through Amazon Athena.

Figure 1: Architecture diagram

The following architecture demonstrates a six-step workflow.

1. Data engineers and analysts connect Power BI Desktop to Athena as a data source.
2. They build their reports locally.
3. They then publish them to the Power BI Service.
4. Microsoft On-Premises Data Gateway on an Amazon Elastic Compute Cloud (Amazon EC2) instance connects to Athena using the instance’s attached IAM role.
5. The Power BI Service then uses this gateway connection.
6. Report viewers access the published reports through Power BI Service to make data-driven decisions.

On the AWS side, Athena queries the data catalog managed by AWS Glue Data Catalog. The catalog references data stored in Amazon Simple Storage Service (Amazon S3). An Amazon SageMaker Unified Studio project governs all access.

In an IDC-based domain (covered in this post), Power BI Desktop uses SageMakerBrowserIdc for Method 1 and SageMakerIam for Method 2. Power BI Desktop can run on-premises or on an EC2 instance. The gateway always uses SageMakerIam (it runs as a Windows service without browser access) and authenticates using instance profile credentials, which rotate automatically. The gateway can only query data within projects where its IAM role has been added as a member. For IAM-based domains, see Part 2.

Prerequisites

Before connecting Power BI to Amazon SageMaker Unified Studio, verify that your environment meets these requirements:

• Athena ODBC driver – The latest Amazon Athena ODBC driver (version 2.2.0 or more recent) for Windows 64-bit.
• Microsoft Power BI Desktop – The latest version installed on your Windows machine.
• Microsoft Power BI Pro License – Required for publishing reports and configuring the on-premises data gateway.
• Microsoft Power BI on-premises data gateway – The latest version installed on the EC2 instance.
• Amazon SageMaker Unified Studio – An Amazon SageMaker Unified Studio IDC-based domain.

You need an Amazon SageMaker Unified Studio project with data assets. For detailed instructions, refer to the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the Amazon SageMaker Unified Studio project Query Editor interface, which runs a preview query against the EIA-860 generators dataset.

Figure 2: SageMaker Unified Studio project with the EIA-860 generators dataset available in the data catalog

Method 1: DSN-based connection (Athena Power BI connector)

This method uses the Amazon Athena Power BI connector with an ODBC Data Source Name (DSN), supporting DirectQuery and Import mode.

You configure Power BI Desktop to connect to your data assets in Amazon SageMaker Unified Studio using the SageMakerBrowserIdc authentication mode. The driver opens a browser window and authenticates through IAM Identity Center (and your external identity provider, if configured).

Add your SSO user as a member of your SageMaker Unified Studio project

Your single sign-on (SSO) user needs project-level access to query data with Athena. Verify your user is listed as a project member or add it by following Add project members in the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the SageMaker Unified Studio project user management page, where project owners can add or remove project users and roles.

Figure 3: Members of a SageMaker Unified Studio project

Gather configuration values to configure your Amazon Athena ODBC DSN

Gather the following values from your Amazon SageMaker Unified Studio project:

1. Open your Amazon SageMaker Unified Studio project.
2. In the top right, select the three dots.
3. Choose Project details.
4. Select JDBC and ODBC details.
5. Under ODBC connection details copy the following information: IDC issuer URL, domain ID, project ID, Athena workgroup name and AWS Region.

The following screenshot shows the Amazon SageMaker Unified Studio project overview page, where you can copy these details.

Figure 4: ODBC connection details

Configure the ODBC DSN

Create a System DSN using the Amazon Athena ODBC driver. For the general DSN creation steps, see Configuring a data source name on Windows in the Amazon Athena User Guide.

Enter the following values:

In the Authentication Options, configure the following values:

Choose OK, then Test to verify the connection. Choose Allow Access when prompted by the browser.

The following screenshot shows the consent prompt.

Figure 5: Browser consent prompt

The following screenshot shows the successful connection test.

Figure 6: Successful connection test in the ODBC DSN configuration with SageMakerBrowserIdc authentication

Connect Power BI Desktop to your data

With the DSN configured, you can connect Power BI Desktop to your data catalog and load the generators dataset.

1. Open Power BI Desktop.
2. Open the Get Data menu and select More.
3. Search for and select Amazon Athena and choose Connect.
4. For Data Source Name (DSN), enter pbi-idcdomain.
5. Select DirectQuery.
6. Choose OK.
7. Choose Use Data Source Configuration and then Connect.
8. In the AwsDataCatalog folder, navigate to your database.
9. Select the core_eia860__scd_generators table.
10. Choose Load.

The following screenshot shows Power BI Desktop successfully connected to the AWS data catalog.

Figure 7: Power BI Desktop connected to the data catalog with the generators table loaded using SageMakerBrowserIdc authentication

Create your dashboard and publish it

You can create a dashboard to visualize U.S. power generation data. To create a visualization, complete the following steps:

1. In the Visualizations pane, choose the Stacked bar chart.
2. Assign the Y-Axis: Drag technology_description to the Y-Axis.
3. Assign the X-Axis (Values): Drag capacity_mw to the X-Axis (automatically summed).
4. Assign the Legend (Stack): Drag operational_status to the Legend field.
5. Choose Publish.
6. Give your report a name (for example, generation-idcdomain) and choose Save.
7. Sign in and choose a destination workspace.

Figure 8: Power BI Desktop report using the EIA-860 generators dataset

After publishing, the report structure is available on Power BI Service.

Method 2: DSN-less connection (Power BI ODBC connector)

In this method, you use the Power BI ODBC connector with a connection string (no DSN required). This method supports Import mode only and SageMakerIam authentication. Because the gateway cannot perform browser authentication, both Desktop and gateway must use SageMakerIam. If your workflow requires SageMakerBrowserIdc, use Method 1.

If your machine already has AWS credentials through another method in the default credential provider chain, skip the following setup.

Administrator setup

Create a custom permission set named SageMakerDataAnalyst in IAM Identity Center with the following inline policy. For detailed steps, see Create a permission set in the AWS IAM Identity Center User Guide.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

Assign your user to this permission set for the AWS account containing your SageMaker Unified Studio domain. Then configure your AWS Command Line Interface (AWS CLI) SSO profile by running aws configure sso. For the full CLI configuration walkthrough with detailed steps, see Part 2. After your profile is configured, run aws sso login to authenticate.

Add the IAM identity as a member of SageMaker Unified Studio project

The IAM identity providing credentials needs both domain-level and project-level access to query data through Athena.

1. Add AWSReservedSSO_SageMakerDataAnalyst_1234example as a domain IAM user: see Managing users in the Amazon SageMaker Unified Studio Admin Guide. Choose Current account.

Figure 9: List of users of your SageMaker Unified Studio domain including the IAM identity

2. Add AWSReservedSSO_SageMakerDataAnalyst_1234example as a project member: see Add project members in the Amazon SageMaker Unified Studio User Guide.

Figure 10: Members of a SageMaker Unified Studio project including the IAM identity

Gather configuration values

Gather the following connection values from your Amazon SageMaker Unified Studio project:

1. Open your Amazon SageMaker Unified Studio Project.
2. On the navigation pane, choose Overview.
3. Select JDBC and ODBC details.
4. Select the Using IAM auth toggle.
5. Copy the ODBC connection string.

Figure 11: ODBC connection string on the SageMaker Unified Studio project overview

Connect Power BI Desktop to your data and publish

With the configuration parameters of your project, you can connect Power BI Desktop to your data catalog and load the generators dataset.

1. Open Power BI Desktop.
2. Open the Get Data menu and select More.
3. Search for and select ODBC and choose Connect.
4. For Data Source Name (DSN), select (None).
5. Expand Advanced Options.
6. In the Connection string field, enter your connection string. For example, Driver={Amazon Athena ODBC (x64)};AwsRegion=us-east-1;Catalog=AwsDataCatalog;Schema=default;Workgroup=workgroup-abcdefghij-klmexample;SageMakerDomainId= dzd-123456example;SageMakerProjectId= abcd12example;SageMakerDomainRegion=us-east-1;AuthenticationType=SageMakerIam;
7. Choose OK.
8. Choose Default or Custom and then Connect.
9. In the AwsDataCatalog folder, navigate to your database.
10. Select the core_eia860__scd_generators table.
11. Choose Load.

When publishing, name your report generation-idcdomain-dsnless.

Configure the on-premises data gateway and view your report on Power BI Service

After creating your reports in Power BI Desktop, configure the on-premises data gateway to view your report on Power BI Service.

You can configure the gateway using either a DSN or a DSN-less connection string, matching the method you used in Power BI Desktop.

Create and attach an IAM role to the Power BI Gateway EC2 instance

Create an IAM role for the EC2 instance that will host your Power BI gateway. Name the role pbi-gateway-role (or a name of your choice). The role must use EC2 as the trusted entity and include the following inline policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SageMakerAccess",
            "Effect": "Allow",
            "Action": [
                "datazone:GetConnection",
                "datazone:ListConnections",
                "datazone:GetDomain",
                "datazone:GetProject"
            ],
            "Resource": "*"
        },
        {
            "Sid": "STSForDriver",
            "Effect": "Allow",
            "Action": [
                "sts:GetCallerIdentity"
            ],
            "Resource": "*"
        }
    ]
}

Attach this role to your Power BI Gateway EC2 instance. For detailed steps on creating and attaching an IAM role to an EC2 instance, refer to IAM roles for Amazon EC2 in the Amazon EC2 User Guide.

Add the Power BI Gateway IAM role as a member of SageMaker Unified Studio project

The gateway IAM role needs project-level access to query data through Athena.

1. Add the IAM pbi-gateway-role role as a domain IAM user: see Managing users in the Amazon SageMaker Unified Studio Admin Guide. Choose Current account (or Associated account if your gateway is deployed in a different account).

The following screenshot, from the Amazon SageMaker page of the AWS Management Console, shows the list of users of your Amazon SageMaker Unified Studio domain, including the IAM gateway role.

Figure 12: List of users of your SageMaker Unified Studio domain including the IAM gateway role

Add the IAM pbi-gateway-role role as a project member: see Add project members in the Amazon SageMaker Unified Studio User Guide.

The following screenshot shows the Amazon SageMaker Unified Studio project user management page listing the project members.

Figure 13: Members of a SageMaker Unified Studio project including the IAM gateway role

Configure the data source on Power BI Gateway

How you configure the data source depends on the method you used in Power BI Desktop.

Method 1 (DSN-based)

Configure a System DSN on the gateway EC2 instance following the same ODBC DSN steps described in Method 1. When configuring, make sure that:

• You use the System DSN tab (not User DSN) because the gateway runs as a Windows service under a separate account.
• The authentication type is set to SageMakerIam regardless of what you used on Desktop.
• The DSN name matches exactly the one configured on Power BI Desktop (for example, pbi-idcdomain)

Method 2 (DSN-less)

No configuration is needed on the gateway machine itself. You configure the data source directly in Power BI Service.

Configure the data source and view your report on Power BI Service

To view your report, complete the following steps:

1. Open the workspace where you saved your report.
2. Search the Semantic Model which has the same name as your report (for example, generation-idcdomain) and choose the More options icon (three dots).
3. Choose Settings.
4. Expand Gateway and Cloud Connection.
5. Choose View Datasources (play icon) on your gateway.
6. Choose Manually add to gateway.
7. Add a connection name (for example, pbi-idcdomain).

The next step depends on the method that you chose:

Method 1 (DSN-based)

8. Add the DSN (for example, pbi-idcdomain) that matches exactly the one configured on Power BI Desktop.

Method 2 (DSN-less)

8. In the Connection string field, enter the connection string that matches exactly the one used in Power BI Desktop.

Next, continue with the configuration:

9. Select Anonymous as Authentication Method.
10. Choose Create.
11. Expand again Gateway and Cloud Connection.
12. For Maps to, choose the connection that you created (for example, pbi-idcdomain).
13. Choose Apply.
14. Return to the workspace where you saved your report.
15. On the Content section, choose your report (for example, generation-idcdomain).

The following screenshot shows a Power BI report on Power BI Service.

Figure 14: Power BI report on Power BI Service

You can now see your report online with the data from your Amazon SageMaker Unified Studio project.

Clean up

To avoid additional charges after testing, delete the Amazon SageMaker Unified Studio domain and EC2 instances. Refer to Delete domains and Terminate Instances for instructions.

Conclusion

In this post, you connected Microsoft Power BI to Amazon SageMaker Unified Studio using an IDC-based domain with both DSN-based and DSN-less methods. This provides a direct connection, with no third-party licensing, that maintains data governance. In Part 2, we cover IAM-based domains.

You can automate many steps of this process. For information about automating DSN creation on the Power BI Gateway or Service, refer to How ENGIE automates the deployment of Amazon Athena data sources on Microsoft Power BI. If you don’t want users adding the gateway IAM role directly, you can create a custom blueprint as a self-service tool for gateway role addition. The blueprint uses a ProjectMembership resource with a configurable parameter that project owners can activate at project creation, automatically adding the gateway role as a project contributor.

For additional best practices, refer to the Using Microsoft Power BI with the AWS Cloud Whitepaper. To learn more, visit Amazon SageMaker Unified Studio and Amazon Athena.

About the authors