Share via

  • Article

Creating a SQL Server Reporting Services Report Based on a SQL Server 2012 Tabular BI Semantic Model (RTM Update)

Version: 1.5

Description

In this lab, you will author a report in Report Builder 3.0 using the Map Wizard. The map analytic data will be retrieved from the Sales Analysis tabular BI Semantic Model.

Overview

In this lab, you will author a report in Report Builder 3.0 using the Map Wizard. The map analytic data will be retrieved from the Sales Analysis tabular BI Semantic Model.

Objectives

The objectives of this exercise are to:

  • Create a Shared Data Source based on the tabular BI Semantic Model
  • Create a Report Builder 3.0 report that references the shared data source
  • Use the MDX graphical query designer to produce a dataset using the shared data source

System Requirements

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

Setup

The setup and configuration for this lab involves running a Setup script included with the training kit, and also performing the following tasks:

Note:
This process may require administrative privileges.

Task 1 – Updating the AdventureWorksDW2012 Database

  1. Open a Windows Explorer window and browse to the lab’s Source folder.

  2. 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>
    <ConnectionString>Provider=SQLNCLI11;Data Source=localhost;Initial Catalog=AdventureWorksDW2012;Integrated Security=SSPI;Persist Security Info=false</ConnectionString>FakePre-6b5fc00225b349a6992b7c3ecc0d40e1-b4df5e1a658e470cb2d3796e8c6df265

  3. Double-click the Setup.cmd file in this folder to launch the setup script.
  4. If the User Account Control dialog is shown, confirm the action to proceed.

Task 2 – 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, in the Connect to Server dialog, select Analysis Services in the Server type combobox.
  3. In the server name, type your SQL Server instance name.
  4. Click Connect.
  5. Click the query window, then click the Query menu, and select Execute.

Task 3 – 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

    Value

    Title

    AdventureWorksBI

    URL

    /sites/AdventureWorksBI

    Template

    Enterprise | Business Intelligence Center

    Primary Site Collection Administrator

    <Administrator username>

Task 4 – Modifying the Site Collection’s Data Connection Library

  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 Data Connections in the Quick Launch.
  3. In the Library tab, inside the Settings group, click Library Settings.
  4. In the Content Types section, click Add from existing site content types.
  5. Scroll down the Available Site Content Types list, select the Report Data Source content type, and click Add.
  6. Click OK.

Task 5 – Creating a new Document Library

  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 “Document Library” and press Enter.
  4. Select the Document Library item, and click More Options.
  5. Type Reports 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. In the Library tab, inside the Settings group, click Library Settings.
  9. In the General Settings section, click Advanced Settings.
  10. Check the Yes radio button in the Allow management of content types option.
  11. Click OK
  12. In the Content Types section, click Addfrom existing site content types.
  13. Scroll down the Available Site Content Types list, select the Report Builder Report content type, and click Add.
  14. Click OK.
  15. Close the Internet Explorer Window.

Cleanup

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

Task 1 – Restoring the AdventureWorksDW2012 SQL Server Database

  1. Open a Windows Explorer window and browse to the lab’s Source\Setup folder.
  2. Double-click the Cleanup.cmd file in this folder to launch the cleanup script.
  3. If the User Account Control dialog is shown, confirm the action to proceed.

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 the 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, in the Connect to Server dialog, select Analysis Services in the Server type combobox.
  3. In the server name, type the VERTIPAQ instance name.
  4. Click Connect.
  5. Click the query window, then click the Query menu, and select Execute.

Feedback

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

Exercises

This Hands-On Lab comprises the following exercise:

  1. Creating a Report Using the Map Wizard

Estimated time to complete this lab: 30 minutes

next >

Downloads

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

Contents