What is BI?
Business intelligence is a technology-driven method which helps you to analyze data and to provide actionable information which helps corporate executives, business managers, and other users to make informed business decisions. In this Microsoft Power BI tutorial for beginners, you will learn Power BI basics like:
What is BI?
What is Power BI?
Why use Power BI?
Types of Power BI tools
Data sources for the Power BI
Key terms used in Power BI
History of Power BI
Architecture of Power BI
Install and run Power BI Desktop
Power BI Dashboard
What is Dax Function?
Power BI vs. Tableau
Who uses Power BI?
Advantages of Power BI
Disadvantages of Power BI
Power BI Interview Questions and Answers
Different Power BI versions like Desktop, Service-based (SaaS), and mobile Power BI apps are used for different platforms. It provides multiple software connectors and services for business intelligence. In this Power BI training, you will learn all the important concepts of Power BI and develop a foundational understanding of how to use Power BI tool.
Why use Power BI?
Here are the most prominent use of Power BI tool:
Pre-built dashboards and reports for SaaS Solutions Power BI allows real-time dashboard updates. Offers Secure and reliable connection to your data sources in the cloud or on-premises Power BI offers Quick deployment, hybrid configuration, and secure environment. Allows data exploration using natural language query Offers feature for dashboard visualization regularly updated with the community.
Types of Power BI tools
Now in this Power BI desktop tutorial, we will learn about types of Power BI tools. Some Important Power BI tools are:
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. Costs: Free
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. Pro license: $10/users/month
Power BI Data Gateway
Power BI Data Gateway works as the bridge between the Power Bl Service and on-premise data sources like DirectQuery, Import, Live Query. It is Installed by Bl Admin.
Power BI Report Server
It can host paginated reports, KPIs, mobile reports, & Power Bl Desktop reports. It is updated every 4 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.
Data sources for the Power BI
Key terms used in Power BI
History of Power BI
Power BI was conceptualized by Ruler and Dhers Netz of the SQL server coverage services team at Microsoft. It was designed by West Chadic George in the year 2010 and named as a Project Crescent. In 2011, It was bundled with SQL Server Codenamed Mount McKinley. Microsoft unveiled the first preview to Power BI in September 2014. The first version of Power BI released on 24 July 2015. It was based on Excel Based Add-ins like Power Query, Pivot, view, and Map.
Architecture of Power BI
Data Integration:
An organization needs to work with data which comes from different sources which 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 much better with 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.
Install and run Power BI Desktop
Step 1) Visit https://powerbi.microsoft.com/en-us/desktop/ And select Download free button
Step 2) You will be redirected to a Microsoft store and select Get button.
Step 3) Click on the Install button.
Step 4) You can see progress status on the screen.
Step 5) Before welcome screen you will ask to register to enter.
Step 6) When you run Power BI Desktop, a Welcome screen is displayed.
Power BI Dashboard
Below figure demonstrates the Power BI Dashboard:
Power Bl Desktop is an enlargement tool used to generate dashboards and reports. Power Bl applications will be accessed by other users through desktop and mobile devices. There are Power BI components are 5 main components released in the market.
Power Query: It can be used to search, access, and transform public and/ or internal data sources. Power Pivot: It is used for data modeling for in-memory analytics. Power View: This component allows you to visualize, analyze, and display data. Power Map: It brings data to life with interactive geographical visualization. Power BI Service: It allows you to share data views and workbooks, which you can refresh from on-premises and cloud-based data sources. Power BI Q&A: It allows you to ask questions and get immediate answers using a natural language query. Data Management Gateway: It offers periodic data refreshers, view data feeds, expose tables. Data Catalog: This component allows the user to discover and reuse queries using the Data Catalog. Metadata can be facilitated for search functionality.
What is Dax Function?
DAX is a formula expression language which is called (DAX) which can be used with various visualization tools like Power BI. It is also known as a functional language, where the full code is kept inside a function. DAX programming formulas contain two data types: Numeric and Other. Each is linked to the other by having common columns. Here is a simple diagram of Power BI Dashboard Example showing the relationships.
There are 3 things in Power BI where you can use DAX –
Calculated Columns Measures Tables
Let’s see all these Power BI examples and see how DAX functions works.
Calculated Columns
Calculated column allows you to create new columns based on the given data. For example, there is no ‘ Final price’ column available in the Items table. Here, the DAX function is used to calculate a new column when only total price & quantity are available. Price = List_Items[MRP]*List_Items[Qty] In the data shown in above Power BI example, each row will now have the respective calculated price.
Measures
You can perform a calculation using measure without the need to add any data as shown in the below Power BI example. This is very helpful for reports where the price can be displayed, without requiring an entirely new column to store it. Example: 1] Total of the MRP column * Total of Qty column
Tables
DAX functions in tabular model return entire tables. For example, In order to generate a list of all the country the organization has clients in, use the function: cities touched = DISTINCT(Customers[City])
A word on Filters
Filters hide rows that don’t fit given criteria. A calculation after filtering out data will be applicable only on a row which matches those criteria.
Power BI DAX Functions
Some Important DAX functions are:
Average
This DAX function allows you to find the average from a given set of values as shown in the below Power BI example. Example – AvgComm = AVERAGE(List_Items[Price])
Max
Helps you to find the maximum from a given set of values. Example – Find out the highest order. HighSale = MAX(List_Items[Price])
Min
Helps you to find the minimum set of values. Example – Allows you to find out the lowest order. LowestSale = MIN(List_Items[Price])
Count
Count any umerical data. Example – Count number of ticket issued. TicketVolume = COUNT(Invoices[Ticket])
Concatenate
This function helps you to join values in calculated columns. You can use ConcatenateX if using in measures. Example – Concatenate the Item names, and MRPs will give a unique code for all the price points at which each product is sold. ProMrp = CONCATENATE(List_Items[Item],List_Items[MRP])
TotalYTD
The function allows you to calculate the sum from the start of the current Year to the specified date. It performs calculate base on a calendar year, not a financial year. Example – Calculate Sales totals for the price column. CumiSales = TOTALYTD(SUM(List_Items[Price]),Invoices[Date])
All
Returns everything. Ignores filters. Example – Used with the calculate function above.
Power BI vs. Tableau
Here, are major differences between Power BI vs. Tableau:
Who uses Power BI?
Here, are an important professional who uses Power BI tool:
PMO – Project and Portfolio Manager Business & Data Analyst Developer & Database Administrator IT Team, IT Professional Consumer for End User Report Data Scientist
Advantages of Power BI
Here are the advantages of using Power BI:
Offers pre-built dashboards and reports for SaaS Solutions Provide real-time dashboard updates. Secure and reliable connection to your data sources in the cloud or on-premises Power BI offers quick deployment, hybrid configuration, and a secure environment. Data exploration using natural language query. Feature for dashboard visualization New features frequently added that are great for excel users. Extensive database connectivity capabilities Q&A feature publish to the web. integration with both Python and R coding to use visualizations. Power Query provides many options related to wrangling and clean the data. Post publishing the data into Power BI web service can schedule refresh without manual intervention. Power BI backed by the superpower of with artificial intelligence and machine learning.
Disadvantages of Power BI
Here, are Cons/drawbacks of using Power BI:
Dashboards and reports only shared with users having the same email domains. Power Bl will not mix imported data, which is accessed from real-time connections. Power Bi will not accept the file larger than 250MB and zip file which compressed by the data of X-velocity in-memory database. Power BI can’t accept file size larger than 1 GB. Dashboards never accept or pass user, account, or other entity parameters. Very few data sources that permit real-time connections to Power BI reports and dashboard.
Summary
BI helps you to analyze data and to provide actionable information which helps corporate executives, business managers to make informed business decisions. Power BI is a Business intelligence and Data Visualization tool which helps you to convert data from a various data source Some important Power BI tools are 1) Power BI Desktop 2) Power BI service 3) Power BI Data Gateway 4) Power BI Report Server 5) Power BI Mobile Apps Excel (.xlsx, xlxm), Comma Separated Value (.csv), Power BI Desktop (.pbi), Databases in the Cloud, Databases on-premises are important data sources used in Power BI. Visualization, Datasets, Dashboard, Reports, Tile are important terms used in a Power BI. Power BI was conceptualized by Ruler and Dhers Netz of the SQL server coverage services team at Microsoft.
- Data Integration 2) Data Processing 3) Data Presentation are important components of Power BI architecture. Power Query, Power Pivot, Power View, Power Map, Power BI Service, Power BI Q&A, Data Management Gateway, Data Catalog are important elements of Power BI Dashboard. DAX is a formula expression language which is called (DAX) which can be used with various visualization tools like Power BI. Power BI offers Complete Dashboards for analysis, while Tableau only allows Ad Hoc Analysis. Important professional who uses Power BI is PMO – Project and Portfolio Manager, Business & Data Analyst, IT Team, IT Professional, etc. The biggest drawback for Power Bi will not accept the file larger than 250MB and zip file which compressed by the data of X-velocity in-memory database.
