Loading Large XML Files

[Doc]

Hi

I'm trying to load about 100GB of XML files which are split into about 400 XML files of 250MB each.

With some patience, I am able to query the data and build the ideal fact and dimension tables. The XML file has various parent and child node relationships so the structure of the file is quite complex and requires about 15 steps each time to transform it into a dimension table. My knowledge of XML is pretty much zero to none.

The issue that I am having is when trying to load to the data model, Power BI simply cannot handle the request since it takes forever to run through all 400 files, up to a point where my PC becomes unresponsive. It needs to do this for each dimension table I created in the query editor, that is, run through the 100GB folder of files for each table.

What are the limitations here? Hardware Limitation?

What am I doing wrong?

What am I missing?

What can I do differently?

Thanks

[k2nneth]

I load the excel file which obtain more than 400k line xml data, and parse in powerbi, it really take quite long time to process. Any solution to overcome this?

[ankitpatira]

@Doc You shouldn't be loading all of that data into power bi. Imagine how long it will take you to publish and then doing refresh. You should consider using DirectQuery feature where your data is stored in data source (not imported into power bi) but power bi connects live to it and you use power bi to visualise that data. DirectQuery is specific for cases similar to yourself where importing is an issue. You can still build relations between your tables with DirectQuery.

[Doc]

@ankitpatira Thanks for the reply.

That makes perfect sense.

That is the ideal and maybe I was just trying my luck, but since we dont have the technical resources and still waiting on IT to arrange our enterprise gateway licenses, we usually try to get by with importing into Power Bi only, especially since most of our data is sitting local and not on server environment.

I guess I have no other option then.

Thanks

[ankitpatira]

@Doc

While you're waiting for IT to get licenses you can start using this feature. So once you publish report with DirectQuery to pbi service it will tell you to get Pro version and you can signup for 60 day trial there which means you can setup enterprise gateway while waiting for IT to sort out licensing.