Dashboarding with Power BI
Power BI is as the name implies a powerful Business Intelligence platform which specializes in the quick and easy development of reporting dashboards. Building upon foundations laid down in Microsoft Office, both the interface and formula syntax are immediately familiar. When coupled with a powerful analysis engine to do most of the heavy lifting the result is a capability that anyone reasonably proficient with Microsoft Excel can pick and start using in no time at all. By lowering the complexity of development Power BI allows subject matter experts to interact directly with the data, generate insights that cut straight to the heart of what's important and share them with their colleagues.
Let’s make it real
Common to both we agreed on the same start and end point.
Start point - a publicly available dataset on Kaggle (video game sales)
End point - a dashboard page with 2 sliders, 11 KPI, a single table and a single chart
We took two completely different journeys. In this article, I'll go into steps used to create a dashboard with Power BI, for a comparison with R Shiny there are two companion articles:
Dashboarding with Power BI vs Shiny (link coming soon)
Dashboarding with R Shiny - steps for creating a R Shiny dashboard
Stuart's journey - Power BI
The steps I took in building the Power BI dashboard included
Create a new Power BI Desktop workbook
Define connection to data source
Define data transformations
Choose visualization plugins
Layout data for presentation
Publish (paid powerbi.com account)
With Power BI getting from data to dashboard is quick, easy and generally an exercise for an afternoon rather than a drawn out development project. With a wide range of built in data source connections, an extensive and ever growing library of visualizations and the awesome power of Microsoft’s analytical data engine (Vertipaq) most of the hard work is done for you so you’re free to get on with exploring your data and laying it out to tell a story.
Once you’ve published a report there are a lot of ways to reuse the data-set published along with it directly from powerbi.com, but when getting started from scratch Power BI Desktop is the place to begin.
With Power BI Desktop you get a Swiss army knife for transforming raw data to Business Insights. Starting with its impressively extensive suite of options to connect to source data, it goes on to provide an easy to use but highly flexible mechanism for transforming data and a click & drag GUI for laying it all out for presentation. Built upon the foundations established in other Microsoft mainstays of data analysis Excel and Microsoft Analysis Services, Power BI’s is immediately familiar and intuitive to use all the way down to DAX (Data Analysis Expressions), the language used for building custom calculations.
Connecting to source data
Saying that the range of data sources Power BI can connect to out of the box is extensive feels like a bit of an understatement. Along with support for most of the major database types and file formats you might be using there is a whole suite of connections to online service plus the ability to build your own with web requests or R/python scripts.
When deciding how to connect to your data source or sources there is an important decision that will impact the overall way you report functions and what reporting features will be available.
For single source reports based on a large datasets or with a requirement to display up to date data Power BI supports direct query connections to Amazon Redshift, AtScale cubes, Azure Analysis Services, Azure Data Explorer, Azure HDInsight Spark, Azure SQL Database, Azure SQL Data Warehouse, BI Connector, Dremio, Essbase, Exasol, Google BigQuery, HDInsight Interactive Query, IBM DB2, IBM Netezza, Impala, Indexima, Intersystems IRIS, Jethro ODBC, Kyligence Enterprise, MarkLogic ODBC, Oracle, PostgreSQL, Power BI datasets, QubolePresto, SAP Business Warehouse, SAP Business Warehouse Server, SAP HANA, Spark, SQL Server, SQL Server Analysis Services, Teradata, Vertica. When creating reports in this manner your options for data transformation are a bit more limited, so you will want most of the required transformations and optimizations to have already been done in the source system.
For other kinds of reporting and the one used in this article the source data is either loaded once when a report is authored or configured to refresh on a schedule. These kinds of reports are where Power BI can do some of its most impressive work. You can load up data from multiple sources and integrate them together through a range of complex transformations but still end up with a highly responsive user experience thanks to the analytical data engine used for storing the resulting data model.
Check out the full list of Power BI data sources for more info on what you can connect to and what kinds of query connectivity is supported.
With data sources defined next up is data transformation. Power BI provides a gui for defining rules for your data to flow through making restructuring and reformatting your data a trivial process and potentially free of any need to cut code. At the same time, the transformation steps defined are all power-query statements so there is still a great deal of freedom and flexibility available by coding steps manually should you have something more advanced in mind. Simplicity here also translates to reliability, ease of support and clarity for what data transformations have been applied. For one dashboard this may not be so important, but as things start to scale and the number of developers grows these factors quickly ramp up to mission critical.
On top of the standard visualization that comes built into Power BI, more than enough to do the job by themselves, Microsoft AppSource has a wide range of additional visualizations you can add in (287 at last count) to help your data tell a story. In this instance I made use of the add-ins “Violin Plot”, “Radar Chart”, “Table Heatmap” and “Tassels Parallel Sets Slicer”.
Layout data for presentation
So by now you're probably only about 30 minutes in and already you have all the foundations sorted with all that's left is to layout your data for presentation. In Power BI this too is just a matter of click and drag. Select your visualization and drag into place. Select your columns from your data-source and drag them into the slots available for them in your selected visualization. Filtering interactions between visualizations happen automatically and are trivial to reconfigure. All the plumbing is taken care of leaving you free to focus on what is most important, selecting the right information to present and the best way to display it for maximum clarity. In the demonstration app this is where I spent the bulk of my 4 hour development time as I experimented with different options visualizations/layouts to use, but with a clear idea of the target state you shooting for another 30 mins might be all you’ll need.
Once you’ve built something you’re happy with it's time to share. Sharing in Power BI has a clear focus on the business users and the sharing reports/dashboards with users / business units across an organization. To share a dashboard it needs to be published to a shared workspace with access to the report determined initially by which users are also members of that workspace. Content/views of specific interest can be subscribed to for regular updates, directly shared via teams / email, or even discussed in app alongside the report content itself.
If published to premium dedicated capacity the requirement for both users to have Pro accounts is lifted, making options for sharing are much more flexible. Any users with a free account can potentially be granted access to shared content provided and organizations data privacy policies allow for it, and content can also be shared to the internet at large as content embedded as part of a standalone website.
Along with just sharing the report you’ve built, the data model built when importing and transforming your data is also published to the same workspace. This means that you and your colleagues don't need to go back into Power BI desktop to generate more insights off the data-set you’ve built, it can be done in-browser using the same data (with the same business rules applied!!). Datasets published to powerbi.com can also be run through its Quick Insights pattern analysis algorithms to produce a collection of visualizations highlighting patterns and anomalies identified within your data-set, a great way to find insights you had not thought to look for.
At the same time the strong focus on data security and privacy ensures access is limited only to users with appropriate levels of access to a dataset's various data sources as defined by privacy levels specified at a data connections initial configuration.