#Free YouTube Series + Getting Started Article Below!
4) Group By, Big Data and DAX Studio
5) Preview Features and Weekday Sorting
#Power BI - An Introduction
The market offers a broad range of Business Intelligence (BI) tools with different features, pricing models and application areas. In case you decided to work with one of these BI tools, you might soon hit the point where you start asking yourself: Which tool should I choose?
This article takes a closer look at Power BI, the BI tool of Microsoft.
I will give you an overview of Power BI in general, the different tools it includes and how these tools are connected. Additionally, we will take a look at selected advanced features Power BI offers and compare the different versions - Free, Pro and Premium - with each other.
#Which Tools do we have?
Starting with the most important concept it is crucial to understand that Power BI is not one single tool but a suite of connected business analysis tools. The core tools of Power BI are:
- Power BI Desktop
- Power BI Service
- Power BI Mobile
The goal of Power BI is to provide tools that allow you to work on your data, create visualizations and share your results with other people. Before we dive deeper into these single tools, let’s first understand the basic concept and by that the general idea behind Power BI.
#Power BI Desktop
Before creating beautiful Visualizations and thinking about ways to publish our data to Power BI Service, we need to build the base first. In the world of data analysis this is of course the creation of a dataset. In Power BI, the Desktop application is the tool to use when it comes to connecting Power BI to different data sources and to create individual datasets based on the information in these source files. In addition to that, the application can also be connected to predefined datasets.
This sounds like a lot of features and thus might be confusing for somebody who never used this tool before. For a better understanding of these functions, taking a closer look at the workflow of Power BI Desktop might be a big help to bring some structure into it.
The first step in Power BI Desktop is performed in a separate tool which is directly integrated into the Desktop application – The Query Editor. As the name says, with this tool Power BI can be connected to a broad range of data sources using queries. Queries are not limited to a single source type, so it is possible to combine multiple data from different sources, for example from a .csv file and a SQL-Database for example.
No matter if you ever heard of this language before or not, the overall interface of the Query Editor is designed in a way that also guides unexperienced users smoothly through the multiple functions available and by that allows people to gain first results within a small amount of time. The functionality covers a broad range of data cleaning and shaping options and includes various editing functions for rows and columns, the combination of queries or of two or more columns and a lot more.
For a business user, the Query Editor is a real powerful tool that even allows you to create your own data model including a specific data model schema. Additional load options further help to specify what information should be loaded into the data model after finishing the data preparation procedures and what part of the information is supposed to remain in the Query Editor to keep the model well structured and to increase overall performance.
Data Modelling
The actual data model is different in many ways when comparing it to the Query Editor. Starting with the actual purpose which is now the work on the real data model over the interface which now consists of 3 different views (the report view, the data view and the relationship view) up to the formula language. In contrast to the Query Editor, the data model uses the DAX language, an expression language that might remind you of Excel in certain aspects. But before we start taking a closer look at how we can apply formulas to our data model, the primary focus should be on another topic first.
Relationships
The quality of a data model is highly dependent on a well-thought structure. This structure can include multiple tables, which might be required to “communicate” with each other. In the world of data, this communication can be generated via relationships. In Power BI you can create all relevant types of relationships, so One-to-One, One-to-Many and Many-to-Many in the relationship view.
And a second table named ORDERS with orders in an online store for example. This second table also has one column with the customer ID to allocate the order to a specific customer and other columns which include the order ID and the products in the corresponding orders for example. Additional information about the customer is not included in that table.
To allocate an order to a specific customer with the required information like name, address, etc. the ID columns of both tables must be connected. As soon as an order is placed, this order becomes part of the ORDER table and is allocated in this table to a customer ID. But additional information about this customer is required, and this information is stored in the CUSTOMER table. In this case, the creation of a many-to-one relationship (one customer can have multiple orders, but exists only once with a unique ID in the CUSTOMER table) between the ID columns of the two tables establishes this communication path and by that increases the capabilities of our model. This is one application area of relationships, which can be easily created in Power BI Desktop.
Calculated Columns
A well prepared data model of course does not solely rely on different tables which are connected via relationships to each other. The analysis of complex data often requires tailored calculations and formulas to be added to the different tables to extract important information out of the data. For this purpose, Power BI Desktop offers two different functions: Calculated Columns and Measures, both being based on the DAX language.
Based on this formula, a new (calculated) column would be created. Of course there are multiple formulas to create Calculated Columns available in Power BI Desktop, the important thing to note is that Calculated Columns always refer to each row of a specific column and thus are always calculated – and by that consume additional memory.
Measures
In contrast to Calculated Columns, we can also create formulas that only result in one single or an aggregated value and therefore are not calculated for each single row of a specific column. These formulas are called Measures in Power BI Desktop. An example for a measure would be the average of all values in one column in a table. As this formula only calculates one single number as a result, it is not calculated and displayed for each single row of a column and further only executed when used in a visualization, so in a specific chart for example.
The combination of Calculated Columns and Measures allows us to create a data model exactly specified to our needs. It you want to get a general overview of the syntax and the number of DAX functions available, make sure to have a look at the official documentation official documentation which gives you a great idea of the various DAX function references available.
Reports
After finishing our data-related work in the relationship view and in the data view, it’s time to bring some live into our data using the third and last view: The report view. In this view, you can create reports in Power BI. A report simply comprises all the visualizations created in your project. Such a report can consist of a single single or of multiple pages, with each page having the same predefined space where you can place your visualizations just as you prefer.
To bring the data to live, you can simply drag-and-drop the corresponding values, so basically the columns of your data model, into the specific fields of a visualization. Additionally, previously created Measures can also be dragged into a chart to present an aggregated result of a specific calculation in that visualization.
Although the visualizations are pre-defined, they include multiple formatting options, which allow the user to customize the way the charts and tables should be displayed. Additionally, different filter types can be applied, for example to limit the number of years being displayed in a single visualization, on one single page of the report or within the entire report.
Finally, all charts have a click-in feature which also affects the data displayed in other visualizations depending on the settings made for these interactions. Depending on that settings, selecting one specific year in one visualization, would also change the information displayed on other visualizations, which then also only display the values for this year.
#From Local to Global
With the report, the local work in Power BI Desktop is finished and thus we could stop right here. But as Power BI consist of multiple tools, now it’s time to understand why we might need additional tools at this stage. The following illustration presents an overview of additional ways how to go on right now, assuming that keeping our data local is not our goal:
#Power BI Desktop in Action
#Power BI Service
The general idea behind Power BI Service is to share your projects, to collaborate in them and to add one new element to your project: Dashboards. Before we take a look at these single features, it is important to understand that Power BI Service is not limited to Windows only. The only thing required to create an account is a work or school E-Mail address.
After creating the Power BI account you are ready to start with Power BI Free and start the work in your workspace, the area in Power BI Service that is linked to your account and where all information is stored. As the name indicates, the Free version of Power BI does not cost you a single cent. In addition to that, two additional Power BI versions are available which are not for free. We’ll take a quick look at the differences between these three version later in this article.
Let’s assume we want to import our locally created dataset and the corresponding report into Power BI Service. One way would be to use the publish function, which is available in Power BI Desktop and which simply exports the data, so the dataset and the report, to Power BI Service. Alternatively we could use a function named “Get Data” in Power BI Service which allows us to import data from Power BI Desktop. No matter what we decide to do – The result is the same, with both functions we are able to load our data into Power BI Service.
But if we change the information in Power BI Desktop afterwards, these changes won’t be applied to the data in Power BI Service, as the data is only imported but not dynamically connected to Power BI Desktop. This requires another feature to be implemented.
Gateways
A dynamic link and by that a so-called scheduled refresh can be established using gateways. In case the Power BI Desktop file is located on a personal computer, a personal gateway must be installed, for server based solutions, an on-premises gateway is required. No matter which gateway we select, both solutions create a dynamic connection between the source data and Power BI Service and by that can ensure that our data is always up to date, depending on the scheduled refresh settings defined.
Dashboards
We ensured that our data is always up-to-date, now one further element can be added to our projects: Dashboards. These are only available in Power BI Service and represent one single page including all relevant visualizations of one single or of multiple reports. These visualizations are all based on the reports that you loaded into or created in Power BI Service in your workspace. By clicking into a visualization in the dashboard, the user is redirected to the underlying report and therefore can see the additional information and the underlying dataset.
Each Power BI account has its own workspace, but if our goal is to collaborate with colleagues in our project, then we can create an app workspace. This workspace has generally the same functionalities as the user’s home workspace, but additionally allows all people that have the corresponding rights, which can of course be specified in this app workspace, to simultaneously work on the projects that are loaded into this app workspace. It is possible to create multiple app workspaces for the same or for different groups, which can be a big help in terms of project organization.
In addition to that collaboration, app workspaces offer one additional feature after the actual work in the project is completed.
Apps
The content created in an app workspace can be shared with other people that are not part of that specific workspace by creating and publishing an app. This app, simply contains all information of the app workspace, so all datasets, reports and dashboards. When publishing the app, it can be specified which people in your organization are allowed to view the information in that app. And that’s the important difference: Users of an app workspace can edit the content (depending on the settings of course) but people who just have access to the app, can only view the information in it but are not allowed to change the included data, reports or dashboards.
#Power BI Mobile
With Power BI Service, our work, so the creation of dashboards and reports is generally finished. We know that we can share these results using apps directly in Power BI Service with other Power BI Service users in our organization. But this only refers to people who are able to log-in to Power BI Service from their computer. But sometimes it might be required to be able to access data from mobile devices. For this purpose, Power BI Mobile was created, an app that can be downloaded for free for Apple, Android and Windows devices in the corresponding app stores.
After downloading and connecting the app to your Power BI account, you can access your workspaces and your shared apps in it. The data that can be accessed include all dashboards and reports, which can also be shared with other people in the app, but does not include the data in the initial dataset.
#Additional Features
For developers or generally people with an increased focus on customization and some programming knowledge, Power BI Developer is an additional tool of Power BI universe. Two features to be named are the option to create customized visuals and to embed your Power BI projects into your own apps using Power BI Embedded.
For the customized visualizations, TypeScript, a programming language, is required so this might not be the core function to use if you work with Power BI Desktop for the first time. This is probably also true for Power BI Embedded, as this requires your own app to display the information created in Power BI, which again might not be available for the normal business user.
But the pure existence of such features, gives a first impression of the broad range of user interest Power BI tries to cover and additionally allows users to further tailor their Power BI experience if they want to.
#Power BI: Free vs Pro vs Premium
If you want to try out Power BI on your own, you can perfectly do that using the Free version which generally comes with all relevant features. As soon as you plan to put your emphasis on dynamic data updates using gateways with scheduled refresh times or if you want to create app workspaces, then Power BI Pro is required as those features are only available to full extent in this Pro version. An overview of the pricing can be found right here. In case you want to try the features Power BI Pro ships with, a 60-day trial version with full functionality is available. Just follow the instruction on this page to activate the trial version.
In addition to Power BI Free and Pro, Power BI Premium was launched in 2017. The target group are companies with the goal to implement large scale BI applications, making this version not the relevant one if you just start using Power BI. However, in case you plan to integrate Power BI in such a large scale environment, this version might me the one to consider in case Power BI Pro hits reaches its limits.
#Outlook
Being available to the public since July 2015, Power BI still receives constant updates. Microsoft provides detailed information about the latest updates and changes in this blog. Power BI Desktop also includes the option to activate different Preview features which are not final yet but can be tried out already in the application. So in case you currently miss certain functionalities in Power BI, chances are given that these features might be added in the near future.
#Is Power BI YOUR Tool?
The honest answer is: It depends. As said in the beginning of this article, the market offers a huge amount of different BI tools so in the end it’s up to you to decide which tool is the right one. My personal point of view is that Power BI is still a young tool, but already allows you to do great things with your data. The different tools might be deterrent when first using Power BI, but as soon as you understand the general workflow, the majority of the functions is relatively self-explaining. This does of course not include the advanced topics: Becoming a real expert in DAX or in the features offered by Power BI Embedded, increases the complexity and amount of work required. But these features are not necessarily the ones used on expert level for business users and thus do not change my overall judgment of Power BI being a great Business Intelligence tool definitely worth taking a closer look at.
I hope this article helped you to get a more detailed view into the Power BI world, into the different tools, their features and the way they interact.