Power Community

Power Community

Announcing Public Preview of Hybrid Tables in Power BI Premium

We are excited to announce support of Hybrid Tables in Power BI Premium, which enables us in turn to announce an equally exciting and groundbreaking enhancement to incremental refresh – incremental refresh augmented with real-time data!!! Now you can have blazing fast performance in import mode and the latest data changes in the data warehouse reflected in user reports without having to perform a dataset refresh. For technical details concerning incremental refresh, see the article Incremental refresh for datasets in Power BI in the product documentation.

Up until now, dataset creators sometimes had to make hard tradeoffs between query performance and data freshness. Import mode delivers best performance, yet data freshness poses challenges if new data arrives at a very high cadence. It is both resource intensive and inefficient to import data into a dataset in very short intervals. DirectQuery mode, on the other hand, delivers data freshness, yet at the expense of report performance. Because Power BI doesn’t import the data but translates the report queries into data source queries, the latest data changes are quicky picked up, but the query/response roundtrips between Power BI and the data source take time and slow down the reports.

As the following diagram illustrates, a hybrid table can help to strike the right balance between query performance and data freshness. A hybrid table is essentially a (large) table that has one or multiple import-mode partitions as well as another partition in DirectQuery mode. If the DirectQuery partition is sufficiently small in comparison to the import-mode partitions, the query/response roundtrips between Power BI and the data source should still be reasonably fast while access to the bulk of the data is already super-fast in import mode. Import and DirectQuery data is presented to users as a single table with business definitions and calculations.

Support of hybrid tables in Power BI Premium offers the following benefits:

  • Blazing fast performance in import mode – The import-mode partitions deliver extremely fast query performance as the data imported during data refresh is readily available in local memory to answer client queries.
  • Latest updates from the data warehouse always included – DirectQuery requests are sent to the data source, so the query results include the latest data updates. The performance depends on how long the data source takes to respond with the results, but the queries are faster if only a small slice of the data is queried, such as data updates that occurred after the last refresh time.
  • Better resource utilization especially for very large datasets – With the latest data updates available in real-time, fewer data refreshes are needed to pick up latest changes. With fewer refresh cycles consuming your Premium capacity resources, more of these resources are available to deliver a good report performance and user experience. You no longer need to refresh your datasets at a very high cadence if the data in the data warehouse changes frequently!
  • Incremental refresh policies with real time support – Incremental refresh applies a sophisticated partitioning scheme in import mode so that refreshes complete much, much faster than if you imported the entire data volume into a single table partition every time. Despite this complexity, however, it is relatively straightforward to configure an incremental refresh policy in Power BI Desktop. The report creator does not require any partitioning skills. And now, with hybrid tables, report creators can easily add a DirectQuery partition to get the latest data in real time as well. Just configure incremental refresh as usual and enable the Get the latest data in real time with DirectQuery (Premium only) checkbox!

The following screenshot shows how to configure an incremental refresh policy with real time in Power BI Desktop. Having published the dataset to a Premium Per User (PPU) workspace or a workspace on a Premium capacity, Power BI will automatically apply the refresh policy and partition the table as a hybrid table during data refresh. For details regarding configuration steps, see the article Incremental refresh for datasets in Power BI in the product documentation.

Although the configuration is as simple as enabling a single Get the latest data in real time with DirectQuery (Premium only) checkbox, note that the presence of a DirectQuery partition on a table limits the table to the features available in DirectQuery mode. Complex Power Query expressions may not fold, which means the DirectQuery partition cannot use these expressions. And calculated columns can also cause folding issues. Moreover, dimension-type tables with relationships to your hybrid table must be in dual mode to ensure good performance. For this reason, we recommend recreating your dataset in DirectQuery mode first before you configure an incremental refresh policy with real time. The dataset in DirectQuery mode reassures you that there are no folding or compatibility issues. Then convert the desired fact table into import mode and the dimension tables into dual mode to ensure good performance, and then configure your fact table as a hybrid table.

