How to store a large amount of data through push dataset PowerBI?

[cte_crest]

Hi everyone, 

I have a use case in which my client usually generates 2.5 Million rows of data per day. I am trying to fulfil this use case by using Power BI rest API and push data set. However, while doing a POC, I stumbled upon these limitations for Power BI API:

https://docs.microsoft.com/en-us/power-bi/developer/automation/api-rest-api-limitations

According to this, I can only add a maximum of 5 Million rows to a table (None data retention policy), and in addition, the maximum number of tables for a particular data set is limited to 75 tables.

Now, in such a scenario, the limit will be reached quickly for my use case. So, apart from this, is there any way I can fulfil this use case? Also, does the same limitations apply to Power BI premium also? Or, these limitations only apply to pro users?

I am deep into this and any help would be highly appreciated.

[GilbertQ · Accepted solution]

Hi there

Your data will always have to be stored somewhere after it is generated.

You could store it in Azure Blob Storage (Which is a data lake) and then take it from there and then use Azure Analysis Services to report on the data?

I would suggest storing a large amount of data like that in some database. It will make it a lot easier to query and aggregate too.

[TomMartens · Accepted solution]

Hey @cte_crest ,

as @GilbertQ already mentioned, use Azure Event Hub to "capture" the events. Then you can use Azure Stream Analytics to process these events.

Azure Stream Anayltics (https://azure.microsoft.com/en-us/services/stream-analytics/?&ef_id=CjwKCAjwqJ_1BRBZEiwAv73uwKwzEB0_...) is able to feed more than 1 data sink from one event

• a live/streaming data set in Power BI, that represents the "hot" data, meaning what's going on "right now"
• a 2nd Streaming Analytics query can push the event data, e.g. into a SQL Server database on Azure
  this will allow batch processing, and can be used to create a more sophisticated data model

Hopefully, this provides some additional ideas.

Personally, I'm very satisfied using these components, to monitor "fast" data, and also provide deep analytical capabilities on the complete data over time, 

[v-eachen-msft]

Hi @cte_crest ,

You can register an instance of Azure Analysis Services and build a model in it to reduce costs. You can then make a real-time connection from Power BI to the model without incurring large overhead. 

[cte_crest]

@v-eachen-msft 

I am really thankful for your suggestion.

So, does that mean that given the volume of data, instead of storing the data in Power BI itself (through Push dataset, or connector), it would be more favourable to store the data in Azure Analysis service?

[GilbertQ · Accepted solution]

Hi there

Your data will always have to be stored somewhere after it is generated.

You could store it in Azure Blob Storage (Which is a data lake) and then take it from there and then use Azure Analysis Services to report on the data?

I would suggest storing a large amount of data like that in some database. It will make it a lot easier to query and aggregate too.

[cte_crest]

@GilbertQ @v-eachen-msft 

Thank you very much for your reply. 

So, after the data is stored to the database, the best way to use the data would be through live connection?

Or through importing the required data and implementing the incremental refresh policy?

And, I am also not accustomed to the different products offered by Azure. So, given this use case of a large dataset, which product allows to use the maximum capabilities of Power BI?

[GilbertQ]

Hi there

For that volume of data I would be looking at Azure Event Hubs, (I know about it and not an expert on it)

Also why would then need to see all 2.5 million rows that will go by so fast you would not notice all the rows?

[cte_crest]

Hi @GilbertQ!

Thanks for suggesting Azure events hub, I will surely have a look at that.

As for the other question, we are monitoring the environment which generates data in high volume. Then the end goal is to create reports based on this data. And we are thinking to leverage PowerBI for that. 

Also, just to confirm from the answer, I assume that even in PowerBI premium, we wouldn't be able to push more than 5 Million rows in a single table. 

Also, would there be any possibility to store the data in Power BI itself, using any of the available methods? Or, it is preferable to store data somewhere else given this use case?