Big Data, Data Warehouses, and Business Intelligence Systems - Database Processing: Fundamentals, Design, and Implementation - page 553

By The WAy The Microsoft SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010
add-in is downloadable from http://www.microsoft.com/en-us/download/
details.aspx?id=29074 ) . It is supposed to be usable in Microsoft Excel 2013 as well, but
we have not been able to confirm this. PowerPivot provides additional tools and the
ability to work with larger datasets than can be handled by Microsoft Excel 2013 itself.
It is a useful tool and well worth looking into.
To connect to the HSD-DW data, we click the Get External Data drop-down gallery
arrow button on the DATA command tab. As shown in Figure 12-21(a), this displays the Get
External Data gallery. Here we click the From Other Sources button, which gives us a list of
data sources that includes SQL Server.
Clicking the From SQL Server button starts the Data Connection Wizard shown in
Figure 12-21(b). In the Connect to Database Server page of the wizard, we select the SQL
Server we want to use and ours means of authentication, and then click the Next button.
By The WAy If the Microsoft SQL Server 2012 instance is on your local machine (your
own computer) and is installed as the default (unnamed) instance, type in
only your computer name. For example, WS12-001 in Figure 12-21(b) is our computer,
and we have installed Microsoft SQL Server 2012 on it as a default instance, we simply
type in WS12-001.
If you are connecting to a non-default, named instance of Microsoft SQL Server
2012, type in both the computer name and the SQL Server 2012. For example, if we
installed an additional version of Microsoft SQL Server 2012 such as SQL Server 2012
Express Advanced on our computer, we would type in WS12-001\SQLEXPRESS to
connect to this named instance.
As shown in Figure 12-21(c), in the Select Database and Table page of the wizard, we
select the DHSD-DW database and HSDDWProductSalesView as the source of our
data—note how useful the SQL-CREATE-VIEW-CH12-01 statement and the resulting
HSDDWProductSalesView view are in making it easy to get exactly the data we want for the
PivotTable. After selecting the database and view, we click the Next button to display the Save
Data Connection File and Finish wizard page as shown in Figure 12-21(d). This step simply
saves the data connection we have created for future use, and there is nothing we need to do
on this page, so we click the Finish button.
As shown in Figure 12-21(e), the Import Data dialog box is displayed. Because we want to
store our data in a worksheet in our Microsoft Excel workbook before we create the PivotTable,
the correct selections are shown here. Click the OK button.
In Figure 12-21( f ), we see the data formatted as a table in the worksheet, which we now
name as HSSDWProductSalesView . Microsoft Excel has opened the ANALYZE command
tab in the TABLE TOOL contextual command tab, but we actually need the INSERT command
tab at this point. Click the INSERT command tab to display the commands on the INSERT
command tab as shown in Figure 12-21(g).
On the INSERT command tab, click the PivotTable button. The Create Pivot Table dia-
log box is displayed, as shown in Figure 12-21(h). The correct table range is selected, and we
select the New Worksheet radio button because we want the PivotTable in a new, separate
worksheet. Click the OK button to create the PivotTable structure, as shown in Figure 12-21(i).
Selecting the appropriate fields in the PivotTable Fields pane then creates the PivotTable
itself, as seen in Figure 12-21( j).
In Figure 12-21( j), the measure is quantity sold, and the dimensions are ProductNumber
and City. This report shows how quantity varies by product and city. For example, four copies
of VB003 (Kitchen Remodeling Dallas Style Video Companion) were sold in Dallas, but none
were sold in Austin.