Forum Discussion

Pandadev's avatar
Pandadev
Icon for Post Prodigy rankPost Prodigy
4 years ago

Time Out Incremental refresh importing data from AWS Athena using SIMBA Athena 64 bit ODBC

My company has moved all of data tables from a windows server to AWS Cloud. I used to be able to use an incremental refresh for a table that has 130 million rows and 8 columns of data , using a ODBC...
Need Help
ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
4 years ago

Hi. 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

  • Pandadev's avatar
    Pandadev
    Icon for Post Prodigy rankPost Prodigy
    4 years ago

    When 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's avatar
      ibarrau
      Icon for Super User rankSuper User
      4 years ago

      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's avatar
        Pandadev
        Icon for Post Prodigy rankPost Prodigy
        4 years ago

        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