Empowering users with the ability to analyse their own data is a worthy objective in its own right. So is enabling them to do so without having to install complex data analysis software on their own PCs. Letting users manipulate the data using a tool that they are already familiar with, i.e. Excel, is another plus point as is the ability to be able to extract the raw data they need directly from their corporate IT systems without having to get involved in networking and security issues.
Power BI does all of these, and more!
A typical Power BI dashboard is shown here:
Power BI is an Office 365 cloud-based Business Intelligence (BI) system designed to let Power users design their own reports, dashboards and charts using familiar tools based on Excel. A Power user designs the reports in Excel, publishes them to PowerBI, which is a SharePoint site, and other Power BI users interact with them as a web page. Sounds simple, and it is, but how does it actually work?
Power BI resides in the Office 365 Cloud and all computations and presentation work is done in the cloud. The raw data being analysed can also reside in the cloud, but often these data sources are large and are in IT systems that reside on premises. Power BI provides Gateway and External Data Source features that allow seamless and secure access from the cloud to on premises data sources. The Gateway supports data sources from SQL Server, Oracle, SharePoint Document Libraries and Power Query which covers most other data sources.
The gateway and data source architecture is shown in the following two diagrams:
The Excel Components in PowerBI that carry out the data analysis are:
|
 |
All these components are free add-ins to Excel. Let’s take a look at these one at a time.
PowerPivot:
Allows multiple data sources, such as a query on SQL Server and data from Oracle to be combined / joined and extra calculations added using familiar Excel syntax. PowerPivot is a cut down version of Microsoft’s Analysis Server from SQL Server. Data from PowerPivot can be used in Power View, Power Maps and Excel Pivot Tables and Charts.
The screens below show the ribbon for PowerPivot in Excel and the diagram pane used to drag and drop links between data sources.
For more details you can read my earlier blog on PowerPivot here.
Power Query:
Power Query is designed to allow a Power user to attach to data sources and carry out queries, the results of which can be inserted into Excel spreadsheets, merged with other queries or be used as a data source for PowerPivot and hence Power View and Power Maps. Power Query is essentially a point and click query tool allowing users to gradually build up a query until they get the required result. For the more advanced users it has its own query language called ‘M’.
The screens below show the ribbon for Power Query in Excel and the results of the query in an Excel sheet.
For more details you can read my earlier blog on Power Query here.
Power View (and Power Maps):
Power View presents a report design surface inside Excel and a user can create their own dashboards and reports by dragging and dropping data from PowerPivot. Power View can contain reports, charts, filters, slicers, pictures and labels. A slicer is a list of items the user can use to select filters with the mouse. The screen below shows the data sources on the right from which data is dragged. On the left is a chart with some filters. If the data contains location information then a map can be created by choosing the Map icon in the ribbon.
To create a dashboard the user would drag multiple reports, charts and slicers into the surface.
Excel:
Excel can take data directly from Power Query, or Power Pivot and just list it in tabular form. However most of the time data is displayed as Pivot Charts and Pivot Tables. Slicers are used to filter the data as shown below:
Power BI:
Once the reports have been authored in Excel the Excel workbook is uploaded to Power BI and Power BI does the rest. Users can share workbooks and can even share Power Queries. Users with just a browser can view reports.
The home page for Power BI and an example dashboard are shown below:
Q&A:
Last and not least is the Q&A feature of Power BI. This allows end users to submit English language queries to the data sources in an Excel Workbook. Power BI converts the English into a query and processes it, presenting the results in a suitable format such as a single numeric answer or a chart.
The administrator can add standard queries that answer useful questions and act as a guide to help the users devise their own queries:
Example questions are:
- What country has the largest population?
- Which religion has the largest population?
- What country has the largest GDP?
- What country has the largest GDP per Capita?
To summarise Power BI is a cloud-based Business Intelligence tool that allows Excel savvy users to create dashboards, reports, charts, maps and queries based on corporate data and publish them to the cloud for other users to browse and share.
By Geoff Ballard, Chief Technical Officer
Have a comment? Let us know below, or send an email to [email protected]
