Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

Microsoft Power BI Tutorial For Beginners

Microsoft Power BI Tutorial Guide for Beginner

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
SQL Server Curriculum

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

Before diving deep into Power BI, 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.

What is the 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 visual 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.

The proficiency in Power BI 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 prepare 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 Power BI?

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

  • Pre-defined dashboards and reports for SaaS solutions
  • It allows real-time updates on the dashboard
  • It offers 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 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 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.

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.
SQL Server quiz

Installation of Power BI

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

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.

Read: SQL Server Recovery Models-Simple, Full and Bulk Log

Installation of Power BI

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

Installation of Power BI

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

Installation of Power BI

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

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

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

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.

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: Most Popular SQL Server Performance Tuning Tips

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

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.

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

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 which can be adjusted to a whole number.

Installation of Power BI

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

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

Reports

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

Read: What is Composite Key in SQL? Composite Primary & Foreign Key Explain with Example

Installation of Power BI

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.

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 SegmentInstallation 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 BI

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

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

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 a 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 Power BI

The architecture of Power BI

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

  • Data Integration: An organization needs to work with data which 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 visualization 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 decision based on the insights.
free SQL Server demo

Building blocks of Power BI

The Power Bi supports 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, tree map, 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.

Explore a career with Power BI

Power BI offers a wide range of potential careers incorporating data scientists, product managers, and business analysts. With the 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!


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

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

Upcoming Class

2 days 14 Nov 2019

DevOps

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

Upcoming Class

3 days 15 Nov 2019

Data Science

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

Upcoming Class

3 days 15 Nov 2019

Hadoop

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

Upcoming Class

4 days 16 Nov 2019

Salesforce

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

Upcoming Class

2 days 14 Nov 2019

QA

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

Upcoming Class

-0 day 12 Nov 2019

Business Analyst

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

Upcoming Class

3 days 15 Nov 2019

SQL Server

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

Upcoming Class

7 days 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews