Share via

  • Article

Creating a SQL Server 2012 Tabular BI Semantic Model with PowerPivot (RTM Update)

Version: 1.5

Description

In this lab, you will create a PowerPivot workbook that will import data from a SQL Server database and an Excel file. The PowerPivot model will then be enriched with calculated columns, hierarchies, a Key Performance Indicator (KPI), and a perspective.

Overview

In this lab, you will create a PowerPivot workbook that will import data from a SQL Server database and an Excel file. The PowerPivot model will then be enriched with calculated columns, hierarchies, a Key Performance Indicator (KPI), and a perspective.

The principal goal of this lab is to create a user-friendly interface to the model to support self-service reporting, analytics and monitoring for Excel PivotTables, PerformancePoint Services dashboards and Reporting Services reports.

You will explore the PowerPivot Field List interface by producing a PivotTable report. The final report will look like the following.

Figure 1

Previewing the PivotTable Report

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:

  • Import data into PowerPivot
  • Create table relationships
  • Create calculated columns
  • Create hierarchies
  • Hide columns
  • Create measures
  • Create a KPI
  • Create a perspective
  • Create a PivotTable report

System Requirements

You must have installed the following items to complete this lab:

  • Microsoft Office:
    • Excel 2010
  • Microsoft SQL Server 2012
    • Database Engine
    • PowerPivot Add-in for Excel

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:

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 then 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.

Cleanup

There is no need to cleanup if you intend to continue the sequence of labs in this training kit.

  1. To drop the TailspinToys SQL Server database, execute the Cleanup.cmd script located under the Source\Setup folder of this lab.
  2. Manually delete the workbook saved to the Source folder.

Exercises

This Hands-On Lab comprises the following exercise:

  1. Creating a PowerPivot Workbook

Estimated time to complete this lab: 45 minutes

Author

This lab was designed and written by Peter Myers of SolidQ.

Feedback

Your feedback is welcome! Please, post it on our UserVoice forums.

next >

Downloads

Download The Offline Training Kit
Download Hands-on Lab Document (DOCX)
Download Hands-on Lab Source Files

Contents