Data Bear – Power BI Training and Consulting

Call us Today! (020) 8720 6880 | info@databear.com

Incremental Refresh and Real-Time Data in Power BI

Filters

Introduction

In today’s blog we will look at key features that significantly enhance data freshness and performance in Power BI are Incremental Refresh and Real-Time Data capabilities. In this comprehensive guide, we’ll delve into the intricacies of these features, exploring their benefits, requirements, configuration steps, and advanced techniques.

Incremental Refresh and Real-Time Data in Power BI

Benefits of Incremental Refresh and Real-Time Data

Incremental refresh extends scheduled refresh operations by automatically managing partitions in tables with frequently updated data. This feature offers several benefits:

  1. Reduced Refresh Cycles: By refreshing only the most recent data, incremental refresh minimizes the need for full refresh cycles, saving time and resources.
  2. Faster Refreshes: With incremental refresh, only the changed data needs to be refreshed, resulting in quicker refresh times.
  3. Improved Reliability: Long-running connections to volatile data sources are unnecessary, enhancing the reliability of data refresh operations.
  4. Resource Consumption Reduction: Incremental refresh reduces memory and resource consumption in both Power BI and data source systems.
  5. Support for Large Models: Semantic models with potentially billions of rows can grow without the need for full model refreshes.
  6. Easy Setup: Incremental refresh policies can be defined in Power BI Desktop with just a few tasks, simplifying the configuration process.

Requirements

1. Supported Power BI Plans:

  • Incremental refresh and real-time data capabilities are available across various Power BI plans, catering to different user needs and budgets.
  • Supported plans include Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded.
  • Users can choose the plan that best suits their requirements and access incremental refresh features accordingly.

2. Compatible Data Sources

  • These features are designed to work optimally with structured relational data sources, ensuring seamless integration and efficient data processing.
  • Preferred data sources include SQL Database and Azure Synapse, known for their robustness and scalability in handling large datasets and complex queries.
  • While incremental refresh can be implemented with other data sources, relational databases offer the best performance and compatibility due to their support for date filtering and query optimization.

Time Limits

  1. Power BI Pro Models:
  • Models hosted on Power BI Pro have a refresh time limit of two hours.
  • This time limit applies to the duration allowed for refreshing the data and updating the model with the latest information.
  • While sufficient for many scenarios, this time constraint may pose limitations for larger datasets or more frequent refresh requirements
  1. Premium Capacities:
  • Models hosted on Premium capacities enjoy a more generous refresh time limit of five hours.
  • Premium capacities offer enhanced performance and scalability, allowing for larger datasets and more complex refresh operations.
  • This extended time limit provides users with greater flexibility in managing their data refresh schedules and optimizing performance.

 

  1. XMLA Endpoint for Advanced Incremental Refresh:
  • Advanced incremental refresh with the XMLA endpoint removes time limits altogether, offering unparalleled flexibility and control over data refresh operations.
  • By leveraging the XMLA endpoint, users can perform incremental refresh operations without constraints on refresh duration.
  • This feature is particularly beneficial for organizations with stringent data refresh requirements or exceptionally large datasets that exceed standard time limits.

Configure incremental refresh and real-time data

1. Create Parameters:

  • Open your Power BI Desktop file.
  • Go to the “Home” tab and click on “Transform Data” to enter Power Query Editor.
  • In the Power Query Editor, navigate to the “Home” tab again and click on “Manage Parameters.”
  • Click on “New Parameter” and define two parameters: RangeStart and RangeEnd, both as type Date/Time.
  • Set appropriate default values for these parameters to define the initial filtering range.
  • Click “OK” to create the parameters.

manage-parameters

2. Filter Data:

  • While still in Power Query Editor, select the table(s) you want to apply incremental refresh to.
  • Locate the date column(s) in your table(s) that you want to filter.
  • Apply custom date filters to these columns using the RangeStart and RangeEnd
  • This can be done by adding filtering steps to your query that specify the date range based on the values of the RangeStart and RangeEnd parameters.
  • Ensure that only the data within the specified date range is loaded into your Power BI model.

custom-filter

3. Define Policy:

  • Once you’ve filtered the data, go back to the main Power BI Desktop interface.
  • Navigate to the “Modeling” tab and click on “Incremental Refresh.”
  • In the Incremental Refresh dialog, select the table(s) you’ve configured for incremental refresh.
  • Enable the incremental refresh option for each selected table.
  • Specify the required settings:
    • Define the historical period by setting the “Archive data starting before refresh date” option. This determines the range of historical data to keep in your model.
    • Set the “Incrementally refresh data starting before refresh date” option to define the refresh period, i.e., how far back in time the refresh should cover.
  • Optionally, configure additional settings such as real-time data fetching if you’re using DirectQuery.
  • Click “OK” to save the incremental refresh policy.
  • incremental-refresh-dialog

4. Publish:

  • Once your incremental refresh policy is configured, save your Power BI Desktop file.
  • Publish the model to the Power BI service by clicking on “Publish” in the top-right corner of Power BI Desktop.
  • Choose the destination workspace and click “Select.”
  • Follow the prompts to complete the publishing process.

 

5. Refresh:

  • After publishing the model to the Power BI service, navigate to the dataset in the Power BI service.
  • Perform an initial refresh operation by clicking on “Refresh Now” or “Schedule Refresh” depending on your requirements.
  • Monitor the refresh operation to ensure it completes successfully.
  • Subsequently, perform scheduled refresh operations to keep the data up to date.
  • You can schedule refreshes to occur at regular intervals to maintain data freshness automatically.

Advanced Features

For users on Premium capacities with the XMLA endpoint enabled, advanced features such as managing partitions and bootstrapping initial refresh operations are available, offering more control and optimization possibilities.

Conclusion

Incremental refresh and real-time data capabilities in Power BI offer significant advantages in terms of data freshness, performance, and resource optimization. By following the steps outlined in this guide and leveraging advanced features where applicable, users can maximize the effectiveness of their Power BI models and derive actionable insights from their data with greater efficiency.

Don’t forget to visit our training page to learn all you can about Power BI.  Also see our other blog posts page.

Leave a Comment

Your email address will not be published. Required fields are marked *