Preparing Access Database for BI Dashboards

[Waseem]

Hello Guys

I need to create Power BI Dashboards by linking to Microsoft Access database. But before I do that, I have a challenge imporing data into Access. I have two issue in hand that I need to resolve with your help:

i.  I have large daily Excel files (around 150K rows) that I need to import in Access. I have a backlog of two years so effectively I need to start with over 700 files. I once googled the solution and imported one month files but the total size of data base went 6 times higher than collective size of excel source files.

ii. The second issue I face is that all of these excel files do not have date field. However, the file name contains the dates they were extracted on. So while importing all these files, I need to make sure that name of each file is also added as an additional field in the Access Data base.

I need experts' help urgently so that I could import data under two conditions while addressing file size constraint.

Cheers

[MattAllington · Accepted solution]

This is not really a power bi question but an Access question. RDBM systems like Access will use space depending on the data types for the columns and also the way the data is normalised. Access has a file size limit of 2GB, so unless you are going to exceed this, I suggest you don't worry too much. Once you import the data into Power BI the data will be compressed anyway. 

This is an ETL question. If it were me, I would use Power Query to load the backlog of data directly into Power BI or Excel Power Pivot. You can read my articles here http://exceleratorbi.com.au/combine-excel-workbooks-power-query-method-1/

once the data is loaded, I would then use Dax Studio to extract the consolidated table into a csv. http://exceleratorbi.com.au/getting-started-dax-studio/

Thne load the csv to Access. This solves the backload issue. You can thne load new Excel files daily to Access. Another approach is to forget access all together and simple turn off the refresh of the historical data load. Then load new data using a similar approach combining both tables into 1 large final table.