Microsoft SQL Server 2012 Bible

Creating BI Semantic Models Using PowerPivot

The PowerPivot add-in for Excel is an intuitive and lightweight development environment of BI Semantic Models that you can download for free as part of the SQL Server 2012 Feature Pack from the Microsoft Download Center at www.microsoft.com/download/en/details.aspx?id=29074.

In the following example, you extract data from AdventureWorksDW2012.

To create a new BI Semantic Model using the PowerPivot add-in for Excel 2010, follow these steps:

1. Open Excel 2010.

2. Locate and click the PowerPivot tab.

3. Click the PowerPivot Window option. The PowerPivot development window opens.

4. In the Get External Data Ribbon section, click the From Database icon. Figure 58.4 shows the database source options available to extract external data.

Figure 58.4 External data database source options.

5. From the drop-down menu, select From SQL Server. The Table Import Wizard dialog window opens prompting for the SQL Server instance and database to connect to. Type the name of the SQL Server instance where the AdventureWorksDW2012 database is hosted. Select or type the AdventureWorksDW2012 database under the database name.

6. Click Next. The Choose How to Import the Data dialog window opens. You are presented with two options:

• Select from a list of tables and views to choose the Data to import.
• Write a query that will specify the data to import. ...