Description
In this Hands On Lab you will learn how to 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.
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.
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:
- Microsoft SQL Server 2012:
- Database Engine
- Analysis Services (Tabular mode)
- Microsoft SharePoint 2010 Enterprise Edition:
- Adventure Works for SQL Server 2012
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
This process may require administrative privileges.
- Open a Windows Explorer window and browse to the lab’s folder.
- Right click on it, and click on Properties.
- In the Security tab, click on Edit
- In the opened window, click on Add
- Add Everyone role to the folder’s permissions and click Ok
- Grant Full Access control to it by checking the Full Access Checkbox and click Ok
- Click Ok
- Browse to the lab’s Source\Setup folder.
- Double-click the Setup.cmd file in this folder to launch the setup script and run all setup tasks for this lab.
- If the User Account Control dialog is shown, confirm the action to proceed.
- 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).
</Annotations>
<ConnectionString>Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Persist Security Info=false</ConnectionString>FakePre-bf209c7f6fae4290b1afc422c6806491-552db7127d6e4051b0315217a4397329FakePre-89ee4aed996a403fb09f1eb78615d463-1bc6f9d3e8b24867ba9cd7bde8375657
Task 2 – Creating a Site Collection
- To open Central Administration, click the Start button, and then select All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Central Administration.
- Login using Administrator credentials, if required.
- Click Application Management.
- Click Create Site Collections.
Configure the new site collection based on the following table.
Title |
AdventureWorksBI |
URL |
/sites/AdventureWorksBI |
Template |
Enterprise | Business Intelligence Center |
Primary Site Collection Administrator |
<Administrator username> |
Task 3 – Activating PowerPivot Feature
- In the Top-Level Site Successfully Created page, click the URL for the newly created Site Collection.
- In the new Site Collection, click Site Actions, and select Site Settings.
- Click Site Collection Features in the Site Collection Administration section.
- Click Activate for the PowerPivot Feature Integration for Site Collections feature.
Task 4 – Creating a PowerPivot Gallery
- Click AdventureWorksBI above the Quick Launch.
- Click Site Actions, and select More Options.
- In the search box at the right upper corner, type “PowerPivot” and press Enter.
- Select the PowerPivot Gallery item, and click More Options.
- Type PowerPivot Gallery in the Name field.
- Check that the Yes radio button in the Display this list on the Quick Launch option is selected.
- Click Create.
- Close the Internet Explorer Window.
Task 5 – Creating the Sales Analysis Database
- Double-click create_SalesAnalysis.xmla file located under the Source\Setup\scripts\Tasks\sql folder of this Lab.
- When SQL Server Management Studio opens connect to the SQL Server instance, with Analysis Services as the server type, running in VERTIPAQ mode.
- 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.
- Execute the Cleanup.cmd script located under the Setup folder in the Source folder of this lab.
Task 2 – Deleting the AdventureWorksBI Site Collection
- To open Central Administration, click the Start button, and then select All Programs | Microsoft SharePoint 2010 Products | SharePoint 2010 Central Administration.
- Login using Administrator credentials, if required.
- Click Application Management.
- Click Delete a Site Collection.
- Click No Selection and select Change Site Collection.
- Click the AdventureWorksBI Site Collection, and click OK.
- Click Delete.
- Click OK to confirm the deletion of the Site Collection.
Task 3 – Dropping Sales Analysis Database
- Double-click drop_SalesAnalysis.xmla file located under the Source\Setup\scripts\Tasks\sql folder of this Lab.
- When SQL Server Management Studio opens connect to the SQL Server instance running in VERTIPAQ mode.
- 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:
- Creating the Country Sales Report
- Publishing the Country Sales Report
Estimated time to complete this lab: 30 minutes