다음을 통해 공유

Creating your first PowerPivot Workbook in Excel 2013 Preview

  • 아티클

Please note I am not a member of the Dev team. My posts are just things that I have found when playing with the new Office bits. My findings may be inaccurate or an error on my part.

After installing Excel 2013 you may would like to create a new workbook containing PowerPivot data. After you install Excel 2013 Preview you are set to go. There is no extra add-in to download.

But you may wonder where the PowerPivot tab are:

excel2013

As you can see there are no tab by default. As described in the help it can be enable by going to FILE –> Options –> Add-Ins –> Manage: COM Add-ins and checking the box in front of Microsoft Office PowerPivot for Excel 2013.

excel2013_1

However the basic modeling capabilities are already built-in to Excel 2013. So in reality you just need to import some data.  You go into DATA –> Get External Data –> From Other Sources –> From SQL Server

excel2013_2

 

As you can see there are some new options in the Data Connection Wizard. First of all I can select multiple tables, second is that I can get related tables and the relationships between the tables. For those of you that have used PowerPivot in earlier releases you know that this is something that you could do in the PowerPivot add-in.

 

After I have selected the tables that I am interested in I get some new options in the next step.

excel2013_3

If I previously have added the Power View add-in in my Excel client I get the option of creating a Power View report directly.

As you also can see there is the option of adding the data to the Data Model. This get selected by default if you import several tables and relationships or if you import the data to a Power View Report. This is using the same Vertipaq storage mechanism as you have in PowerPivot.

 

So what do the different options do? After my tests I have come to the following conclusions.

Table imports the data to a normal Table, if you have imported several tables then is that you will get one sheet for each table. If you have or checked the box “Add this data to the Data Model” what it will also do is that it will add the data to the data model. Note that this will actually store the data twice in the workbook. Both in the Excel worksheet as well as in the data model. Note also that the data is disconnected it does not use linked tables in PowerPivot.

PivotTable Report and PivotChart this will add the data to the data model. It will also create a pivot table that is connected to the original data source and not a pivot table connected to the data model.

Power View Report will import the data into the data model and then create a Power View work sheet connected to the Data Model.

 

Happy playing with the new Office 15 bits.

Comments

  • Anonymous
    July 30, 2012
    Is it possible to filter the data?

  • Anonymous
    July 30, 2012
    Hi Dan, As described in blogs.msdn.com/.../going-all-in-with-excel-2013.aspx filtering is not supported directly in Excel 2013 when you import data. If you want to filter you need to import using the PowerPivot add-in. //Simon