Ideas for housing and maintaining large data sources and cross references

[Anonymous]

GOAL:  Find a process and/or application that can house large amounts of data that I can then easily access in other applications (Access, Excel, Power BI, etc.). 

My role is currently made up of three processes that use a lot of the same data from multiple sources.  Currently, all the data sources are either sent via email in an Excel or I download/export them from its source application.  This issue I'm looking for ideas on is how/what I should use to store these data sources in that I can then easily access via an excel data refresh/pivot table.  

Right now, I'm experimenting with SharePoint lists but with the size of my data sets and lists not being so conducive to changes I'm starting to wonder if there's a better route/application.  We are currently using Excel as our final stop for the data before we send out the analysis, so I like the fact that I can directly connect to the SharePoint Lists in Excel.  This problem is getting the data into the SharePoint Lists (can take hours) and then managing it (making prior changes).  

Issues

1. These data sources can be 10,000-50,000 rows of data per calendar year.  Looking at housing 2-3 rolling years for each source.

2. We pretty consistently have to make changes to the data behind us due to cross reference changes in the data.  Example - Person A no longer sells Widget 123 so we need to go back 2 months and adjust the cross reference to Person B.

3. We have to still be able to manipulate the data somewhere in the middle of its origin and its final resting place.  We have multiple cross references we manage that we use to create additional, needed columns in the data set.

[dpoggemann]

Hi @Anonymous ,

Looking at your general requirements above, I would think that Dataverse might actually be a place to store this information and then utilize a model-based application to manage the data.  You can search, do updates, etc. and track as a relational data model.  

As far as accessing with Excel, you can export the data from the application to Excel and even make updates in Excel and reload.  Check out this link discussing dynamic pivot tables and Excel Templates as well (https://powerusers.microsoft.com/t5/Microsoft-Dataverse/Who-can-explain-the-difference-between-export-to-a-dynamic/td-p/1272494) 

You could setup dataflows to import the data into the Dataverse from Excel or manually import as well through Excel or CSV imports.