I will show you how you can use Power BI
Parameters to create templates that you can use yourself or pass to others to quickly create
new Power BI models.
Hey, I'm Avi Singh Microsoft MVP and best-selling Power BI author
and if you would like to become a Power BI Pro, make sure to subscribe and click that bell
So you are notified whenever I go live to answer your Power BI questions.
This video is part of a complete
Playlist series on Power BI Oarameters and you would be able to access that complete playlist at the end of this video,
so keep watching. I want to jump right into the demo and show you how you can use
templates and parameters to quickly create new Power BI models.
But before that I do want to give you a heads up that this is gonna lead to one of those
existential questions my friends to be or not to be kind and this is the question about
Whether you build one big Power BI model or you build multiple small models
and I'm gonna cover that at the end of this video.
I would love to hear from you whether you have used Power BI Parameters or templates when building Power BI models
So just leave a comment down below. So let's dive into the demo
So we're gonna start with our first file
Which is just a power bi model that I've built so I'm gonna open this file now
This is just a regular power bi model. So the same
Approach that I prescribed in my step-by-step tutorial
We have built it using that so we connected to some data sources pulled that data in
We define the relationships defined acts and measures and finally we built the visualization nothing special here
but the scenario we're talking about is where you want to use this as a
Template to quickly create new power bi reports now you could be doing this for yourself
or
Maybe you want to pass around this template to others so they can get a jump start on creating power bi models
Maybe others within your organization's or even people outside your organizations now
That's where templates come in where you want to pass around the model
Now the templates serve two key things
One is so let me just show you how you would create this save this as a template
It's really really simple. What you're gonna do is you're gonna click on you're gonna click on file
save as and
Here you can notice down here
That there are two options if you click save as type and the second option is power bi template file
So that's the one we're gonna use Bar be a model and I'm just gonna overwrite the file that he had saved earlier
So I'm just gonna save obviously you can save to a new file here
You can provide a template description if you need now. This would be visible for people importing the template later on
We're gonna hit OK here. So now that the template to save the first thing I want you to notice
Is that the file size so our model?
Bahria model, they were working with was about five megabytes. Not a small model with test data
But you can notice right away that the template is actually smaller now
That's the key thing with the template the template stores the metadata. It does not store the data inside the file
so if the file contained whatever data
You see here. None of that data is stored back in the template now that has two advantages
Number one is that it keeps the file size small?
So even if you have a very very large power bi model, I don't know 250 megabytes larger
Gosh, you can still pass around a template pretty easily
You can keep it keep it to yourself emails to anybody else, right? So it keeps a file size small now
Another benefit is even bigger, which is you can still use templates
Even if you have sensitive data
even if you're sharing it with somebody outside of your organization because you know that no data is actually
Being saved as part of the file
So if you have your sensitive sales data, or I don't know Social Security numbers or anything like that
None of that would be actually stored only the the metadata
what do I mean by that so it's gonna store the
Structure of the tables the field names the Dax measures the visuals and everything else
So then how would these people actually use the template? Let's take a look
So now let's say I am the recipient of the template maybe you send the similar to me
I may be within your organization outside of your organization's or what I'm gonna do is simply just open this power bi template
What opening this file does is bar bi attempts to create the bar bi?
model file based on that so it just goes
Whatever the the data sources were and tries to load that data
now
Of course if let's say you're within the same organization
You have access to the same files and the files you want to use are actually in the same location. Then this good work
I mean they would just connect back to the same source
And it'll just load the data and they would be good to go
You notice that it just created the file. And now this is my new file. You can tell by the title
This is what I imported from the template and I can save it to my own folder own locations
This is something you gave to me and I essentially recreated it kind of like I'm thinking of dehydrated food packs
We just add water right so instead of adding waters. It's just as data, but now in many scenarios this is not gonna work and
Because your file location may not be the same so I'm gonna show you this file and I'll show you where it connects
so this is
Actually, I'm gonna open the original file and show you what it connects
So here we are in the original file or original model and I'm gonna click Edit queries and here if you look at the query
dependencies view
There we go
You can see that it connects to this
Location C colon slash to learn pbi and the same thing for this other file
Now, of course you can have many many different scenarios and many different data sources
And let's let me show you
What's gonna happen if the user?
Who's trying to use this template is does not have access to the same location or the file is in a different place or something
like that, so now I'm in
pretending to be a user who doesn't have access to the original file now instead of opening the power bi template file you can also
Go here and say import
So we're gonna import that original that power bi template file created off our model and watch what happens here
it attempts to connect to those data sources, but it errors out because again,
I could be somebody outside the organization's or even within the organization's but the files that I need to use for my model are
In a different location now
This is where parameters come in really handy and make it a snap to
Transfer file to others and have these other users easily change that one setting to point to the files
Locations or server locations that they need to use. So now I have opened the other file that I had in my folder Barbie
I is the same file except it uses
parameters now by the way quick note any file in in any of these videos you can download from
Learn power bi comm slash download now
The only thing that we have changed in this file is we have created some parameters now
we have covered that in the playlist and you can access that playlist by clicking on the link at the top or
Down in the description and that's gonna give you access to the complete playlist on power bi parameters where I cover that in extensive detail
So here we have specified the parameters. Let me just fix this for one second and come back to you
So in the original file, we were directly
Referencing the location of the folder in this file. We have created a parameter
So as you can see
We have a folder location parameter. And right now it does default to the same location that we had originally
So I am the author and my files are at this location. And if this parameter is
referenced in other ones
so
let's see as you can see in this query dependencies view that both of these these sources are are
Connected to my parameter folder location and you can examine the file to see exactly how I have done that
So now let's see that if I save this file as a template then what is the behavior?
So again the step you're gonna take from here is exactly the same
You have the same model
The only differences you have used parameters to reference in this case the folder locations
But you can use parameters to reference anything in the world. You can reference a server name a port number whatever you want
Whatever points you lets you change the data set and make it point to a different location
And again, you would do save as and I already have this file saved
So we're gonna leave that and now let's see what happens when I use this new template with the parameters defined
So this is my template with the parameters
I'm gonna open this and let's see how this behaves so notice now
that instead of just going by default to the folder location that had I pointed I
as an author at point of the locations do now, this is somebody I'm now in and in person to
Somebody who?
You know who was sent this template?
so now when I as a
Recipient open this for the first time. It doesn't assume that all my folder location server names are gonna be the same
It's gonna ask me that hey, you're all trying to open this template, but maybe your files are in a different location
so here I'm gonna go ahead and
instead of learned pbi
I'm gonna point to a different path name
learn power bi and and now it's gonna point go to that location for me and load the complete data and
Give me that quick start on the power bi mano
so I use that template to create a retraining my own model, but was able to point it to my
My where my data resided by the use of the parameters
You can see that it loaded all the data from the new cloak ation. And now we're good to go
Now that you've seen how you can use parameters and templates to quickly create multiple models
now this leads us to that question we talked about which is whether
You create one big power bi model or create multiple smaller models now
There are pros and cons to both approaches. So let's cover a few of them now
The first thing we're gonna look at is the speed of development
Now clearly here the the multiple models has an advantage
So this is the scenario where let's say you're building multiple models for different audiences
maybe
you're a power bi model author power bi developer and you have the sales team talking to you the marketing team talking to you and
Operations and everybody else and you just build separate models for them to keep things clean for you if sales comes to you you open
their model and add whatever changes they need and it's quick and easy and you can just focus on them and so similarly for the
other models now if you combine all the model into one and try to serve that all the
Audience's with that single model what's gonna happen is sales comes to you and they want to quit change
You're gonna have to say, huh?
Well, I know that makes sense for sales
But maybe that scenario whatever they're asking for a change maybe something in the calendar table or something even something minor or something big you
Would have to think about how does impact all of your other customers and that inevitably slows down development
You have to be more methodical you have to have a more formal approach
Maybe you do change requests and so on so forth, right? So it's it's a little slow methodical
but this approach also has a
Big advantage and which is when it comes to scaling now think about what happens down the line
when you have been operating in this in this
scenario multiple models for a while
Now what's gonna happen?
is that even though you may have a template that you base all of your models on the models are gonna
Diverge from that so sales are gonna come up with their own request marketing operations
They're gonna come up with their own request. So over time those models will look very different
Now what that's gonna mean is that it's gonna be really hard for you to keep them maintained if you make one
Change and let's say the calendar table now
I have to repeat that change in all of the models like oh, yeah
That field would be nice or if you made a mistake somewhere and said oh my gosh
I made a mistake and I'm gonna stick with a calendar table example, you have to fix it
Not just in one but all the multiple models you have created now, of course, sometimes people create multiple models for quick analysis
And if there's a throwaway analysis, you're never gonna look at it again. That's fine
but if it's something you need to revisit if you end up with three 10
20 50 models it gets crazy really fast
And that's where the one big model has an advantage
So it's slow and methodical but over time whatever changes you make to the single model
You're not gonna have to repeat it. You're not gonna have to worry about
Repeating it in multiple places or whether it's gonna be consistent in all of those places
It's all gonna be in one place due to those reasons. I
Personally lean heavily towards creating one big power bi model
If I'm the power bi model author now
I still give my end-users freedom to create their own quick models and have the ability or
Are trying to make sure that I monitor what's going on with these user created models and if there are any good changes
I want to pull them in into my one big model now if you would like to talk more about this
I certainly love talking about this topic. Make sure you subscribe so you can join me on my next live
Talk Barbie eye on Friday
And if you're just getting started on power bi make sure to check out my power bi tutorial until next time bar on my friend
Hey keep watching more videos and keep learning power bi but if you did enjoy this video
I would love to hear from you. So leave a comment like subscribe
All the good stuff while we're on my friends