MONTH START OFFER: Flat 15% Off with Free Self Learning Course | Use Coupon MONTH15

- SQL Server Blogs -

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: DB2 Interview Questions and Answers

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: What is SQL Server Replication and How it Works?

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.

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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: How to Prevent SQL Injection Attacks?

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: SSIS Package - SSIS DB, Security and Upgrades

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.

Installation of Power BI

This is the link to directly download Power BI files ?

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

Installation of Power BI

Installation of Power BI

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.

Installation of Power BIWhen you run the installation file, the following screen is displayed.

Installation of Power BIAccept the license agreement and follow the instructions on the screen to finish the installation.

Installation of Power BI

Read: Step By Step SSAS Tutorial 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.

Installation of Power BI

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

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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.

Installation 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.

Installation of Power BI

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

Installation of Power BI

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: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

Installation of Power BI

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.

Installation of Power BI

Read: Different Types of SQL Server & SQL Database Functions

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

Installation of Power BI

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.

Installation of Power BI

Try experimenting with other data sources too.

Read: What is SQL Server? Microsoft SQL Server Tutorial Guide for Beginners

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

Installation of Power BI

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.

Installation of Power BI

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.

Installation of Power BI

Read: SSIS Tutorial for Beginners

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.

Installation of Power BI

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.

Installation of Power BI

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: How To Quickly Get An Entry Level SQL Jobs?

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

  • Sales by Country

Installation of Power BI

  • Sales and Profit by Segment

Installation of Power BI

  • Sales & Profit by Month

Installation of Power BI

  • Sales by Product

Installation of Power BI

  • Profit by Discount Band

Installation of Power BIThe Dashboard created is interactive which means a change in one tile affects the other.

How Microsoft Power BI works?

How 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: SSRS Pie Chart - Having a Slice of the Pie

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

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.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Building blocks of Microsoft Power BI

The Power BI supports the following building blocks:-

Building blocks of Power BI

  • 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: Top 20 SSAS Interview Questions and Answers For Freshers, Experienced 


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

-0 day 04 Dec 2020

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

8 days 12 Dec 2020

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 09 Dec 2020

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

7 days 11 Dec 2020

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

-0 day 04 Dec 2020

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

1 day 05 Dec 2020

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

-0 day 04 Dec 2020

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

-0 day 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-0 day 04 Dec 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

1 day 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

16 days 20 Dec 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

15 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews