Storage | MS Excel or MS Access

Question

Hi,&nbsp;Given a scenario where I only have two options to store data&nbsp; &nbsp; Multiple Excel Workbooks&nbsp; or One(1) Access Database&nbsp;Also, let's say potentially it could reach just around 10 million records, would it make a difference in terms of query time.&nbsp;Would it be faster to load 10 million records from an Access database versus 10 million records coming from multiple workbooks?&nbsp;I'm asking because I'm building a structure and I want to streamline a process.&nbsp;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.&nbsp;Guide please, thank you.

zlokesh · Answer

Yes&nbsp;ovetteabejuela&nbsp;definately it requires. Another way is to conver the same as below link&nbsp;&nbsp;https://support.office.com/en-us/article/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106f78850b4

anonymous · Answer

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.&nbsp;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.&nbsp;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.&nbsp;&nbsp;https://blog.crossjoin.co.uk/2016/04/04/timing-m-query-execution-in-power-query-and-power-bi-revisited/&nbsp;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.

zlokesh · Answer

Hi&nbsp;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&nbsp;MS-Excel to MS-Access and use the same Access file.&nbsp;In SSIS select MS Excel as SOURCE and MS-Access as Destination.&nbsp;If this help you Please "Accept as Solution" else let me know. Thanks.

ovetteabejuela · Answer

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

ovetteabejuela · Answer

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.&nbsp;What I really trying to get an answer from is would it help to convert Excel data to access prior to loading into PowerBI&nbsp;so it's like&nbsp;Multiple Excel Workbook &gt; Power Query (PowerBI)versusMultiple Excel Workbook &gt; MS Access&nbsp;&gt;&nbsp;Power Query (PowerBI)&nbsp;But i think you initially favored the latter on your first answer.

Forum Discussion

Storage | MS Excel or MS Access

5 Replies

Related content

Storage trigger event

Access rights and semantic model storage capacity

OneLake storage keeps growing?

Mirrored Snowflake Database Storage

Storage increasing daily