Tip:
Highlight text to annotate it
X
David Ludwig: Owen, tell me if there are people who are curious about Power Pivot but
they havenít quite installed it or maybe they did but and they didnít quite get as
far as they needed to, can you give them a definition?
What is Power Pivot? Owen Duncan: Well, Power Pivot provides the
ability to extend the data model that exists in Excel. When Power Pivot was first
introduced with Excel 2010 as an add-in that you could download we gave you that ability
to create data models. In Excel 2013 and Office 2013 Pro Plus editions you have the
ability to create a data model in Excel. Those are relatively simple data models where you
have two or more table that you can create relationships and you can create Pivot tables
and Power View reports based on those. Owen Duncan: Now, with Power Pivot you get
extended capabilities. You can create whatís calculated columns where you can add
your own customized data to tables. You can create calculated fields which are also
known as measures. You can create KPIs. You can create hierarchies that group similar
fields. And you get a lot power more especially with measures and your ability
to aggregate your data. David Ludwig: Okay. Cool. Who typically uses
Power Pivot then? Because people might have heard a bunch of jargon there and
they might be a little worried that this doesnít apply to them. But who uses it?
Owen Duncan: Well, itís typically a power Excel user that wants to do some self-service
BI and needs to analyze some of their data. That they need to pull data from multiple
different sources whether it be from just a relational database in their own environment,
that they need to crunch some numbers. But typically that has been owned by IT
departments and people have created their own reports but now you kind of have the
ability to do your own self service BI. David Ludwig: Okay. Cool. So is it fair to
say that the Power Pivot add-in helps the power Excel user with their Pivot tables,
particularly? Or could it beÖ Owen Duncan: Sure, not only with Pivot tables
but also with Power View reports and other types of reporting services reports.
If you have two or more tables and you need to
relate that data in some way, rather with how you want to slice and dice your data in
your pivot tables, Power Pivot gives you a lot
of great capabilities to do that. David Ludwig: Okay Cool. So why donít you
show me here. Weíre going to start in Excel. Go ahead and show me what Power Pivot
looks like? Owen Duncan: So we have the Power Pivot add-in
enabled all ready in this Excel. So Iím going to go ahead and click on manage
on the Power Pivot tab and this opens the Power Pivot window. And Iíve all ready got
some data that Iíve imported from a relational database. This is just a sample, a Venture
Works database that is readily available. It can
be downloaded. And what weíre looking at here is called data view. And as we see on
the top part of the table here we see some data
that belongs to our Internet sales table. And
down along the bottom we see tabs just like you would in Excel and those are some of
the tables that weíve imported. Owen Duncan: Now, I canít actually edit any
of the data values that are in this table. That has to be done at the source. I can refresh
the data if changes are made. Now, down along the bottom here we have whatís
the calculation area. Itís also known as the
measure grid and weíll come back to that in a little bit when we create some measures.
David Ludwig: It looks a lot like Excel. Owen Duncan: It does look a lot like Excel
but like I say, itís much different functionality. If I click on diagram view, we can get more
of a map diagrammatic view of our data model. This is something you cannot see in
the native data modeling capabilities in Excel. So what weíre seeing as the tables
that weíve imported and we see some other relationships that were automatically detected
and were automatically created when we imported the data.
Owen Duncan: When I imported this data, I used whatís called the data import wizard
from this external data area up here. Itís similar to Excel but you have some increased
functionality with the ability to filter the data before you import it and rename things
as youíre importing. It helps you create a cleaner
data model. David Ludwig: So this window is showing a
data model. And this add-in window even when youíre in data view itís showing your
data model, is it fair to say that a data model is
sort of this in between state between the source of your data, maybe in a database or
a file or something. Then thereís the data
model and then thereís Excel. Is that right? And
the data model is where you are working with the Power Pivot at andÖ
Owen Duncan: Thatís a good way to put it. That data model exists in memory. So you
can have millions and millions and millions of rows of data in a table.
David Ludwig: Volume. Owen Duncan: Yeah, volume is one great thing.
It uses a really elaborate compression to get that data into memory. So when you
actually are slicing and dicing that data and
aggregating it, it is really fast. David Ludwig: Okay. So this is what you were
saying before, usually people depended on an IT department to do those reports because
theyíd probably have the tools or the access to do that, but now that frees up the
IT department and now just an end user can do this with the add-in?
Owen Duncan: Yes. David Ludwig: So when you have your data model
up here, do you need to prepare it in any sort of way before you start working with
it? Owen Duncan: Well, anytime youíre adding
two or more tables to a data model you need to be able to relate that data in some
way. Like in Excel you used to use VLOOKUP to go get a column or get a cell from another
table. But here, when we look at a Pivot table, a Pivot tableís field list we can
see two or more tables and we can start slicing and
dicing our data that way. But we may need to create a relationship. Now, Iím going
to go had and create a Pivot table here.
David Ludwig: Gosh, look at how easy that is. Okay.
Owen Duncan: Yeah. I can create a Pivot table just from right within the Power Pivot
add-in. And Iím just going to go ahead and say, well, letís slice on calendar year and
letís add that to rows.
Owen Duncan: And then letís look at our Internet sales. This is transaction data, so
thereís one row for each transaction. And if I just click on sales amount it wants to
sum up sales amount for each year that I want to
slice on but it canít do it because it doesnít know how to relate the sales amounts to the
year. David Ludwig: To year, okay.
Owen Duncan: So you see over here in the field list we got this relationship between
tables we needed. So if I click create it opens the create relationship dialogue box
and I can see well I can create the relationship
between my date table and my Internet sales transaction table here.
Owen Duncan: Now, another way to do it is with the diagram view and this is kind of
a neat way to do it. Here you can do what are
called drag and drop relationships. And if I
look at my date table, I see I have a date key here. That would be my one side that I
will need from a relationship. And if I look at
my Internet sales table I can see I got a date key
here. So really all I need to do is I just need to click on my many side and drag it
to my one side. And there it automatically created
the relationship. And now if we go back to our Pivot table you can see itís updated
automatically because now it knows how to relate the sums, sales amounts for each year
that we sliced on. David Ludwig: Cool. So what you did is you
basically took the same field name or column name from those two tables and linked
them together. Owen Duncan: Yeah.
David Ludwig: And youíre often doing that, finding the matching column.
Owen Duncan: Yeah. Relationships are super, super important for data models and itís
important to get a really good grasp on relationships. I wish we could go more into it here.
David Ludwig: I bet. Yeah, I have a feeling we could. Okay. So now that weíve done
that, what are some typical things people would do here in this window, this Power Pivot
window? Owen Duncan: Well, calculated columns are
one of the first things you want to learn when youíre working with Power Pivot. Calculated
columns are a way you can add your own columns of data to a table in the data
model. Now, letís take for instance our date table. I have a date table that I had imported.
And it has a lot of dates in here that I can use to slice my data but it didnít have fiscal
year, fiscal quarter or fiscal month in there. So I was able to create a column for fiscal
year and create a column for fiscal month and
create a column for fiscal quarter and use a DAX formula that simply takes values from
other columns in this table and for each role calculates a fiscal year, a fiscal month and
a fiscal quarter. This is covering some of our
documentation online. So itís a little-- it looks
a little daunting at first, but itís actually quite simple.
David Ludwig: So you donít use regular old Excel formulas in this formula bar here. You
use DAX formulas. Owen Duncan: Thatís right. DAX is very similar
to Excel formulas. Many of the functions even look the same but DAX is meant to work
with tables and columns of data rather than individual cells like Excel.
David Ludwig: Cool. So this is a good way to add a column that you need in case the
raw data that came from the source doesnít have quite the friendliness that it needs,
in this case the real year, the month and the
quarter. Owen Duncan: Thatís correct.
David Ludwig: And we have a link to the DAX expressions documentation on the blog
post. So I was playing with this and I was reading somewhere, probably in some of your
documentation, that itís not always best to add a calculated column. That a good practice
is to use a field. Can you show me that? Owen Duncan: A calculated field. Well, when
I added sum of sales amount to our Pivot table here it automatically went to the values
area and it was aggregated with a sum function. Thatís called an implicit measure.
Owen Duncan: Now, I can create whatís called an explicit measure by going to my
sales amount column in our table here and I can just go up and I can click on auto sum.
Now, thereís other ways I can aggregate this and right off the bat it creates a sum of
sales amount measure. And Iím just going to rename this.
David Ludwig: I see youíre putting the name of the field there.
Owen Duncan: I can rename it. And the measure name is in the formula itself. And Iím
going to go ahead and create another one for my costs of goods sold.
David Ludwig: COG stands for cost of goods sold.
Owen Duncan: So now I have a total sales amount, and if I add this total sales amount
measure to my table you can see Iíll get the same amounts. So I donít really need
this one because I canít rename that one. Whenever
possible youíll want to create what I call explicit measures. And we can see we have
our total costs of goods sold. Owen Duncan: Now, often times youíll see
people create a calculating column to calculate a profit margin, but I can do that
with these two measures that Iíve all ready created. I can just go down here and I can
say Iím just going to name it total profit. And
Iím just going to say take my total sales amountÖ
David Ludwig: Cool. And does that M stand for measure?
Owen Duncan: Yeah. And thatís just auto-complete thatís helping me along the way and
Iím just going to say total cost of goods sold.
David Ludwig: Right. So youíre using those fields you made.
Owen Duncan: Yeah. And if I just click enter. Now, with a measure I can also format it
in a way to display it that I want it to always display. And now if I go back to my Internet
sales table, I can see that I have now a total profit measure. And I can use this in any
number of Power Pivot tables that are based on this data model and I get my total profit.
David Ludwig: Cool. So often what you do in Power Pivot is you make things that are
then usable in a Pivot that you can then Pivot on.
Owen Duncan: Thatís right. Thatís right. The great thing about measures is they can
be extremely powerful. And if you need to filter
your aggregation on some way you need to filter out a particular type of data. Itís
where the real power is in Power Pivot. David Ludwig: Okay. Cool. Now, show me real
quickly hierarchies because that I read about and I was curious about that one.
Owen Duncan: Sure. Hierarchies you can create in the diagram view. Now, say, for
instance, I created that fiscal year, fiscal quarter and fiscal month columns. I can group
them by simply multi selecting, right clicking and say create hierarchy. And Iím just going
to go ahead and name that fiscal. And now if I go back to my date table and the field
thereís my new hierarchy. So letís go ahead and letís just take calendar out there and
put fiscal in. Iíve got to change the order.
So itís real easy for me just to change the order.
Iíve got just to click and drag it to the proper place.
David Ludwig: Cool. Owen Duncan: So now, you can see I automatically
updated. Thereís my fiscal year. I can expand. Thereís my fiscal quarter. I
can expand. Thereís my fiscal month for each fiscal quarter. Very fast.
David Ludwig: Thatís cool. So when you create a hierarchy youíre adding a field that
will be then used in the Pivot table but itís somewhat like a super power field that has
more dimensions or something. Owen Duncan: Itís a great way to group similar
dimensions and really drill down. David Ludwig: Drill down. Yeah. Okay. Cool.
Well, thank you very much for this demo. It really shed a lot of light for me and I
hope for our customers. So thank you very much
for joining us this morning. Owen Duncan: Well, thank you.
David Ludwig: Folks, this is just the tip of the iceberg. If you want to learn more
about Power Pivot, go to aka.ms/PowerPivot123. Thereís
a ton of links at the bottom of the page there for you, including a tutorial,
how to get the add-in, et cetera. Itís all to here.
Just for more general information about our webinar series, you can go to aka.ms/offweb
and you can get a calendar of our webinars, download a reminder, so forth and so on, so
check that out. David Ludwig: Next week, weíre going to talk
about how One Drive works with Office online. Doug will be back to give you a demo
on that one. But very special thanks to the Office webinar team today Doug, Bruce, Chris,
Owen and Casper and stick around for the Q&A. Weíll be happy to answer any questions
you have. Thank you. Owen Duncan: Thank you.
#### End of OW_098_full.mp4 #### Ref#: MSFTOFT-72 Microsoft Office / Weekly
Webinar page 2 of 7 David Ludwig; Owen Duncan / OW_098_full.mp4
Microsoft Office 2/27/14 Page 2 of 7 RF# MSFTOFT-72 www.ProductionTranscripts.com
888-349-3022