Storage | MS Excel or MS Access

[ovetteabejuela]

Hi,

Given a scenario where I only have two options to store data

    Multiple Excel Workbooks  or One(1) Access Database

Also, let's say potentially it could reach just around 10 million records, would it make a difference in terms of query time.

Would it be faster to load 10 million records from an Access database versus 10 million records coming from multiple workbooks?

I'm asking because I'm building a structure and I want to streamline a process.

Users would definitely submit their data throught Excel, I just don't know if it's worth it to convert those or collate those in an Access database before actually loading into PowerBI.

Guide please, thank you.

[zlokesh]

Hi @ovetteabejuela, You are right its always good to access data from Single source instead of multiple workbooks. Here i will suggest write the package in SSIS to convert MS-Excel to MS-Access and use the same Access file.

In SSIS select MS Excel as SOURCE and MS-Access as Destination.

If this help you Please "Accept as Solution" else let me know. Thanks.

[ovetteabejuela]

Wait, SSIS... you need SQL server for that right? Is it present in MS Access?

[zlokesh]

Yes @ovetteabejuela definately it requires. Another way is to conver the same as below link

https://support.office.com/en-us/article/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106...

[ovetteabejuela]

Yes, I'm simply refering to this method as I don't have access to SSIS, for a moment there I thought there is an SSIS package within MS Access... hahaha.

What I really trying to get an answer from is would it help to convert Excel data to access prior to loading into PowerBI

so it's like 

Multiple Excel Workbook > Power Query (PowerBI)

versus

Multiple Excel Workbook > MS Access > Power Query (PowerBI)

But i think you initially favored the latter on your first answer.

[Anonymous]

I do not have much experiance with using access with Power BI. My gut feeling is that access will be faster to read than excel spreadsheets. However, the excel spreadsheets if there are multiple of them might parrelize better.

I am assuming you are concerned about pwoer query performance as once the data is loaded into the model it doesnt matter where it comes from in terms of performance as it is all in memory at that point.

The only way to say for sure is to do some testing. If I had this problem I would manually test it and time how long each query took using this method. 

https://blog.crossjoin.co.uk/2016/04/04/timing-m-query-execution-in-power-query-and-power-bi-revisit...

Make sure you use data with a similar number of rows and columns and the same cardinality/sorting as what your final production process will use.