The array of tools and software that can be used to report on data and to analyse the results can appear bewildering. It helps to start by first being clear on a number of key points;
- Where is your data going to be stored?
- Who will be allowed access?
- How do you want to share the results?
- Do you want to report a set of results at fixed intervals, or do you want to look through the data for trends and insights?
- And the bottom line, how much is it likely to cost and what will be the return on investment?
Data can come from anywhere. There might already be an ‘on premise’ data warehouse, with a suitable reporting structure and carefully thought out data loads. It might just be a departmental set of Excel workbooks. Maybe the data exists in the cloud, or as a web service.
Once you have something, be it an insight or a dashboard, what is the best way of sharing it? The options available include Internet and Intranet portals, emails and shared documents.
So how does Microsoft’s Power BI fit in? At its simplest, Power BI can be downloaded as a free desktop application. Connect to a data source and then start to drag and drop data fields onto the blank dashboard to create tables and charts. There is an opportunity to join different data sources together, like SQL Server and Excel, but it’s important to remember that there must be some common data element to effectively tie them together. For example, there might be an outlet Id in either sets of data, or a common date. As an alternative to directly querying the data, it should be possible to provide a view on the data that already incorporates the relationships between tables. Or go one step further and provide a data cube as the source. Keep in mind that the data source does not have to be Microsoft-related. It can be Oracle, DB2, MySQL or a number of others well-known brands. It can be text, JSON, XML, it can be Google Analytics, Facebook or QuickBooks.
To share your results with a colleague, publish them to the online Power BI portal. From here, send them a link and they can then see what you’ve been up to. To create a portal, you will need to create a Microsoft account using a work email, but once created, it will only be visible to users who create accounts with the same email address domain.
If you envisage lots of reports (or dashboards), or different reports for different departments, then there is the facility to add groups to the portal, but this is only available with Power BI Pro. This is offered as single user licenses or as part of some of the higher tiered Office 365 subscriptions.
So what if you want to share the original data, or make sure a dashboard always has the latest data? Data files such as Excel can be shared using OneDrive or Sharepoint. On premise databases can be accessed using a suitably configured Gateway, a free download from Microsoft. Depending on how the source is queried, data is either gathered whenever a user opens the report or it can be refreshed on a schedule. Cloud sources such as Azure SQL Database require the desktop app to create, but can then be accessed, once published, from the PowerBI portal.
This is a whistle stop tour of Power BI. It is intended to help explain how it might be used, and how it might fit into an organisation. It should give you a flavour of what’s possible, and what you would need to do to integrate it into a work environment.
A great book that goes through the basics can be found at
or call us on 01635 889222 for more information.