Power BI: A Comprehensive Guide to Business Intelligence, Slides de Competências em MS Microsoft PowerPoint

Baixe Power BI: A Comprehensive Guide to Business Intelligence e outras Slides em PDF para Competências em MS Microsoft PowerPoint, somente na Docsity!

POWER BI

Draft on Understanding PowerBI

Follow Raushan Kumar on LinkedIn

POWER-BI INDEX

1. BUSINESS INTELLIGENCE (BI) CONCEPTS

1.1 INTRODUCTION TO BUSINESS INTELLIGENCE

1.2 RELATION BETWEEN BUSINESS INTELLIGENCE AND DATA

WAREHOUSE

1.3 RELATION BETWEEN BUSINESS INTELLIGENCE AND DATA

WAREHOUSE (CONT.)

1.4 BI TOOLS

2. MICROSOFT POWER BI (MSPBI) INTRODUCTION

2.1 POWER BI INTRODUCTION AND OVERVIEW

2.2 POWER BI ARCHITECTURE

2.3 INTRODUCTION TO POWER BI DESKTOP

2.4 WHY TO CHOOSE POWER BI OVER EXCEL

3. INTRODUCTION AND GETTING STARTED

3.1 HOW TO GET POWER BI DESKTOP?

3.2 POWER BI DESKTOP INTERFACE

3.3 CHANGE DEFAULT SETTINGS

4. CONNECT TO VARIOUS DATA SOURCES

4.1 CONNECT TO CSV/TEXT/EXCEL FILES

4.2 CONNECT TO CSV/TEXT/EXCEL FILES (CONT.)

4.3 CONNECT TO CSV/TEXT/EXCEL FILES (CONT.)

4.4 CONNECT TO SQL SERVER

4.5 CONNECT TO SQL SERVER (CONT.)

4.6 CONNECT TO A WEB PAGE

4.7 CONNECT TO A WEB PAGE (CONT.)

4.8 ENTER DATA DIRECTLY

4.9 CONNECT TO DIRECT SQL QUERY

5. POWER QUERY FOR DATA TRANSFORMATION

5.1 POWER QUERY

5.2 HOW TO OPEN POWER QUERY EDITOR?

5.3 POWER QUERY EDITOR INTERFACE

5.4 POWER QUERY EDITOR INTERFACE (CONT.)

5.5 DATA TYPES

5.6 TRANSFORMATION PROCEDURE

5.7 MERGE & APPEND QUERIES

9. TYPES OF VISUALIZATIONS

9.1 CARD

9.2 BAR GRAPH

9.3 PIE CHART

9.4 RING CHART/DONUT

9.5 LINE CHART

9.6 AREA CHART

9.7 STACK AREA CHART

9.8 TREE MAP

9.9 MAP

9.10 GAUGE CHART

9.11 MATRIX

9.12 SLICER

9.13 DECOMPOSITION TREE

9.14 Q&A

9.15 CUSTOM VISUALIZATION TO A POWER BI

10. DASHBOARDS IN POWER BI

11. PUBLISHING REPORT

1. Business Intelligence (BI) Concepts

2. Introduction to Business Intelligence
3. The importance of Business Intelligence
4. The relation between Business Intelligence and Data Warehouse
5. Tools and Technologies in Business Intelligence area

1.1 INTRODUCTION TO BUSINESS INTELLIGENCE

Business Intelligence (BI) is a process of analyzing data through technology and presenting it to the end user(s) which helps them to make an informed decision. With the use of historical and current data, a BI tool serves predictive view. Usually, a BI tool can perform tasks like data connection, data mining, data transformation, data modelling through building relationships, complex calculations, report building, dashboard creation, online analytical processing and predictive analysis.

1.2 RELATION BETWEEN BUSINESS INTELLIGENCE AND DATA

WAREHOUSE

To understand the relationship between BI and Data warehouse, lets first understand what is Data warehouse?

DATA WAREHOUSE

It consists of a huge storage of data gathered from single or many sources to aid the process of making an informed decision at any level of an enterprise. A typical data warehouse follows an ETL (Extract, Transform, Load) process.

ETL

Extract :- The first step in using Data Warehousing is to extract data from single or multiple sources to load in its environment. Transform :- The Data which has been extracted, may not come in the desired format or size etc, so there may be the need to transform the incoming data to meet business requirements and objects. Load :- Once the data is being transformed, its ready to be loaded in targeted tables.

1.3 RELATION BETWEEN BUSINESS INTELLIGENCE AND DATA

WAREHOUSE (CONT.)

A Business Intelligence tool takes data from a Data warehouse to generate reports and help the end user to make informed decision. By this, we can call Data warehouse as a part of a complete Business Intelligence process.

2. MICROSOFT POWER BI (MSPBI) INTRODUCTION

Microsoft PowerBI (MSPBI ) introduction

1. Power BI introduction and overview
2. Power BI Architecture
3. Introduction and Power BI in Desktop
4. Why Choose Power BI over Excel

2.1 POWER BI INTRODUCTION AND OVERVIEW

Power BI is a collection of software/tools that works in synchronization to turn unrelated sources of data into meaningful and interactive insights. Power BI support 100’s of data sources including the most common one’s like Excel spreadsheets, Text/CSV, SQL, Oracle etc. PARTS OF POWER BI

