Forum Discussion
Is there a better solution to pulling data from CSV files located on SharePoint ?
Depends on your definition of efficient - what is the database source? Usually the way this is handled is using an ETL tool to put the data into some kind of SQL database. SharePoint will work in a pinch if you don't have a data engineer to integrate it in a data warehouse/mart/lake/whatever, but you'll start running into issues when you get into anything in the hundreds of thousands of rows or more.
The dataset sources are CSV extractions from our system saved in a SharePoint folder and then fed into Dataflows where transformations happen before connecting to PBIX.
My question is if this would be more effecient than the current set up. My resoning is that the Query Folding is not possible with CSV Files so if there's a Database between extractions and Dataflows it would speed up the process.
Current: Extraction > SharePoint Folder > Dataflows > PowerBi
Proposal: Extractions > SharePoint Folder > Database > Dataflows > PowerBI
Could a Datamart function as a Database to feed Dataflows?
I mean the source of the CSVs. Usually what people do with external systems is go:
System database -> data integration tool w/ scheduled ETL -> organizational database -> Power BI
SharePoint is an unneccessary step unless you have no other place to put it (e.g. you don't have a data engineer). The number of rows will probably drive whether it needs to go in a database or not... there will probably be performance issues with the SharePoint connector when you start getting around 100k+ rows. If you can get it in a database, you can connect directly to that - the dataflow is a bit superflous in the latter case.