This week I modeled a database. The client provided a series of reports. I went through each of the reports. Listed the fields. Determined if they overlapped. Put them into buckets of things or measures.
The things became the dimension tables.
The measures became the fact tables.
Within a short time I had a bus matrix, which connects the dims with the facts by fields.
And that took less than a day, based on a limited subset of reports.
So based on the quantity of reports the client decides they want on the first phase, that will determine which tables go into the Data Warehouse. Which means they'll need to be mapped back to the source system. Extracted, transformed and Loaded.
In addition, I had a few meetings with the software vendors who host the data in the cloud. One company offers an ODBC Client which you can download and VPN into.
The other client offers connectivity through Web Services. Luckily, Microsoft SSIS can connect with a standard component, with credentials.
Perhaps ours solution can be hosted in Azure VPN, with Active Directory. And the reports in PowerBI is definitely a possibility.
So that would be a fun project. We'll see what develops~!