PowerBi mapping with DataSources on AWS Cloud

Hello Readers ,

Warm Welcome !!!

Do you use Power Bi Tools in your Work day ??

Has your company migrated the data sources to Cloud Platform which were on-premise that you connected through PowerBI before ?

 Now you look for solution on how to connect to data sources hosted on AWS Cloud from your powerbi application hosted on local machine

Guess What ? This blog  will definately help you find answers to your problem or definately put on track to solving it.

 Architecture Overview with redshift as Target data source : 

Step 1: Identify The target Datasource 

--> It is necessary to identify the target resource where data is stored on AWS

You can have data in below target data sources

1) Redshift 

2) S3 

3) RDS

Step 2 : Creating EC2 Instance required to Host Gateway for PowerBi

You will need to create a EC2 instance with Windows OS as the gateway is micrsoft product and requires a windows machine to host it.

 If you have access to aws account where data source resides you can create it yourself or ask responsible infra or Operations team who have access to aws resources

Recommended Requirements of EC2 instance with Windows OS ( suggested is t2.large instance type ) 

• An 8-core CPU
• 8 GB of memory
• A 64-bit version of Windows Server 2012 R2 or later
• Solid-state drive (SSD) storage for spooling

Step 3 : Start session with New EC2 windows machine ( either client RDP or through ssm-agent)

  -> This step will vary from user to user network setup and also little complex step in entire setup

-> For users who are behind a VPN connection ideally a direct RDP is not allowed so you need to follow below steps 

--> Assign the ssm-instancecore permission to your role example abc  used to perform action on aws resources in IAM 

--> then assign that role to Ec2 instance from security option in instance setting-> modify IAM profile --> assign role you used eg. abc

--> Once this is done you need to open command prompt on your local machine with administrator privilages 

Install aws cli on it & session manager 

set your aws account on command prompt using below command 

Run command - > aws configure

follow steps mentioned in below documentation 

Configuration and credential file settings - AWS Command Line Interface (amazon.com)

Once Profile is set and you are able to connect with your aws account 

Test connectivity with below command 

aws s3 ls --> if this command doesnt respond quikly it means the account is not reachable yet 

if it list buckets or even if no buckets are listed but command exits it means the connectivity is good 

Next to start session manager with RDP run below command 

aws ssm start-session --target <instance-id> --document-name AWS-StartPortForwardingSession --parameters "localPortNumber=55678,portNumber=3389"

here instance-id ==> Ec2 instance id you have spin up

then this command should give output like waiting for connections  which mean you need to start RDP installed on your local system  or your operations teams can do this who have access to aws resources.

you can put IP : 127.0.0.1:3389 and user as adminsitrator

For password you will get it from EC2 connect where you need to upload private key which you downloaded when you created the Ec2 instance 

Step 4: Installing & configuring The Microsoft Gateway on EC2 Instance

-> This is most critical Step of all , for connecting from your local machine to Powerbi you will need to install Gateway on Ec2 Machine created above .

Download & configure the Gateway from below link 

Note : you will need a account with Microsoft office365 access enabled to login to the Gateway )

Gateway Installation process : 

Install an on-premises data gateway | Microsoft Learn

Gateway once setup should show below status

Step 5 : Creating a AWS Datacatalog for Data translation into database which can be queries through PowerBI

Go to AWS account where you have S3 Bucket stored and search for service AWS Glue 

Once you enter AWS Glue service --> click on create Database under Datacatalog section 

For creating Database & corresponding Tables you will need to run Crawler , make sure you assign permission to s3 bucket where aws glue can perform actions

Try to check if you are able to query aws datacatlog with aws Athena to check if data is correctly converted from S3 bucket to Datacatalog Database either from same account or different account

Step 6 : Install Simbha ODBC driver on EC2 machine where gateway is installed

Download & configure the Simbha ODBC  driver from internet using below link

https://docs.aws.amazon.com/athena/latest/ug/connect-with-odbc.html  

Setup the simbha driver based on authentication method required in your case use the abc role which is created above

Test the connectivity of powerbi by using role used for above operations

Thanks!!!