Structuring and storing big data for Power BI

[MarkStokes]

Hi all. I’m after some help and advice structuring and storing big data for reporting in Power BI.

I have an application that generates some complex object data in JSON. It’s not flat data and there are properties that contain objects. I can already load an array of this structured JSON data into Power BI from a single file in an Azure Storage container, create the custom columns and report on it in Power BI.

My concern is that this file could get very large if we have hundreds of thousands or millions of “records” (objects in the array).

What would be better for Power BI? A multi-gigabyte / terabyte JSON file? Or can I store smaller JSON files (say one per day) and have Power BI auto discover and load these files without me needing to add them to the data source?

Or, can I make my application store each individual “record” as a JSON file and have hundreds of thousand or millions of these in a storage container and get Power BI to load all these in as they are added?

I have considered Storage Tables but I don’t think I can store the complex data in the tables.

Or, any other big data suggestions that won’t escalate in cost very quickly (Azure Data Lake I’m looking at you!)

[parry2k · Accepted solution]

@MarkStokes I would do Power BI dataflows, store your daily files on blob storage and with dataflow process the files which spits out the data to Azure Data Lake and then connect Power BI with dataflow to visualize/analyze the data. I have done this many times. You will have cheaper storager and I have done this many times, not using json files but other csv/excel files. Hope this helps.

Would appreciate Kudos 🙂 if my solution helped.

[MarkStokes]

Hi @parry2k 

I am just trying to set this up as a Proof of Concept now. Does this approach require a Power BI Premium license? Or is Power BI Pro enough?

Regards,

[parry2k]

@MarkStokes Power BI pro is enough to get what you need.