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

● or connect to a DBMS data source using the Get External Data Command:
● Click the Get External Data command on the DATA command tab.
● Select an SQL Server database as the data source.
● Specify that the data should go into a Microsoft Excel table.
● Create the PivotTable.
● or use the Microsoft PowerPivot for Excel 2013 add-in feature to connect to a DBMS
data source, and then create the PivotTable.
We can use an SQL query if we copy the data into an Excel worksheet. The SQL query, as
used in SQL Server, is:
/* *** SQL-Query-CH12-02 *** */
SELECT C.CustomerID, CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.[Year], T.QuarterText,
SUM(PS.Quantity) AS TotalQuantity
FROM CUSTOMER C, PRODUCT_SALES PS, PRODUCT P, TIMELINE T
WHERE C.CustomerID = PS.CustomerID
AND P.ProductNumber = PS.ProductNumber
AND T.TimeID = PS.TimeID
GROUP BY C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.QuarterText, T.[Year]
ORDER BY C.CustomerName, T.[Year], T.QuarterText;
However, because SQL Server (and other SQL-based DBMS products, such as Oracle
Database and MySQL) can store views but not queries, we need to create and use an
SQL view if we are going to use an Excel-data connection. The SQL query to create the
HSDDWProductSalesView, as used in SQL Server, is:
/* *** SQL-CREATE-VIEW-CH12-01 *** */
CREATE VIEW HSDDWProductSalesView AS
SELECT C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.[Year], T.QuarterText,
SUM(PS.Quantity) AS TotalQuantity
FROM CUSTOMER C, PRODUCT_SALES PS, PRODUCT P, TIMELINE T
WHERE C.CustomerID = PS.CustomerID
AND P.ProductNumber = PS.ProductNumber
AND T.TimeID = PS.TimeID
GROUP BY C.CustomerID, C.CustomerName, C.City,
P.ProductNumber, P.ProductName,
T.QuarterText, T.[Year];
We can now use the DSDDWProductSalesView when we connect to the database as the
data source for an OLAP report. We will do this using the standard Microsoft Excel 2013 tools
on the DATA command tab, and Figure 12-21(a) shows our starting point, a blank Microsoft
Excel 2013 workbook (named DBP-e13-HSD-BI.xlsx) with the Microsoft SQL Server 2012 SP1
Data Mining Add-Ins for Office (downloadable from http://www.microsoft.com/en-us/download/
details.apsx?id=35578) .