Data Warehouse Concept

· What is DW?
Data Warehouse is the Database which store historical data to provide
critical business information to the business user to take a business decision.
· Why we need to have DW?
· DW Schemas: Star & Snowflake
· Dimension & Measure or Fact
· Slowly Changing Dimension
· Surrogate Key
· Data Mart
Power BI
Introduction
Introduction
· Power BI is a business analytics service by Microsoft.
· It aims to provide interactive visualizations and business
intelligence capabilities with an interface simple enough for end users to
create their own data reports and perform self analysis of data
 What is Power BI ?
· Power BI is a collection of
software services, apps, and
connectors that work together
to turn your unrelated sources
of data into interactive insights.

· The three elements – the Desktop,

the service, and Mobile – are
designed to let people create, share,
and consume business insights in
the way that serves them, or their
role, most effectively
Pre-requisites
Pre-requisites for Power BI Report
There are 2 things needed to start with, while creating a Power BI report –
1. Power BI Desktop

Power BI Desktop

2. Sample Data / Database along with connection strings

Power BI Terminologies
· Power BI Desktop - Free Windows application of Power BI which can be
installed on local computer that lets you connect to, transform, and
visualize your data. Used by report designers and admins.
· Report - A multi-perspective view into a single dataset, with visualizations
that represent different findings and insights from that dataset. Can have a
single visualization or many, a single page or many pages.
· Dashboard - A selection of visuals that communicate status and metrics.
A dashboard organizes and presents data in an easy-to-understand way.
· Workspace - Containers for dashboards, reports, and datasets in Power BI.
See app workspace in this term collection.
Power BI Home Page
KPIs and Data Visualizations
 What is KPI ?
· A Key Performance Indicator (KPI) is a visual cue that communicates the
amount of progress made toward a measurable goal.
· It creates an insight to the data

When to use a KPI ?

· To perform most common data analysis, eg - Average, Sum etc.
· To provide calculated results

How to use KPIs ?

· Calculated columns and measures are created using DAX queries, such as
total of a certain value, average of a commodity etc.
· Visualizations such as Card, Table, KPI etc., are selected to show the output
of these calculated measures.
KPIs in Sales Analysis Report
1. Worldwide Sales
2. Actual Sales
3. Average Sales
4. Sales Target
5. % Achieved
6. Sales Difference etc.
Creating a Power BI Report
Sales Analysis

The report is about the Sales Analysis of a vehicle company which has a
production of 7 type of items. The report captures their Sales Analysis –
based on various parameters like sales, demand, price etc.
Consuming Data

Data Transformation

Data Modelling

Data Visualization

Configuring Dashboard & Power BI

Services
A - Consuming and Transforming Data
using Power BI Desktop
Step 1 : Connect to Data Source
· Click on Get Data
· Select the Data source from where the data will be imported. The data
source could be anything, files, folder, SQL Azure, Big Data, SQL Server
Analysis Services (SSAS)
In this case the data sources are Text/CSV or Azure SQL DB.

(i) (ii)
Step 2 : Perform Transformations
· Apply Transformations – You can apply various changes and transformations
to data such as change or modify the data type, format etc.
· Power BI Data Pane is used in this.

(i) (ii)
Step 3 : Cleanse Data

· Manage Incomplete data - You can add or remove any column , replace
blank values etc., to make the data clean
· You can also replace missing data and incomplete values
B – Modeling and Visualizing Data Using
Power BI Desktop
Step 1: Create and Optimize Data Model

· Once data is consumed, data tables can be

joined and interlinked using the Relationship
Pane in Power BI.
· Active relationships and joins can be made.

(i)

(ii)
Step 2 : Create calculated columns, tables, and
measures
· In the Modeling page, under the
Calculations tab, select what you
want to create, either a New
Measure, New Column or a New (i)
Table
· Write the DAX query, stating the
condition or statement to build a new
column/measure (ii)

(iii)
Step 3 : Measure performance by using KPIs,
gauges and cards
· Various important counts such as 1. Select
Visualization,
average, actual value, target value, such as KPI,
actual vs target, total values etc. can gauge, card etc.
be represented in report
· Calculate values using measures or 2. Drag the data
column from the
calculated columns
fields tab, which you
· Represent using visualizations such have to represent
as KPI, card etc.

3. Result Visualization
Step 4 : Create Hierarchies

· Right click the data column to create a new hierarchy. Click on New
Hierarchy.
· Right click the columns to add to the hierarchy. Select Add to Hierarchy
option, and selected the hierarchy.

3. Hierarchy Created

1. Creating Hierarchy 2. Adding columns to

hierarchy
 Step 5: Create and format interactive visualizations
· Visualizations display insights that have been discovered in the data.
· A Power BI report might have a single page with one visual or multiple
pages full of visuals
· In Power BI service, visuals can be pinned from reports to dashboards

3. Result Visualization
1. Select the type
of Visualization

2. Drag the data column from

the fields tab, which you have
to represent
 Step 6 : Manage custom reporting solutions
· After building the report on Power BI Desktop, you can publish it to Power
BI Services for further uses

(i)
· To access Power BI Service, Sign in to Power BI.
· Select the Workspace in which you published
the report, and open the report.

