Optimizing and fast refresh of data connected from athena

[amirthaP]

Hi all,
I'm looking for methods to improvise the speed of refresh and querying when powerbi data source connection to AWS Athena which has few queries.
It'd be really helpful if any optimization methods used in practice could be suggested 

Thanks in advance!

[amirthaP]

Hi pete,

Due to IP constraints I can't share details, but Lemme give the scenario, I have bunch of measures around 15, and no calculated columns, I pushed all those to SQL, and 4 views in athena when I run the performance analyzer I get the following:

maybe due to too any visuals also? idk

I have connected to athena using odbc and I'm not sure the refresh time is inclusive of running athena query as well?

[BA_Pete]

Ok, so the example you have there looks fine i.e. the DAX query is only 20ms, it's the 'Other' part that's making it feel slow for you.

This 'Other' time is usually due to queueing, so check the DAX Query times on all of the visuals and, if there's any that are significantly higher than the others, or over about 150-200ms, then look to optimise the measure(s) in those visuals.

If you're running the ODBC connector in Direct Query mode, then these times will include the communication with, and materialisation of, views etc. If you use import mode this will not be included.

You can also reduce the number of visuals on your report page. The more that have to load, the more queueing is required.

Pete

[amirthaP]

ok, I'll look into those measures, also If there;s any solution at source as well? 

would storing the query results to s3 and then connecting to powerbi a better way?

any heads up on that?

[BA_Pete · Accepted solution]

Sorry, I just updated my previous answer around this.

If you want the fastest reponse time in a report, you should use IMPORT mode to bring your data into Power Query, rather than using DIRECT QUERY.

If your ODBC connector is operating in a Direct Query way, then you'll have to wait for communication and processing with the server every time you update your visuals (slicers, interactive filtering etc.).

Pete

[BA_Pete]

Hi @amirthaP ,

This question is way too big.

If you can provide examples of queries and M code that you think are running too slow, we can look at specifically speeding those up.

Pete