A friend asked me about automating their processes for providing financial information. It was all done in Excel and the input of data would take at least an hour a day, every day. Then there was the reporting. There are at least 3 major elements that we needed to consider - the data input, the data store and the reporting. Even just using SQL Server as the data store, replacing Excel, is a significant change - let alone getting data in and out of SQL Server.

So I decided to prototype a solution using some market data that I had been maintaining and charting using Excel. In my learning of Power BI, I had been using this as a datasource to create visualisations in Power BI. So this was the opportunity to actually use Power BI with SQL Server.

So in summary we needed to get data from web sites, put this data into SQL Server and then report form SQL Server all using Power BI Desktop.

These are the steps that I created:

1. In Power BI get data from web site 1 and import into the first table. 2. Export that data into a export staging table in SQL Server. 3. In Power BI get data from web site 2 and import into the second table. 4. Export that data into a second export staging table in SQL Server. 5. In Power BI execute a SQL Server stored procedure (R Script). This will insert data (populate) into other tables from the staging tables. 6. In SQL Server the insert daily stored procedure from Step 5 does: a) Waits for 45 seconds to make sure previous steps has finished b) Gets data from the first export table (Step 2) and then the second export table (Step 4) and then inserts this data into a single daily table. c) Export tables are truncated afterwards. d) Trigger on the single daily table then executes stored procedures to transform and insert data into two aggregation tables. 7. In Power BI Visualisation for first aggregation table is refreshed from table in Step 6(d). 8. In Power BI Visualisation for second aggregation table is refreshed from table in Step 6(d). 9. In Power BI publish (if required).

Now all I have to do is to open the pbix file and refresh that data and everything gets created.

If only there was a way to schedule this data refresh then it would be truly automated.