Microsoft Power BI Tutorial For Beginners

Introduction

Microsoft Power BI tutorial Microsoft Power BI is a new way to transform the company’s data into rich visuals to collect and organize them in a better manner. The tutorial will help you understand and create Power BI reports and dashboards for both team members and customers.

Who is this for?

• Everyone who plays with data
• Developers, IT professionals, Subject matter experts, Business analysts, Solution architects
• Project manager, Business executives, CIO, and CEO

What is Business Intelligence (BI)? Do we really need it?

Before diving deep into Microsoft PowerBI tutorial, it is preferable to have some fundamental knowledge of Business Intelligence. Business Intelligence (BI) is a collection of methods and tools that are used to analyze raw data accumulated in various organizations. It refers to the strategies and methods used by a company for performing data analysis of any business information. The BI follows the ETL process; E- Extraction, T- Transformation, and L- Loading data into the database which also involves the process of data mining, online analytical processing, and business reporting. Organizations use a BI tool to improve the performance and operations of the company efficiently.

Read: Windows SQL Function-All you Need to Know

What is the Microsoft Power BI?

Power BI is a data analytics tool developed by Microsoft that turns your bits of unstructured data into a visually compelling story. It consists of Power BI Desktop, SaaS Power BI Service, and a range of mobile apps (iOS and Android). These tools take help from Big Data to analyze data. Power BI takes information from disparate data sources and turns them into customized visually designed sources to help an organization to not only read the information but get a clear idea of what to do.

The Power BI tool uses real-time dashboards to sort through and present data from multiple sources, giving businesses a better way to handle their customers, operations, and future.

Read: Learn SQL Union All Query Operators with Examples

The proficiency in Microsoft PowerBI allows you not just to collect the numbers, but to create real value for a business. Whether it’s a simple spreadsheet or a complicated collection of on-premises data and cloud data, Power BI sort it out easily.

The Power BI prepares data engineers and managers with the skills they needed to access Big Data and scrub it for insight. These business users are better equipped to make industrial decisions.

Why use Microsoft Power BI?

Following are the primary reasons for using Power BI tool:-

• Predefined dashboards and reports for SaaS solutions
• It allows real-time updates on the dashboard
• It offers a secure and reliable connection to the data sources in the cloud
• It offers quick deployment, secure environment, and hybrid configuration
• It allows exploration of data using natural language query
• It offers a feature for dashboard visualization regularly updated with the community

Types of Microsoft Power BI tools

• Power BI Desktop: Power BI desktop is the primary authoring and publishing tool for Power BI. Developers and power users use it to create brand new models and reports from scratch.
• Power BI service: Online Software as a Service (SaaS) where Powe Bl data models, reports, dashboards are hosted. Administration, sharing, collaboration happens in the cloud.
• Power BI Data Gateway: Power BI Data Gateway works as a bridge between the Power Bl Service and on-premise data sources like DirectQuery, Import, Live Query. Bl Admin installs it.
• Power BI Report Server: It can host paginated reports, KPIs, mobile reports, & Power Bl Desktop reports. It is updated every four months and installed/managed by the IT team. The users can modify Power Bl reports and other reports created by the development team.
• Power BI Mobile Apps: Power BI mobile app is available for iOS, Android, Windows. It can be managed using Microsoft Intune. You can use this tool to view reports and dashboards on the Power Bl Service Report Server.

Read: The Types Of Database Query And How To Use Them

Key terms used in Power BI

Term	Description
Visualization	A visual display of information to achieve one or more objective. It offers a single-screen display of information. It alerts users on issues or problems Operational, Performance, Personal, etc.
Datasets	A dataset is something which you import or connect to. Datasets can be renamed, refreshed, removes, and explored.
Dashboard	The dashboard is a collection which contains zero or more tiles and widgets. It is used to represent a customized view of some subset of the underlying datasets.
Reports	A Power BI report is one or multiple pages of visualizations. It can be created from scratch, imported to a dashboard, and created using datasets.
Tile	It a single visualization found in a report or on a rectangular dashboard box which contains each visual.

Data Sources for the Power BI

Data source	Description
Excel (.xlsx, xlxm)	A workbook can have data entered manually or data, which is queried and loaded from external data sources.
Comma Separated Value (.csv)	Files are simple text files with rows of data. Every row can contain one or more values, which is separated by a comma.
Power BI Desktop (.pbi)	You can use Power BI Desktop to query and load data from external data sources.
Databases in the Cloud	It allows you to connect live to Azure SQL Database, Azure SQL Data Warehouse, etc.
Databases on-premises	You can connect directly to SQL Server Analysis Services Relational model databases. A Power BI Enterprise Gateway is required.

