Can a power BI push dataset change a table schema on-the-fly?

[Anonymous]

We have been asked by a client to do some work involving pushing data into a PowerBI 'push dataset'. We have examined the API documentation, and the basic functionality appears fine.

However, we will occasionally have a need to add extra columns to the data table(s) within this push dataset. From the documentation, it seems that it is only possible to adjust a table's schema by re-creating the push dataset; this would entail dropping the entire dataset and all of its data within it; then re-creating (with an extra column), and then re-populating. https://docs.microsoft.com/en-us/rest/api/power-bi/pushdatasets/datasets_postdataset

Is this really the case, or are we misreading the documentation?

If this is the case, we are concerned that when re-populating the data, we may hit some of the data limitations listed here: https://docs.microsoft.com/en-gb/power-bi/developer/automation/api-rest-api-limitations This would mean that it may take multiple hours to re-populate the data. Does this match your expectation here also?

[dm-p · Accepted solution]

Hi @Anonymous,

The push dataset functionality is very limited in its features and can only work with its defined JSON schema. As you've observed, changing this schema will remove the historical data you have accumulated, so this feature is not suitable for more sophisticated use cases.

The advice I have been given previously is to really think about whether a streaming dataset is the solution for my requirements, and whether DirectQuery might be a better fit. 

If you want to do anything more ambitious then you should really set up some form of intermediate and more durable storage which would work with DirectQuery in Power BI - this would ingest the data and handle any logic. Then, set up a data model with DirectQuery capabilities against that particular data source.

In my case, I used a Flow to send the data to a SQL Server database table rather than the streaming dataset and then revised my model to query the table.

I've recently confirmed with MS that there haven't been any subsequent changes to the streaming dataset functionality, so the above advice holds up in terms of current state.

In the case where your schema might change over time, you might need to add some handling at the point of ingestion to deal with this. Your data model may also need some changes to adapt accordingly.

I appreciate this is not the answer you're after but will hopefully allow you to approach this from a different angle.

All the best,

Daniel