Introduction to Power BI
UNIT 1
Debangshu Chatterjee 1
What is Power BI?
• Power BI is a tool that lets you interactively explore data to gain insights.
• Power BI enables you to report insights effectively through easy-to-use customizable visualizations.
Debangshu Chatterjee 2
Data Visualization in Power BI
• Visually representing data lets people interpret and analyze data faster.
• For example, it's easier to find the most profitable year in a bar plot than scrolling through a spreadsheet!
Debangshu Chatterjee 3
Why Power BI?
• According to Gartner, Power BI is the leading BI tool.
• Over 97% of Fortune 500 companies use Power BI.
• In total, Power BI has over six million customers.
Debangshu Chatterjee 4
Power BI Components
Power BI Desktop Power BI service
Desktop version Cloud based version
Data analysis and report creation tool Light report editing
Includes Power Query Editor Share and distribute reports
100% free Paid
You will commonly use Power BI Desktop to create a report and Power BI service to share that report.
Debangshu Chatterjee 5
Power BI Pro
• Exclusive features
• Publish and share across the Power BI cloud platform
• Mobile app
• Collaborate with other Power BI users
• In this course will use the free version of Power BI.
• Even though we're using the free version, everything you learn in this course will be applicable to the paid version.
Debangshu Chatterjee 6
Power BI Interface
Debangshu Chatterjee 7
Power BI Interface - Three views
Along the left side are icons for
the three views:
Report,
Data, and
Model.
You can change views by
selecting any of the icons.
Debangshu Chatterjee 8
Power BI Interface - Report View
Report view is the default view.
In this view, you can create
reports and visuals.
Debangshu Chatterjee 9
Power BI Interface - Data View
In the data view, you can see
the data used in the data model
associated with your report.
Debangshu Chatterjee 10
Power BI Interface - Model View
In the Model view, you can see
and manage the relationships
among tables in your data
model.
Debangshu Chatterjee 11
Power BI Interface - Canvas area
Go back to the report view.
The canvas area in the middle is
where visualizations are created
and arranged.
Debangshu Chatterjee 12
Power BI Interface - Filters pane
In the Filters pane you can filter
data visualizations.
Debangshu Chatterjee 13
Power BI Interface - Visualizations pane
In the Visualizations pane you
can add, change, or customize
visualizations.
Debangshu Chatterjee 14
Power BI Interface - Fields pane
Fields pane shows the available
fields.
You can drag these fields onto
the canvas, the Filters pane, or
the Visualizations pane to
create or modify visualizations.
Debangshu Chatterjee 15
Data Requirements
• Shaping the direction of the project
• What data do we need?
• What insights are we seeking?
Debangshu Chatterjee 16
Retail Sales Analysis
• Business Objective: Our organization is a retail company looking to enhance
decision-making through data-driven insights. We want to analyze our sales
data to identify trends, track performance, and make informed decisions to
optimize our product offerings and sales strategies.
Debangshu Chatterjee 17
Step 1: Identify Key Business Questions
• Sales Performance:
• How are our sales performing over time?
• Which products are top sellers, and which ones are underperforming?
• Customer Behavior:
• Who are our most valuable customers?
• What is the average transaction value?
• Inventory Management:
• How is the inventory turnover rate?
• Are there products that are consistently out of stock?
Debangshu Chatterjee 18
Step 2: Define Key Metrics and KPIs
• Sales Metrics:
• Total Sales
• Sales Growth Rate
• Top-selling Products
• Customer Metrics:
• Customer Lifetime Value
• Average Transaction Value
• Customer Segmentation
• Inventory Metrics:
• Inventory Turnover
• Stockout Frequency
Debangshu Chatterjee 19
Step 3: Identify Data Sources
• Sales Data:
• Point-of-sale systems
• Online sales platforms
• Customer Data:
• Customer relationship management (CRM) systems
• Sales transaction records
• Inventory Data:
• Inventory management systems
• Purchase and restocking records
Debangshu Chatterjee 20
Step 4: Consider Data Granularity and Time Period
• Granularity:
• Daily, weekly, or monthly sales data?
• Individual or aggregated customer transactions?
• Time Period:
• Historical data for trend analysis
• Real-time or near-real-time data for current insights
Debangshu Chatterjee 21
Step 5: Identify Potential Challenges
• Data Quality:
• Ensure consistency and accuracy of sales, customer, and inventory data.
• Integration:
• How will we integrate data from different sources into Power BI?
• Security:
• How will we handle sensitive customer and sales data?
Debangshu Chatterjee 22
Power BI Project Roles
• Power BI Developer:
• Designing and building Power BI reports and dashboards.
• Implementing data transformations and modeling in Power BI Desktop.
• Data Analyst:
• Analyzing and interpreting data to extract meaningful insights.
• Collaborating with business stakeholders to understand data requirements.
• Data Modeler:
• Designing the underlying data model for Power BI reports.
• Establishing relationships between different data tables.
Debangshu Chatterjee 23
Power BI Project Roles
• Power BI Administrator:
• Managing Power BI workspaces and access permissions.
• Overseeing data refresh schedules and gateway configurations.
• Business User (Consumer):
• Interacting with and consuming Power BI reports.
• Providing feedback to improve report usability.
Debangshu Chatterjee 24
Collaborative Workflows
• Development Workflow:
• Iterative process from data discovery to report deployment.
• Effective communication between developers, analysts, and modelers.
• User Feedback Loop:
• Role of business users in providing feedback for report improvement.
• Power BI features like comments and annotations facilitate communication.
Debangshu Chatterjee 25
Admin and Project Role Collaboration
• Power BI Administrator Responsibilities:
• User Management:
• Creating and managing user accounts in the Power BI Service.
• Assigning appropriate roles to users based on their responsibilities.
• Project Role Collaboration:
• Workspace Creation:
• How administrators create workspaces for project teams.
• Granting access to developers, analysts, and other roles.
• Collaboration Features:
• Sharing and Collaboration:
• Discussing methods for sharing Power BI reports and dashboards.
• Utilizing collaboration features such as commenting and sharing insights.
Debangshu Chatterjee 26
Security and Access Control
• Row-Level Security (RLS):
• How administrators implement RLS to control data access based on user roles.
• Data Gateway Management:
• Ensuring secure data access for on-premises data sources using gateways.
Debangshu Chatterjee 27
Power BI Licenses
• Power BI Free:
• Features:
• Basic report and dashboard creation in Power BI Desktop.
• Limited sharing and collaboration features.
• Use Case: Individual users, learners or small projects with minimal collaboration needs.
• Power BI Pro:
• Features:
• Full access to Power BI Service collaboration features.
• Sharing and collaboration on reports and dashboards.
• Use Case: Teams and organizations requiring advanced collaboration and sharing capabilities.
• Power BI Premium:
• Features:
• Dedicated cloud resources for enhanced performance and scalability.
• Capacity for large datasets and high-concurrency scenarios.
• Use Case: Large enterprises or organizations withChatterjee
Debangshu high-performance and scalability requirements. 28
License Assignment and Administration
• Assigning Licenses:
• How administrators assign licenses to users based on their roles and needs.
• License Considerations for Different Roles:
• Developer vs. Consumer Licenses: Understanding the appropriate license for Power BI developers and
report consumers.
• Pro vs. Premium: Determining when to opt for Pro licenses or upgrade to Premium for enhanced
features.
Debangshu Chatterjee 29
License Assignment and Administration
• Assigning Licenses:
• How administrators assign licenses to users based on their roles and needs.
• License Considerations for Different Roles:
• Developer vs. Consumer Licenses: Understanding the appropriate license for Power BI developers and
report consumers.
• Pro vs. Premium: Determining when to opt for Pro licenses or upgrade to Premium for enhanced
features.
Debangshu Chatterjee 30
Licensing Best Practices
• Monitoring License Usage:
• The importance of monitoring license usage to ensure efficient allocation.
• Scaling with Power BI Premium:
• How organizations can scale their Power BI deployment using Premium capacity.
• Practical Considerations: Upgrading to Premium may be cost-effective for an
organization.
Debangshu Chatterjee 31
Data Warehouse Bus Matrix
• A Data Warehouse Bus Matrix is a
visual representation that aligns
business processes (or functions)
with corresponding data
dimensions.
• It helps establish a standardized
framework for organizing data
across the data warehouse.
Debangshu Chatterjee 32
Data Warehouse Bus Matrix
• Example: Imagine a Grocery Shopping List
• Think of a Data Warehouse Bus Matrix like a well-organized grocery shopping list. When
you go to the grocery store, you usually have different categories of items you need, like
fruits, vegetables, dairy, and so on.
• Now, imagine you're not the only one shopping. Your family members or friends are also
contributing to the list, and everyone has their own preferences and needs.
Debangshu Chatterjee 33
Data Warehouse Bus Matrix
• Applying the Idea to Data
• In the business world, departments have their own needs for information (just like family
members have their preferences for groceries). For example, the sales department might
be interested in information related to products, customers, and time periods. On the
other hand, the finance department might be interested in financial data, expenses, and
budgeting.
• The Data Warehouse Bus Matrix is like a smart way of organizing this information. It's a
chart that shows which departments (or business processes) need which kinds of
information (or data dimensions). So, it helps everyone stay organized and make sure they
get the data they need without any confusion.
Debangshu Chatterjee 34
Benefits of the Data Warehouse Bus Matrix
• Clarity: It makes everything clear and
organized, just like a well-structured
shopping list.
• Consistency: Everyone uses the same
standards, so there's no confusion
about what each department needs.
• Efficiency: Just as you don't waste time
at the grocery store searching for items,
businesses don't waste time figuring
out what data to use because it's all laid
out in the matrix.
Debangshu Chatterjee 35
Creating a Data Warehouse Bus Matrix
• List Departments (Business Processes):
Identify each department's specific
needs or interests.
• Identify Data Categories (Data
Dimensions): Determine the different
types of information each department
needs.
• Make the Matrix: Create a simple chart
that shows which departments are
interested in which types of
information.
Debangshu Chatterjee 36
Dataset Design Process
• Data Source Connection:
• The first step is connecting Power BI to your data source, whether it's a database, Excel file, or an
online service.
• Data Import vs. Direct Query:
• Understanding the difference between importing data into Power BI for analysis and using Direct Query
to connect to data in real-time.
• Data Profiling:
• Analyzing the characteristics of the data, such as data types, null values, and unique values, to
understand its structure.
Debangshu Chatterjee 37
Data Transformation and M Query Design
• Query Design per Dataset Mode:
• Depending on whether you're working in Import Mode or Direct Query Mode, the design of your
queries may differ.
• M Queries and Data Transformations:
• M is the language used in Power BI for data transformations. Understanding how to use M queries is
crucial for shaping data.
Debangshu Chatterjee 38
Best Practices in Dataset Design
• Data Types and Relationships:
• Ensuring proper definition
of data types and
establishing relationships
between tables for
accurate analysis.
• Bridge Table Queries and
Security Tables:
• Creating bridge tables to
handle many-to-many
relationships and
incorporating security
tables for data access
control.
Debangshu Chatterjee 39
Data Profiling
• What is Data Profiling?:
• Data profiling is the process of examining and analyzing your data to understand its structure, quality,
and characteristics.
• Think of data profiling as investigating the ingredients before cooking—they help you understand the
quality and potential of what you're working with.
• Importance of Data Profiling:
• Data profiling helps identify issues like missing values, outliers, and patterns, ensuring data quality and
guiding data transformation decisions.
Debangshu Chatterjee 40
Data Profiling
• Power Query Editor for Data Profiling:
• The Power Query Editor in Power BI provides tools for data profiling, allowing you to analyze data
types, distribution, and statistics.
• Data Profiling in Power BI Desktop:
• Built-in data profiling features in Power BI Desktop help you understand your data before creating
visualizations.
Debangshu Chatterjee 41
Key Aspects of Data Profiling
• Data Types and Cardinality:
• Understanding the types of data in each column and the cardinality (distinct values) helps in shaping
data appropriately.
• Missing Values and Outliers:
• Identifying missing values and outliers is crucial for data quality and informs strategies for handling
these issues.
Debangshu Chatterjee 42
Best Practices in Data Profiling
• Consistency and Documentation:
• Maintaining consistency in data profiling practices across projects and documenting findings for future
reference.
Debangshu Chatterjee 43
Dataset Planning
• What is Dataset Planning?:
• Dataset planning involves strategically outlining the structure, components, and connections of your
dataset before you start building it in Power BI.
• Analogy: Consider dataset planning as creating a blueprint before constructing a building—it ensures a
solid foundation and efficient design.
• Importance of Dataset Planning:
• Well-thought-out planning reduces errors, improves performance, and ensures that your dataset aligns
with the business goals and reporting needs.
Debangshu Chatterjee 44
Key Considerations in Dataset Planning
• Define Business Requirements:
• Clearly understand the business questions and reporting needs to define what data is essential for
analysis.
• Identify Data Sources:
• Determine where your data resides—whether it's in databases, spreadsheets, or cloud services.
• Data Granularity and Time Period:
• Decide on the level of detail (granularity) needed for analysis and establish the time periods relevant
to your reporting.
• Relationships and Join Types:
• Plan how tables in your dataset will relate to each other and choose appropriate join types for
combining data.
Debangshu Chatterjee 45
Strategies for Optimal Dataset Design
• Normalization vs. Denormalization:
• Decide whether to normalize your dataset (reduce redundancy) or denormalize it (combine data for
simplicity).
• Considerations for Calculated Columns and Measures:
• Strategically plan where to use calculated columns and measures for efficient data calculations.
Debangshu Chatterjee 46
Data Transformations
• What are Data Transformations?:
• Data transformations involve modifying, cleaning, or restructuring your data to make it suitable for
analysis and reporting.
• Analogy: Think of data transformations as preparing ingredients before cooking—a necessary step for
a successful dish.
• Importance of Data Transformations:
• Well-executed transformations enhance data quality, ensure consistency, and make the data more
meaningful for analysis.
Debangshu Chatterjee 47
Common Scenarios for Data Transformations
• Cleaning and Handling Missing Values:
• Identify and address missing or inconsistent values to ensure data integrity.
• Text and Date Transformations:
• Modify text formats, extract information, and standardize date formats for consistency.
• Aggregations and Calculations:
• Create new calculated columns or measures to perform aggregations and derive additional insights.
Debangshu Chatterjee 48
Power BI Tools for Data Transformations
• Power Query Editor for Transformations:
• The Power Query Editor is a powerful tool for shaping and transforming data before it enters the
Power BI model.
• Steps to Perform Data Transformations:
• Outline the general steps involved in data transformations, including filtering, sorting, and merging
data.
Debangshu Chatterjee 49
Advanced Data Transformations
• Conditional Logic and Custom Functions:
• Implement conditional logic and custom functions for more complex and tailored transformations.
Debangshu Chatterjee 50
Import Mode and Direct Query Mode
• What is Import Mode?:
• Import Mode involves loading data from source systems into the Power BI model, where it resides for
analysis.
• Analogy: Imagine bringing ingredients into your kitchen to cook—a chef decides what to use before
starting the cooking process.
• Scenarios for Import Mode:
• Import Mode is suitable when data needs to be transformed, aggregated, and cached for faster and
more efficient analysis.
• Benefits and Trade-offs of Import Mode:
• Faster performance and offline access but discuss trade-offs like potential delays in reflecting real-time
data changes.
Debangshu Chatterjee 51
Import Mode and Direct Query Mode
• What is Direct Query Mode?:
• Direct Query Mode allows Power BI to send queries directly to the source system in real-time without
importing data into the Power BI model.
• Analogy: Visualize a chef cooking in a restaurant kitchen with direct access to a farm—ingredients are
used directly without bringing them into the kitchen.
• Scenarios for Direct Query Mode:
• Direct Query Mode is beneficial when real-time access to the latest data in the source system is
critical.
• Benefits and Trade-offs of Direct Query Mode:
• Benefit of real-time data access but trade-offs like potential performance issues with large datasets or
complex queries.
• Factors Influencing Mode Selection:
• Consider factors such as data volume, frequency of updates, and the need for real-time data when
selecting between Import and Direct Query Modes.
Debangshu Chatterjee 52
30.11.23
• Opening a file in Power BI
• Getting data to Power BI - Load
• Data View – How the imported data looks
• Report View – Creating visualizations
• Problem in creating visualization
• Model View – the concept of data modelling
• Different Aggregation
• How to format the visualizations
• Clustered Column
• Table
• Card
• Text Box
• Slicer
• Saving the file
• How to make the canvas bigger
Debangshu Chatterjee 53
Visualizations
• Stacked Bar Chart –
• includes multiple elements in one bar.
• It shows the different series as a part of the same single column bar, where the entire bar is the total
• Clustered Bar or Column Chart –
• shows multiple bars to represent values
• they are located next each other instead of being stacked
• 100% Stacked Bar Chart –
• shows the relative percentage of multiple data series in stacked bars
• the total of each stacked bar always equals 100%
• Combo Chart –
• combines a column chart and a line chart
Debangshu Chatterjee 54
Visualizations
• Line charts –
• show multiple lines in one chart.
• They emphasize the overall shape of an entire series of values, usually over time.
• Area charts –
• based on line charts with the area between the axis and line filled in
• Pie and donut charts –
• show the relationship of parts to a whole
• Donut charts are similar to pie charts
• Tree maps –
• Shows relationship of parts to a whole
• They are charts of colored rectangles, with size representing the proportional values
Debangshu Chatterjee 55
Visualizations
• visuals that are designed to show one or two values and are used for showing the overall level of
performance
• Cards – show one value
• Multi-row cards – display multiple values
• Gauge – show actual data compared to budgeted data
• KPIs – show actual data compared to budgeted data
• Table – a grid that contains related data in a logical series of rows and columns. It may also contain
headers and a row at the bottom for totals
• Matrix – a matrix can be collapsed and expanded by rows and/or columns.
Debangshu Chatterjee 56
13.12.23
• Sorting and formatting data
• Creating Hierarchy
• Drill Down
• Filtering
• Turning off filtering
Debangshu Chatterjee 57
Email Id: [email protected]
Contact Number: +919971366473
Debangshu Chatterjee 58
