If your operational data lives in Amazon S3, your head office wants dashboards in Power BI, and the two stubbornly refuse to talk to each other, you’ve hit one of the most common cross-cloud walls in analytics. To connect Power BI to AWS Athena and S3, you have to bridge two ecosystems that don’t know each other exists: Power BI is Microsoft; Athena and S3 are AWS. The setup is scattered across an ODBC driver, an IAM policy, and a gateway guide that assumes you already know what a gateway is.
Those are the exact symptoms our team at ScriptsHub Technologies ran into on a logistics engagement. The client had stored all raw operational data in S3 and queried it with Athena – cheap storage, pay-per-query, no database to manage. It worked well until their UK head office standardised on Power BI and asked for dashboards. Below is the full walkthrough, with the gotchas that cost everyone else an afternoon documented up front.
QUICK SETUP – To connect Power BI to AWS Athena and S3: install the Amazon Athena ODBC driver, configure a System DSN, connect through Power BI’s native Amazon Athena connector, then add an on-premises data gateway for scheduled refresh. In full:
- Prepare Athena and S3- create a partitioned external table over your S3 data, plus an S3 query-results location.
- Create a least-privilege IAM user – grant Athena, S3 (data and results buckets), and Glue catalog read permissions.
- Install the driver and create a System DSN – use the Amazon Athena ODBC v2 driver, and make it a System DSN, not a User DSN.
- Connect Power BI Desktop – Get Data → Amazon Athena connector → enter your DSN → pick your database and tables in the Navigator → choose Import or DirectQuery.
- Configure the gateway – for scheduled refresh, install the on-premises data gateway, register the same System DSN, and map an Amazon Athena data source to it in the Power BI Service.
Why Power BI and AWS Athena Don’t Connect Out of the Box
Amazon Athena is a serverless query service that runs standard SQL directly over data stored in Amazon S3, billed only for the bytes each query scans, with no servers to provision. Power BI, by contrast, has no idea how to read the Parquet files sitting in an S3 bucket. It speaks ODBC and its own native connectors; raw object storage is not one of them. Athena solves half the problem by putting a standard SQL interface on top of S3, but Power BI still needs a translator to reach it.
That translator now exists as a first-class component: since 2021, Microsoft ships a certified, native Amazon Athena connector in Power BI’s Get Data menu. But the connector is only the front door – underneath it you still need the Athena ODBC driver and, for scheduled refresh, the on-premises data gateway. Most setups stall on those two pieces.
How the Power BI to Athena Connection Actually Works
It helps to see the full chain. Power BI never touches S3 directly: Athena queries your S3 files as SQL tables, the Amazon Athena ODBC driver carries Power BI’s calls over a configured DSN, and the gateway relays them for cloud refresh. Athena scans only the relevant files and returns the result Power BI renders.
Scheduled refresh in the Power BI Services adds one more piece: an On-Premises Data Gateway bridges the cloud Servic to Athena.
The surprise is the third link: even though every component lives in the cloud, the Power BI Service can’t reach Athena on its own – it routes through the gateway, which we’ll return to.
Step 1: Prepare Athena and S3 for Power BI
If you’re connecting to an existing Athena setup, skip ahead. If you’re starting fresh, Athena needs a database and an external table pointing at your S3 data.
Why this works:Athena bills per byte scanned, not per query. Partitioning by
yearandmonthmeans a dashboard filtered toyear = 2025 AND month = 12scans only December’s files instead of your entire history. On a large table that’s the difference between a $0.03 query and a $4.00 query – and Power BI fires a lot of queries.
One more piece: Athena writes every query result to an S3 results location before returning it. Therefore, create a dedicated prefix, such as s3://your-bucket/athena-query-results/, separate from your source data. You’ll then need that exact path when you configure the DSN.
Step 2: Create an IAM User for Power BI
Don’t point Power BI at root or admin keys. Create a dedicated IAM user scoped to what the connector needs: Athena query execution, read on your data bucket, read/write on the results prefix, and Glue catalog reads so tables appear in the navigator.
Why this works: The Glue block is the one people forget. Athena uses the Glue Data Catalog to resolve table and partition definitions, so without
glue:GetTableandglue:GetPartitionsthe connection authenticates but returns no tables – a confusing “connected but empty” state.
Step 3: Install the Athena ODBC Driver and Configure a System DSN
Install the Amazon Athena ODBC v2 driver – Amazon’s own, current driver, which supersedes the older Simba-branded 1.x driver many tutorials still reference. Then open ODBC Data Sources (64-bit) and create a System DSN (not a User DSN – this matters later) with your AWS region, the S3 results location, your schema, and either the IAM access keys or, on EC2, the instance profile role.
Step 4: Connect Power BI Desktop to Athena
With the DSN ready, Power BI Desktop’s Get Data → Amazon Athena connector reads your catalog, database, and tables. The one choice that matters most is the connectivity mode:
When to use which: Import mode trades freshness for speed and cost predictability – one scan per refresh, and then everything is local. DirectQuery, meanwhile, trades that for currency, at the price of an Athena query behind every slicer. For most dashboards that refresh nightly, therefore, Import is the right call; reserve DirectQuery for datasets too large to hold in memory. If you’re rethinking storage longer-term, [migrating to Microsoft Fabric’s Direct Lake mode] sidesteps the Import-vs-DirectQuery trade-off entirely.
Running data in AWS but need Power BI dashboards on top of it? Our data engineering team has built this cross-cloud bridge for clients across the US, UK, and India. Talk to ScriptsHub Technologies if you’d rather skip the afternoon of gateway troubleshooting.
Step 5: Configure the On-Premises Gateway for Scheduled Refresh
The gateway is the step that catches everyone. Scheduled refreshes in the Power BI Service route through the on-premises data gateway – which, despite the name, runs fine on a cloud VM.
It’s worth understanding why the gateway exists, because it’s a feature, not a tax. It makes only outbound connections to the Azure Service Bus and accepts no inbound traffic, so your AWS data sources never have to be exposed to the public internet. It also spools and compresses each result set before transmission – AWS reports ratios around 10:1 – cutting both refresh time and S3 egress charges. The gateway isn’t redundant infrastructure; it’s doing your security and cost optimisation.
Setup is short once the DSN is right: install the gateway in standard mode, map an Amazon Athena source to your DSN under Settings → Manage gateways, then publish and bind the dataset. A daily or hourly schedule then runs on its own.
Red Flags: Where Power BI to Athena Connections Break
Almost every failed setup we’ve inherited traces back to one of a handful of causes. This is the diagnostic table we now run through first.
The one that burns the most hours: The error only shows up through the gateway, never in Power BI Desktop, which makes it feel random. The cause is mundane: a gateway runs as a Windows service under a service account, and a User DSN lives in that user’s
HKEY_CURRENT_USERregistry hive – which the service account can’t see. A System DSN lives where the service can read it. Register it as a System DSN and the error disappears.
Keeping Athena Queries Fast and Costs Low
Three patterns keep a cross-cloud setup quick and cheap. First, push filters down in Power Query so date ranges become Athena WHERE clauses, limiting partition scans. Second, serve summary dashboards from aggregated views rather than the raw grain, so Power BI imports thousands of rows instead of millions.
Why this works:A monthly dashboard doesn’t need row-level grain. Pre-aggregating in Athena moves the heavy
GROUP BYto where the data already lives and ships Power BI a tiny result, which keeps both refresh time and scan cost down.
Third, put a budget alert on Athena. We configure an AWS Cost Anomaly Detection alert so a runaway refresh schedule scanning terabytes surfaces as a notification, not a month-end invoice surprise.
Bottom Line
Connect Power BI to AWS Athena and S3 without migrating data out of AWS or disrupting existing analytics workflows. The native Athena connector, the ODBC v2 driver, and the on-premises gateway, together, bridge the two clouds cleanly. As a result, our logistics client now powers several Power BI dashboards covering shipment volumes, carrier performance, and revenue by lane, refreshing nightly for about $0.12 of Athena scan cost per run. By contrast, the alternative they’d been quoted-lifting everything into Azure-was a multi-month, five-figure project. Ultimately, the best solution is often the one that lets you use what you already have.
If you’re running analytics in AWS but need a Power BI reporting layer on top, ScriptsHub Technologies builds and validates these cross-cloud pipelines end to end. Talk to ScriptsHub →
Key Takeaways
To recap: Power BI reaches Athena only through an ODBC DSN, never directly. Make that DSN a System DSN, not a User DSN, or the gateway fails with “data source name not found.” Scheduled refresh still needs the On-Premises Data Gateway even with cloud-only data; the IAM role must include Glue permissions or no tables appear; and partitioning with aggregated Athena views keeps each refresh at cents per run.
Frequently Asked Questions
Q. Can Power BI connect directly to Amazon S3?
No. Power BI can’t read S3 objects directly. Athena puts a SQL interface over your S3 data, and Power BI connects to Athena through the Amazon Athena connector and ODBC driver.
Q. Do I still need the Simba ODBC driver?
Use the current Amazon Athena ODBC v2 driver instead. It’s Amazon’s own driver and supersedes the older Simba-branded 1.x driver that many tutorials still reference.
Q. Why do I need a gateway if my data is already in the cloud?
Because the Power BI Service can’t reach Athena on its own. The on-premises data gateway bridges them, makes only outbound connections, and compresses results to cut egress costs.
Q. Should I use Import or DirectQuery for Athena?
Use Import for most dashboards with scheduled refresh – it’s faster and cheaper. Use DirectQuery only when the dataset is too large to load into Power BI memory.
Q. Why does my gateway say “data source name not found”?
Because the DSN was created as a User DSN. The gateway runs as a Windows service that can’t read user-scoped DSNs. Recreate it as a System DSN.
