BI and Composites Service Applications Configuration

Hands-on-Lab - BI Services Configuration

BI and Composites Service Applications Configuration

Hands-on-Lab

Terms of Use

This document is provided “as-is”. Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it.

Some examples depicted herein are provided for illustration only and are fictitious. No real association or connection is intended or should be inferred.

This document does not provide you with any legal rights to any intellectual property in any Microsoft product. You may copy and use this document for your internal, reference purposes.

© 2011 Microsoft Corporation. All rights reserved.

This document reflects current views and assumptions as of the date of development and is subject to change. Actual and future results and trends may differ materially from any forward-looking statements. Microsoft assumes no responsibility for errors or omissions in the materials.

THIS DOCUMENT IS FOR INFORMATIONAL AND TRAINING PURPOSES ONLY AND IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT.

“About the Authors” is an important item as it establishes the credibility of the text by explaining how much experience the author team has in the field. Add as many “About the Authors” as needed from the Quick Parts Gallery. Change “Author” to a different title, such as “Subject Matter Expert” if that is more appropriate. Include SMEs who assisted with the development of the course. Add this to every Module.

Table of Contents

Lab Environment

Lab Overview

Exercise 1: Prepare the External Data Source

Exercise 2: Create the SSS Target Application

Exercise 3: Create the External Content Type

Exercise 4: Create the External List

Appendix

BI and Composites Service Applications Configuration- Appendix

Lab Environment

Several paragraphs – or longer – describing an overview of the module including a description of why this content is important and a concise description of the main points of the module

During this lab, you will work in a simulated environment with the following computers or virtual machines and prerequisite databases.

Computers and virtual machines used in this lab

Logon Credentials

The lab environment might require you to logon to the Hands-on-Lab Environment using the following credentials, unless specified differently throughout the lab manual.

AdventureWorks Database

The AdventureWorks database is a set of sample databases that are currently available for Microsoft SQL Server 2005 and Microsoft SQL Server 2008. The AdventureWorks sample database installer is located on the DEMO2010A virtual machine at C:\AdventureWorksDB\AdventureWorks2008R2_RTM.exe and the instructions can be found in the Appendix of this document.

Lab Overview

Several paragraphs – or longer – describing an overview of the lab including a description of the lab and why certain topics are covered. This is also called the ‘LAB ABSTRACT’ that will be used for hand-off to conferences as part of the content hand-off process

Abstract

Inthis lab, you will you will perform several exercises to achieve a better understanding of the capabilities and functionality of the Microsoft Business Connectivity Services and its use of the Secure Store Services within SharePoint 2010.

You start by creating a new Secure Store Service (SSS) Target Application which will store the credentials of a specific user account that will be used to access data stored in the AdventureWorks data warehouse database. You will then use SharePoint Designer 2010 to create a new Business Connectivity Services (BCS) External Content Type which will use the credentials stored by the SSS Target Application to connect to the AdventureWorks database on behalf of users who do not have direct access to this database. Finally you will create an External List that will display the information from the AdventureWorks Products table to the enduser.

Every exercise will consist of a series of steps to help you understand how the Business Connectivity Services uses the Secure Store Services to access external data.

Learning Objectives

After completing the exercises in this lab, you will be able to:

• Create a Secure Store Services Target Application to store credential mappings
• Create an External Content Type to use the SSS Target Application
• Create an External List that utilizes the External Content Type to display data to the end-user

Estimated time to complete this lab: 30 minutes

Exercise 1: Prepare the External Data Source

Each exercise consists of a scenario and learning objectives, the scenario describes the purpose of the exercices, while the objectives are listed and have bullet points.

Scenario

Before youcan configure the Secure Store Services Target Application or the BCS External Content Type and External List, youmust first create the service account that will be used by the Secure Store Service to connect to the AdventureWorks database. In this exercise you will create this account and grant it read permissions to the appropriate database.

This exercise is merely a prerequisite to prepare the environment for use by the Secure Store Services and BCS.

1. Open the Active Directory Users and Computers management console.
2. Select contoso.comUsers, and then click the add user icon to create a new user in the Users container.

1. On the New Object - User dialog:
2. Set the Full name to SharePoint Unattended User Account.
3. Set the User logon name to spunattended.
4. Set the User logon name (pre-Windows 2000) to spunattended.

1. Click Next to continue to the next screen in the wizard.
2. Enter a password (pass@word1) for the new user account and select the Password never expires checkbox.

1. Click Next to continue to the next screen in the wizard.
2. Click Finish to complete the wizard and create the new user.

1. Open the SQL Server 2008R2 Management Studio.
2. In the Server name dropdown, type DEMO2010A.

1. In the Object Explorer, expand the tree to DEMO2010ADatabasesAdventureWorksDW2008R2Security and right-click the Users folder.
2. Select New User… to create a new user for the database.

1. In the new user dialog set the User name and Login name fields to contoso\spunattended.
2. In the Role Members panel, select thedb_datareaderrole, and then click OK to create the new user.

Exercise 2: Create the SSS Target Application

