Implementing BI Reporting using Power BI, SQL Server and Linked Servers

The Issue

The client was running their Finance, Sales, Purchasing, Stock and Manufacturing on a system with an open source database (Firebird) at the backend. Interaction with the database was via command line and the client struggled to get meaningful and timeous information out of the database, it could take a week to get the information required.

Client Requirement

The Client wanted to dramatically increase the availability and speed in providing Business critical information to the relevant Stakeholders. They were open to suggestions on the quickest and best way forward.

Solution

A decision was made to initially use Power BI as the reporting tool as the client already had Office 365 and to install SQL Server Express to provide the data for reporting, this provided a cheap and nearly cost-free option to introduce the client to the potential of BI reporting.

As the data resided on an Open Source Database (Firebird) a Linked Server was set up in SQL Server to load the relevant data into a staging database on SQL Server on a daily basis, we still have potential issues with data volumes and this is being reviewed with a view to doing daily incremental loads and a full rebuild over the weekend. This data would then be transformed in SQL Server to provide a Presentation Layer to be used for reporting in Power BI.

See below for a Dashboard created for the client (Please allow a few seconds for the interactive screens to load).

If the graphs do not appear (could be due to anti-virus software, firewalls or Power BI Web issues) please scroll to the bottom of the page to download a pdf version of the Reports.

2018-09-06T07:02:08+00:00