(ii) (iii)
C – Configure Dashboard, Reports in Power
BI Service
Exporting a Report to Dashboard
· For exporting the complete report
– Select Pin Live on the top of the
report Page
(i)
· For exporting individual visuals,
select the icon
· In the dialogue box appearing,
select the Dashboard to which
report/visual has to be exported. (ii)

(iii)
Configure Dashboard

· In the dashboard, you can add

web content, image, text, video
etc.,
· Click on ADD TILE, and add
whatever custom content you
want
· To modify a visual, right click.
Select the option to perform. (i)

(ii)
Publish and Embed Reports

Dashboards can be published via –

· Web – attaching dashboard url in an online article
· Power BI Embed – creating app workspace and sharing it with third-party
users. Mainly for developers.
Power BI Service, Report Server and Office
365
Power BI Service
· Cloud – based business analytics service that allows us to conceive and
interpret data with a higher rate, performance, and intelligence
· It relates people to a vast variety of data via easy-to-use dashboards,
powerful reports, and beautiful visualizations that bring our data to life
Power BI Server

· An on-premises report server with a web

portal in which reports and KPIs are
displayed and managed.

· It has the tools to create Power BI reports,

paginated reports, mobile reports, and KPIs.

· Users can access the reports in different

ways: viewing them in a web browser or
mobile device, or as an email in their in-box.
Office 365

· Cloud-based service that is designed to help meet your organization's

needs for robust security, reliability, and user productivity
· Provides a comprehensive set of software tools and services that facilitate
office documentation, communication and management tasks
· No upfront installation or integration is required, other than an Internet
connection and supporting Web browser
Difference between Power BI Service, Power BI
Report Server & Office 365
Serial Feature Power BI Service Power BI Report Office 365
No. Server
1 Deployment Cloud On-premises or hosted Cloud
cloud
2 License Power BI Pro and/or Power BI Premium or Office 365 Business,
Power BI Premium SQL Server EE with SA Office 365 Enterprise,
Office 365 Kiosk
3 Create Power BI Yes No Yes ( Power BI is available
reports in the browser for Office 365 E5
subscription or you can
purchase licensing for it
separately)
4 Gateway Required Yes for on-premises data No Yes (For licensed version
sources with Power BI access, for
on-premises data
sources)
5 Real-time Streaming Yes No Yes (For licensed version
with Power BI access)
6 Dashboard Yes No Yes (With Power BI
Subscription)
Serial Feature Power BI Service Power BI Report Office 365
No. Server
7 Analyze in Excel Yes No Yes
8 Email subscriptions for Power Yes No Yes (For licensed
BI reports version with
Power BI access)
9 Email subscriptions for No Yes No
paginated reports
10 Data Alerts Yes No Yes (For licensed
version with
Power BI access)
11 Advanced Office 365 Yes No Yes (For licensed
collaboration version with
Power BI access)
DAX (Data Analysis Expressions)

· Data Analysis Expressions (DAX) is a library of functions and operators that

can be combined to build formulas and expressions in Power BI Desktop,
Azure Analysis Services, SQL Server Analysis Services, and Power Pivot in
Excel.
· Analyze growth percentage across product categories and for different
date ranges? Or, you need to calculate year-over-year growth compared to
market trends? DAX formulas provide this capability and many other
important capabilities.
· Data Types:
Binary
Boolean
Currency
Date Time
Decimal
Integer
String
Variant
Syntax:

A-Measure Name
B-Operator
C-DAX Function SUM
D-Parenthesis()
E- Referenced Table ‘Sales’
F-Referenced Column [SalesAmount]
DAX Calculations:
There are two primary calculations you can create using DAX:
· calculated columns
· calculated measures
DAX Functions:
There are many functions available to shape, form, or otherwise analyze
your data. These functions can be grouped into a handful of categories:
· Aggregation functions
· Counting functions
· Logical functions
· Information functions
· Text functions
· Date functions
Aggregation functions
DAX has a number of aggregation functions, including the following
commonly used functions:
· SUM (Ex: Measure Name=Sum(Table Name.[Column Name])
· AVERAGE (Ex: Measure Name=Average(Table Name.[Column Name])
· MIN (Ex: Measure Name=Min(Table Name.[Column Name])
· MAX (Ex: Measure Name=MAX(Table Name.[Column Name]))
· SUMX (and other X functions) (Ex: =SUMX(FILTER(InternetSales,
InternetSales[SalesTerritoryID]=5),[Freight])
Counting functions
Often-used counting functions in DAX include the following:
· COUNT
· COUNTA
· COUNTBLANK
· COUNTROWS
· DISTINCTCOUNT
Logical functions
The collection of logical functions in DAX include:
· AND
· OR
· NOT
· IF
· IFERROR
Information functions
Information functions in DAX include:
· ISBLANK
· ISNUMBER
· ISTEXT
· ISNONTEXT
· ISERROR
Text functions
The text functions in DAX include the following:
· CONCATENTATE
· REPLACE
· SEARCH
· UPPER
· FIXED
Date functions
DAX includes the following Date functions:
· DATE
· HOUR
· NOW
· EOMONTH
· WEEKDAY
Using variables in DAX expressions

DAX tables and filtering

Administration

· Creating Workspace
· Assign AD Group /user to the Workspace
· Publish Report to Workspace
· Security-Data level/Row Level