Connect data from Amazon AWS Services

[skoalit]

Hi I am new at this, but I would like to know how I can: 

1. Connect live data from Amazon AWS Services (right now the crawler dumps the data on Amazon S3 as zip files), or even to an SQL server 

2. How often it refreshes and how can I create the limits of when it imports data and refreshes the vizualisations? 


Thank you very much! 

[skypurplecloud]

Hi.  If you are still having issues, please reach out and I can try to offer some advice about connecting external services into AWS.

I come from AWS background, so if you need assitance with secruity groups, routing or network configurations (this is usually the main stumbling block), I can attempt to help you out.

This should be fairly straighforward if you can see the AWS data source from the PowerBI environment.

Regards,

Roy.

[skaranth10]

Hi @skypurplecloud ,

I am currently facing issues trying to connect Power BI to the AWS database as well. Been trying to read various articles on how this is being done, but I am not able to connect to the database. Any help would be appreciated! 

[PhilipTreacy]

Hi @skaranth10 

It's best to start a new topic and explain in detail your issue there.

What exactly is the problem?  What exactly are you trying to connect to?  AWS API?  SQL on AWS?  Amaxon S3?  Are you receiving error messages?

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[skaranth10]

Hi,

I am still getting this following error message when I try to connect Power BI to AWS with the Redshift connector- Error occurred while trying to connect: [SQLState 08S01] could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "ec2-34-215-220-108.us-west-2.compute.amazonaws.com" (34.215.220.108) and accepting
TCP/IP connections on port 5439?
"

I have added an inbound rule to the AWS EC2 server for port 5439 with the source as 0.0.0.0/0 

What changes do I have to do? Any suggestions?

Thank you in advance!

@PhilipTreacy @Greg_Deckler 

[Greg_Deckler]

@skaranth10 Is this just a SQL Server running in AWS or something else? Generally when I put in firewall rules for Azure I have to specify the exact IP address of the host that is connecting. There is a button I believe to add client ID. Would think there would be something similar for AWS. Network firewall rules aren't my forte though quite honestly. 

[skaranth10]

Hi @PhilipTreacy ,

I am trying to connect to the SQL on AWS. I have tried the Redshift Connector, but couldn't successfully connect. I get the error message as 

"Details: "ODBC: ERROR [08S01] [Microsoft][Amazon Redshift] (10) Error occurred while trying to connect: [SQLState 08S01] could not connect to server: Connection refused (0x0000274D/10061)
Is the server running on host "ec2-34-215-220-108.us-west-2.compute.amazonaws.com" (34.215.220.108) and accepting
TCP/IP connections on port 5439?"

Is Redshift the best way to connect? Do you recommend any other means? 

Regards

[Anonymous]

Hi @skypurplecloud ,

I am having the reverse issue - I'm having trouble accessing my AWS files from Power BI so that I can set up transforming my daily data feeds. Would you be able to assist?

Thanks!

[jorgeutd0202]

Hello All,

It is possible to connect POWER BI desktop to DENODO (AWS)?

[v-yuezhe-msft]

Hi @skoalit,

Do you want to connect to SQL Server in Amazon AWS from Power BI? If that is the case, in Power BI Desktop, you can use Amazon Redshift connector to connect to the instance. When you choose to Load data from Power BI Desktop Navigator , you'll prompted whether to use Import or DirectQuery mode to load the data. For more information, please review this article. After you import data from  Amazon AWS to Power BI Desktop,  you can create reports in the desktop, then publish the Power BI Desktop file(PBIX file) to Power BI service.

In Power BI Service, you can set schedule refresh for your dataset, in order to set how often it refresh your dataset, you would need to configure on-premises gateway and add Amazon AWS data source under the gateway. For more details, you can review this article.

There is also a similar thread for your reference: https://community.powerbi.com/t5/Service/Power-BI-and-SQL-Server-in-the-AWS/td-p/54057 .



Thanks,
Lydia Zhang

[skoalit]

@v-yuezhe-msft Hey Lydia! Thank you very much for answering! 

I tried all the links that you recommended, and download and installed the gateway. however i was unable to connect the server as power bi could not point to aws. 

Is there an alternative way? 

[v-yuezhe-msft]

Hi @skoalit,

What is the error message do you get? Have you connected to the SQL Server instance in Power BI Desktop?

Another possible option is to create a ODBC data source for SQL Server on AWS, then connect to the ODBC data source in Power BI Desktop.

Thanks,
Lydia Zhang

[skoalit]

@v-yuezhe-msft Hey Lydia, Thanks for your great suggestion but I was unable to connect SQL Server to Power BI. Apparently, it had to be listed as one of the applications that Amazon AWS is working with. 

Is there any alternative though? I've tried downloading the Data Gateway, https://powerbi.microsoft.com/en-us/documentation/powerbi-gateway-enterprise-manage-sql/, but there was an error connecting to the server.