Everyone wants real-time BI, even when it doesn’t have to be really “real time”. Today Microsoft announced General Availability of Power BI Real-Time Streaming Datasets. There are actually three types of Power BI real-time datasets, as mentioned in the documentation.
- Push – Power BI permanently stores the data, enabling historic analysis, and reports creation atop the dataset. Behind the scenes, Power BI provisions an Azure SQL instance when the dataset is created. New data is pushed into SQL. Power BI then connects to that dataset via DirectQuery. Query Refresh (sending new queries to Azure SQL to update dashboard visuals) occurs whenever data is pushed in. When you create the dataset programmatically, you can specify a retention policy (defaultRetentionPolicy setting). When defaultRetentionPolicy is set to None, the dataset accumulates data to the maximum allowed Power BI limit (currently 1 GB). When set to basicFIFO, the dataset holds up to 200,000 rows and after that older rows are pushed out when the new ones come in.
- Streaming — Power BI stores the data only in a transient cache – this means report creation, historic analysis is disabled, but in return there is consistently lower latency between when the data is pushed in and when the visuals update. The data flows into a Redis cache, and the dashboard visuals directly pull data from that Redis cache. Therefore, consider streaming datasets when you want the lowest latency (we are talking about milliseconds here) but you are limited to a few pre-defined visualizations supported by the Power BI dashboard real-time tiles. You can’t create custom reports.
- Hybrid — Hybrid datasets send data to both the “push” and “streaming” endpoints, thereby getting the benefits of both at the expense of duplicate storage.
Unless you use StreamInsight (currently, it supports only push datasets) or PubNub (supports streaming datasets), you must create the dataset programmatically using the Power BI REST APIs. Currently, you can’t use Power BI Desktop to create real-time datasets.