Forum Discussion
Post ProdigyTime Out Incremental refresh importing data from AWS Athena using SIMBA Athena 64 bit ODBC
Super UserHi. Let's see. It might be a good idea to handle millions of rows with the properly engine. I mean that it is recommended to use a data warehouse like Snowflake for analytics query in millions of rows. Before jumping an think that is a PowerBi issue, you need to run performance test on your new environment. For example, how much time it takes to select * from the big table in S3 with Athena? if this takes hours, then it's now a PowerBi issue. (I mean select all the rows and not just limit 200).
On the other hand you need a data gateway to take that much data. Where are you hosting your gateway to use ODBC? have you installed the standard one? this might also take a lot of time if it's not on the same environment (an EC2 in AWS)
Last, I'm not sure you can do incremental refresh with Athena. I don't think the source has query folding that is required for that.
I hope that helps
Post ProdigyWhen I run the query from AWS web portal in Athena , it takes 35 minutes to run.
When publishing it to PowerBI I could only get the Personal GateWay to work , as it need my windows creds to be able to connect via ADFS.
I used the same personal gateway when connecting to the Remote SQL server , which worked.
The incremental refersh does appear to work fine , when I pull in 60 days of data which is about 8 million rows , the full refresh took 30 mins , and now the incremental refresh of 3 days of data takes about 8 mins.
- ibarrau
Ok. Where is that personal gateway hosted? we need to be sure the movement of data is the faster and closer we can. I would say that it needs to be in the AWS cloud.
Are you completely sure you can't use a standard gateway? I'm not familiarized with ADFS but I have connected AWS without ADFS many times using standard gateway.
Regards
- Pandadev
The personal gateway is hosted on my laptop , when I attempt to connect with standard gateway I get this error
Activity ID: 57e58ea1-4961-42a0-aa27-d7ec23970ca9 Request ID: c1d0fca0-4344-620e-6969-0e305f0d1777 Cluster URI: https://wabi-us-east2-c-primary-redirect.analysis.windows.net/ Status code: 400 Error Code: DMTS_PublishDatasourceToClusterErrorCode Time: Thu Feb 17 2022 12:48:27 GMT+0000 (Greenwich Mean Time) Service version: 13.0.17665.52 Client version: 2202.1.09931-train OPRA: Invalid connection credentials. Underlying error code: -2147467259 Underlying error message: The credentials provided for the Odbc source are invalid. (Source at dsn=Athena.) DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259 Microsoft.Data.Mashup.CredentialError.DataSourceKind: Odbc Microsoft.Data.Mashup.CredentialError.DataSourcePath: dsn=Athena Microsoft.Data.Mashup.CredentialError.Reason: AccessUnauthorized Microsoft.Data.Mashup.MashupSecurityException.DataSources: [{"kind":"Odbc","path":"dsn=Athena"}] Microsoft.Data.Mashup.MashupSecurityException.Reason: AccessUnauthorized - ibarrau
We can talk about the connection later, but the problem here is that you have a gateway in your laptop. You are downloading millions of rows to your laptop and then uploading all over again. That's why you need an EC2 in AWS to install the gateway. Otherwise it will take a long time even if you have an amazing bandwith.
I would strongly suggest moving this to have 24hours a day support for refreshing and way more faster than a laptop.
Regards,