As mentioned earlier, don’t forget to perform at least one data refresh after you published your dataset to the Power BI service so that Power BI can apply the incremental refresh with real time policy. This is an important step because the DirectQuery partition does not exist in the dataset until Power BI applied the policy and partitioned the table. This only happens during scheduled or manual data refresh in the Power BI service.

As soon as the DirectQuery partition has been added, it is also a good idea to configure automatic report page refresh if desired in the Power BI service so that your report always shows the latest data updates from the data source. Automatic page refresh causes your report visuals to query for new data, at a predefined cadence. For details, refer to Automatic page refresh in Power BI in the product documentation.

Of course, you can also apply an incremental refresh with real time policy to a table programmatically by using the Analysis Services client libraries with XMLA endpoints in Power BI Premium. The following code snippet demonstrates how to do this. Just make sure you set the RefreshPolicyMode to Hybrid and IncrementalPeriodsOffset to -1 so that there is no lag or leading period to the rolling refresh window head, as hybrid policies only support refreshes of complete days. It is also a good idea to trigger a refresh along with the policy update so that the Power BI service can partition the table accordingly.

using System;
using TOM = Microsoft.AnalysisServices.Tabular;
namespace Hybrid_Tables
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var server = new TOM.Server())
            {
                server.Connect("powerbi://api.powerbi.com/v1.0/myorg/AdventureWorksTarget");
                TOM.Database database = server.Databases.FindByName("AdventureWorks");
                if (database == null)
                {
                    throw new ApplicationException("Database cannot be found!.");
                }
                if(database.CompatibilityLevel < 1565)
                {
                    database.CompatibilityLevel = 1565;
                    database.Update();
                }
                TOM.Model model = database.Model;
                TOM.Table salesTable = model.Tables["Sales"];
                // Apply a RefreshPolicy with Real-Time to the Sales table.
                TOM.RefreshPolicy hybridPolicy = new TOM.BasicRefreshPolicy
                {
                    Mode = TOM.RefreshPolicyMode.Hybrid,
                    IncrementalPeriodsOffset = -1,
                    IncrementalGranularity = TOM.RefreshGranularityType.Day,
                    RollingWindowGranularity = TOM.RefreshGranularityType.Month,
                    IncrementalPeriods = 2,
                    RollingWindowPeriods = 4,
                    SourceExpression =
                    "letn" +
                    "    Source = Sql.Database("awsampledw.database.windows.net", "AdventureWorksDW"),n" +
                    "    dbo_FactInternetSales = Source{[Schema="dbo",Item="FactInternetSales"]}[Data],n" +
                    "    #"Filtered Rows" = Table.SelectRows(dbo_FactInternetSales, each [Date] >= RangeStart and [Date] < RangeEnd)n" +
                    "inn " +
                    "   #"Filtered Rows""
                };
                salesTable.RefreshPolicy = hybridPolicy;
                model.RequestRefresh(TOM.RefreshType.Full);
                model.SaveChanges();
            }
        }
    }
}

But wait, there is more! Hybrid tables support is also available without an incremental refresh policy! For example, you can partition a table programmatically via XMLA endpoints to apply any partitioning scheme you like. For example, you could add a DirectQuery partition to a table for old (and possibly very large) historic data. This is the opposite of the incremental refresh approach where newer data is fetched from the data warehouse. If your reports query newer data much more frequently than the historic data, it might make sense to keep the historic data in the data warehouse and import the new data. This is not a real-time scenario, yet it is a useful strategy for big data solutions where the historic data volume is just too large for import mode, but you still want it available in a report. A future blog post is planned to show in greater detail how to add a DirectQuery partition to a table programmatically, so stay tuned!

And that’s it for this exciting announcement and a whirlwind tour through hybrid tables in Power BI Premium. We hope that you can take advantage of this groundbreaking new capability in your enterprise BI solutions to deliver blazing fast report performance to your users while at the same time incorporating the latest data changes from the data warehouse side as they occur, without having to resort to a high cadence of resource-consuming data refresh cycles. And as always, please provide us with feedback as you try out this feature. We would love to hear from you!

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement -spot_img
- Advertisement - Advertisement