• Power BI Desktop - A Windows desktop application
• Power BI service - for Windows, iOS, and Android devices
• Power BI mobile apps - An online SaaS (Software as a Service) service
• Power BI Report Server - to publish Power BI reports to an on-premises report server, after creating them in POWER BI FLOW It starts with connecting to data then transforming it, building relationships and finally creating reports and publishing it to Power BI service. Later it can be shared so that end users in the Power BI service and mobile devices can view and interact with the report.

2.2 POWER BI ARCHITECTURE

2.3 INTRODUCTION TO POWER BI DESKTOP

It starts with connecting to data then transforming it, building relationships and finally creating reports and publishing it to Power BI service. Later it can be shared so that end users in the Power BI service and mobile devices can view and interact with the report.

2.4 WHY TO CHOOSE POWER BI OVER EXCEL

• Store and analyze huge amounts of data smoothly: With powerful compression algorithms to import and cache the data within the .PBIX file, it can easily handle huge databases. On the other hand, Excel struggles even in opening an file having few hundred thousands of rows.
• Find Data insights and show trends in minutes: With build-in time intelligence functions, it becomes very easy to dig into vast amount of data and draw trend (unlike Excel).
• User Friendly Report Interface: It’s just about drag and drop of the fields when it comes create impressive visualizations. Even a complex report with diverse visualizations won’t take more than 10 to 20 mins to create. If you think that pre- enabled visualizations are not enough, then you can import a custom visualization anytime in just few clicks from the library of 100’sof custom visuals.
• Publishing and Sharing the Report: Just by hitting the publish button, one can publish the report on Power BI service and whosoever has access to it can view the updated report or dashboard always. On the other hand, in Excel, one need to send emails or putting in the share drive or share point and telling them that we have updated the file.
• Defining Roles: Power BI gives us an option to define roles to make sure people from different departments or locations can see only their respective data (Which can’t be done in Excel).

3.2 POWER BI DESKTOP INTERFACE

CONNECT TO POWERBI APP FROM MICROSOFT STORE

How Microsoft App look like?

POWER BI DESKTOP INTERFACE (CONT.)

• Report Name: We can rename the report while saving the same for the first time.
• Views: o Report View – Under this we can use different visualizations to build report. o Data View – Once data is being loaded to PBI Desktop, the same can be seen here in the form of tables and fields. Here we can create calculated columns and measures. o Relationship View – This view is useful to build relationships to create data model.
• Page Name: We can have multiple pages into a single report. Each page contributes a part of a report. Its just like “Sheet” tabs in MS Excel.
• Add New Page: By clicking the plus sign, we can add a new page in the report.
• Tabs: o Home: This is a general-purpose tab and used for connecting new data, editing queries etc. o View: One can set the view and even design the phone layout too.
• Help: It’s a good resource to learn this program and even post your queries in PBI forums/community.

4. CONNECT TO VARIOUS DATA SOURCES

Connecting PowerBI with Different Data sources

• Connect to CSV files.
• Connect to Excel
• Connect to text.
• Connect to SQL Server
• Connect to a Web page.
• Enter data directly.
• Analysis Services Tabular data
• Connect to Direct SQL Query

What is the purpose of the ‘Get Data’ icon in Power BI?

When users click on the Get Data icon in Power BI, a drop-down menu appears, and it shows all data sources from which data can be ingested. Data can be directly ingested from any source including files in Excel, CSV, XML, JSON, PDF, and SharePoint formats and databases such as SQL, Access, SQL Server Analysis Services, Oracle, IBM, MySQL, and much more. Also, Power BI datasets and Power BI data flows are compatible. Data can also be taken in from Azure and other online sources.

4.1 CONNECT TO CSV/TEXT/EXCEL FILES

Home Tab - > Get Data - > Click on Text/CSV or Excel - > Choose File - > Open 4.2 CONNECT TO CSV/TEXT/EXCEL FILES (CONT.) When we click on the open button, a new dialogue box will get open. In which, following delimiter can be selected to extract the data–

• Comma
• Colon
• Equal sign
• Semicolon
• Space
• Tab
• Custom
• Fixed with

Choose which dataset you want to choose- For long dataset search via navigator.

Select particular excel sheet and transform dataset.

4.4 CONNECT TO SQL SERVER

Home Tab - > Get Data-> Choose SQL server Database or More-> Choose SQL server database -

Connect.

4.5 CONNECT TO SQL SERVER (CONT.)

• Following are the list of available fields in order to connect Power BI desktop to SQL Server Database
• Server- In this section we will provide default SQL server Instance.
• Database- If we want to use custom SQL query then this option is required.
• Data Connectivity Mode- Choose whether we want to import or directly connect through query.

Page 1G of 60

4.5 CONNECT TO SQL SERVER (CONT.)

After selecting the tables that need to be added in the model, we can click on “Load” to load them into PBI environment directly.

4.6 CONNECT TO A WEB PAGE

Home Tab - > GetData - > Web data - > Type the URL - > Connect.

4.7 CONNECT TO A WEB PAGE (CONT.)

Once Power BI desktop connects with the web page, it presents the data available into the navigator window. When we click on any table showing inside the navigator pane, it will display the preview of data. When we select the Load option in the navigator, Power BI imports the selected item data and makes them visible inside the Fields Tab.

4.8 ENTER DATA DIRECTLY

Home Tab - > Enter Data This will trigger “Create Table” dialogue box.