How things are done elsewhere
This blog post is an answer to a question that I was asked, which was, "How are things done in other places?"
I'm lucky enough that I've worked in a number of places doing similar work (in fact, this was exactly what I wanted for myself).
Red Company
Red Company is an engineering consultancy that is a subsidiary of a bigger engineering/EPCM (engineer-procure-construct-manage) company. Their main asset are their consultants. They get their consultants to sell consultancy hours to their clients. Their main concern is selling hours, and winning jobs so that they can continue to sell hours. They are a few hundred people worldwide.
Tracking utilisation
This was a Power BI application that was use to enter a forecast number of hours, so that management could have visibility on how much work was ahead.
Each office had its own SharePoint List. In every List, each project was one line, and each month was one column. In each cell was a number of hours forecasted. This table arrangement was so that users can edit directly in the SharePoint List.
Power BI would combine all of these SharePoint Lists, and graph the result.
Application Power BI
There were also many separate Power BI reports which visualised data generated by Power Apps. Power Apps would write data to SharePoint Lists, and Power BI would pick up on the data.
Purple Company
Purple Company was a gas operator that ran several facilities. They had plant monitoring software. Their ERP was SAP, and Power BI could go straight into it (with the right permissions).
Teal Company
Teal Company was a government department that, at the time, had the most sophisticated business intelligence infrastructure that I had worked on in my career. It did have some jankiness in it, but parts of it were automated in a clever way. I worked in the team that handled the finance system.
There was a daily HR list of names and managers. We didn't have direct access to the the HR system, but IT had an automation that would drop a .csv file a common drive location. We would then pick that up and copy it into our "working" copy.
They had a data warehouse, and also a repository of set reports that can be downloaded in Excel. All of my work was based on Excel spreadsheets, from these reports.
There were also some Paginated Reports.
Blue Company
Blue Company is very similar to Red Company, except with more people in it, and with a bigger parent company. Even though it was a big company, knowledge and expertise was siloed. I worked in corporate, and I was the only person who worked in business intelligence in my part of the business, and I built everything from scratch.
ERP system was Oracle. There was a report repository were you can download pre-set Excel reports, given a set of parameters. All of my data was from this, in the form of Excel files.
I had to make a "data warehouse" inside Power BI. All I had were fact tables, in Excel. Initially, I splintered these off into dimension tables and fact tables, inside of Power BI. When the size of the data became too large for Power BI to handle in a timely manner, I had to write PowerShell scripts to break these up into .csv and do pre-processing in PowerShell before pulling it into Power BI.
Somewhere down the line, I had managed to snag a data source so that it could not be refreshed in Power BI Service. The data model had gotten too complicated for me to iron out which data source it was, and I never did. This meant that all refreshed needed to be done on the machine, while I had the .pbix file open. So, the routine was that every Monday, I would manually download reports, save them and rename them, run a series of PowerShell scripts in sequence, refresh Power BI Desktop, eyeball the resulting data to make sure there were no mistakes (e.g. through human error, I could have duplicated one of the source files, resulting in double the number) and then publish to the workspace.
The source data had many mistakes. There was so much that had to be force-fixed in either Power BI or PowerShell.
This process could only be done by me. I couldn't engineer it so that it was hand-over-able. The few times that I was on leave, the system fell apart and had to wait for my intervention.
When I left, the whole thing was abandoned.
There was no data warehouse with a regular export from the source database. The Finance department, however, did have an automated process where exports from the reporting system would be dumped daily into a network drive. It was essentially what I did every week, except it was automated and had the support of a full IT team.