Read: The Database as Part of the System Architecture

Installation of Microsoft Power BI

In this Microsoft Power BI tutorial, let us now proceed further for the installation of it! To check the system requirements, installation files detail, users have to navigate to “Advanced download options.” Following are the system requirements to download Power BI tool ?

Supported Operating Systems

• Windows 10, Windows 7, Windows 8, Windows 8.1, Windows Server 2008 R2, Windows Server 2012, Windows Server 2012 R2
• Microsoft Power BI Desktop requires Internet Explorer 9 or higher
• Microsoft Power BI Desktop is available for 32-bit (x86) and 64-bit (x64) platforms

Users can select a language in which they want to install Power BI, and the following files are available for download.

This is the link to directly download Power BI files ?

https://www.microsoft.com/en-us/download/details.aspx?id=45331

Read: What Is The Difference Between Tables And Views In SQL?

PBIDesktop_x64.msi shows a 64-bit OS file. Select the file you want to install as per OS type and click Next. Save the installation file on the local drive.

When you run the installation file, the following screen is displayed.

Accept the license agreement and follow the instructions on the screen to finish the installation.

Read: SAS Tutorial Guide for Beginners

When Power BI is installed, it launches a welcome screen. This screen is used to launch different options related to getting data, enrich the existing data models, create reports as well as publish and share reports.

Request for a demo class to master all required SQL skills.

1). Getting Started

Let us now get an idea about working with Power BI Desktop. In this section, we shall explore it a bit to get accustomed to its interface.

Power BI Workspace

The image below highlights the major components of the workspace of Power BI.

2). Basic Views

• Report View: This is the main view where the Dashboard is created.
• Data View: The data view gives a preview of the entire data.

• Relationship View: The relationship view displays the relationship between various objects.

3). Connecting to a Data Source

Power BI can be connected to several data sources. The Get Data icon displays all the possible available options from where data can be imported into Power BI.

Read: How to Become a SQL Database Administrator?

Let’s look at a few of the most commonly used data sources:

Excel data

Let’s connect to an Excel data source. The workbooks consist of some fake financial data. Power BI Desktop loads the workbook and reads its contents, and shows you the data in the file using the Navigator window.

Read: Different Types of SQL Server & SQL Database Functions

Once loaded, the data can be viewed in the Fields pane.

Web

You can also use the data from the web. Here is a dataset that presents the best and the worst states for retirement in the U.S.

Simply select Web as an option in Get Data and enter the name of the URL.

Try experimenting with other data sources too.

Read: How to Become a SQL Database Administrator?

4). Transforming Data

After the data has been loaded, it becomes visible under the Fields Tab. From here, we can modify our datasets with the help of Query Editor. Query editor can be used for modifying datasets irrespective of their data source. We can do manipulations like renaming a dataset, removing a single or multiple columns, etc. in the query editor. The Query Editor can be accessed by clicking the Edit Queries button on the Home Ribbon.

5). Creating a Custom Column

Using the same Financial data, that we used above, let’s shape data to meet our needs. Let’s create a custom column called New Manufacturing Price, which is equal to ([manufacturing Price])*3

6). Changing Column Data Types

The data types of the columns can also be changed easily. The Units Sold column has a floating-point data type that can be adjusted to a whole number.

7). Removing Columns

Removing columns is also easy. Simply select the column to be selected and choose the Remove Columns option, as shown in the following figure. Let’s get rid of the Discountcolumn as it is adding no value to our dataset.

Read: A Comprehensive SQL Server Tutorial Guide for Beginners & Experienced

Similarly, there are other multitudes of functions that can be carried out like removing and adding rows, transpose, pivot and split, which can be easily achieved through the query editor. Note that all the steps that you undertake to transform your data also appears in the Query Settings panel.

8). Reports

Reports are a collection of visualizations that can be created on one or more pages. These visualizations are usually related to one another.

9). Dashboard

A dashboard is a collection of several views, enabling one to compare a variety of data simultaneously. Whereas the report can encompass various pages, a Dashboard is a single-page interface. 

Creating a Dashboard

Once we have the dataset ready with all the manipulations done, we can proceed for the Dashboard creation process. A Power BI dashboard, also known as canvas, consists of many visualizations on a single page which helps to tell a story. These visualizations called tiles are pinned to the dashboard from the reports.

Read: All About SQL Joins and Subqueries

Let’s now try to understand what insights we can get using superstore data set:-

