DEV Community

Cover image for A quick way to have your AWS data into PowerBI via S3 and SharePoint
Sebastiano Caccaro
Sebastiano Caccaro

Posted on

A quick way to have your AWS data into PowerBI via S3 and SharePoint

#terraform #sharepoint #lambda #s3

You would expect an official connector between AWS/S3 and PowerBI to be available, and you would be wrong.

PowerBI can be integrated with dozens of connectors for multiple DBs, platforms etc. There are also a few AWS options.
What you cannot do, unfortunately, is integrate with S3 or some sort of direct push.

PowerBI connectors

To be fair, PowerBI is designed to parse through great volumes of structured data, so this makes sense.
But what if you only need to send a few business metrics and you wanna keep it simple? No DBs, no Athena, OpenSearch etc?

The idea is to exploit the SharePoint folder connector to get files from AWS into PowerBI.

Here is the simple architecture:
Architecture

In AWS, basically anything can output files to S3. Whenever an object is created in the bucket, a Lambda function is triggered and uploads the file to SharePoint via the Microsoft Graph API.
In PowerBI you can then use the SharePoint folder connector and you're all set!

A few notes:

  • This architecture reacts only to ObjectCreated event, although extending it to handle deletion, or other events would be quite trivial
  • This architecture only goes one way: whatever happens to the files in SharePoint would not impact what you have in your S3 account.

The system was designed this way in order to satisfy the requirement I had at the time, and I haven't had the need to extend it.
After coming to this design, Claude Code did the rest :). You can find the complete Terraform module for you to use in this repository
📦s3-to-powerbi-connect

Quick Start

If you're going to use the module, you'll need some Azure credentials to allow your Lambda to call SharePoint. First, register an app in Azure AD:

Register app in Azure AD

Then grant it Microsoft Graph > Files.ReadWrite.All permissions:

Grant API permissions

Finally, create a client secret:

Create client secret

Note the tenant_id, the client_id and the client_secret. You'll need these for the Terraform module.

To get the SharePoint Site ID, open Microsoft Graph Explorer and run:

GET https://graph.microsoft.com/v1.0/sites/{tenant}.sharepoint.com:/sites/{site-name}
Enter fullscreen mode Exit fullscreen mode

Copy the id value from the response.

Pass these values as variables to the Terraform module and you're ready to deploy.
Here's a minimal example of how to use the module:

module "s3_to_powerbi" {
  source = "github.com/sebacaccaro/s3-to-powerbi-connect"

  s3_bucket_name       = "my-business-metrics"
  tenant_id            = var.tenant_id
  client_id            = var.client_id
  sharepoint_site_id   = var.sharepoint_site_id
  sharepoint_folder    = "PowerBI/Reports"
}
Enter fullscreen mode Exit fullscreen mode

Once deployed, you'll need to manually put the actual secret values into AWS Secrets Manager. Terraform creates the secret but leaves it empty on purpose.

Now that everything is set up, you just need to configure the SharePoint connector in PowerBI and you're good to go!

Top comments (0)