Each exercise consists of a scenario and learning objectives, the scenario describes the purpose of the exercices, while the objectives are listed and have bullet points.

Scenario

In this exercise you will use the Central Administration site to create a new Secure Store Target Application. You will start by creating the Target Application and then you will associate the appropriate credentials with the new Target Application.

By the end of this exercise you will be able to:

• Create a Target Application
• Associate credentials with a Target Application

1. On the Home page of the SharePoint Central Administration Web site, in the Application Management section, click Manage service applications.

1. On the Manage Service Applications page, click the link for Secure Store Service (or select the item and click Manage in the ribbon).

1. On the Secure Store Services Management page, click New in the ribbon to create a new Target Application.

1. On the Create New Secure Store Target Application page:
2. Set the Target Application ID to AdventureWorks.
3. Set the Display Name to AdventureWorks Target Application.
4. Set the Contact E-mail to .
5. Under Target Application Type, select Group as shown in the following illustration. (A target application type of Group indicates that you want to map multiple identities to a single set of credentials). Click Next.

1. On the next page, leave the credential fields set to Windows User Name and Windows Password, and then click Next.

1. On the next page, in the Target Application Administrators text box, add your administrative account. In the Members text box, add the Contoso FTE security group. Click OK to save the new Target Application.

1. On the Secure Store Service page, on the AdventureWorks drop-down list, click Set Credentials, as shown in the following illustration.

1. On the Set Credentials for Secure Store Target Application page, provide the contoso\spunattended credentials that were created previously and are required to access the AdventureWorks database, and then click OK.

Exercise 3: Create the External Content Type

Each exercise consists of a scenario and learning objectives, the scenario describes the purpose of the exercices, while the objectives are listed and have bullet points.

Scenario

To access external data using the Business Connectivity Services Service Application, you must create an External Content Type. In this exerciseyou will use SharePoint Designer 2010 to create the External Content Type in the Intranet Web Application ( You will then associate this External Content Type with the AdventureWorks database and finally, you will create the appropriate read operations.

By the end of this exercise you will be able to:

• Create External Content Types
• Associate External Content Types with a Secure Store Services Target Application
• Create operations for accessing data from an external system

1. Open SharePoint Designer 2010.
2. Open the Intranet Site Collection at .
3. On the left hand navigation, find and select External Content Types.
4. Select External Content Type in the New section of the ribbon in the upper left hand corner of the page.

1. In the External Content Type Information panel, click the default name and change it to AWProducts; change the default display name to Adventure Works Products.

1. Select Click here to discover external data sources and define operations.

1. Select Add Connection.
2. Select SQL Server as the Data Source Type and click OK to continue.

1. On the SQL Server Connection dialog:
2. Set the Database Server to DEMO2010A.
3. Set the Database Name to AdventureWorksDW2008R2.
4. Set the Name to AdventureWorksDW2008R2.
5. Select Connect with Impersonated Windows Identity.
6. Set the Secure Store Application ID to AdventureWorks.

1. If prompted for credentials provide the username and password for the unattended service account.

1. In the Data Source Explorer, expand to AdventureWorksDW2008R2Tables and right click the DimProduct table.
2. Select New Read List Operation and click Finish on the Read List Operation Properties page to accept all defaults.

1. Repeat the previous step and select New Read Item Operation to create a Read Item Operation.
2. Click the Save icon to save the External Content Type

Exercise 4: Create the External List

Each exercise consists of a scenario and learning objectives, the scenario describes the purpose of the exercices, while the objectives are listed and have bullet points.

Scenario

Once you have created the External Content Type you can now create a list that provides end-users with a view into the data exposed by the External Content Type operations. This, again, is done using SharePoint Designer 2010. In this exercise you will create the External List using SharePoint Designer 2010 and then, to complete the configuration you will grant end-users execute permissions on the External Content Type. Finally, you will test the External List.

By the end of this exercise you will be able to:

• Create an External List using SharePoint Designer 2010
• Grant permissions to External Content Types
• Test an External List

1. Open SharePoint Designer 2010.
2. Open the Intranet Site Collection at .
3. On the left hand navigation, find and select External Content Types.
4. Double-click the previously created AWProducts External Content Type to open the summary view.
5. Click Create Lists & Form in the Lists & Forms section of the ribbon.

1. Set the List Name property to Products.

1. Open the Central Administration site and navigate to the Manage Service Applications page.
2. Select the Business Data Connectivity Serviceservice application.

1. On the dropdown menu for the AWProducts External Content Type, clickSet Permissions.

1. Add the Contoso FTE security group and grant it Execute rights.

1. Navigate to to view the new External List and validate that results are returned from the SQL Server database.

1. To validate that a non-administrative user can view the data sign in with an unprivileged account.
2. Username: contoso\adamb
3. Password: pass@word1

Appendix

This Appendix provides information about the actual lab manual and labcode, as well as the virtual machine requirements needed for this lab. Use this page to keep information about lab updates / modifications and corrections to the manual, as well as the uniquely defined labcode.

Lab Notes

Virtual Machine Requirements

1Microsoft Confidential - For Internal Use Only