• Sales by Country

• Sales and Profit by Segment

• Sales & Profit by Month

• Sales by Product

• Profit by Discount Band

The Dashboard created is interactive which means a change in one tile affects the other.

How Microsoft Power BI works?

• Connecting Your Data: You can either use Power BI Desktop or Power BI Services to connect various types of data from various data sources like MySQL Server, MySQL, Oracle, etc. There are two ways to do that- either upload your file to Power BI or import the file into Power BI.
• Uploading your file to Power BI: Connect to the data in your workbook so that you can create Power BI reports and dashboards for it.
• Importing your file into Power BI: If you want to view your file, you need to fetch it in Power BI, and you can interact with it just as you would do in the case of Excel Online.
• Transforming Your Data: Once your data is loaded, you can transform the data as per your requirement. You can do this by using the Transform menu. It has a set of operations like reverse rows, count rows, rename, replace values and errors, pivot and unpivot columns, etc.
• Modeling Your Data: You may add some functions, calculations, relationships, measures, etc. In this section of Power BI for the better visualization of data and analytics so that it can be used for business insights. You can even write a query to your files so that you can accomplish different tasks in a short duration of time.
• Data Visualization: In this step, you can create your reports, dashboards, etc. based on your modeled data and depending on the company’s requirement. Report creation can be done in many ways; you need to select any field you want from your CSV file or data file and then choose the tool you want to give to your data to generate the desired report. You can use a variety of tools and even add a custom visual gallery effectively.
• Sharing the Generated Reports: You can share or publish your reports securely using Power BI Services and set up automatic data refresh. You can do this by the Publish menu. It provides near real-time analytics of trends and indicators for everyone.

Read: SAS Tutorial Guide for Beginners

What is the DAX function?

DAX stands for Data Analysis Expressions, that is, such expressions that are used for data analysis and calculations. These expressions are a collection and combination of functions, operators, and constants that are evaluated as one formula to get the result. In the case of Power BI, DAX formulas are very useful as they help data analysts to use the data sets at the fullest potential.

In other words, DAX-

• Is a functional language whose complete code is always a function. An executable DAX expression may contain conditional statements, nested functions, value references, etc.
• Have two primary data types- Numeric and Non-numeric. The numeric data type includes integers, decimals, currency, etc. On the other hand, non-numeric consists of string and binary objects.
• DAX expressions are evaluated from the innermost function going to the outermost one at the last, which makes DAX formula important.

The architecture of Microsoft Power BI

Database as a part of System Architecture

The overall architecture of Power BI consists of three major components:-

• Data Integration: An organization needs to work with data that comes from different sources that can be in various file formats. The data should be extracted from a different source which can be from different servers or databases. This data is integrated into one standard format in a common staging area.
• Data Processing: In this stage, the integrated data is still not prepared for visualization as the data needs processing. This data is pre-processed. For example, redundant values, missing values will be removed from the data set. The business rule should be applied to the data when the data is cleaned. You can load that data back to Data Warehouse.
• Data Presentation: Once the data is loaded and processed, it can be visualized well with the use of various visualizations that Power Bi has to offer. Use of dashboard and report helps one represent data more intuitively. This visual report helps business end-users to take business decisions based on the insights.

Sign up for SQL training classes online and learn how to advance your career as a DBA expert.

Building blocks of Microsoft Power BI

The Power BI supports the following building blocks:-

• Visualization: It can be a chart, graph, or any kind of data that can be presented visually. It can include- Map representation, card visualization, stacked area chart, treemap, pie chart, etc.
• Dataset: It can be a simple set of data or a combination of data from many different resources.
• Report: It is a collection of items that are related to one another.
• Dashboard: A Power BI dashboard is a single-page interface. It is most commonly known as a canvas that uses visualizations to tell a story.

How to increase the speed of Query execution?

Explore a career with Microsoft Power BI

Power BI offers a wide range of potential careers incorporating data scientists, product managers, and business analysts. And this Microsoft Power BI tutorial must have helped you in attaining the information of the tool. You can even test your knowledge with Power BI online tests. With a deeper understanding of the power of query, the role played by data warehouses, and everything from Microsoft flow of self-service analytics makes knowledgeable users valuable resources for a variety of organizations. Data analysts make use of the robust platform to discover primary insights that can modify the course of the businesses for the better.

With more experimentation and practice, Power BI becomes a lot more familiar and will unleash amazing features to help the data analysts to present their data. Happy Learning!

Read: How to Increase the Speed of SQL Query Execution

FaceBook Twitter LinkedIn Pinterest Email