Introduction to MSSQL Server Analysis Services: Reporting Options for Analysis Services Cubes: MS Excel 2003 and More ... - Page 2September 12, 2005 Reporting Options for Analysis Services Cubes: Microsoft Excel 2003MS Office 2003 continues to build upon the robust OLAP reporting features for the desktop user that began to appear, in earnest, in MS Office 2000. The basic operation of the PivotTable report has remained the same as with previous versions. We will examine the PivotTable report in a hands-on fashion in the sections that follow, to update the information we provided in Reporting Options for Analysis Services Cubes: MS Excel 2002. Next we will examine another Excel 2003 OLAP reporting option, the Microsoft Office Excel Add-in for SQL Server Analysis Services, discussing its capabilities and providing information regarding where to obtain this exciting add-in. PivotTable Reports in MS Office Excel 2003 When a PivotTable report accesses a multidimensional cube, it receives data from a specified Analysis Server via the PivotTable Service. As the first exercise in our tutorial, we will create a PivotTable report that accesses the Warehouse sample cube that comes along with Analysis Services. We will use the PivotTable Wizard found in Excel 2003 in our initial efforts. The procedures we cover are essentially the same for Excel 2000 and 2002, although some of the terminology used in the latter, as well as the appearance of dialogs / other objects, differ, to a degree. The Wizard accesses the Microsoft Query application in Excel to build a query file. Query file creation is a one-time event for any given PivotTable report, and defines the connection between Microsoft Excel and the Analysis Services cube. Connecting Excel to the Cube The PivotTable Wizard walks us through a guided process for connecting Excel 2003 to a given Analysis Services cube. We begin by taking the following steps: 1. Open a new Excel 2003 workbook. 2. Click Data (top menu), then select PivotTable and PivotChart Report, to initialize the PivotTable and PivotChart Wizard, as shown in Illustration 1.
The PivotTable and PivotChart Wizard - Step 1 of 3 dialog appears. 3. Select the External Data Source radio button on the Step 1 of 3 Wizard dialog. 4. Ensure that the radio button to the immediate left of "PivotTable" is selected in the lower half of the dialog, underneath "What kind of report do you want to create?" The PivotTable and PivotChart Wizard - Step 1 of 3 dialog appears as depicted in Illustration 2.
5. Click Next. The PivotTable and PivotChart Wizard - Step 2 of 3 dialog appears, as shown in Illustration 3.
Here we specify the source of our data. For this tutorial, we will use the sample Warehouse cube that accompanies an installation of Analysis Services. 6. Click the Get Data button. Microsoft Query starts, and presents the Choose Data Source dialog. 7. Click the OLAP Cubes tab. The dialog box appears as depicted in Illustration 4.
8. Click and highlight <New Data Source>, as necessary. 9. Click OK. The Create New Data Source dialog appears. 10. Type the following in Box 1 of the dialog, where we name the data source: Warehouse Cube 11. Select Microsoft OLE DB Provider for OLAP Services 8.0 in Box 2. The Create New Data Source dialog appears as shown in Illustration 5.
12. Click the Connect... button. The Multidimensional Connection dialog appears. 13. Ensure that the Analysis server radio button is selected as the location of the multidimensional data source we wish to access. 14. In the Server text box, type the name of the server. The Multidimensional Connection dialog appears, as depicted in Illustration 6 (where we see the name of one of my servers, MOTHER).
NOTE: The name localhost can be supplied, if Excel and the cube share the same server, according to the Microsoft documentation. 15. Click Next. The next dialog of the Multidimensional Connection appears, asking that we select the targeted Analysis Services database. Here we will select the FoodMart 2000 database that accompanied the Analysis Server installation, as shown in Illustration 7.
NOTE: The databases that appear within the selection list of the Multidimensional Connection - Select the database ... dialog will differ, depicting those Analysis Services databases that exist within your own environment. 16. Click Finish. The Create New Data Source dialog reappears, with the new target data source indicated to the right of the Connect... button. 17. Select the Warehouse cube in Box 4 of the dialog, where we notice that all cubes in the Foodmart 2000 Analysis Services database appear. After selecting the Warehouse cube, the Create New Data Source dialog should resemble that depicted in Illustration 8.
18. Click OK. We return to the Choose Data Source dialog. 19. Ensure that the Warehouse Cube data source remains selected, as shown in Illustration 9.
20. Click OK, to return to the Step 2 of 3 dialog, where we left off with the PivotTable and PivotChart Wizard. The Step 2 of 3 dialog appears, as depicted in Illustration 10. Note that a new message, "Data fields have been retrieved," now appears to the right of the Get Data button.
21. Click Finish. An empty PivotTable report appears, allowing us to begin browsing the cube / designing the report immediately. In addition to the PivotTable report template, the PivotTable toolbar and the PivotTable Field List appear; the PivotTable Field List provides a selection of report building components (we discuss these in the next section). The Analysis Server is now providing the dimension and measures information to the PivotTable report directly from the cube. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
