>> Learn the top three best practices that you need to
know when you work with Power BI this week on Data Exposed.
>> [MUSIC].
>> Hi, I'm Anna Hoffman and welcome
to this episode of Data Exposed.
Today I'm joined by
two very special guests from the Power BI team and guy in a Cube.
Can you tell us who are you guys?
>> I'm Adam.
>> I'm Patrick.
>> Four Guy in a Cube and we're also on the Power BI product team.
>> Yeah.
>> Awesome, cool and it's exciting to have you on the show.
I don't think you've ever been on Data Exposed.
We're really excited.
Today we're going to be talking about the top three best practices
that people probably working on
databases should know about Power BI.
>> Absolutely.
>> Why did you pick these things?
>> These are things we worked with the world's largest customers.
If there's a brand, I'd give you a brand name,
you would know who they are. We work with them.
These are the three things that we go
over with them over and over again.
Like it's so it's it's like a broken record.
Like these are the things that people just don't
understand and we have to educate them on how to use this.
Typically, this is for working with large-scale data.
But I would encourage everyone to become
familiar with these things as you're working with smaller data,
as you're getting ready for the next stage in your career,
you're just setting yourself up for success by doing this.
>> Also, it helps future proof your models.
If you start adapting and using
these best practices, Azure model grows.
It protects you against all these unforeseen performance puzzling.
>> Because we know how to start. You've got
this little Excel file that's under your desk.
We want to create this amazing thing
and then you publish it out there.
Everyone loves it.
Now they want to productize this.
Now this is going out to everyone and then you're like,
oh my gosh, what's going on?
This will help you take care of that.
>> Cool awesome, so let's get into, what's the first one?
>> Patrick let's talk about query folding or power query.
This is where the magic starts.
>> Adam likes call this query squishy.
Let's head over to this laptop.
Right here in the query editor,
this is where you, Adam and I like to say massage your data,
get intimate with your data.
A lot of people, especially analysts,
when they get access to data,
they need to clean it up.
They need to make the data pristine before they
do it and if you take a look here,
we have multiple steps that Adam and I have applied.
At the very last step,
if you right-click on that very last step,
you'll see this button view Native Query.
It's grayed out now.
Now, that's an indication that
query folding may not be happening.
Adam what is query folding?
Explain query folding.
>> Query folding, I like to call it query squishing.
This is where we want to go to the data source with
everything one time and push it all upstream as far as possible.
This idea is the steps and transformations that you take,
bake it into that T-SQL statement that's going to go
back to your source to be the most efficient.
>> Just because those buttons disable doesn't mean query squish.
I mean query folding. Query phonons are not happening.
That's just an indication that it may not be
happening and in this case what I would do is I
would use to like maybe SQL profile or
Azure Data Studio with a profile of plugging in.
I will actually see what's going on on the back-end just to
verify whether or not query folding is
happening. We intentionally did this.
Adam and I intentionally added this step.
Now Adam let's remove that step and
let's add a step where we bring query folding back in
>> I'm going to go to the little format selector
and I'm going to come down and choose, "Date."
Now you can see that it says, "Change Type".
In this case, this one will actually
fold that transformation back to the source.
>> Show us how you do it?
>> Then if I right-click on that
and now view native queries enabled,
and I can actually see
the T-SQL statement that's being sent back now.
>> All those transformation convert date.
All those steps that we did there,
we extracted some data,
we feel just some worlds, we did some calculations.
It's all baked into that T-SQL statement.
When we do a refresh of our data using Power BI,
it sends a single SQL statement
back to the source. It's aimed brings.
>> Power BI doesn't do the work. SQL do a lot.
>> Do I have to copy this query and change the source?
>> No.
>> It just takes you out of my mind.
>> Every time you refresh, it's going to go through
all those steps and just some that one query.
>> Nice. This is one of the big things.
Make sure that this is happening.
>> Make sure that query folding is happening.
> What we say as a best practice,
try to get all your all steps that fall back at the top.
Because there's some steps,
there's some things that you can do in
Power Query that they just won't fall back.
It's because we can't convert that transformation into whatever
the SQL is on the back-end and so
being the mashup engine in Power BI has to perform this step.
Try to put all your fold in steps first and anything that
the mashup engine has to do or anything that
doesn't fall back put those after all those efforts.
>> Awesome, I love it. Cool. I can use that.
>> Moving on, so we've transformed our data.
We got it nice and it's feeling good right now.
We got it into the model.
Now we've got our data model or a dataset in Power BI.
You can interchange those two.
In this case the Power BI
really likes is what's referred to as a star schema.
If you're familiar with data warehousing,
this should be familiar to you.
It's been around for a long time.
Patrick talk to us a little bit about what's going on here.
>> First of all,
Adam, I don't like this model.
It's not really a star schema.
>> It's a star schema.
>> I'm just going to say it's not a star schema.
> We know SQL folks out there,
data folks, you'd like 29th normal form.
But when we're doing a star schema,
we want a fact in the middle,
and we want our dimensions on the side like a star.
We're not going to normalize it to the nth degree.
>> In this case, this is what's referred to as a snowflake now.
Sometimes that's fine, but we want to try and
keep it as simple as possible that's the goal.
>> What I would do is you see that product, that subcategory,
net product category table,
I would flatten those out.
I would denormalized those into a single table
because even if you look at
the subcategory and the product category, they're single.
Maybe do attribute tables.
There's not many attributes there,
so it's really unnecessary to have those tables when I can
flatten those out into the product table and denormalize it.
I'll go down to the second and third normal.
>> Just have our hierarchy right there.
>> Yeah. Also as a best practice in your fact table,
one thing that we highly
recommend is keep that table as narrow as possible.
When I talk about narrow,
I'm not talking about the number of columns.
I'm talking about the column Datatypes.
Try to avoid putting strings and doubles and floats,
try to be really precise like
integers and fixed decimal types and just dates.
If you have a date and time, split that out in the two columns.
>> I worked with a customer very largest
dataset I've ever seen in production.
That table was 12 columns that were all integers. Super clean.
>> How many rows?
>> We were talking trillions.
>> Imported, take all that data.
>> That wasn't important. It was using a layered approach.
But it was super clean and it's fast.
>> Yes. That's good to know.
>> Keep it clean, keep it simple that's the name of the game.
We've got our data right.
There are times where we want
to get to the visualization part. It's Power BI.
This is where the fun part is.
We can slice and dice,
we can make cool visuals.
Please don't judge me on the way these visuals look.
But one thing we've seen with people and you're going to like,
no way, this is not happened,
this absolutely happened recently.
I'm going to go to my blazing fast report and you'll
see that things are just sluggish.
It wasn't fast.
You sit here and go like, it's not performing well,
how many visuals are on here?
One thing I notice here is there's
a scroll bar and it just keeps going.
>> Because all these are loading it?
>> Yeah. If we go to fit a page,
we scroll, we can change the Canvas size.
We've seen people do this when they're coming over
from older operational reports.
When we think of like reporting services,
the old paginated type reports,
Power BI is the cool think.
>> It's old, but new and
so they want to take
that report because the business wants that report.
Because the business knows what they want.
You got to have a conversation with him now. Please talk to him.
>> It's a business.
>> What do they actually need?
What are they looking at on this report?
There's probably like three things. Do we need all of it?
The problem here is you're going to have
two performance problems with this.
One is going to be the data part because you're
hammering the data model with a ton of queries.
Each visual has one or more queries going back to the back-end.
But the other thing is these items run
in not all at the same time.
Think of it like a train.
There's cars and so they're batched.
We got to wait for the next one ahead of us before we can
even start and so it starts back logging at that point.
We've got to be smart about how we approach the actual report.
Power BI reports are meant to be
interactive and slicing and dicing,
not these long printed pages,
like paginated reports are still there in Power BI to do that.
Think about that when we're working with visual.
We've got Power Query for query folding, getting our queries.
We've got our data model keeping that lean and
mean, so that's fast.
Then our visuals, we got to be smart about that and not load up
too many visuals and be
smart about the visuals that were actually used.
>> Got it. These are three things
that I feel like I can actually do.
These aren't like hugely complicated things.
>> We talk about this all the time with customers.
This is like we get paid to do this over and over again.
If you become experts at this,
YouTube could work at Microsoft.
>> You wouldn't believe the people we
talked to about this because you'd think, I thought she knew this.
>> Awesome. Well,
thanks for coming on Data Exposed.
Really appreciated. Any final tips
or tricks or applies you guys want to make?
>> Just be nice to your data.
Then Power BI is going to continue
to have some just awesome improvements every year.
It's like Christmas every month.
We get new things all the time.
It's always a gift.
I appreciate everyone using Power BI.
>> Awesome, cool. Thanks again so
much for coming on the show to our viewers.
Thanks for tuning in and we'll put
some links in the description if you want to learn more,
if you like this episode, go ahead.
Give us a like, leave us
a comment and let us know what you think of
these three tips and we hope to see you next time on Data Exposed.
>> [MUSIC]