And thank you for coming to this
session and this session is about
architecture blueprints for power.
BI architecture is quite wide topic
to talk about in like 20 minutes.
So I try to just focus on a specific
area of that which will go through it.
Before I start time razorhead.
I'm power. BI console time trainer.
I'm based in the other side of the
word NZ but I'm traveling around doing
consulting training all around the world.
I've written some.
Power be, I one of them you can find
it actually in the library over here.
It's a power guy architecture book
and these are my contact information.
Feel free to reach out to me.
Anytime you have any questions,
OK talking about architecture in power be.
I there are different aspects that you
can look at power VI architecture.
First, let's look at the development
architecture and that is what I'm
going to focus mainly in this area
now before I start at how many of
you are already using power BI.
OK, that's a good show of hands so almost
most of you are already using power be OK.
So let's talk about different personas.
Let's talk about the first persona off
the person who is a single developer's
single sole developer business analyst
in the team building a analysis solution
for users might have multiple users.
But it's a single person developer
right for such a person.
You need you need a solution
which is very agile fast.
It works,
everything really fast at doesn't need.
Approval from it to get the
specific things done, an power.
BI is quite good at that right
so you already use power be.
I you know that in power be.
I everything is like quite easy to use
you just import the data in power.
BI you do all your analysis with tags.
You do the visualization.
You publish it and you give it access
to your users right SO1 single power.
BI file 1 single TVIX file.
Everyone is happy, no problem OK.
That's the easy option.
Now let's talk about another.
Scenario let's talk about the scenario
that we have multiple developers,
not single one developer multiple developers.
They want to do an analysis solution.
They might solve one analysis solution
or multiple analysis solution and
they want to deliver it to their
users right so in this scenario.
If we use, one single file.
We have a lot of challenges right.
This might be a little bit hard to
read from those guys in the back.
But I highlighted those areas that
are some of the challenges it when
you have one single file your file
grows bigger and bigger overtime.
You add more tables you add.
Complicate things more relationships
as a result,
this would become slower and slower
for your data.
Refresh you end up with 200 pages of
visualization 300 pages visualization.
It's hard to maintain quite high
maintenance solution that would
be and only one single developer can
work with one single PIX file right.
You cannot have multiple people working with
one single TVIX file So what is an example of
that an example of that is when you use one?
In multiple files. When I talk about QWERTY.
I mean, a power query query,
which is actually a table.
Let's say is there a scenario that you need
to use one table in multiple power BI files.
This is an example of that.
Let's say I have a sales power.
BI file sales PBIX file.
I analysed everything analyze
everything about sales in it,
and I have an inventory power.
BI file which I analyze
anything about inventor.
Because these are like 2 separate solutions.
An I don't want to combine
them together now there are.
Data table for example,
or product table.
These are tables that you need to
be in both files So what you do in
these scenarios do you go and like
create a new table then you have to
do all of those transformation again?
Do you go select your first table
copy the entire Emma script power
query script go to the second
file paste it over there,
which is probably what most of us
doing right and then problem solved.
Now, after 2 weeks, you realize that OK.
I missed the transformation.
Let's go and add to have one C to have
one single version of your source code,
but to have one single version
of your source code,
but you have one single version
of your source code.
But you have one single version
of your source code,
but to have one single version
of your source code.
But you have one single version of
your source code to have one single
version of your source code, but you.
Have one single version of your source code,
but you have one single version
of your source code.
But you have one single version
of your source code,
but you have one single version
of your source code.
But you have one single version
of your source code,
but to have one single version
of your source code,
but you have one single version
of your source code.
But you have one single version
of your source code to have one
single version of your source code.
I'm sure that the solution for
that is dataflow.
Power BI data flow?
How many of you heard of data
flows in power gate OK.
That's a good show off and not as many
as you who are working with power gate.
That's why I'm going to talk about dataflow.
This is power.
BI data flow not Azure data.
Factory data flow or other data flows right.
We are talking about power.
BI data flow power.
BI data flow is power query process.
Running in the cloud.
Independence from power BI report the
most important part of this definition
is being indypendent because right now,
you when you create a power.
BI file when you create a PBX file
when you publish it to the cloud.
When you schedule it to refresh.
You are running power coding cloud
right so that's not dataflow,
making it independent from power BI report.
Makes it a dataflow that means that after
running that power query is gripped your
data is not stored in a power BI model.
So where it is stored then you might
ask where my data is stored in a power.
BI data flow that is stored at the
moment in Azure Data Lake Storage.
There are discussions that might
be might have other storage is
available for this later on,
but at the moment this is the only.
Place that it is stored it's stored as
a CSV files like to all the schools
and now when I talk about Azure data.
Lake, a lot of people say OK.
I don't have actually data links of
what happened. I don't have Azure data.
Lake subscription what happens
for my scenario.
The fact is that when you
have a power BI account.
I'm not talking about free account because
data flow is not available in free.
But it's available in pro am premium
when you have a power V account as part
of the space that you have in power BI.
Cloud part of that can go to
power BI Desktop and say,
get data from that data flow right.
It's like creating an intermediate storage.
It's like creating middle layer
data warehouse or a database right.
This can help in a lot of scenarios.
One of scenarios that this can help
with is actually putting your shared
tables tables that you want to share in
multiple solutions in your data flow
or dataflow runs those share tables.
Story in CSV file in Azure Data Lake store,
then multiple power.
BI files get that and use it
in different solutions right?
Which is what I'm going to show
you in a second.
This will also help to have a faster.
Refresh time because instead
of running your power be.
I fall doing all those data transformation,
which might take like a couple of hours,
you are doing that as your dataflow process.
So when power BI reads that data.
It's already transformed you don't
spend time for transforming that
data right so let me show you that
and I'm not going to show a full
example of dataflow how this is work
so let's switch to this machine.
OK, so here I'm in power,
BI website data flow is the
cloud only solution.
We don't have it on premises
so I have a work space here.
I have a work space here now in this
workspace. I go through data flows.
I already have you in this case it seems?
I don't have it so I'll go and create
a dataflow so this is the place that
you can go and create a dataflow.
There are different ways that
you can create I'm going
to define a new entity tables are
called entities here because this
is working based on the same concept
of like CDM common data model so I
can use power query cloud based.
Functions to go and build
my transformations right,
I can connect to any of those data
sources that I want and do my
transformations or Alternatively.
I can go and use one of the existing
transformations available and copy
it into the power. BI data flow.
So this is a power BI File Desktop
I have a date table here.
Uh, which I can go and go to edit queries,
I can go to advanced editor for this,
I can copy this script.
And I say OK I want this to be a blank query.
Script here as soon as it comes here,
so I paste the script here and
then click on next now.
If this requires on premises
gateway and things like that.
You have to go and set up those
as well because if it is local
or a local domain sources.
You need to configure those so
here you can see that I have
all of those transformations.
In here this is power query in cloud
or power query in the service as you
can see you can add more transformations.
Here you can add more tables.
You can connect to other data sources.
You can rename your tables to be
whatever it is so let's see I call it
date table and then after doing all
of these you can save and close right,
which you can call this dataflow,
something let's say I called it.
Ignite.
Now I save this data flow.
This data flow at the moment
includes only one table.
I can also go and refresh it.
So that it runs that process.
It save the output in dot
CSV file in Azure Data Lake.
After saving that I can go to
files that I need to use these
table and get data from that,
so this is one of the files
that I have a power BI file.
It has sales table product
table customer table.
Let's say I want to add a table into that
now let me see that already refreshed or not.
Not this one.
I think it is refreshed so.
Or it is still.
Right so here you can see it
is refreshed just right now,
so I'll go to this file and in this file.
Let's say.
I want to add a date table.
I simply go get data from power be.
I data flows.
So I don't really need to
go and build that again.
I go and say, get data from power.
BI data flows and when I go there.
I see all data flows that I have
access to you have to login usually
so this one was under this workspace.
And it just works player had
a lot of data flows you can
consider each of these as like
a database and then under this.
I had that table,
which I can select this preview window will.
Show me a preview of that table.
I select the table and load.
I don't really do transformation
anymore right.
And I can't use it in any other power.
BI file so it is coming in a second.
I can't even go to this file,
which I also needed a table and add it.
I can go to any other files and add
it all of your shared tables are good
candidate to be in the dataflow you
are actually building a data warehouse.
Right so that the table that I
added is right now here I can create
relationship and do other stuff right.
But you got the point how it is
working so let's go back to here.
On the slides.
OK So what you do actually through
dataflow for your architecture is
to build your data warehouse right.
It's not.
SQL Server database,
but it is still a bunch of tables
you can create relationships with
those instead of processing.
It every single time you go
and build it that way.
And then you reuse.
It multiple times.
Another problem in this kind of scenario.
So when we have multiple files.
We have also another problem.
Sometimes you need to have
your data set duplicated.
Now let me talk about what this
data set first because data set
is quite like unclear concept.
Data set is where actually data lives.
Open power BI Desktop You'll have
the data in power, BI Desktop.
You have the visualization in power,
BI Desktop.
Everything is in one place behind the scene,
it's actually 2 objects visualization is.
A separate layer data is a separate layer
data is sitting in a place we call it a data
set when you have power, BI Desktop Open.
You can actually go to task manager and see
that there is actually different task running
Microsoft SQL Server Analysis Services.
Even if you run power BI Desktop
on a machine that doesn't have
SQL Server analysis services.
You still see that becaus power.
BI data model is based on SQL
Server analysis services.
Tabular engine right that is where the data
lives that is what the data set is right.
So data set is a little bit clearer
in the website when you publish
your solution in the website.
You actually see data set is one tab report.
Another tab in the desktop you
just leave all in one, but in fact,
they are serving so now let's go
back to the problem that we had
so if I'm talking about data set?
What if I use a calculation.
Tax calculation I'm talking about.
Let's say I created calculation
running total of sales or rolling
average rolling 12 month sales.
I want to.
Replicate this from this file
also to have it in another file.
So what you do in these scenarios,
you cannot use dataflow dataflow
is all about power query is all
about making your power query.
One single version of the code
data flow is not good for this.
But you should be using is something
called shared data set in power,
BI word.
You can have one data set serving multiple.
You can do it in different ways
in the power BI website.
Power BI website when you go and
select the data set there is a
little create new report on there,
which you can go and create a new
report or when you use a power get
desktop you have the option to say
get data from power BI data set?
That means it won't have any data,
it would be just visualizing the
data in that data set.
That means that you can have one data
set serving four five 10 different reportes.
If you have your calculation in
this data set then you are using it
across all of those visualization.
It's alive connection to this
right so let me show you that also
as another quick demo.
Here, I have let me close this one.
I don't need that so here
and not this once already.
I have a desktop like blank power,
BI Desktop. All I'm going to say.
I want to get data from a power BI data set.
And I click on that and this will.
Show me all datasets that my account has
access to write these datasets might
be datasets that I have published it
or it might be datasets that someone
else published it and then I can go
and select any of these datasets.
Let's say I go and select this
one now before I select that one.
You also see that there is an endorsement
system a labeling system that you can
go and put a label on your data set
becaus when you create like 200 data sets,
you have to somehow tell to your business.
This is the data set that is good
to use the concept of like gold,
Silver Bronze report things like
that right something like that.
So I can say this is certified go ahead
and use that or this is promoted which
is you can consider it as a silver
so I can go and select any of these.
That data set in the power BI service
and then I can do my visualization.
It's coming in a second so here
you can see I have my power.
BI report just for visualization
purposes only.
If you have used power by you know that
this means power gay live connection.
I don't have data to the data
tab or anything like that.
I'm just visualizing so here I
can come and do any analysis like
worldwide sales of movies by title.
And it shows me that visualization
this is a power be highly report
live connected to a power.
BI data set then I can
go ahead and publish it.
Let's say I save it as.
Version 2 or something like that.
And then I publish it.
In this case, I'm going to publish it to
this workspace and while that is publishing.
I go to that workspace just to
show you what's happening in that
workspace so in this workspace,
you can see that I have that.
Right. You can actually see that for that
movies data set I have actually more than.
Let me show you this way,
I go to that data set. I click on this icon,
which shows me the related items in that
data set an it shows me that this data set
has actually 2 Reportes joined to that.
You can have like 10 report 15 reforms
right so this way actually ID coupled.
My let's say modeling layers are going
back to the slide and showing you the
last part so the architecture that
I'm proposing is this architecture.
We are actually decoupling the three
layers of building by solution.
We have data flow part that you go
and build your data transformation.
You load it into your data warehouse
style type of solution.
Then you have your shared data models
shared data. Mart shared datasets.
Whatever you might call it,
which has all the calculations.
The tabular engine of power BI and
then you can have multiple reportes
visualizations to show that information.
How you build this architecture.
You need to try different approaches
that data transformation part of it.
Is getting data from different
data sources that you have?
Data modeling you go and get data
from power be data flows not those
data sources separately and in your
last part which is your visualization
you get data from a power.
BI data set so that brings you to
that architecture of having these 3
layers separately and it's not just
about power BI if you use Excel?
Export to Excel but if you use
analyzing Excel that means that
with Excel you can actually.
So same approach works with Excel?
How many of you use other tools
than power BI for visualization.
OK,
if you use other tools if you
use tableau if you use any other
tools they have some limitation.
Yep,
yeah in power BI.
We take care of that with power BI
with XML am point you can actually
have your tableau your click any
other visualization tools connecting
to the power model because power BI
has a really powerful data model,
which they don't and what about
Paginated Report.
You can have paginated report
again in the same architecture,
so all of these Excel.
Power BI tableau click all of those
can be your visualization layer,
he will still have your modeling layer.
And your data transformation layer?
What are benefits of such architecture
the benefit of this are like you
are D coupling these 3 layers as a
result of these decoupling you can
have multiple developers working
on different areas,
those who are good at power query.
Let them take care of dataflow.
Those who are good at.
Dax let it take care of the shared
data set and usually you have more
people good at visualization they go
and build the visualization part of it right,
I wanted also to mention you
at rule which is.
We built it's pretty available.
Everyone can use it Powerball helper.
I don't have time to demo it.
You can go and download it from our website.
It gives you a lot of information
like documenting all of your power.
BI file information helping in
better performance in your power.
BI file telling you information like
where it is a slow rate is bigger make
it smaller things like that references to
study more as I said 20 minutes is very
short time to talk about all of this,
I have written blog articles about.
Every single part of this in details if
you are interested in going download.
Those all of these are available
in Reddiquette website.
And I'm sure somehow you will have access
to these slides anyway and the book
that I mentioned is also in the library Ann.
I'm here I'm just wrapping up for
the sake of the next presenter.
Feel free if you have any questions
to come round.
Thank you.
