Power BI Tutorial
Power BI is a data visualization or business intelligence tool which helps to convert data occurred from different data sources into interactive dashboards and business intelligence.
- What is BI?
- Introduction of Power BI
- History of Power BI
- Downloading and installation
- Advantages and Disadvantages
- Power BI Components
- Basic building blocks
- Power BI Dashboard
- Power BI Reports
- Compare Dashboard and Report
- Power BI Data Modeling
- Power BI Data Source
- Power BI Gateway
- Power BI DAX
- Comparison with Other BI Tools
What is BI?
BI stands for Business Intelligence. Business Intelligence (BI) describes tools and systems that play a key role in the strategic planning process within a business. These BI systems allow a company to collect, store, access, and analyze its data to help in decision-making.
Introduction of Power BI
Power BI is a data visualization or business intelligence tool, which helps to convert data occurred from different data sources into interactive dashboards and business intelligence. The Power BI package provides multiple software, connectors, and services: Power BI desktop, SaaS-based Power BI service, and Power BI mobile applications available for different platforms. Business users use this set of services to use data and create BI reports.
The Power BI desktop application is used to build reports. Power BI (Software as a Service – SaaS) services are used to publish documents, and the Power BI mobile application is used to display reports and dashboards.
History of Power BI
The Power BI application was thought by Thierry D’Hers and Amir Netz of Microsoft’s SQL Server Reporting Services team.
It was first designed by Ron George in the summer of 2010 and was called the Crescent Project. The Crescent project was initially available for public download on 11 July 2011, included with Denali from SQL Server Codename, and later renamed as Power BI. It was introduced by Microsoft in September 2013 as Power BI for Office 365.
The first version of Power BI was based on Microsoft Excel-based add-ins: Power Pivot, Power Query, and Power View. Over time, Microsoft has also added many additional features such as Question and Answers, enterprise-level data connectivity, and security options through Power BI gateways.
Power BI was launched for the first time to the general public on 24 July 2015.
Downloading and installation steps of Power BI
Here are some system requirements for downloading Power BI Desktop.
- These are operating systems that support the installation of Power BI: Window 7, window 8, window 8.1, window 10, and windows server 2008 R2, windows server 2012, windows server 2012 R2.
- Power BI Desktop needs Internet Explorer 9 or higher.
- Power BI Desktop is accessible for 32-bit and 64-bit platforms
Installation process of the Power BI Desktop step by step:
Step 1:- Click the following link to download Power BI Desktop.
Step 2:- Then click use it free button. Consider the below image:
Step 3:- Now, you will redirect to an Open Microsoft Store.
Step 4:- Click the Get button as given in below image:
Step 5:- Then click on the Install button.
Step 6:- After Installation, You can see “Welcome to Power BI Desktop” screen and then register yourself on the desktop.
Step 7:- When you run the Power BI desktop, the Homepage or Welcome screen appears.
Advantages and Disadvantages of Power BI
Advantages of Power BI
- Affordability:- One of the essential benefits of using Power BI for data analysis and visualization is that it is affordable and relatively inexpensive. The version of Power BI Desktop is free. You can download it and start using it to create reports and dashboards on your computer.
- Use to simple:- Power BI is easy to use. Users can easily find it only in the name of a short learning curve.
- Custom Visualizations:- Power BI offers a wide range of custom visualizations, viz visualizations made by developers for a specific purpose. In addition to all available viewings, you can use custom Power BI visualizations in your reports and panels. The range of custom visualizations includes KPIs, maps, graphs, R script visuals, and more.
- No Memory and Speed Constraints:- Modifying an existing Power BI system into a robust cloud environment with integrated Power BI removes the memory. Speed constraints ensure that data can be retrieved and analyzed quickly.
- EXCEL Integration:- In Power BI, you can also download and view your data in Excel. You can select /filter/split data in a report or Power BI panel and place it in Excel. You can then open Excel and view the same data as a table in an Excel spreadsheet. In other words, Power BI’s Excel integration capability allows users to see and manipulate raw data behind a Power BI visualization.
Disadvantage of Power BI
- Reports and Dashboards of Power BI panels cannot accept or transmit user account. This makes it challenging to create entity-specific dashboards, such as a dashboard for an account.
- While a dataset can include multiple types of data, Power BI reports and panels can only get data from a single dataset. Similarly, Power BI cannot mix imported data with data that is available from real-time connections.
- Power BI will not accept files greater than 250 MB. Power BI files- .pbix – are a type of archived zip file. They compress the data until the xVelocity database engine needs it. But the maximum size of the accepted files can still limit Power BI to subsets of enterprise data stores.
- The solution can be implemented on-site using the Power BI Report Server; however, its cost increases dramatically.
Power BI Components
Below are Components of Power BI:
- Power Query
- Power Pivot
- Power View
- Power Map
- Power BI Service
- Power BI Question & Answer
- Power BI Mobile Apps
Power Query:- Power Query is a data transformation and mash up the engine. Power Query can be downloaded as an add-in for Excel or used as part of Power BI Desktop. With Power Query, you can derive data from many separate data sources.
Power Pivot:- Power Pivot is a data modeling engine that runs on a tabular engine based on xVelocity memory. The xVelocity Memory engine gives Power Pivot, an extremely fast response time. The Modeling engine is a great place to create your star schema, calculated measures and columns, relationships between entities, and more. Power Pivot uses the Data Analysis expression (DAX) language to generate steps and compute the columns.
Power View:- The primary data visualization component of Power BI is Power View. Power View is an interactive visualization of data that can be connected to data sources and obtains the metadata that will use for data analysis. Power View has many graphics to display on your list. Power View allows you to filter data for each data display item or the entire report. Power View reports are interactive, the user can highlight some of the data, and the different elements of Power View will be displayed on the screen.
Power Map:- Power Map is used to display geospatial information in 3D mode. When the visualization is processed in a 3D manner, it will give you another dimension in the visualization. You can view measurement as the height of a 3D column and another size as a thermal map view. You can highlight data by location such as city, state, address, and country. Power Map runs with Bing maps to get the best possible visualization from geographic information, whether it’s latitude and longitude, country, state, city, or address. Power Map is a complement for Excel 2013 and integrated with Excel 2016.
Power BI Service:- You can share data views and workbooks which are replaced from on-premises and cloud-based data sources.
Power BI Question & Answer:- Power BI Question & Answer is a natural language engine for questions and answers to your data model. Once you have created your data model and implemented it on the Power BI website, you or your users can ask questions and quickly get answers. Power BI Question & Answer works with Power View for data visualization.
Power BI Mobile Apps:- There are mobile apps for three major mobile OS providers: Android, Apple, and Windows Phone. These applications give you an interactive view of the dashboards and reports on the Power BI site. You can share them from the mobile app also. You can highlight part of the story, write a note about it, and share it with others.
There are five basic building blocks of Power BI
Visualizations:- The visualization is a visual representation of the data.
You can visualize the data using Line Graph, Pie Chart, Side by Side Bar Charts, Graphical Presentation of the source data on top of a geographical map, treemap, etc.
Datasets:- A dataset is a group of data that Power BI uses to create its visualizations. You can create and store your dataset information using Oracle, Excel sheets, or SQL server tables.
Reports:- Report is a set of visualizations that appear together on one or more pages.
Example: The Sales by Country, State, City Report, Logistic Performance Report, and Profit by Products report, etc.
Dashboards:- The dashboard is a unique layer presentation of multiple visualizations. This means that we can integrate one or more visualizations into a page layer.
Example: The Sales dashboard can have pie charts, geographical maps, and bar charts.
Tiles:- Tile is a single visualization in a report or on a dashboard.
Example: Pie Chart in Dashboard or Report.
Power BI Dashboard
The Power BI dashboard is an individual page, also called a drawing area that visualize the information using pie charts, graphs, tree, etc. It is limited to one page. Therefore, a well-designed table contains only the most essential elements of this story.
You can add important or major visualizations made on a Power BI development tool in a dashboard. A tile on the dashboard describes each visualization. Tiles make a dashboard to look more established and understandable. To add a visualization of your report to a dashboard, only pin it on the dashboard. A user or consumer can access the source report from which the visualization is taken by clicking on its dataset.
Designers build a dashboard and publish it on Power BI exchanging platforms, and customers use the dashboards from there. A consumer cannot edit a dashboard. They use the dashboard for analytical purposes, such as obtaining a quick overview of their activities, making an important decision based on the information provided on a chart, and so on.
Creating a Dashboard in Power BI
Step 1:- Open the desktop of Power BI.
Step 2:- Click on the Get Data option and select the file formats of your file.
Step 3:- Open the file.
Step 4:- Then load the tables and sheets of your file.
Step 5:- Now, display the dashboard.
When you publish your Power BI report, you can share reports and dashboards with other users in your team. Sharing a dashboard is very simple in Power BI.
You must open the dashboard in the Power BI service and click the Share option at the upper right corner of the screen.
Note: The sharing feature is only available with the Power BI Pro version. You can also use the 60-day free trial for Power BI Pro, as shown in the above screenshot.
Power BI Reports
A Power BI report is a multi-perspective view of the dataset, with visualizations representing different insights and findings from that dataset. A report can be a single visualization or pages full of visualizations.
Note: An important point to remember is that reports are based on a single dataset.
These visualizations are not static, and you can add and delete data, edit visualization types, and apply filters in your search to discover ideas and find answers. As a dashboard, a report is highly interactive, highly customizable, and the visualizations are updated as the underlying data changes.
Difference between Dashboards and Reports
|Reports can have one or more pages.||Dashboard has only one page.|
|You have only one dataset per report.||You have one or more reports and data sets per dashboard.|
|Yes, Report can set alerts.||Dashboard cannot set alerts.|
|There are many ways to filter, highlight, and cut.||You cannot filter or cut.|
|You cannot create a feature report.||You can set one dashboard as the featured dashboard.|
Power BI Data Modeling
Data modeling is one of the features used to combine various data sources in the Power BI tool through a relationship. A relationship describes how data sources are connected and can create interesting data visualizations in multiple data sources.
With the modeling purpose, you can create custom calculations in existing tables and these columns can be presented directly in Power BI visualizations.
Modeling involves preparing your connected data for below uses:
- Create relationships with your data sources.
- Optimize data by hiding fields and ordering visualization data.
- Create a new field with calculated columns.
- Create a measure to execute calculations on your data.
- Use a calculated table to create a connection between two tables.
- Format the time-based data so that you can search in more detail.
Power BI Data Source
With Power BI Desktop, you can connect to data from many different sources. A complete list of available data sources can be seen at the bottom of Desktop.
To connect to the data, select the Get Data from the Power BI desktop, and it will show you all the available data connections.
Data types are classified into the following categories:
- Power BI
- Online Services
ALL:- This category covers all data connection types of all category.
File:- The File category gives the following data connections:
- SharePoint Folder
Below screenshot shows the Get Data window for File category.
Database:-The Database category gives the following data connections:
- SQL Server Database
- Access Database
- SQL Server Analysis Services Database
- Oracle Database
- IBM DB2 Database
- IBM Informix database (Beta)
- IBM Netezza
- MySQL Database
- PostgreSQL Database
- Sybase Database
- SAP HANA Database
- SAP Business Warehouse Application Server
- SAP Business Warehouse Message Server
- Amazon Redshift
- Google BigQuery
- AtScale cubes (Beta)
- BI Connector
- Indexima (Beta)
- InterSystems IRIS (Beta)
- Jethro (Beta)
- Kyligence Enterprise (Beta)
- MarkLogic (Beta)
Below screenshot shows the Get Data window for Database category.
Power Platform:- The Power Platform category gives the following data connections:
- Power BI datasets
- Power BI dataflows
- Common Data Service
Below screenshot shows the Get Data window for Power Platform.
Azure:- The Azure category gives the following data connections:
- Azure SQL Database
- Azure SQL Data Warehouse
- Azure Analysis Services database
- Azure Blob Storage
- Azure Table Storage
- Azure Cosmos DB
- Azure Data Lake Storage Gen2 (Beta)
- Azure Data Lake Storage Gen1
- Azure HDInsight (HDFS)
- Azure HDInsight Spark
- HDInsight Interactive Query
- Azure Data Explorer (Kusto)
- Azure Cost Management (Beta)
Below screenshot shows the Get Data window for Azure.
Online Services:- The Online Services category gives the following data connections:
- SharePoint Online List
- Microsoft Exchange Online
- Dynamics 365 (online)
- Dynamics NAV
- Dynamics 365 Business Central
- Dynamics 365 Business Central (on-premises)
- Microsoft Azure Consumption Insights (Beta)
- Azure DevOps (Beta)
- Azure DevOps Server (Beta)
- Salesforce Objects
- Salesforce Reports
- Google Analytics
- Adobe Analytics
- appFigures (Beta)
- Data.World – Get Dataset (Beta)
- GitHub (Beta)
- MailChimp (Beta)
- Marketo (Beta)
- Mixpanel (Beta)
- Planview Enterprise One – PRM (Beta)
- Planview Projectplace (Beta)
- QuickBooks Online (Beta)
- SparkPost (Beta)
- Stripe (Beta)
- SweetIQ (Beta)
- Planview Enterprise One – CMT (Beta)
- Twilio (Beta)
- tyGraph (Beta)
- Webtrends (Beta)
- Zendesk (Beta)
- Dynamics 365 Customer Insights (Beta)
- Emigo Data Source (Beta)
- Entersoft Business Suite (Beta)
- Industrial App Store
- Intune Data Warehouse (Beta)
- Microsoft Graph Security (Beta)
- Quick Base
- TeamDesk (Beta)
Below screenshot shows the Get Data window for Online Services.
Other:- The Other category gives the following data connections:
- SharePoint List
- OData Feed
- Active Directory
- Microsoft Exchange
- Hadoop File (HDFS)
- R Script
- Python script
- OLE DB
- BI360 – Budgeting & Financial Reporting (Beta)
- Information Grid (Beta)
- QubolePresto (Beta)
- Roamler (Beta)
- SurveyMonkey (Beta)
- Tenforce (Smart)List (Beta)
- Workforce Dimensions (Beta)
- Blank Query
Below screenshot shows the Get Data window for Other category.
Power BI Gateway
Power BI Gateway is software, which is necessary to access data on a local network. The gateway acts as a guardian of the local data source. If someone wants to access local data from a cloud or web application, the request goes through the gateway. The gateway processes all connection requests, and access is granted based on your requirements and authentication.
The gateways do not transfer data from the local source to the client platform. But connect this platform directly to the local data source. The customer can access the data directly from their local location to use in the creation of reports, dashboards, and data analysis. The gateway is used to facilitate connections between one or more data sources and the local data source.
Two types of gateways available in Power BI
- Personal Mode
- Standard Mode
The personal mode of the local data gateway is used by a single user to connect to different data sources. It is recommended when only one person needs access to data sources. To create reports and panels with Power BI, the user cannot share access privileges with other users.
This version of the local data gateway allows connection to multiple local data sources for multiple users. You can use data in Power BI, Azure Analysis Services, Azure Logic Applications, Microsoft Flow, PowerApps, etc. You can establish direct connections to multiple data sources by installing this type of data gateway once. This data gateway is recommended for difficult scenarios where multiple users must access multiple data sources.
Power BI – DAX
DAX refers to Data analysis expressions, i.e., such expressions or formulas used for data analysis and calculation.These expressions are collections of functions, operators, and constants evaluated as a unique formula to obtain results (value or values). DAX formulas are beneficial in BI tools, such as Power BI, because they help data analysts make the most of the data sets they have.
Using the DAX language, analysts can discover new ways to calculate the data values they have and offer further information.
Check out some of the key DAX points that will help you to better understand the concept.
- DAX is a functional language; i.e. its complete code is always a function. An executable DAX expression can contain conditional statements, nested functions, value references, etc.
- DAX formulas have two types of primary data; Numeric and Non-numeric or Others. The type of numerical data includes integers, decimals, currency, etc. while the non-numeric type includes binary strings and objects.
- DAX expressions are evaluated from the private function of the last to the last. This makes the formulation of a DAX formula important.
DAX Calculation types:
There are two main calculations you can design with DAX:
- calculated columns
- calculated measures
Calculated columns create a new column in your present table. The only difference between a regular column and a calculated column is that it is necessary to have at least one function in the calculated column. These columns are used when you want to create a column with filtered or sorted information.
Steps to create a calculated column:
1. Go to the Modeling tab in Power BI Desktop.
2. Then select the new column option. A formula bar will open, indicating “Column =”. You can replace the word “Column” with the name of the desired column.
3. After that, enter the calculated column expression to the right of the equals to(=) sign. Consider the below image:
A calculated measure creates a field that contains aggregated values such as sum, percentages, percentages, averages, etc.
Steps to create a calculated measure:
1. Go to the Modeling tab in Power BI Desktop.
2. Then select the New Measure option. A formula bar will open and say “Measure =”. You can replace the word “Measure” with the desired measure name.
3. After that, enter the expression of the calculated measure to the right of the equals to(=) sign.
4. Once you create the measure, you can change the it’s name with a calculator icon next to it, under the name of the table where you created the measure. Consider the below image:
In Power BI, you can use different types of functions to analyze data and create new columns and measures. It includes functions of different categories, such as:
Power BI provides a simple way to view a list of all features. When you start entering your function in the formula bar, you can see a list of all the functions starting with that alphabet.
Aggregate Functions:- DAX has some aggregate functions.
Counting Functions:- Other counting functions in DAX include −
Logical Functions:- Following are the collection of Logical functions −
Comparison of Power BI with Other BI Tools
Differences between Power BI and Tableau:
|Data Access||Power BI cannot connect to Hadoop databases but can extract data from Azure, Salesforce, and Google Analytics.||Tableau allows you to access cloud data and connect to Hadoop databases. It also identifies the resource automatically.|
|Visualizations||Power BI provides many data points for visualization. It has approximately 3,500 data points to expand the dataset and perform an analysis.||In the absence of coding language, using the drag-and-drop method, users can create charts, scatter charts in the tableau, and not restrict the number of data points.|
|Customer Support||Power BI provides short customer support.||Tableau offers powerful customer service and has community forums for the discussions. You have categorized online, desktop, and server support.|
|Cost expensive||It is very cheap compared to Tableau.||Tableau is more expensive than Power BI. You must pay more when connected to third-party applications.|
|Target Audience||Naive users and Expert users.||Although access is easy and simple, analysts and experienced users use it for analytical purposes.|
|Performance||It can manage a limited amount of data.||It can handle a large amount of data with better performance.|
Differences Between Power BI and SSRS
|Stands for||Power BI stands for Power business intelligence.||SSRS stands for SQL Server Reporting Services.|
|License||Power BI is free to use.||Requires a SQL Server license.|
|Applicability||Power BI is used to offer both cloud-based and server-based reports.||SSRS is used to offer only server-based reports.|
|Technology||Microsoft Power BI is a new business intelligence technology used for dealing with data and creating reports. It is easy-to-use software with easy-to-use functions to create, publish and share reports.||SSRS is the SQL Server reporting service, which is also a Microsoft product. It is a conventional server-based data analysis and reporting software.|
|Accessibility||You can access Power BI software on three different types of platforms. Web, mobile devices (applications) and desktop computers.||You can use SSRS on the web and desktop.|
|Size limit||Power BI has a limit of 1 GB of data in the free version, and you can create multiple datasets.||There is no limit to the size of a data set in SSRS.|