Learn about the five benefits of PowerPivot in this introductory video from MrExcel.
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
This video is designed to accompany the book, PowerPivot for the Data Analyst: Microsoft Excel 2010
Transcript of the video:
MrExcel netcast is sponsored by Easy-XL.
PowerPivot for the Data Analyst Chapter One – What is PowerPivot?
Oh, hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen. We’re starting off here on a series of podcasts in support of the book PowerPivot for the Data Analyst: Microsoft Excel 2010.
These podcasts are going to go along with that book and talk about PowerPivot.
One video from each chapter.
And, first of all, let’s just get started with the question of what is PowerPivot and why should I care?
Now, this is a utility that came out from Microsoft, not from the Excel team, but from the Microsoft Sequel Server Analysis Services team.
In my opinion, this is the best piece of software to come out of Microsoft in 20 years.
It enables five amazing things in Excel 2010 and the great news is, the client version, the version that works here with Excel, is free.
You just go to powerpivot.com and download this, provided you have Excel 2010.
Alright, five things that PowerPivot does for us that, I think, just are amazing.
First thing: You’re no longer limited to a million rows.
Here I have a dataset that I loaded up just from a CSV file.
This dataset has a whopping 7.2 million rows.
Very fast to work with this.
I can sort, I can filter, I can create pivot tables from this data and it all works very, very quickly because the data is stored in memory.
So that’s the first thing we have.
Second thing: I have sheet one here with the 7.2 million rows.
And, typical of the IT department, they gave me store ID, but they did not give me store name, region, and all that other stuff I need.
So somewhere on my computer I had this nice little lookup file.
It converts store ID, gives me the store name, the mall developer, the region, when it was opened, and selling square feet.
Now, if you’re used to Excel, you can go, “Hey, this is simple.
This is VLOOKUPs, right?” VLOOKUPs will join these two tables together, but think about it.
7.2 million VLOOKUPs just to get store name, another 7 million to get region, another 7 million to get mall developer.
You’re looking at many, many VLOOKUPs that will take forever to calculate.
The great news about PowerPivot is you don’t have to do VLOOKUPs anymore.
Just define a relationship.
This store column is related to this store column and PowerPivot is going to do all of the joins for you.
Alright, so that’s the second thing.
Third: Let’s go back to Excel.
We’ll take a look at the pivot table that was created here.
The amazing thing: here’s our list of fields.
These are all the fields in sheet 1.
So let’s say that I want to see revenue.
Simple enough.
We’ve always been able to do that although this is now revenue from 7 million rows.
As I scroll down further in the PowerPivot field list, here are all the fields on sheet 2.
So if I want to take a look at that revenue cut by, for example, region from sheet 2, bam, that’s done.
So they’re now joining sheet 1 and sheet 2.
I didn’t do any VLOOKUPs.
Now this is all happening in memory.
Very, very amazing way to go.
Fourth, and I’m not going to show you in this video, is the new DAX function language.
DAX, well A) the functions in DAX are exactly like the functions in Excel, so you’ll be very comfortable with those.
Left, right, mid, month, year, minutes, day, things like that.
Great, but there’s 60 new functions and those new functions are some of the amazing ones.
There’s time intelligence functions, like show me all the dates that are month-to-date from this date.
Or show me all the dates in a parallel period one year ago.
Also functions to handle fiscal years.
If your fiscal year doesn’t end on December 31st, finally Microsoft has provided something for you.
Alright.
And then, finally, the last thing, the last cool feature is what I call Asynchronist Reporting.
This is where we want to show, for example, actuals for all the months that have gone by and budget for the future months.
Regular pivot tables can’t do that.
In Excel 2010, they added a feature called Name Sets that does that, but, unfortunately, Name Sets don’t work with data that is just coming from regular Excel.
It only works with data that is coming from OLAP.
Here’s the beautiful thing.
When you take that regular Excel data, run it through PowerPivot, bring it back to Excel, all of a sudden your dataset is now an OLAP dataset, which means that you can use Name Sets to create the Asynchronist Reports.
Excellent, excellent feature.
Alright, so as we go through the book, each chapter… There’s a lot of topics in each chapter.
I’ve just chosen one to put here in the videos, in the blog and out on YouTube.
So you’ll see one feature from each chapter, learn a lot about PowerPivot as we go through.
Okay, I’m going to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
PowerPivot for the Data Analyst Chapter One – What is PowerPivot?
Oh, hey.
Welcome back to the MrExcel netcast.
I’m Bill Jelen. We’re starting off here on a series of podcasts in support of the book PowerPivot for the Data Analyst: Microsoft Excel 2010.
These podcasts are going to go along with that book and talk about PowerPivot.
One video from each chapter.
And, first of all, let’s just get started with the question of what is PowerPivot and why should I care?
Now, this is a utility that came out from Microsoft, not from the Excel team, but from the Microsoft Sequel Server Analysis Services team.
In my opinion, this is the best piece of software to come out of Microsoft in 20 years.
It enables five amazing things in Excel 2010 and the great news is, the client version, the version that works here with Excel, is free.
You just go to powerpivot.com and download this, provided you have Excel 2010.
Alright, five things that PowerPivot does for us that, I think, just are amazing.
First thing: You’re no longer limited to a million rows.
Here I have a dataset that I loaded up just from a CSV file.
This dataset has a whopping 7.2 million rows.
Very fast to work with this.
I can sort, I can filter, I can create pivot tables from this data and it all works very, very quickly because the data is stored in memory.
So that’s the first thing we have.
Second thing: I have sheet one here with the 7.2 million rows.
And, typical of the IT department, they gave me store ID, but they did not give me store name, region, and all that other stuff I need.
So somewhere on my computer I had this nice little lookup file.
It converts store ID, gives me the store name, the mall developer, the region, when it was opened, and selling square feet.
Now, if you’re used to Excel, you can go, “Hey, this is simple.
This is VLOOKUPs, right?” VLOOKUPs will join these two tables together, but think about it.
7.2 million VLOOKUPs just to get store name, another 7 million to get region, another 7 million to get mall developer.
You’re looking at many, many VLOOKUPs that will take forever to calculate.
The great news about PowerPivot is you don’t have to do VLOOKUPs anymore.
Just define a relationship.
This store column is related to this store column and PowerPivot is going to do all of the joins for you.
Alright, so that’s the second thing.
Third: Let’s go back to Excel.
We’ll take a look at the pivot table that was created here.
The amazing thing: here’s our list of fields.
These are all the fields in sheet 1.
So let’s say that I want to see revenue.
Simple enough.
We’ve always been able to do that although this is now revenue from 7 million rows.
As I scroll down further in the PowerPivot field list, here are all the fields on sheet 2.
So if I want to take a look at that revenue cut by, for example, region from sheet 2, bam, that’s done.
So they’re now joining sheet 1 and sheet 2.
I didn’t do any VLOOKUPs.
Now this is all happening in memory.
Very, very amazing way to go.
Fourth, and I’m not going to show you in this video, is the new DAX function language.
DAX, well A) the functions in DAX are exactly like the functions in Excel, so you’ll be very comfortable with those.
Left, right, mid, month, year, minutes, day, things like that.
Great, but there’s 60 new functions and those new functions are some of the amazing ones.
There’s time intelligence functions, like show me all the dates that are month-to-date from this date.
Or show me all the dates in a parallel period one year ago.
Also functions to handle fiscal years.
If your fiscal year doesn’t end on December 31st, finally Microsoft has provided something for you.
Alright.
And then, finally, the last thing, the last cool feature is what I call Asynchronist Reporting.
This is where we want to show, for example, actuals for all the months that have gone by and budget for the future months.
Regular pivot tables can’t do that.
In Excel 2010, they added a feature called Name Sets that does that, but, unfortunately, Name Sets don’t work with data that is just coming from regular Excel.
It only works with data that is coming from OLAP.
Here’s the beautiful thing.
When you take that regular Excel data, run it through PowerPivot, bring it back to Excel, all of a sudden your dataset is now an OLAP dataset, which means that you can use Name Sets to create the Asynchronist Reports.
Excellent, excellent feature.
Alright, so as we go through the book, each chapter… There’s a lot of topics in each chapter.
I’ve just chosen one to put here in the videos, in the blog and out on YouTube.
So you’ll see one feature from each chapter, learn a lot about PowerPivot as we go through.
Okay, I’m going to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
