Partners in Technology Bringing Solutions to Public Transportation
October 15, 2018
Getting Started with Data Analytics in Power BI
Microsoft Power BI is a data analytics and visualization tool that was developed in 2015 on the back of Excel Add-ins such as Power Pivot, Power Query, and Power View. Not only does it appear to be a tool that is gearing up to replace SQL Server Report Services from Microsoft, it also seeks to compete directly with desktop visualization tools and reporting modules from third-party services. Power BI aims to give data owners the “Power” to access their data to drive business making decisions through “Business Intelligence”. If you are familiar with Power BI or are looking to start using the tool for the first time, this post seeks to go beyond some of the basic features and showcase some analytics-specific aspects of the platform.
Power BI separates itself from similar tools by including powerful data modeling and transformation features. It is common practice to clean a dataset before importing it to reporting software. With Power BI, all the cleaning and transformation is done after connecting to a data source. Techniques that have traditionally required programming languages such as R or Python for data manipulation, can be done within the tool, without knowing any code. However, Power BI makes use of several programming languages for users who want to go beyond the options presented by the GUI. These languages are DAX, M, and R. DAX stands for Data Analysis Expressions and is the query language originally used in Power Pivot. It is similar to Excel formulas but contains more functions catered to relational data. M is the language behind every step in Power BI’s Query Editor and comes from Power Query in Excel. It is similar to F# and is used to build queries that mashup data. Lastly, R is a statistical analysis programming language that is a prominent part of a data scientist’s toolkit. Power BI can use an R script as a data source, transform tables in the Query Editor, and to create visuals. There are many online resources available for each programming language used in Power BI. Some helpful links can be found at the end of this post.
While the Query Editor and support for various data related programming languages make Power BI a strong tool for data analysis, there are other, simple-to-use features that can be used to generate further insights from your data. The analytics pane gives users the ability to add dynamic lines to visuals such as a constant, min, max, average, median, percentile, or forecast line. The lines available can vary for each visual, but Microsoft plans to expand the functionality.
Parameters are a way of adding a slicer to a page in your report that acts as a variable. For example, if the parameter is a percentage from 0 to 100 that represents sell-through, you can project how much revenue would be generated from any percentage using the slicer. Query Parameters are an entirely different variable that can be created inside the Query Editor and used for setting a variable when connecting to a data source. Parameters in either the reporting or data modeling side of Power BI are a great way to speed up the data cleaning process and improve functionality for the end user of the report.
Power BI has created data connectors to directly access many of the data sources you are already using. A full list of data sources can be found in the resources section below. In addition to these data connectors, Microsoft has added Power BI Apps, or Solution Templates, to their online store, AppSource. These can be accessed from the AppSource website or by selecting Get Data from the Power BI Online Service. A solution template not only includes a connection to the particular data source, but also has pre-built visuals and a working data model all-in-one. In most cases, you simply need to sign in using the log in credentials to the particular app, and a report will be automatically created. While many of the solution templates are for paid services, there are plenty for common free applications as well.
In addition to the default visualizations used to display data in reports and dashboards, Power BI also has a custom visuals section on AppSource where any developer can create and publish an open source visual to be installed on Power BI Desktop. Microsoft ensures each visual is secure before adding, but it is important to read the terms for the visuals not created by Microsoft and create a governance around said visuals for your organization. The custom visuals can also be installed directly from Power BI Desktop and offer many great features not available in vanilla Power BI. Just like parameters, utilizing custom visuals saves time and increases the functionality of the tool.
You may have heard of Power BI and are aware that it is a tool for building reports and dashboards. However, Power BI contains many unique data analytics features. Through the use of the powerful data modeling features, dynamic reference lines, report-level variables, free solution templates, free custom visuals, and more, you can go from basic pie charts and graphs to predictive modeling and actionable data insights. It is commonly stated that 70 percent of a data analyst’s time is spent cleaning data and only 30 percent is spent performing analysis. Power BI greatly decreases the time it takes to clean and model data, so you can focus on obtaining data driven insights more quickly.
R Programming – The R Bloggers website contains many great tutorials from how to install R to advanced techniques.
M Programming – The majority of M code used in Power BI is done using the GUI in the Query Editor, but Microsoft has extensive documentation on their site.
DAX Programming – If you are familiar with Excel formulas, you should be able to pick up DAX very quickly. Power BI also has extensive autofill features to help users out. Like M, Microsoft’s website is the best resource for learning how to used the functions in DAX.
Solution Templates – All available Apps are posted to Microsoft AppSource.
Custom Visuals – A full list of custom visuals can be found on Microsoft AppSource.