Forum Discussion
Best Option for Power BI Data Repository
There are definitely plenty of ways to store data. If you are currently building reports off of a bunch of Excel files on your local hard drive, then probably the simplest approach is to save those same Excel files to the cloud (e.g. SharePoint) instead so you don't need to worry about data gateways or converting Excel to SQL tables. Not ideal for huge amounts of data (maybe setting up intermediate Power BI dataflows would help some?) but should be fine for lighter loads.
There are definitely plenty of ways to store data. If you are currently building reports off of a bunch of Excel files on your local hard drive, then probably the simplest approach is to save those same Excel files to the cloud (e.g. SharePoint) instead so you don't need to worry about data gateways or converting Excel to SQL tables. Not ideal for huge amounts of data (maybe setting up intermediate Power BI dataflows would help some?) but should be fine for lighter loads.
AlexS The problem I see with dataflows is the need to refresh the dataflow AND the dataset if looking to update a report for a new month. There is the capability to use direct query, but it can really slow down the report is what it seems (not best for end-user experience) and there is also not the option to view the data in the "Data" view (under report view). Other than that, I do like the idea of having dataflows because it makes the data already prepped and ready for someone to grab. I just have a hard time getting passed the long refresh for imported data (dataflow + dataset).
I use this approach often, and do all my transformation in dataflows, so the dataset is just reading in the output of dataflows. One of my larger models is 8 GB and takes an hour to refresh from dataflows.
