A data model is the basis for any Power View and PowerPivot.
Let's create that. In this demonstration, I'm using Excel 2013. The difference in Excel 2010 may not be significant. You may want to double check that.
Open an empty Excel workbook. Check if the PowerPivot add-in is enabled. If you don't see PowerPivot in the ribbon panel, it's not enabled. To enable, go to File | Options | Add-Ins. Under the Add-ins list, select COM Add-ins from the Manage drop-down box, then click on Go, as shown in the following screenshot:
In the COM Add-Ins dialog box, select Microsoft Office PowerPivot for Excel 2013 and Power View. Click on OK when done.
You may notice that there's now a new tab created in your workbook named PowerPivot. Click on that. Select Manage from the ribbon panel.
That displays the PowerPivot for Excel pane. Click on Get External Data and go to From Database | From Analysis Services or PowerPivot as shown in the following screenshot:
That displays the Table Import Wizard window. In Connect to Microsoft SQL Server Analysis Services, connect to the SQL Server 2012 Analysis Services we created in Chapter 6, Let's Get Analytical!, SQL2012-01 in my case. In the Database name drop-down list select AdventureWorksDW2012Multidimensional-EE. Test your connection. Click on Next when done.
Click on the Design button in Specify a MDX Query.
That displays the MDX query designer. Select Sales Summary from the cube selector. Then, drag the Sales Territory dimension to the designer. For the measure, expand Sales Summary and drag Sales Amount, Gross Profit, and Gross Profit Margin next to the dimensions in the designer:
Validate the MDX query. Then, click on Finish.
You'll receive a confirmation when the data import is completed successfully. Click on Close.
Save the Excel workbook. Name it SalesTerritory.xlsx. We're going to need that in the next exercises.
Here's a simple chart that was created by this model:
You can do a lot in Excel with data like this, especially with the Power View and PowerPivot add-ins. A lot of books cover that topic.