Hands-On Lab
Creating Excel 2010 Reports Based on a SQL Server 2012 Tabular BI Semantic Model (RTM Update)
Lab version: 1.2 (RTM Update)
Last updated: 7/2/2012
CONTENTS
Overview 3
Task 1 – Installing TailspinToys and AdventureWorksDW2012 Databases 4
Task 2 – Creating a Site Collection 4
Task 3 – Activating PowerPivot Feature 5
Task 4 – Creating a PowerPivot Gallery 5
Task 5 – Creating the Sales Analysis Database 5
Task 1 – Dropping the TailspinToys and AdventureWorksDW2012 SQL Server Databases. 6
Task 2 – Deleting the AdventureWorksBI Site Collection 6
Task 3 – Dropping Sales Analysis Database 6
Exercise 1: Creating the Country Sales Report 7
Task 1 – Preparing the Excel Workbook 8
Task 2 – Creating the Workbook Connection and PivotTable 8
Task 3 – Defining the PivotTable Layout 9
Task 4 – Defining a Dynamic Date Filter 11
Task 5 – Inserting and Filter with a Slicer 13
Task 6 – Inserting a Sparkline 16
Task 7 – Formatting the Report 17
Task 8 – Inserting a Bar Chart 19
Task 9 – Finalizing the Report Layout 19
Exercise 2: Publishing the Country Sales Report 21
Task 1 – Publishing the Excel Workbook to SharePoint 21
Task 2 – Reviewing the Published Workbook 23
Task 3 – Finishing Up 23
Summary 24
Overview
In this lab, you will create a report in Excel 2010 based on a tabular BI Semantic Model. You will then publish the workbook to SharePoint Server and then explore the report rendered by Excel Services in the Web browser.
Note: Before you start with this exercise you must ensure that your machine meets the system requirements detailed in the next section. Additionally, you must complete the setup steps described in the next section.
Objectives
The objectives of this exercise are to:
· Create an Excel Report based on a tabular BI Semantic Model
· Work with PivotTables
· Work with the Excel CUBE functions
· Work with slicers, sparklines and charts
· Publish an Excel Workbook to SharePoint Server
· Interact with the published workbook in the Web browser
System Requirements
You must have installed the following items to complete this lab:
· Microsoft Office 2010:
◦ Excel
· Microsoft SQL Server 2012:
◦ Database Engine
◦ Analysis Services (Tabular mode)
· Microsoft SharePoint 2010 Enterprise Edition:
◦ Excel Services
· Adventure Works for SQL Server 2012
◦ AdventureWorksDW2012
Setup
The setup and configuration for this lab involves running a Setup script included with the training kit. To run all setup tasks for this lab:
Task 1 – Installing TailspinToys and AdventureWorksDW2012 Databases
Note: This process may require administrative privileges.
1. Open a Windows Explorer window and browse to the lab’s folder.
2. Right click on it, and click on Properties.
3. In the Security tab, click on Edit
4. In the opened window, click on Add
5. Add Everyone role to the folder’s permissions and click Ok
6. Grant Full Access control to it by checking the Full Access Checkbox and click Ok
7. Click Ok
8. Browse to the lab’s Source\Setup folder.
9. Double-click the Setup.cmd file in this folder to launch the setup script and run all setup tasks for this lab.
10. If the User Account Control dialog is shown, confirm the action to proceed.
11. If your instance of SQL Server is other than localhost you will need to modify the connection string in the create_SalesAnalysis.xmla file located under Source\Setup\scripts\Tasks\sql folder of this Lab (line 13529).
XMLA
</Annotations
ConnectionStringProvider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Persist Security Info=false</ConnectionString
ImpersonationInfo
Task 2 – Creating a Site Collection
1. To open Central Administration, click the Start button, and then select All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Central Administration.
2. Login using Administrator credentials, if required.
3. Click Application Management.
4. Click Create Site Collections.
5. Configure the new site collection based on the following table.
Property / ValueTitle / AdventureWorksBI
URL / /sites/AdventureWorksBI
Template / Enterprise | Business Intelligence Center
Primary Site Collection Administrator / <Administrator username>
Task 3 – Activating PowerPivot Feature
1. In the Top-Level Site Successfully Created page, click the URL for the newly created Site Collection.
2. In the new Site Collection, click Site Actions, and select Site Settings.
3. Click Site Collection Features in the Site Collection Administration section.
4. Click Activate for the PowerPivot Feature Integration for Site Collections feature.
Task 4 – Creating a PowerPivot Gallery
1. Click AdventureWorksBI above the Quick Launch.
2. Click Site Actions, and select More Options.
3. In the search box at the right upper corner, type “PowerPivot” and press Enter.
4. Select the PowerPivot Gallery item, and click More Options.
5. Type PowerPivot Gallery in the Name field.
6. Check that the Yes radio button in the Display this list on the Quick Launch option is selected.
7. Click Create.
8. Close the Internet Explorer Window.
Task 5 – Creating the Sales Analysis Database
1. Double-click create_SalesAnalysis.xmla file located under the Source\Setup\scripts\Tasks\sql folder of this Lab.
2. When SQL Server Management Studio opens connect to the SQL Server instance, with Analysis Services as the server type, running in VERTIPAQ mode.
3. Click the Query menu, and then Execute.
Cleanup
There is no need to cleanup if you intend to continue the sequence of labs in this training kit.
Task 1 – Dropping the TailspinToys and AdventureWorksDW2012 SQL Server Databases.
1. Execute the Cleanup.cmd script located under the Setup folder in the Source folder of this lab.
Task 2 – Deleting the AdventureWorksBI Site Collection
1. To open Central Administration, click the Start button, and then select All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Central Administration.
2. Login using Administrator credentials, if required.
3. Click Application Management.
4. Click Delete a Site Collection.
5. Click No Selection and select Change Site Collection.
6. Click the AdventureWorksBI Site Collection, and click OK.
7. Click Delete.
8. Click OK to confirm the deletion of the Site Collection.
Task 3 – Dropping Sales Analysis Database
1. Double-click drop_SalesAnalysis.xmla file located under the Source\Setup\scripts\Tasks\sql folder of this Lab.
2. When SQL Server Management Studio opens connect to the SQL Server instance running in VERTIPAQ mode.
3. Click the Query menu, and then Execute.
Feedback
Your feedback is welcome! Please, post it on our UserVoice forums.
Exercises
This Hands-On Lab comprises the following exercise:
1. Creating the Country Sales Report
2. Publishing the Country Sales Report
Estimated time to complete this lab: 30 minutes
Exercise 1: Creating the Country Sales Report
In this exercise, you will create a report in Excel 2010 based on a tabular BI Semantic Model. You will commence by creating a PivotTable based on the Sales Analysis database. Then you will convert the PivotTable to formulas and customize the layout with a slicer, a sparkline and a bar chart.
The finished report will look like the following.
Figure 1
Previewing the Country Sales Report
Task 1 – Preparing the Excel Workbook
In this task, you will create a new workbook and prepare it for the report design.
1. To open Excel, click the Start button, and then select All Programs | Microsoft Office | Microsoft Excel 2010.
2. Right-click worksheet tab Sheet1, and then select Rename.
3. Rename the worksheet to Country Sales Report, and then press Enter.
4. To delete worksheet Sheet2, right-click worksheet tab Sheet2, and then select Delete.
5. When prompted to delete the worksheet, click Delete.
6. Repeat the last two steps to delete worksheet Sheet3.
7. In cell A1, enter the text Country Sales Report.
8. Ensure cell A1 is selected, and the on the Home ribbon tab, inside the Font group, increase the font size to 20pt.
9. On the File ribbon tab, click Save.
10. Browse to this HOL’s Source folder, type Country Sales Report.xlsx for the File Name and click Save.
Task 2 – Creating the Workbook Connection and PivotTable
In this task, you will define a workbook connection to the Sales Analysis database, and create a PivotTable based on the connection.
1. Select cell B4.
2. On the Data ribbon tab, inside the Get External Data group, click From Other Sources, and then select From Analysis Services.
Figure 2
Getting Data From Analysis Services
3. In the Data Connection Wizard, in the Connect to Database step, in the Server Name box, enter the name of the Analysis Services instance that hosts the Sales Analysis database.
4. Click Next.
5. In the Select Database and Table step, in the dropdown list, ensure the Sales Analysis database is selected.
6. Notice that the database contains three cubes. The Model cube is the default perspective, and the Monitoring and Sales cubes are perspectives of the model.
7. Ensure Model is selected, and then click Next.
8. In the Save Data Connection File and Finish step, in the Friendly Name box, modify the text to Sales Analysis.
9. Click Finish.
10. In the Import Data window, accept the default configuration to add a PivotTable, and then click OK.
Task 3 – Defining the PivotTable Layout
In this task, you will define the PivotTable layout to display sales by the quarters of calendar year 2002, and by country. You will then convert the PivotTable to formulas so that the layout can be customized beyond what can be achieved with a PivotTable.
1. In the PivotTable Field List (located on the right), in the ResellerSales measure group, check the Sales measure.
2. Notice that the measure is added to the Values drop zone, and that the PivotTable displays the aggregate of all sales.
3. From the Date dimension, drag the Calendar hierarchy into the Column Labels drop zone.
Figure 3
Adding the Calendar Hierarchy to the Column Labels Drop Zone
4. In the Reseller dimension, check the Geography hierarchy.
5. Notice that the Geography hierarchy is automatically added to the Row Labels drop zone.
6. To filter on CY2007 sales, right-click the CY2007 member (cell E5), and then select Filter | Keep Only Selected Items.
7. To reveal the quarters, expand the CY2007 member (cell C5).
8. To hide the year level, right-click the CY Q1 member (cell C6), and then select Show/Hide Fields | Year.
9. Verify that the PivotTable looks like the following.
Figure 4
Verifying the PivotTable Layout
10. To convert the PivotTable to CUBE functions, on the PivotTable Tools Options ribbon, inside the Tools group, click OLAP Tools, and then select Convert to Formulas.
11. To explore the formulas based on the CUBE functions, select cell B4, and notice the CUBEMEMBER function uses the Sales Analysis workbook connection, and retrieves the Sales measure.
12. Select cell C5, and notice that the CUBEMEMBER function retrieves the member representing the first calendar quarter of 2003.
13. Select cell G5, and notice that the CUBESET function retrieves a set of members representing the four quarters, and displays the label Grand Total.
14. Select cell B6, and notice that the CUBEMEMBER function retrieves the member representing the country Australia.
15. Finally, select cell C6, and position the cursor in the formula bar (as if to edit the formula). Notice that that CUBEVALUE function retrieves a value at the intersection of the three members highlighted in the worksheet (cells B4, C5 and B6).
Task 4 – Defining a Dynamic Date Filter
In this task, you will modify the four CUBEMEMBER functions that retrieve the calendar quarter members to reference a calendar year filter value entered by the user.
1. To introduce a calendar year filter, in cell B2 enter the text Calendar Year.
2. In cell C2, enter the text 2007.
3. To create a named range, ensure cell C2 is selected, and then on the Formulas ribbon tab, inside the Defined Names group, click Define Name.
4. In the New Name window, in the Name box, replace the text with Year.
Figure 5
Defining a Name
5. Click OK.
6. To hide row 4, right-click the row guide, and then select Hide.
7. In cell B5, replace the text with Country.
8. In cell C5, modify the formula to dynamically reference the member based on the calendar year value entered in cell C2. The expression will look like the following.
Excel
=CUBEMEMBER("Sales Analysis", "[Date].[Calendar].[Year].[CY" & Year & "].&[CY Q1]")
9. Repeat the last step to modify the formula in the three cells D5, E5 and F5.
10. To modify the width of all value columns, select the column C heading, and then while pressing the Shift key, select the column G heading.
11. Right-click any column header in the selection, and then select Column Width.
12. In the Column Width window, enter 12, and then click OK.
13. To format the values in report, select the range C6:G12, right-click inside the range, and then select Format Cells.
14. In the Format Cells window, in the Category list, select Number.
15. In the Decimal Places control, reduce the number to 0 (zero).
16. Check the Use 1000 Separator checkbox.
Figure 6
Formatting the Cells
17. Click OK.
18. To test the calendar year filter, in cell C2, modify the value to 2006, and then press Enter.
19. Notice that the values in the report automatically refresh according to the new filter context.
20. In cell C2, modify the value to 2007.
Task 5 – Inserting and Filter with a Slicer
In this task, you will add a slicer to the worksheet to allow users to filter by the product categories.
1. To accommodate the slicer, right-click the column A heading, and then select Column Width.
2. In the Column Width window, enter 30, and then click OK.
3. To insert a slicer, on the Insert ribbon tab, inside the Filter group, click Slicer.