• Adam Thurgar

Power BI Desktop prototype


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.


3 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t