Lesson 1: Let’s talk about ….. Microsoft Power BI by Nicola Filosi

Microsoft Power BI is, generally speaking, a Business Analytics platform provided by Microsoft. Specifically, it is a data visualization tool: its main purpose is creating automated, interactive, user-friendly reports and dashboards. Through it, users can digest huge amounts of data in just a few minutes. It was first released to the general public on July the 24th 2015, just five years ago. From now on, we will refer to it only as PBI.

But what do we mean when we say Business Analytics and Data Visualization?

Business Analytics is the set of skills, technologies, practices, for continuous investigation of past business performance. Its purpose is gaining insight and driving business-related decision-making processes through very specific methods and tools.

Data Visualization is both an art and a science: it is the graphic representation of data. Its purpose is communicating information efficiently and making complex data more accessible and digestible through the use of graphical measures, graphics, chart, and customized visuals. Especially to a non-technical audience.

And why are we talking about PBI specifically?

According to Gartner (one of the world’s leading research and advisory company) Magic Quadrant regarding Analytics and Business Intelligence Platform, PBI is today the leading Business Analytics and Data Visualization tool worldwide. You can download the Gartner full report at the following link: bit.ly/2ZjIMmW.

 
bi.jpg
 

PBI has endless potentialities and amazing features. It exists on many levels: on premises, cloud, enterprise.

  • PBI Desktop (on-premises): the segment of the PBI galaxy we will explore. It is the starting point of each user, either basic or advanced, who wish to design either a report or a dashboard with PBI;

  • PBI Service (cloud): the portion of the PBI galaxy designed specifically to improve reports portability and to foster collaboration between users;

  • PBI Premium (enterprise): the portion of the PBI galaxy built to meet the expectations of the biggest companies and organization. A very important feature here is PBI Report Server.

In order to build reports and dashboards, PBI relies on a specific process on three levels. Let’s go through it.

1) The PBI ETL process: Power Query

First of all, PBI needs at least a data source. “At least” because it can actually get data from potentially an endless number of data sources at the same time. With data source we mean a place, a box, from which PBI can absorb the data it needs to start. A data source can take many forms and names: Microsoft SQL Server, Oracle Database, Teradata Database, and many more. Sometimes, even a properly structured CSV file or Excel file will do. To absorb that data, PBI relies on a tool called Power Query. Power Query is PBI own ETL tool: ETL stands for Extract, Transform, and Load.

Extract stands for the physical extraction of the data from the chosen data source. If the content of the data source is not yet ready to be loaded in the PBI data model, for example, because it does not satisfy the needs of the business, it is important to make sure we can prepare it accordingly. And that is called Transform. The last phase, Load, stands for the physical loading of the data into PBI data model. As a side note, Power Query runs with its own functional language: the Mashup language (also known as M language).

2)  The PBI data model: Tabular Models

Once the data gets Extracted and Transformed, it is Loaded into PBI data model. The PBI data model is nothing more than a collection of tables related to each other, to which we apply several features. In particular, the PBI data model is called Tabular Model. The tables which make up the data model must be structured in a columnar form: simply put, they must resemble a collection of records. A record is a row divided in several columns, and every column represents a specific information. For example: Customer Name, City Name, Quantity Amount, Gross Revenues Amount.

Through the PBI data model , we can add several features to the collection of related tables extracted from one or more data source. At this stage, its main purpose is allowing us to apply calculations in order to support properly our reports and dashboards. Those calculations are called Calculated Measure. They are nothing more than mathematical and statistical operations, such as sums, ratios, variances, standard deviations, and many others. They are quite important: they represent the figures shown to the end users. As a side note, calculation are finalized through the Tabular Model own functional language, called DAX.

3)  The PBI visualization level: graphical measures, graphics, chart, and customized visuals

Once the data gets through the ETL process, and once the data model is properly structured, it is possible to start designing the report of dashboard itself. This phase is actually rather intuitive and it takes place through a simple drag and drop.

Want to see a video presentation of PBI Desktop? Check out this link from Guy in A Cube: bit.ly/3h6oiUA.

We came to the end of this first introduction of what PBI is and how its internal process works. Next time, we will start delving in the first of the three phases described here above: the ETL process with Power Query.

Nicola

About the Author

Nicola Filosi, MCSE is a graduate student at the UCD Michael Smurfit Graduate Business School studying Business Analytics. He is interested in Data Analysis, Data Modelling, Data Visualisation, Reporting, Performance and Financial Analysis

 
Team ISCGComment