Adding a data source to a model

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.

  1. 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:
    Adding a data source to a model
  2. In the COM Add-Ins dialog box, select Microsoft Office PowerPivot for Excel 2013 and Power View. Click on OK when done.
    Adding a data source to a model
  3. 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.
  4. 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:
    Adding a data source to a model
  5. 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.
    Adding a data source to a model
  6. Click on the Design button in Specify a MDX Query.
  7. 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:
    Adding a data source to a model
  8. Validate the MDX query. Then, click on Finish.
  9. You'll receive a confirmation when the data import is completed successfully. Click on Close.
    Adding a data source to a model
  10. 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:

Adding a data source to a 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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.128.173.53