If you're a little confused, or just want to know more, this article is for you.
What are PowerPivots for Excel?
Basically, it's a new way to manage and analyze data in Excel. It's main features are:
- in-memory processing of large amounts of data (forget the old limit of 65k rows - think millions instead)
- import disparate data and create relationships between the tables and columns to join data from different sources (no need for vlookup formulas anymore)
- a powerful new expression language (DAX) for calculated fields
- integration with visualization tools within Excel, including Slicers
- no need for IT assistance (big one for business users like me)
What does that mean?
It means that you will be able to handle much more data than before.
It also means that you can, for the first time, connect different tables (from different sources) using a common column.
So?
That's huge! Imagine you have a Sales table, which you get from a corporate database, and a Targets table, which you maintain in Excel (oh, what a familiar feeling...)
Both of them have a "Sales Representative" column. With PowerPivot, you can create a report using a single PivotTable that compares actual sales with the sales target without using formulas.
You're effectively modelling a database within Excel!
That means I don't need databases anymore! I can fire half of the IT department!
Hold on!
Excel should be used to analyze data and design reports, not to store data, and not to serve as the standard reporting. Databases are reliable, Spreadsheets are volatile. You need both to handle your information needs.
Is that all there is to it?
No.
PowerPivot is a crucial part of Microsoft's BI strategy and integrates with its main products: SQL Server, SharePoint, and Excel. The main advantage of this integration is the possibility of publishing and sharing your PowerPivot. I'm not going to go into that right now, because we're an Office focused blog.
I'm tired of reading. I want to try it!
PowerPivot is a free plugin for Excel 2010. Download it here.
Let's try a really simple thing to get you started.
Step 1 - Data: Create the following tables in Excel:
Step 2 - Adding the data to PowerPivot: Select the first table, and then click "Create Linked Table" on the PowerPivot tab.
Do the same for the other table.
Step 3 - PowerPivot configuration:
Go to the PowerPivot Window. Rename the tables "Sales" and "Countries" by double clicking on "Table1" and "Table2" (bottom of the PowerPivot Window).
Then create a relationship between the tables by clicking on the appropriately named "Create Relationship" icon in the Design tab.
What you want is to link the sales data to the corresponding country. Select "Sales" as the first table and "Country" as the column. Then "Countries" as the second table and "Country" as the Related Lookup Column.
Note: one of your tables should have a column with unique values, and that column should be the Related Lookup Column. You want each row of your sales table to lookup the country information.
Step 4 - Create a Report: It's as easy as a PivotTable. Just click the PivotTable icon on the Home tab of the PowerPivot Window.
The first thing you'll notice is that the field list now contains a tree. The fields appear within your tables, which makes navigating a lot of field much more easy - another advantage of PowerPivot.
As you see, I can now filter the sales data by Region, even though there is no region information on the Sales table - just country.
Conclusion
The way I see it, there's not much you can do with PowerPivots that you couldn't before, using LookUp formulas and calculated fields. But you can do it faster and more easily. Which is what we lazy people like.
Besides, DAX opens a lot of opportunities, and we'll be sure to talk more about that in the future.
No comments:
Post a Comment