Forum Discussion

GauravSinghPBI's avatar
GauravSinghPBI
Helper III
1 year ago

POWER BI Semantic Layer Incremental Refresh Best Approach connecting to AWS Athena

Hi Team,

We are planning to pull data from AWS Athena in Power BI using data Flow. We would like to achieve below functionality.

 

  • All data engineering and data modelling will happen in AWS. Data will be stored business date wise. When data is changed/loaded in AWS for T-1 or last business date, we would like to pull the data in Power BI using data flow. Historic Data might also change for ex. 20th last month

What is best way to achieve the incremental refresh in Power BI. We have thought of 2 ways to achieve this. What is the best way and if possible what are the teps will be taken. In Either way hope we are not storing any data in dataverse.

 

  1. Should Orchestration happen in AWS and set the high water mark to inform Power BI to trigger the data flow? What are steps involved to achieve this method. We are looking for this approach as there will be other tools for reporting pulling same data from AWS so it will be better if archestration has to be triggered from AWS side instead on Power BI Data Flow. There should be flag in AWS that this data status is sent to internal and this data staus is sent to external tool.
  2. There should power BI or Microsoft API and we just send the authenrication token using personal or principal account to sign in and if the principal account has access to data flow on the workspace then it can trigger the refresh. API will keep checking when data flow is complete and then fire to refresh all semantic model. 

 

Thanks,

Gaurav

Need Help

12 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    Hi GauravSinghPBI ,

    This is exactly how I’d approach it for a reliable incremental refresh from AWS Athena into Power BI.


    Orchestrate everything on the AWS side. When your data in Athena is updated and ready (maybe flagged with a control column), use AWS Lambda or Step Functions to trigger a refresh in Power BI using the REST API. First, call the Dataflow refresh endpoint, then (after that finishes) trigger the dataset (semantic model) refresh. This keeps your refreshes efficient and only pulls new or changed data.

     

    A quick rundown:

     

    • Partition Athena data by business date and use a “ready” flag or metadata table.

    • Parameterize your Power BI Dataflow for incremental loads with RangeStart/RangeEnd.

    • Trigger both Dataflow and Dataset refreshes via Power BI API from AWS as soon as data is ready.

    • Use a service principal or an account with the right access for API calls.

    • Optionally, monitor refresh status and alert for failures.

    This way, Power BI only updates when there’s actually new data, and you keep full control from the AWS side.

     

    • GauravSinghPBI's avatar
      GauravSinghPBI
      Helper III
      1 year ago

      Thanks @rohit1991 for suggestion to keep control on AWS side.

       

      What if we decide to control and triggering initiation of changed data load in Power BI instead orchestration from AWS side? Is there any way to achieve this?

       

      We discussed if there is any data load for last business date but how we load the incremental/changed data in historic dates for ex. any business date in last month.

       

      Thanks,

      Gaurav

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi GauravSinghPBI,

    Thank you for using Microsoft Fabric Community Forum.

     

    Thank you for providing the detailed use case and outlining the integration scenario between AWS Athena and Power BI. Given your requirement to store and update data in AWS, including both recent and historical business dates and to have Power BI refresh only when there are relevant changes, we suggest managing the orchestration on the AWS side using the Power BI REST API.

     

    Store your data in Athena, partitioned by business_date. Maintain a metadata table or flag column (e.g., is_data_ready) to indicate when data is ready for external consumption. Then keep a "high water mark" (last processed date) to track changes efficiently.

    Create a parameterized query in Power BI Dataflow using RangeStart and RangeEnd. Enable incremental refresh based on business_date and publish the Dataflow in a Premium workspace.

    After new data is loaded or modified in Athena, use AWS Lambda or Step Functions to trigger a Power BI Dataflow refresh via:
    POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/dataflows/{dataflowId}/refreshes
    Authenticate using either a Service Principal or a personal account token with access to the workspace.

    Once the Dataflow refresh completes, use the API to trigger the Dataset refresh:
    POST https://api.powerbi.com/v1.0/myorg/groups/{workspaceId}/datasets/{datasetId}/refreshes
    Monitor refresh status using the /refreshers GET endpoint.

    Thank you.


    • GauravSinghPBI's avatar
      GauravSinghPBI
      Helper III
      1 year ago

      Thanks @v-sgandrathi for suggestion to keep control on AWS side.

       

      What if we decide to control and triggering initiation of changed data load in Power BI instead orchestration from AWS side? Is there any way to achieve this?

       

      We discussed if there is any data load for last business date but how we load the incremental/changed data in historic dates for ex. any business date in last month.

       

      Thanks,

      Gaurav

  • GauravSinghPBI's avatar
    GauravSinghPBI
    Helper III
    1 year ago

    Thanks v-sgandrathi and rohit1991 for suggestion to keep control on AWS side.

     

    What if we decide to control and triggering initiation of changed data load in Power BI instead orchestration from AWS side? Is there any way to achieve this?

     

    We discussed if there is any data load for last business date but how we load the incremental/changed data in historic dates for ex. any business date in last month.

     

    Thanks,

    Gaurav

    • v-sgandrathi's avatar
      v-sgandrathi
      Community Support
      1 year ago

      Hi rohit1991,

       

      Yes, you can manage and trigger data loads from Power BI instead of AWS, but there are key factors to consider for accurate incremental refresh, especially when historic data may change. When you use incremental refresh in Power BI with RangeStart and RangeEnd parameters on the business_date column, only data within the set window (like the last 30 days) is refreshed. This works for recent updates, but if changes occur outside this window—such as updates to data from the previous month—Power BI won't automatically refresh it. To address this, it's best to set up a change tracking system in Athena, such as a metadata or log table listing all business_dates with updates. Your Power BI query can then use this table to load only the affected dates. This method provides control when triggering from Power BI. You can also automate refreshes using the Power BI REST API. However, for more scalable and centralized orchestration, managing this process on the AWS side remains preferable, especially if multiple tools use the same data.

      Thank you.

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        Hi GauravSinghPBI,

         

        Hi  

        We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

        If you need any further assistance, feel free to reach out.

         

        Thank you for being a valued member of the Microsoft Fabric Community Forum!