🔥 What Is Power Bi Introduction To Power BI Power BI For Beginners Simplilearn

welcome to Simply learn in this video we
will see what is powerbi and some
fundamentals of powerbi so before we
begin consider subscribing to Simply
learn and hit the Bell icon to never
miss any updates from us we will first
talk about why powerbi you know why it's
a popular tool and what problem it
solves what is
powerbi uh and what are the primary
features of power VI uh which you can
use in your day-to-day data analytics
visualizations creating fancy reports
creating meaningful intelligent reports
uh for your organization for your
personal use uh for crunching numbers
for generating reports real time
Etc now the most popular tool for
powerbi is the powerbi desktop uh I'll
show you certain uh aspects of powerbi
desktop and then I'll show you the steps
how to install powerbi desktop on your
machine and then definitely power bi
desktop is a free tool provided by
Microsoft but then you can also
subscribe uh for an Enterprise version
which is primarily used uh by
Enterprises for publishing their data uh
so we'll see the difference and then
overview of O uh dashboards which can be
created uh you know what kind of
dashboards can be created in
powerp so this is the agenda for us
today
now why powerbi so generally uh you know
visualization tools reporting tools are
required in order to create and prepare
and analyze meaningful data it could be
a data for an organization it could be a
social media platform data it could be a
data from iot devices but something
which needs to be analyzed and some
intelligent inferences and data mining
has to be done on top of it now imagine
there is today we are in a world where
terabytes of data and information is
getting generated on a instantaneous
places on minute by- minute basis so it
becomes very essential to churn out
something meaningful something
intelligent out of it in the market
there are lot of other tools where which
are available like kilick uh altrix tblo
and powerbi so powerbi is a mic
Microsoft product which is one of the
most popular products and it comes as a
free to download product Microsoft
powerbi desktop which is available and
I'll show you couple of ways how you can
install it on your machine but why
powerbi is um uh popular is because it
provides a lot of outof the-box features
Dragon doop features which we will talk
about in our subsequent sessions and uh
you know classes but today's session is
primarily focused on giving you guys an
introduction on what is the purpose of
powerbi and what all problems it solve
uh in the in the real world so powerbi
allows you to VI view analyze and
visualize huge quantities of data and
the data could be in any format Excel
CSV text or it could be uh a direct
connection to a database like SQL MySQL
Azure Oracle anyone IBM db2 so it
supports n number of uh uh you know data
types or data sets and it's very
powerful in terms of data connectivity
so it uses powerful compression
algorithms to import and cache the data
within the PB file so it's as convenient
as a simple software if suppose you
import a data and then you prepare a
report and then you can easily share the
reports uh with your peers or someone
who's Cod developing with you either
through powerbi cloud services or even
you can share the PBX file in an email
or through any other means and you can
share the data set with the concern and
they can then work on the report
independently so there are different
ways there is no uh kind of a limitation
for you know uh working on powerbi there
are multiple ways and it is very fast it
is the most fast uh tool to work with
Excel because definitely Excel is also a
Microsoft Technology so it works very
fast on Excel based data and gives you
numbers and Reporting at a very high
speed so now once you have imported the
data powerbi allows you to model the
data allows you to work intelligently on
the data it allows you to model the data
in a way that if you are importing data
from multiple Excel sheets importing
data from multiple tables you can easily
create a relationship between those
tables or data sets in powerbi and then
create visually appealing reports
meaningful reports as I've been
emphasizing and make sense out of that
data no data in silos is of any use data
in Silo means a single worksheet or a
single data set will not churn out any
meaningful information until unless you
basically join it Club it merge it Union
it append it with some other data sets
because a single data set will never be
able to hold that much amount of
information which is generally required
for a you know important
report so it has easy drag and drop
functionality with features that allow
you to copy all formatting across
similar visualizations so just like in
Microsoft Excel we use format painter to
copy the format of one cell to another
it similar features very very similar to
excel products or Microsoft product they
have provided that if you have applied a
a particular theme on a report you can
easily replicate that on any other
report the font uh the header size the
background color you don't need to do it
again and again so there's lot of
reusable features which are also
available okay now now as I said Excel
is a Microsoft product powerbi is a
Microsoft product so they have
intercompatibility you can publish data
from Excel to powerbi now with the
latest developments and
enhancements as of today Exel has also
uh plugged in a new feature called Power
pivot which I'll uh show you uh later
down the line but that also allows you
to do a quick analysis not you can't
create create of course complex reports
or uh fancy reports like powerbi but
Power pivot allows you to create you
know quick measures quick functions
quick calculations on your data quickly
only in Excel so it's an Excel plug-in
but whereas you know you can powerbi is
also compatible with Excel so when you
create a report in powerbi it gives you
inbuilt feature to export your powerbi
report into Excel format directly you
don't need to do any programming for it
also uh you can easily publish when you
publish your powerbi reports it allows
you to give some inbuilt intelligence of
analyzing your reports in Excel and it
gives you all those of all those
features of exporting your powerbi
reports into Excel which is not
available in any other tool or otherwise
those tools have to create plugins
create addins and probably they might
might charge for it uh but powerbi comes
with lot of out of the box features
which are very very helpful for
analyzing data in Excel and vice
versa Azure Cloud now Azure itself again
is a Microsoft cloud uh Tex tack so
using powerbi with Azure allows you to
analyze and share large volumes of data
so Azure basically Azure database or
Azure Cloud servers are meant to hold
huge amount of data and powerbi allows
you to have seamless connection you can
easily connect to Azure data Lake uh you
can reduce the time it takes to get
insights and increase collaboration
between business analysts data engineers
and data scientists so as your data L
becomes the central focal point where
all your uh analysts Engineers uh can
keep working on on the centralized piece
of data and churn out their reports data
SCI scientists primarily job is to keep
the data in a structured way optimized
way optimize the input output operations
disk operations and memory utilization
so that the reports also get churned out
in a faster manner so every uh you know
every person has their own role in order
to give a quick refreshable report a
quick rendered report any report which
is taking huge amount of time to get
rendered will not eventually be used by
the business users because then it does
not solve the purpose the report should
be fast report should have uh you know
appropriate filters slices and dices you
should be able to you know uh create the
reports or dynamically or you should be
able to analyze visualize the data
dynamically so all those visualization
features are available in powerbi and it
works seamlessly either it is small data
or huge data uh it allows that you to
work on those kind of data in a seamless
fashion right so this is just a very uh
quick uh example of uh how a typical
dashboard looks like dashboard is
nothing but you know you have clubbed
couple of multiple reports on a single
page and you know if you change a filter
uh a single filter on the page all the
reports will honor that filter and the
numbers will change accordingly so if
you see the in this example there's a
filter or a drop down of product ID
product name employee name or supervisor
or a date range so whatever date range
or filter you will apply all the reports
on this dashboard will get changed based
on the filter you have selected so
powerbi allows you to get insights from
data and turn insights into action to
take data driven business decision and
that is the ultimate goal of any
visualization tool that is the purpose
for which visualization tools are
bought uh and purchased by the
organizations and data is fed into
them now powerbi fetches data from
Factory sensors social media sources to
get access to realtime analytics so that
you are always ready to make timely
business decisions to so basically there
is a uh feature of live connection or
cut off data connection so either you
can work on data which is deciding on a
machine and you can just work on the cut
off data like for example it's there's a
data which is available for sales 2017
2018 and you're just working on a
historical data it's a cut off data or
it could be possible that you want to be
connected live uh to a uh realtime iot
based sensor based data or social media
data like Twitter Facebook feeds or you
know you connected to live Google
worksheets that is also possible you
just need to publish your Google sheet
for a public domain embed the UR into
powerbi and then whoever updates that
Google sheet automatically the powerbi
report will also start honoring and
consuming the new data which is added in
the uh Google sheet so all those kind of
realtime streaming analytics is also
possible and that is one big feature uh
and very important feature of powerbi
which is widely used and has a very huge
uh you know Market acceptance and Market
utilization now what is
powerbi powerbi is a business analytics
service provided by Microsoft that lets
you visualize your data and share
insights right so earlier you know U uh
Microsoft used to have a technology
called ssas now they have replaced
actually ssas and S SSRS with powerbi so
basically you can use powerbi on the
data which is there in your Excel or any
other data source and the PBI service or
PBI desktop uh basically creates a
connection to those data sets and uh
import it cach it and give you a handle
to it in order to work with it so you
can create these fancy meaningful
visualizations like for example there's
a geographical map if if you are
importing data for a country or a
continent or a region powerbi will
automatically detect that it's a
geographical information and give you a
map with latitude longitude information
and you just need to plot uh your uh
your numbers on the map either you can
use bubbles or or either you can use
triangles or whatever data uh structure
you want to use but all the mapping will
be available geographically then you can
create pie charts which is shown in this
visualization you can create tree map
apps you can create cards where you know
you can highlight the most important
numbers like sales total sales of your
company uh across all the regions or the
growth chart or the month-on-month uh
you know sales of your organization or
number of total number of products or
units sold so whatever is important and
to be highlighted for the management to
take any meaningful decision or any
insights you want to share powerbi
visualization tool the powerbi
visualization uh uh uh chart allows you
to drag and drop and create wonderful
reports okay so what are the features of
powerbi so powerbi desktop is something
uh Standalone tool which you need to
install on your machine it allows you to
build reports by accessing data easily
you do not need Advanced report
designing or query skills to build a
report report though yes it is
beneficial that if you know uh some SQL
programming analytical programming or
you are aware of advanced features of
any analytical tool that might help you
but that's not a showstopper uh you can
easily build reports in quick turnaround
time without H needing any technical
background you just need to have some
analytical uh mindset and you can create
uh Savage visualization and you know
analytical
reports stream analytics as I mentioned
you can create a live connection uh with
any kind of uh data it could be iot it
could be media social media it could be
Google Docs it could be uh you know any
other kind of uh you know live
connection it could be a live database
connection itself so any insertions or
updations or deletions happening will
automatically reflect in your
report yes multiple data sources and
that has to be the uh primary criteria
for any tool to be popular if any
visualization tool is limited to certain
data sets then you know it'll not be
highly acceptable in the
market and custom visualizations right
so as I showed you certain uh examples
in the past uh in the previous
presentation uh that feature is very
important because someone might want to
look the kpis look at the kpis from a
different perspective some management
might want to look at the kpis from a
different perspective so you need to you
need to have uh that capability to
create different visualization from the
same data set now let's take a look that
how to install powerbi desktop on your
machine so basically what you need to do
is
you need to go to this
URL powerbi do
microsoft.com enus desktop okay and you
need to just enter
this now you can download it for free so
just click over here
and
it will open Microsoft store so
basically now what Microsoft have done
in the latest operating systems is that
when you are trying to download you can
actually directly go to the Microsoft
Store and search for
powerbi so let's wait for a couple of
seconds
right here so powerbi desktop in
Microsoft store for me it's already
installed so it's asking me to open it
I'll open it in a while but for you for
anyone who has not installed he will see
the button of install over here and it
will automatically install in your uh
machine and then you can easily go and
open powerbi Desktop now if I click open
over
here
now this is
the uh UI of the powerbi desktop I'm not
going to go right now in creating
reports right away we will talk about
that in a subsequent session with sample
data sets and we will cover the features
of powerbi desktop one by one but this
is what it is this is the whole tool of
powerbi which is having the
visualization Paine all the different
visualization are you know can be
created from this pane then this is the
pane which allows you to select the data
data fields then there is a report view
data View and relationship view the data
model view where multiple relationships
you can create you can view the data uh
in the grid of the tables which you will
create and the reports so you can create
multiple reports uh on multiple Pages
you can keep adding pages either you can
D create multiple reports on a single
page and it will become a dashboard or
you can create separate independent
reports on the single
page and these are the menu options
which we will talk about how you can
change color scheming you can do data
modeling you can create new reports and
you can also transform data which is the
biggest feature extract transform and
load the data apply different logic
change changing data types massaging the
information creating new joins appending
the data you know adding new columns etc
etc uh we that is what you can do in
transform data so this itself is a whole
different world it's a diff uh dedicated
topic so we will talk about that in our
subsequent sessions so what's in it for
us today we will be learning how to
connect to data different data types
data files like Excel PDF then what are
the different data importing modes and
then I will also show you practically
different sets how to import them in
power VI and use it for your
visualization purpose now what are the
steps to connect to data so now we will
go directly into powerbi and try to
import one by one few most commonly and
popularly used data sets which are most
commonly used uh in a day-to-day activ
activity rest of course there are uh
powerbi supports n number of data
sources uh but we will do something
practical on the most popular ones so
let's let's open our
powerbi now this is my powerbi and first
I want to show you that how can I import
data directly from a web page and import
the data now it is asking for a URL in
order to import data so what I have done
is I have created a Google Excel sheet
with simple data with rows and columns
and what I've have done is I've have
shared this sheet as published to
web okay so you just need to say publish
to the web the link as web page and say
done it's it's automatically published
and say link so copy the link
which you have published on the web copy
this link and then go back to your Tablo
paste it link over here and click
okay now powerbi will try to establish a
connection with this Google doc sheet
because it's published on the
web you need to wait for a while while
it is
reading okay now it has read one of the
HTML tables so I'll select this one now
you can see it has it is showing me a
preview of the table which is there on
my Google sheet right it has 11 rows so
it has all showed all the 11 rows so now
I can go and transform this data because
I can see my headers are there starting
from the second row so there's an
opportunity for me to transform the data
so I'll go and transform it so that it
looks
clean okay so first is I need to remove
the first row which is
the null row remove the top
rows okay and then I need to use the
first row now as a header so you just
click this option use first row as
headers that's it so now if you see my
row ID order ID order date ship date all
my data is now ready so I can say close
and
apply click apply
changes now this is an example of web
data import you can go and preview your
data right now uh the biggest advantage
of this data connection is that it's a
live data so for example I insert
another
row let me change the order
ID some some I've some changed some
basic stuff and
I it's Auto saved crl s now I'll go to
my Tabo and I'll
refresh
now you can see as I refreshed my power
query editor I clicked refresh all and I
got my new row which is there in the
live data I got that fetched from
my okay I got that row the row row ID
number 12 so I I have to say close and
apply
now you can see the new row the row
number 12 is now available in my new
data set in the data set because it's a
live connection it's a live connection
with the web based Google sheet okay so
this is one important way in which you
can import data
now let's try to import data from a text
file now I have already prepared a text
file called subcategories do
txt now let me just open it in a notepad
now it's a very plain simple file tab
separated file in which you have product
subcategory ID subcategory name and
product category key so basically to
which product category this particular
sub product belongs to right so what I'm
going to do is I'm going to go back to
my get data
option and I'm going to select text SL
CSV
option and I'm going to select option
mod product subcategories do
txt okay so now powerbi has identified
that it's a tab delimited file it has
recognized the headers Etc right and I
can now directly load this
file okay so now once the data is
imported in powerbi it is like
irrelevant to me it's a composite data
in import I'm doing right so in my
presentation when I'm talking about
importing data there are different
importing modes right import data import
can happen through different ways
okay one is direct query mode in which I
create a live uh connection to the
database which I'll also show you uh
using MySQL and mssql server and also
you can do a composite mode in which you
can have data imported from Excel plus
you can have direct query modes so you
can have multiple uh modes to connect
and create a composite data model and
that's what we are doing right now in
our practical so what we doing over here
is one we have imported data from the
web second we have imported data from a
text table now after doing text now our
next task is to import from CSV let's
try another one so now I have imported
product
subcategory now I'll
import a CSP file so again I'll choose
the option text / CSV and now in this
CSV file let me open this CSV file and
show you what in is
it so this is a list of all my products
product key product subcategory key
products uh stock keeping unit Etc a
simple CSV file and I'm going to import
that okay so now it has identified the D
limiter is comma rather than a tab and
it has already recognized the headers
correctly so I'll load
it okay so now my products are there
product subcategories are there for
product categories now what I have done
is I have created a Excel mode now so
now Excel I'm am using to import my
product category so now I have to click
on the option of import data from Excel
and I'll say product
categories select the sheet
load and now so my products product
categories product subcategories though
with different uh data storage types but
still now the data is imported into
powerbi it is a composite data model now
another very important data type which
you can import is the PDF also right so
what I've have done is I have created a
PDF called customers my customers data
is lying in a PDF so what I've done is
I've created a
PDF which has data for some columns are
there like you know custom customer key
prefix first name last name birth date
marital status gender email address
annual income total children etc etc so
this is the data set which I have
created in PDF so what I'm going to do
is I'm going to select PDF
now and import customers. PDF and see it
has recognized my table on page one
which I'm going to
load
okay you can rename this as
PDF
table so this basically these are the
different typ type
of
data types we have imported PDF Excel
text CSV and web page now let's take a
look at another interesting data set
which we want to import is the my SQL
Server data
set so what I've done is I've already
installed my SQL server on my local
instance and there's already a schema of
SQL live tutorial over there and I have
certain tables
already prepared over there like
Department employee Etc so my goal is
now to import this data or create a live
connection with this data set now in
order to
import my SQL database Connection in
powerbi you need to
First download a
connector my SQL powerbi connector so
you need to go to this
link and then click on download and
install the MySQL connector based on the
operating system you have you click on
download and install it after you have
done this go back to
powerbi and then give the IP address of
the database in my case it's there in
this local machine and and the schema
which I want to import is SQL live
tutorial so I'll give the
name click
connect okay now it's connected so now
it is asking me which particular tables
you want to create a connection with I'm
choosing department and
employee and I'm just loading
them
okay so now this is the exact data which
is there in the employee and Department
in my SQL okay so this is one example of
how to create connectivity between
powerbi and MySQL now I want to do the
same thing using SQL Server Microsoft
SQL Server so I have also installed
Microsoft SQL server on my machine and I
have used the SQL Express so this is the
name of my server so which I'll copy the
server name and go to get data select
SQL
server and for now database is optional
I can say direct query click
okay okay now it is showing me what all
tables I can import so in my SQL Server
tutorial in my SQL Server I
have I have these three tables customers
employe TR Olympic events so I can use
probably the customers one which
is
now you can see this is the data the
customers data which is lying in my SQL
Server okay so I can preview it and load
it so now you can you can preview the
data in uh
powerbi that this is the data so I can
rename is customers from
mssql and this is
from my
SQL
and okay okay so now this is not the
only uh
data sets you can import now if you take
a look at the options which powerbi gave
of what different type and variations of
data it can it has compatibility to
import
from okay so we can just take a look at
the categorization on the left hand side
first there are file based like Excel
text XML Json is also possible you can
evenly directly import entire folder and
within the folder whatever uh data types
of files are there it will detect it PDF
Pary or even SharePoint folder which is
itself
Microsoft uh technology then different
kind of databases SQL server and my SQL
we just saw but it's not only limited to
this you can connect to Microsoft Access
ssas Oracle database IBM db2 postgress
uh sbase Terra dat and then sap uh
databases Amazon red shift Impala
vertica Snowflake and N number of
databases which are there in the market
today uh Amazon Etc then it also allows
you to connect with its own power
platforms powerbi platforms data Mars
powerbi data flows data ver Etc aure
there are different kind of storage uh
mechanisms in azour and azour itself is
a Microsoft Technology uh so it has a
compatibility with lot of aour uh based
data storages like azour SQ database
blob storage uh azour data braks right
aor HD Insight spark so if you have
those kind of services running on your
Azor cloud services you can even import
them over
here now online services like you know
you have erps running uh or some data
which is shared on the internet if you
want to import it uh that is also
possible through certain products uh
Dynamics 365 Microsoft Exchange online
Salesforce Google analytics Adobe
analytics GitHub uh LinkedIn sales if
you want to do some analysis of some
social
networking uh you know feeds that also
you can
import then other miscellaneous are also
there web based Hive R script python
script if there's something to import
get data from uh Google Sheets like we
saw one example in our video right now
so there are multiple options
available now once you have imported the
data which is relevant to you um in our
subsequent sessions we will see how to
create relationships but just giving you
a glimpse that whatever data you're
importing powerbi autod detect certain
relationships and it will create for you
but then you can go and manually also
change so this is the composite data
model which is getting created in the
back end while you are importing the
data you can easily go and manage these
relationships either keep them as is you
can delete and create new ones manually
so there is no limitation in
that so this is what we have witnessed
we have imported data from different
files types data types and then you know
we have tried to once it is imported
into uh powerbi then there is no
limitation of how you use it you can
create visualizations across different
data sets and then create
your standard
reports so this is the example of
importing data from web importing data
from a database from a PDF and then once
you have data you can shape and combine
data you can basically do what whatever
transformation you want to do you want
to uh make joins merge the data so for
example if you go back to our powerbi
and if I go back to my transform data
section now as I have now different data
sets available with me I have I can do
any kind of uh you know operation
transformation on the data right uh so
like I showed you I uh upgraded the
header row because one of the imported
data was not showing the header
correctly uh or this columns like this
exact one column is extra I can remove
the
column right all those Transformations
whatever I do in the back end gets
captured in the applied steps section
right this is the customer data you can
create uh you can merge it you can
append it uh you know with other data
set right let for example I want to
create a merge data set of my categories
and subcategories so I can say mer
select the two data sets and say merge
queries as
new and I can select product categories
and product
subcategories
select product category key on both the
side and then they do a left Auto join
so whatever product categories are there
I'll get the subcategories associated
with it and I'll create a new table
which will
have
now I have the table which has the
category and the subcategory and
subcategory in one table itself so I can
rename it now to
as
category
subcategory table it's a it's a merge
basically it's a join between category
and subcategory and now I have a common
table right and I can close and
apply so imagine I have created a new
table which is imported created from one
data set is which is Excel page and
another data set which is text
base see this category subcategory table
so now I can use it the way I want in my
visualization reports so that's what the
presentation says right that once you
have uh the imported data you can shape
you can combine you can adjust you can
do whatever transformation you want to
do and create your visualization okay so
what topics we are going to cover today
we going to talk about different types
of data modeling and the most important
part and aspect of data modeling is the
cardinality the cardinality which you
basically decide after reviewing the
nature of data and after you imported it
what kind of cardinality you have to
basically highlight right and and there
are different type of quties which you
might have heard earlier also if you are
from plsql background like 1 is to one 1
is to many Etc we will we will talk
about
that now what are the different types of
data modeling now dimensional data
modeling is one of the most popular and
most uh you know widely used uh modeling
in dimensional data modeling you have
Master data uh like for example customer
data date store data product data so
these are like you know uh less
frequently changing data sets so there
is an organization right and you have
set of customers their email ID phone
numbers Etc that will change less
frequently as compared to the sales
transactions because transactions are
happening every day every minute so
sales is a more fast changing data set
in dimensional modeling which is in the
terminology of data uh is also called as
a fact and customer store product which
are like more of static data and less
changeable data is sometimes called a
dimension so this is a typical
dimensional data model which is
typically used uh sometimes right and
then there is another model which is
relational model this is a typical model
which we have been using in database
design like you know primary key key
foreign key relationships so for example
you have a customer who has purchased a
product so probably he might have the
customer might have the details of the
product which he has purchased and you
will make a join between customer and
product table and even uh you can make a
join between product or product type or
customer or product type customer table
will also have a key to the product type
so this is less uh conducive for
reporting but it is more of a
transactional uh Rel ation model but of
course this is also feasible but from
the powerbi perspective when we talk
about reporting and visualization this
is the most extensively used dimensional
data model and this is what we going to
see in our example now so what I'm going
to do is I'm going to show you certain
data sets first we will prepare and uh
create certain of uh data sets and then
we will Import in our sample powerbi
file and then slowly slowly we will
create the
relationships now one important thing
which you need to understand that in
powerbi if you go to powerbi there is an
option that that powerbi autod detect
new relationships after data is loaded
and import relations from the data
source on first load so for example if
you are importing the data from a
database where you have already defined
the primary keys and the foreign key
relationships so uh that is the first
option which powerbi will autod detect
and secondly if suppose you are
importing two different kind of data
sets one is Excel one is csb and if
powerbi detects a common column key
columns it will autod detect a
relationship which you can go and later
change modify manage in your
relationship uh menu manage relationship
menu menu in powerbi which I'm going to
show you okay so if I open a powerbi and
this is where the option lies go to file
go to options and setting
options data
load and these are the two options which
are by default checked you can uncheck
it and auto and manually prepare
relationships there's no limitation into
that but if you keep it checked then
powerbi will do its job to detect the
relationships
okay now coming to the next important
factor cardinality now before I start
playing around with my data and start
showing you certain relationships it's
very important to understand these four
types of cardinalities one is many to
one right so basically many to one means
that many orders contain data of One
customer so per order one customer is
there so from customer to order or
product or delivery address it's a one
to many relationship and from the other
side from order to customer perspective
it's a many to one
relationship okay second other
cardinality is one is to one one is to
one relationship is only applicable when
you are saying it's an extension of the
current table so for example in one
table you have employee details and you
are extending the details of the
employee in another table like employee
address employee ID so that is like one
is to one there's no multiple records of
a single employee in the address table
only one employee ID exists
right now one is to many as I said is
the reverse side of many is to one so in
customer table only one customer record
exists per customer and one customer can
place many orders for multiple products
and can also have multiple delivery
addresses so that way this is a typical
1 is to many relationship we will be
seeing this example also in our sample
data
set and last is the many to many
relationship now many to many is a very
typical example so which I'm going to
show you pra practically and in our case
we will see that like for example you
have placed an order for a particular
product uh you know but there are
multiple fulfillments which has happened
so suppose you made order for 10
products but at the back end when the
company is fulfilling it is first
fulfilling the first two products then
the rest three so basically your the
Fulfillment is happening in batches so
one order ID might have a multiple
fulfillments for the same order ID so
there will be a multi many to many
relationship which I'll show you
practically so now with this background
let's start importing our data now the
first important thing which we need to
import is the master data so first I'll
import all my master Dimensions which
are uh which I'm going to you know use
in my example so first is the customers
table customers
data so this is the customer details
like customer key prefix first name last
name birth date marital status and
gender some redundant columns are also
present but we'll remove
it so my customer data is loaded now
today's session is all about this
section of modeling so we will keep our
Focus over here
okay now some columns probably some
blank columns are there I can select
them and say delete from model
yes okay so now this is my customer's
data with the relevant columns and the
key per customer customer key now
there's no relationship in this model
right now right because only single
table is there and the associate data is
only imported now let me also import my
another important master table is the
products select the product data product
key product subcategory product SKU
product name model name product
description color size so just see all
the relevant information only specific
to the product is available so I'll
import
it okay now see there's no relationship
between product and customers directly
because until unless a customer makes an
order places an order for a particular
product there is no join right so now
between these two tables the most
important now another table which will
now make sense is the sales order table
sales
table
now I I'm assuming that powerbi have
autod detected the relationship now you
can see that because I've already uh
ticked that check box now let's see what
powerbi what relations powerbi has autod
detected let's first check the relation
between customer and sales I'll double
click
this uh join now what it has done is it
has created a join of many to one
between sales and customer so what does
that mean is that one customer has can
uh Place many orders right and that is
that it has detected by the quality of
the data and the data sampling which
powerbi has done you can also reverse
this relationship here I can select
customers and I can select sales now it
has become one too many so that you can
also do manually so that is what I said
whatever powerbi is detected it is up to
the discretion of powerbi internal U
configuration and algorithm but you can
go and change it so this is now you can
make this is by default active so we
want to keep it active One customer many
sales orders cross filter Direction
means that only from customers to sales
is the filter applicable not reverse
I'll come to this with my another
example but first let's change the
relationship so one is to many means
from One customer and many sales
orders similarly let's see what has
happened at the product side of the
relationship similarly powerbi many
sales orders for one product you can for
Simplicity sake you can say products
sales product key is the join now just
focus one more thing please uh also see
the co the column on which the join is
is the grade column grade out column
product key is also here and product key
is also there and it is what we wanted
so one is too many relationship from
product to sales table and active now
looks fine this is something which is
looking logical and probably now we can
proceed further to create a report now
let me explain the cross filtering with
an
example now now for example I want to
check in a report that what is the count
of products
which uh which a particular customer has
ordered okay so what I'll do is I'll
select the product count of product
name now if you
see the and for each customer in front
of each customer name the count is is
coming as 293 293 it is getting
repetitive because because there is a
oneway filter Direction filter between
customers and sales and sales and
products right so this
join is single sided it means that from
customer to product you can't find a
relationship because it's a single side
cross filter right what does this if I
change it to both it means that it is
equal to a join between product and
sales and every product detail now is
appended to the sales table so if I want
to make you visualize this you need to
go
here I'll first open my sales
table we can also open it here let's
make click it is okay now if I click
okay you can see the single arrow is
changed to Double Arrow it means it's a
it's a both side filter So when you say
a both side filter it means that
implicitly within powerbi you can
imagine that all the product columns now
will get appended because of both way
filter you have applied and if you go to
your report now see the change of the
numbers now 40 20 so the total count of
products across all my customers come
out to be 29 93 now the report looks uh
correct if I change the relationship
from back to single between product and
sales then you can't make a joint
between customers and product basically
you can't derive the product count from
the product table see
this if you have to live with it then
you would have to go to the sales table
get the product key and get the value of
count of product key but that is not
correct
okay so if you want a report in which
you want the count of product name and
even if you want a count of distinct
product name so this will not come
correctly you would have to go and
change the direction of the filter which
is from single to both so this is a
typical example there where you want to
use a twood directional filter
now let's proceed further and import
other data set in order to give show you
another example now I want to show you
an example of 1 is to
1 so I have another table which is
called customer details so the key in
this table is again customer key but
only email address annual income total
children education level Etc other
details of the customer is
there so I'm loading the customer
details now you see it has autod
detected a 1 is to one
relationship but what is the meaning of
1 is to one means One customer key only
has one entry in customer details there
is no multiple entry so if you click
this
button it's a 1 is to one and the cross
filter can be both or single doesn't
matter matter because one customer will
have only one value you can make this as
active okay and if you go to the
customer report table you can now
easily associate
a email address with the first name you
will get 1 is to one
record so now you can see that with 1 is
to one relation
ship with the first name I have
Associated the email ID and for each
email ID there is a Associated first
name with that so this is an example of
1 is to one relationship so in this
example what we have explained is that
for each customer there is an Associated
customer detail right uh so you have the
first name email address education level
homeowner occupation and total children
count so in this report what we have
done is uh if you click over here so the
first name and the email address okay so
there's a one is to one
relationship and
then and if you
drag the customer
key uh report takes time to render and
even if you
can so this is the reporting output you
have the customer key first name
associated email address and the count
of product names uh which the customer
has ordered now this is an example of 1
is to
one now I want to show you an example of
many to many now for that I'll import my
my fulfillment data
set okay now in my fulfillment data set
there is a column for
order number so basically what I'll do
is I'll drag order number from here to
here okay so now what has um uh powerbi
detected I'll do one thing I'll select
sales over here fulfillment over here
and order number to order number okay so
it's a many to many relationship so it
means that per order I have created
multip multiple batches to fulfill that
particular order
now many to many relationship is a
definitely a candidate for both ways
cross filter detection uh Direction but
you can you can check that but
definitely uh powerbi shows a warning
that this relationship has cardinality
to many to many and this should only be
used if it is expected that neither
column contains unique values Okay so so
we know that fact that's why we are
accepting this relationship as many to
many because we know there are multiple
order numbers over here in the sales
table which are mapped to the multiple
order numbers in the Fulfillment table
we'll click
okay now you want to keep uh the uh
direction as both ways or One Direction
that is up to you the way you want to uh
map the report so I can double click
over here and you can even click so now
you can select from which way single
filter you want from fulfillment to
sales or sales to fulfillment I'll
prefer sales to fulfillment and click
okay okay now we have our all our
different kind of relationships over
here uh which we have tried to short
list one to many many to one one to one
which is uh this example and many to
many now if I show you further
relationships which you can keep on
adding like for example I have uh uh the
example of
territories now in which particular
territory the sales was done so I can
map it over
here okay so now it's a typical 1 is to
many relationship because territory is
my master table uh where I have a static
list of continent country region and it
is mapped to the uh territories which
are for in which my orders have been
placed so it's a typical one is too many
so that way you know you can keep on
adding data then you
have uh details of
returns now this is another
transactional table which is about the
orders which have been returned rather
than being you know returned by the
customers so you have a product key and
so automatically powerbi has detected a
relationship between the product key and
the product uh table right and even if
you can join the territory key in which
territory the return has happened right
so mostly the most common relationship
which you will observe is the one is to
many because as I told earlier the most
common relational model is the
dimensional model uh the static data the
slow changing Dimensions the STDs are
the master tables and the most frequent
changing are the fact table so if I talk
about a typical dimensional model the
Fulfillment table sales table and the
tritor table sorry uh the Fulfillment
table sales table and my returns table
are the fact tables of my data model now
so far what we have done as per our last
session is that we did data modeling on
the different data sets which we had
imported in powerbi like products sales
data returns fulfillment customer
details uh and customer Master data
calendar details Etc so in the last
session we prepared a data model and
established the relationships between
these different data sets like 1 is to
one 1 is to many many to one 1 is to one
Etc and we saw the examples now once our
relational model is uh prepared our data
model is prepared now our next activity
is to create certain additional columns
which we want to derive bases the data
which we have imported so for example
I'll start with my product data set now
in my product data set I want to
introduce a column which basically
categorizes that if any product which
has a color uh you know red black or
gray I am going to t tag it as a colored
product rest I'm going to say not a
colored product right so all these are
like example of B type product skus so
for that now in order to introduce a new
column you just need to do what you need
to select the table in the data grid go
to the table tools and say new
column okay so column will get appended
to the rightmost part
and you will start seeing a uh formula
section typical to like you get in your
Excel now I'm going to say that the name
of my column is going to be bike type
color okay and I'm just creating a if
condition
if
product
color is equal
to Black
okay
or or if it is equal to red or if it is
equal
to
gray then say yes it's a colored product
I'll
say
no
okay so now you can see Bas is the
product color red and black they are
saying bike type color Yes blue is no
multi is no etc etc so this is a classic
example of an if and else condition
based conditional
column okay so you can create such
columns now second column custom column
which I want to create is I'm going to
call as
discount now Bas is the pricing of my my
products I want to associate certain
discounts which I'm ready to give to my
customers Bas is the product category
like what is the pricing of the category
again I'm going to use make use of if
else but in a nested way so if I'm
saying if
my
product price is less than 100 then I
will
give 0%
of uh 0 percentage of uh discount so 0o
into product price just to keep it
consistent now I'm
seeing else if less than 100 than zero
else I'll check again that
if the price
is less
than 500 then I'm ready ready to give 1%
discount on the product
price
else I'll move further so like this I
have created a
formula so what I'm saying is if product
price is less than 100 give 0% if it is
less than 500 then give 1% less than
2,000 then
1.5% less than 3,000 then 2% and
otherwise else less less than 3,000 if
it 2% else 3% right now after this
column is
created now you can check right so this
see the product price for this
particular product it is less than 100
so that's why there's no discount it is
uh between 100 to 200 then this has been
given a 1% discount so like this all the
discount column is now calculated now
this column is available just like a
regular column in my product
table now after this I'll go to my sales
table now in sales table I want to
identify I
uh create a column called as cost there
is no product cost column over here so
that will be derived so let's create a
column called as
cost and it is derived by order
quantity into now
the cost of the
product is in the product table and I
know I've already created a relationship
between product and sales
table so I just need to select the
product cost column now only keyword
which I have to use in powerbi is the
related keyword so this will pick up the
relation and now for this particular
sale or order the cost has already been
derived so this order number this is the
cost for which uh the product has is the
costing of the product for this
particular
order
okay
now I'm going to create another
conditional column over here called as
order
status I'm saying if any order whose
order quantity is greater than
two then for my organization it's an
urgent order else it is
a normal order
oh
sorry
I lost
it so this is
my order status column and I have my
order
quantity
urgent or
normal so any order which has order
quantity one is normal any order which
is having order quantity as greater than
two is urgent you can see this so
there's
a this whole
powerbi uh tabs and sheets allow you to
also review the data what you're doing
so it's very convenient
now I have my sales data now what I want
to bring within the sales is my discount
column so here also I want to bring the
discount which I have
created so I'll say discount
discount will be order
quantity
into
related product discount right so the
discount calculated column which I had
created under products I'll bring over
here now I am creating the order level
discount so if you see for this
particular order there's a
25 uh uh you know for 5 rupee discount
at the cost is 100,000 rupees
okay and what is the order price now so
I have taken the order cost the discount
now I have to create a column called as
price order price so that will
be
again order
quantity into
related price which is per
product price
enter okay so now I have the cost the
discount and the price right available
with me now I want to calculate the
total uh
total revenue total profit and loss
right per order how
much so first I'll calculate per order
how much revenue I'm generating so now I
have to generate a column called as
Revenue
the revenue is
price minus
discount
so 1700 - 25 1700 - 25 2071 - 42 and if
I want to calculate the profit per
order then it is
revenue Minus
cost
okay so now you can see you know typical
custom columns calculated columns which
we have created are all playing around
with the number numeric values numeric
data primarily and trying to give
inferences into per order cost discount
per order price revenue and profit so
typical calculation columns which I have
prepared in front of
you now let's take a look at other
different variations of custom columns
uh I'll create certain columns for text
base custom columns calculated columns
uh using text based data so I'm now
moving towards my customer table in
which I have customer key prefix first
name last name birth date marital status
and
gender now I want to create a new column
in which I want to derive the age of
each customer as of today right so I'll
use another function a date function
called
as
date
diff now date diff so I want the
difference between the birth date of the
customer and as of today in
years okay so this customer as of today
68 year old one is 74 68 57 Etc so this
is one derivation of uh calculated
column of
age let's take another example now this
is a text based column where I want
to derive the full name of the
customer now here I'll say
first
lower case in lower case I'll
concatenate the
prefix then
ENT
space
ENT first
name
ENT space
ENT last
name and closing brackets
Etc full
name okay so this is an example of full
name in lower
cases now another calculated column
conditional column at the customer level
I want to identify a flag which says who
is my Target customer verus the
demographics shared over here Target
customer so I'll say
if the marital status is equal to
M
and total
children
fre and total children annual
income
so okay so let me change the logic a bit
so marital status is M and
age
is less
than
50
these customers are my Target customers
okay so I would
say
yes else
no see this his marital status is
married Lanas and age is less than 50
else everyone so if I try to
filter so these are the my Target
customers 69 out of 1178 so this is just
a conditional column but a logical
condition an example which I'm trying to
highlight over
here okay now let's look at certain
calendar date oriented column
calculated
columns very typical like now I have a
simple date column now I'll keep adding
certain columns which uh you know which
help you which will help you understand
how we can uh know do some calculations
on the dates so like for example I want
a date which is 12 days after the
current date the date in the column so
just simple 12 Days After select the
date and add 12
now if you see the date format you can
go and change the format at the top and
if whatever you feel like like
this now see 12 Days After 1st Gen 2015
is 13 gen 2005 you can go and change the
format and other
details let me also show you if I go to
my call cost and other columns I can go
and change the format this is like a
currency cost is currency so I can go
and uh select the change the currency
type and you can even show the dollar
value or whatever currency type it
is so for numbers you can do currency or
text or dates you can select the format
so this is available at the column tools
level now in
customers like we had
our column of full name so now what all
things are available format as text okay
data type text so very minimal options
are there with date you have options of
the date
format now next
I want a column which defines the expiry
date okay so 8 months prior to the
expiry date with is which is uh coming
up in 8 months so I'll create a column
called as 8 months
expiry and then there is a e dat
function I'll use that I'll use
my date in the data
set comma I'll say eight so now this
date column will append 8 months to
my actual date and again I can go and
change the
format correct now another important
column like I want to know the date
name so I'll use a function called as
format and I'll select my calendar date
column and I'll say give me the
ddddd format of it so it'll give me the
day name the day the day name of on that
particular
date
next years in
between so I want the years in between
the today's date and the date of my
calendar so equal to date
diff the calendar
date comma
today comma year
end 7 years 2015 to
2022 then last date of the month so if I
want what is the last date of this
particular calendar month I will use a
function EO month which is there
available in
the powerbi so I'll
say last date of the month
equal to EO
month
then just select the calendar CSV date
comma zero in months and
enter change the
format
then similarly start of the
month so I'll use a function called
start of
month so for for all January dates end
of the month is 31st Jan and start of
the month will remain 1st of
Jan
change the
format next I want to know what is the
week number of that particular date so
now there is an inbuilt function F
called week number week num and just
pass the date and you will get the week
number first week of the year second
week of the year
Etc
now another
very good example is whether the
weak day is a week day or a weekend
right so what is it's a week type
okay so I'll CH I'll put a if condition
and there is a function called
weekday and I'll pass the
calendar if it is less than six it means
it's a
weekday else it's a
weekend so all
Saturday and Sunday day names will come
as weekends else everything else will
come as week day let's embark on this
transformative adventure together I hope
I made myself clear with the agenda that
said if you are an aspiring business
analyst looking for online training and
certifications from the prestigious
universities and in collaboration with
leading experts then search no more
simply learns postgraduate program and
business analytics from P University in
collaboration with Amazon Google and
Microsoft should be your right choice
for more details use the link in the
description box below also if you are
already a business analyst and want to
upgrade your skills to become a
successful data scientist with proper
training and certification from the
experts and reputed universities then
Caltech data science boot camp should be
the right choice for more details use
the link in the description box below
now today we will discuss how to create
relationships and different kind of data
models within powerbi based on the
structure of data you're
importing okay so what topics we are
going to cover today we going to talk
about different types of data modeling
and the most important part and aspect
of data modeling is the cardinality the
cardinality
which you basically decide after
reviewing the nature of data and after
you imported it what kind of cardinality
you have to basically highlight right
and there are different type of
cardinalities which you might have heard
earlier also if you are from plsql
background like 1 is to one one is to
many Etc we will we will talk about
that now what are the different types of
data modeling now dimensional data
modeling is one of the most popular and
most uh you know widely used uh modeling
in dimensional data modeling you have
Master data uh like for example customer
data date store data product data so
these are like you know uh less
frequently changing data sets so there
is an organization right and you have
set of customers their email ID phone
numbers Etc that will change less
frequently as compared to the sales
transactions because transactions are
happening every day every minute so
sales is a more fast changing data set
in dimensional modeling which is in the
terminology of data uh is also called as
a fact and customer store product which
are like more of static data and less
changeable data is sometimes called a
dimension so this is a typical
dimensional data model which is
typically used uh sometimes right and
then there is another model which is
relational model this is a typical model
which we have been using in database
design like you know primary key foreign
key relationships so for example you
have a customer who has purchased a
product so probably he might have the
customer might have the details of the
product which he has purchased and you
will make a join between customer and
product table and even uh you can make a
join between product or product type or
customer or product type customer table
will also have a key to the product type
so this is less uh conducive for
reporting but it is more of a
transactional uh relational model but of
course this is also feasible but from
the powerbi perspective when we talk
about reporting and visualization this
is the most extensively used dimensional
data model and this is what we going to
see in our example now so what I'm going
to do is I'm going to show you certain
data sets first we will prepare and uh
create certain of data sets and then we
will Import in our sample powerbi file
and then slowly slowly we will create
the
relationships now one important thing
which you need to understand that in
powerbi if you go to powerbi there is an
option that that powerbi autod detect
new relationships after data is loaded
and import relations from the data
source on first load so for example if
you are importing the data from a
database where you have already defined
the primary keys and the foreign key
relationships so uh that is the first
option which powerbi will autod detect
and secondly if suppose you are
importing two different kind of data
sets one is Excel one is csb and if
powerbi detect a common column key
columns it will autod detect a
relationship which you can go and later
change modify manage in your
relationship uh menu manage relationship
menu in powerbi which I'm going to show
you okay so if I open a powerbi and this
is where the option lies go to file go
to options and setting
options
data
load and these are the two options which
are by default check you can uncheck it
and auto and manually prepare
relationships there's no limitation to
that but if you uh keep it checked then
powerbi will do its job to detect the
relationships
okay now coming to the next important
factor cardinality now before I start
playing around with my data and start
showing you certain relationships it's
very important to understand these four
types of cardinalities one is many to
one right so basically many to one means
that many orders contain data of One
customer so per order one customer is
there so from customer to order or
product or delivery address it's a one
to many relationship and from the other
side from order to customer perspective
it's a many to one relationship
okay second other cardinality is 1 is to
one one is to one relationship is only
applicable when you are saying it's an
extension of the current table so for
example in one table you have employee
details and you are extending the
details of the employee in another table
like employee address employee ID so
that is like one is to one there's no
multiple records of a single employee in
the address table only one employee ID
exists
right now one is to many as I said is
the reverse side of many is to one is so
in customer table only one customer
record exists per customer and one
customer can place many orders for
multiple products and can also have
multiple delivery addresses so that way
this is a typical 1 is to many
relationship we will be seeing this
example also in our sample data
set and and last is the many to many
relationship now many to many is a very
typical example so which I'm going to
show you practic practically and in our
case we will see that like for example
you have placed an order for a
particular product you know but there
are multiple fulfillments which has
happened so suppose you made order for
10 products but at the back end when the
company is fulfilling it is first
fulfilling the first two products then
the rest three so basically you the f
fillment is happening in batches so one
order ID might have a multiple
fulfillments for the same order ID so
there will be a multi many to many
relationship which I'll show you
practically so now with this background
let's start importing our data now the
first important thing which we need to
import is the master data so first I'll
import all my master Dimensions which
are which are which I'm going to you
know use in my example so first is the
customers table customers
data so this is the customer details
like customer key prefix first name last
name birth date marital status and
gender some redundant columns are also
present but we'll remove
it
so my customer data is loaded now
today's session is all about this
section of modeling so we will keep our
Focus over
here okay now some columns probably some
blank columns are there I can select
them and say delete from model
yes okay so now this is my customer's
data with the relevant columns and the
key per customer customer key now
there's no relationship in this model
right now right because only single
table is there and the associate data is
only imported now let me also import my
another important master table is the
products select the product data product
key product subcategory product SKU
product name model name product
description color size so just see all
the relevant information only specific
to the product is available so I'll
import
it okay now see there's no relationship
between product and customers directly
because until unless a customer makes an
order places an order for a particular
product there is no join right so now
between these two tables the most
important important now another table
which will now make sense is the sales
order table sales
table now I I'm assuming that powerbi
have autod detected the relationship now
you can see that because I've already uh
take that check box now let's see what
powerbi what Rel
powerbi has autod detected let's first
check the relation between customer and
sales I'll double click
this uh join now what it has done is it
has created a join of many to one
between sales and customer so what does
that mean is that one customer has can
uh Place many orders right and that is
that it is detected by the quality of
the data and the data sampling which
power be has done you can also reverse
this relationship here I can select
customers and I can select sales now it
has become one too many so that you can
also do manually so that is what I said
whatever powerbi is detected it is up to
the discretion of powerbi internal U
configuration and algorithm but you can
go and change it so this is now you can
make this is by default active so we
want to keep it active One customer many
sales orders cross filter Direction
means that only from customers to sales
is the filter applicable not reverse
I'll come to this with my another
example but first let's change the
relationship so one is to many means
from One customer and many sales
orders similarly let's see what has
happened at the product side of the
relationship similarly powerbi many
sales orders for one product you can for
Simplicity sake you can say products
sales product key is the join now just
focus one more thing please uh also see
the co the column on which the join is
is the grade column grade out column
product key is also here and product key
is also there and it is what we wanted
so one is to many relationship from
product to sales table and active now
looks fine this is is something which is
looking logical and probably now we can
proceed further to create a report now
let me explain the cross filtering with
an
example now for example I want to check
in a
report that what is the count of
products
which uh which a particular customer has
ordered okay so what I'll do is I'll
select the product count of product
name now if you
see the and for each customer in front
of each customer name the count is
coming as 293 293 it is getting
repetitive because because there is a
oneway filter Direction filter between
customers and sales and sales and
products right so this
join is single-sided it means that from
customer to product you can't find a
relationship because it's a single side
cross filter right what does this if I
change it to both it means that it is
equal to a join between product and
sales and every product detail now is
appended to the sales table so if I want
to make you visualize this you need to
go here
I'll first open my sales
table we can also open it here let's
make click it is okay now if I click
okay you can see the single arrow is
changed to Double Arrow it means it's a
it's a both side filter So when you say
a both side filter it means that
implicitly within powerbi you can
imagine that all the product columns now
will get appended because of both ways
filter you have applied and if you go to
your report now see the change of the
numbers now 40 20 so the total count of
products across all my customers come
out to be 293 now the report looks uh
correct if I change the relationship
from back to single between product and
sales then you can't make a joint
between customers and product basically
you can't derive the product count from
the the product table see
this if you have to live with it then
you would have to go to the sales table
get the product key and get the value of
count of product key but that is not
correct
okay so if you want a report in which
you want the count of product name and
even if you want the count of distinct
product name so this will not come
correctly you would have to go and
change the direction of the filter which
is from single to both so this is a
typical example there where you want to
use a twood directional
filter now let's proceed further and
import other data set in order to give
show you another example now I want to
show you an example of 1 is to 1
so I have another table which is called
customer details so the key in this
table is again customer key but only
email address annual income total
children education level Etc other
details of the customer is
there so I'm loading the customer
details now you see it has autod
detected a 1 is to one
relationship now what what is the
meaning of 1 is to one means One
customer key only has one entry in
customer details there is no multiple
entry so if you click this
button it's a 1 is to one and the cross
filter can be both or single doesn't
matter because one customer will have
only one value you can make this as
active okay and if you go to the
customer report table you can now
easily associate
a email address with the the first name
you will get 1 is to one
record so now you can see that with one
is to one
relationship with the first name I have
Associated the email ID and for each
email ID there is a Associated first
name with that so this is an example of
1 is to one relationship so in this
example what we have explained is that
for each customer there is an Associated
customer detail right uh so you have the
first name email address education level
homeowner occupation and total children
count so in this report what we have
done is uh if you click over here the
first name and the email address okay so
there's a one is to one relationship
ship and
then and if you
drag the customer
key uh report takes time to render and
even if you
can so this is the reporting output you
have the customer key first name
associated email address and the count
of product names uh which the customer
has ordered now this is an example of 1
is to 1 now I want to show you an
example of many to many now for that
I'll import my fulfillment data
set
okay now in my fulfillment data set
there is a column
for order number so basically what I'll
do is I'll drag order number from here
to
here okay so now what has um uh powerbi
detected I'll do one thing I'll select
sales over here fulfillment over here
and order number to order number okay so
it's a many to many relationship so it
means that per order I have created
multiple batches to fulfill that
particular order
now many to many relationship is a
definitely a candidate for both ways
cross filter detection uh Direction but
you can you can check that but
definitely uh powerbi shows a warning
that this relationship has cardinality
to many to many and this should only be
used if it is expected that neither
column contains unique values okay so we
know that fact that's why we are
accepting this relationship as many to
many because we know there are multiple
order numbers over here in the sales
table which are mapped to the multiple
order numbers in the full fillment table
we'll click
okay now you want to keep uh the uh
direction as both ways or One Direction
that is up to you the way you want to uh
map the report so I can double click
over here and you can even click so now
you can select from which way single
filter you want from fulfillment to
sales or sales to fulfillment I'll
prefer sales to fulfillment and click
okay
okay now we have our all our different
kind of relationships over here uh which
we have tried to short list one to many
many to one one to one which is uh this
example and many to many now if I show
you further relationships which you can
keep on adding like for example I have
uh uh the example of
territories now in which particular
territory the sales was done so I can
map it over
here okay so now it's a typical one is
to many relationship because territory
is my master table uh where I have a St
static list of continent country region
and it is mapped to the uh territories
which are for in which my orders have
been placed so it's a typical one is too
many so that way you know you can keep
on adding data then you
have uh details of
returns now this is another
transactional table which is about the
orders which have been returned rather
than being you know returned by the
customers so you have a product key and
so automatically powerbi has detected a
relationship between the product key and
the product uh table right and even if
you can join the territory key in which
territory the return has happened right
so mostly the most common relationship
which you will observe is the one is to
many because as I told ear earlier the
most common relational model is the
dimensional model uh the static data the
slow changing Dimensions the scds are
the master tables and the most frequent
changing are the fact tables so if I
talk about a typical dimensional model
the Fulfillment table sales table and
the teritories table sorry uh the
Fulfillment table sales table and my
returns table are the fact tables of my
data
model staying ahead in your career
requires continuous learning and
upscaling whether you're a student
aiming to learn today's top skills or a
working professional looking to advance
your career we've got you covered
explore our impressive catalog of
certification programs and cuttingedge
domains including data science cloud
computing cyber security AI I machine
learning or digital marketing designed
in collaboration with leading
universities and top corporations and
delivered by industry experts choose any
of our programs and set yourself on the
path to Career Success click the link in
the description to know
more hi there if you like this video
subscribe to the simply learn YouTube
channel and click here to watch similar
videos to nerd up and get certified
click
here