Latest News

Power Fx: Error handling graduates to preview

We are thrilled to announce that the long-time experimental feature Formula-level error handling has moved forward to preview. As a result, you and your end users will enjoy higher reliability and more transparency about what is happening in your apps. It’s a huge step. Adding error handling to an existing language turned out to be a very tall order, touching almost everything, from data types, to functions, to the runtime. Thank you for all of your support, feedback, and patience as we got this done. What does it mean for you? Your apps will more reliably detect and report errors.You can write blank/null values to a database.You can detect and replace errors with the IsError, IsErrorOrBlank, and IfError functions.You can control error reporting and logging at a central location with App.OnError.You can create and throw your own custom errors with the Error function. Error handling is a big change in behavior. By entering preview, we are signaling that we believe we are done, that we anticipate no further significant changes from here. Many of you already use error handling in production and this move to preview should only embolden more of you to do so. If significant changes are needed from here, we will treat them as a separate feature. We are rolling this out slowly as it is such a big change. All of you will soon see that the Formula-level error handling switch has moved from experimental to preview in the settings (as of version 3.22082). It will still be default to off for most tenants. Over the coming weeks we will slowly change the default for new apps only to on across the tenants. Makers can still disable this feature and will be able to do so for a long time. I say again: we are changing the default for new apps only. Existing apps will continue running as they always have. We have no plans at this time to turn this on for existing apps, and as this is such a big change, we may never do this and make this a permanently available switch. Your feedback will guide us. The documentation for Error, IfError, IsError, IsErrorOrBlank functions and the App.OnError property covers these changes. IfError and IsError are very similar to their Excel counterparts. We are also working on overview docs that will be released shortly. But before that, let’s take a brief tour. Let’s start with what Excel does, the inspiration for Power Fx. For an error like division by zero, Excel is very clear that something has gone wrong with a # error message that shows right in the cell. This error will propagate to other cell formulas if A1 is used in a formula: Today, without error handling, Power Apps won’t report anything in this scenario, instead treating the division by zero error as a blank value. That’s not good, as the maker and the end user of the app have no idea something may have gone wrong: Errors happen. Unexpected data flows in, networks go down, storage fills up, to name just a few situations that an app may encounter in the real world. Makers don’t often think through all the ways that things can go sideways which makes default error handling even more important. Returning a blank for an error is also a problem because blank is a legitimate value in our type system and in many databases. Without error handling, Power Apps won’t allow you to write a blank to a database instead thinking it is an error. So, instead of returning an easy to ignore or misinterpret blank value, with error handling turned on we now report an error to the end user (the error banner) and show the formula as having an error to the maker (the red filled in circle on the control): Further, if you look at the value of the formula, it is not a blank but an error value. Just as any formula can result in a blank, now any formula can also result in an error: Now, we still aren’t showing an error in the label control itself as Excel does. We couldn’t do this generically because, unlike Excel, the error could be on a property of a control for which there is no way to display the error. For example, where should an error on a slider control? Where should an error be shown for an imperative operation in the middle of a button’s OnSelect formula? We settled on showing the end user banner and flagging the control in the design experience. That’s not to say you can’t detect and display an error in that label control. Error handling provides a wealth of mechanisms to control how errors are handled and reported. For example in this case, we can wrap the division by zero with an IfError function to return a custom message in the label: The Text function call is required for type compatibility. Or we can use IfError to throw a different, more specific error with the Error function: Or we can have a catchall for all errors in the app with App.OnError. For example, we can log the error and present a different message to the end user: If we look at the log, we see the details of the captured error from FirstError (and there is also an AllErrors), including where it happened and when it was detected: The possibilities are endless! You now have all the tools you need to detect, replace, report, and log errors, including a good default behavior if you never take advantage of these tools. And, bonus, you can also now write blank (or null) values to databases. Please let us know what you think in the Power Apps community forum. There is a dedicated and active space for error handling discussions at Error Handling – Power Platform Community (microsoft.com).

More Articles Like This

- Advertisement -spot_img