Thank you all for your responses. That answers my questions.
It is the processing that is taking the time I am pretty sure as I have a lot going on in power query, and also in DAX, doing row by row calculations of running totals to calculate cummulative totals that need to reset at the beginning of new events through a large table. And also needing to look up against the other tables to join the result of the before mentioned calculations against rows in another table based on whether a number of criteria, such as being within a certain date range while also being in a particular room, are matched.
The actually data pull doesn't take that long. We are going to put the enterprise gateway in our Data Centre as we move to a larger rollout, I have been using the Personal Gateway so far as we have tested the viability of Power BI for larger projects.
The main challenge using power BI is doing computations over large data sets in terms of refreshes timing out, especially as the data models grow and require more ETL to bring in different data sources. Maybe incremental data loading might help. But I also think I will need to move the analytic side of the building of the data model into a more robust system, and then use power bi to just point at already processed data, apart from where the processing is minimal in nature.
