Forum Discussion
One Hybrid table with multiple sources - KQL & BigQuery
Hello,
Let me start by saying that I'm putting this question under Power BI Service Forum, but I'm not sure if it's the right place for it.
I have a requirement where data needs to be read in real time. I have two sources available for this data:
- A BigQuery table that is refreshed daily with data from yesterday.
- An API that can provide the data in real-time but is limited to 1 hour interval (Last 1 Hour).
I already built an Event Stream that has a Python notebook source that gets the data from the API. This Python code runs every 5 seconds and gets the data from the last 4 seconds. The event stream then pushes the data into a KQL database. This currently works great!
What I want to do now is the following: Import historical data until yesterday from Bigquery, and Direct Query today's data from the real time KQL database. I want to have both results under the same table in my Power BI dataset to combine them with dimentional data and create some sort of a centralized datamart for reporting purposes.
My question is the following: Is it possible to either:
- Use Hybrid Tables (Dataflows) to Incrementally Refresh my data from BigQuery until yesterday and the rest get it from KQL in Real-Time?
- Or combine/append the data from these both sources while keeping the mixed direct query/import aspect of it?
The first scenario would be preferably for maintainability reasons.
If neither one of these scenarios is possible, what architecture/tips would you recommend to both have real-time data from an API and historical data from BigQuery and be able to combine them into one table/dataflow to be used in a centralized dataset?
In advance, thank you very much for any help.
5 Replies
Hi ramiouaness
Both options can be achieved in Power BI
https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models
Hello Amine,
Thank you for the reply.
However, your link explains the composite model where we would have two queries, one that's in direct query and the other in import mode. What I need is for the same table to be hybrid. Meaning that the same table would take today's data in direct query from KQL and older data up til yesterday from BigQuery.
What do you mean by same Table?
You can create 2 Dataset from each source and then combine the 2 into 1 or you can merge the 2 queries in the composite dataset
