Sharepoint 2010 BI Services

SharePoint 2010 BI Services Step-by-step Tutorials

This book provides step-by-step tutorials for setting up the essential SharePoint Server 2010 business intelligence services.

ii

About This Book Setup SharePoint 2010 BI Services: Step-by-step Tutorials This book provides step-by-step tutorials for setting up the essential SharePoint Server 2010 business intelligence services.

Published by Stephan Albert Bren November 20, 2013 Copyright Š by Stephan Albert Bren

iii

Table of Contents Introduction .................................................................................................................................................. 1 What this book covers .............................................................................................................................. 1 What you need to use this book ............................................................................................................... 1 How this book is structured ...................................................................................................................... 1 How to Setup Business Connectivity Services .............................................................................................. 3 Overview ................................................................................................................................................... 3 Step 1: Provision LOB accounts and security groups ................................................................................ 4 Step 2: Create new Secure Store Service target application .................................................................... 6 Step 3: Create new external content type ................................................................................................ 9 Step 4: Set Object Permissions ............................................................................................................... 16 Step 5: Test ............................................................................................................................................. 17 References .............................................................................................................................................. 18 How to Setup Excel Services ....................................................................................................................... 21 Overview ................................................................................................................................................. 21 Preparation ............................................................................................................................................. 21 Step 1: Configure the data access account ............................................................................................. 22 Step 2: Configure a new Secure Store target application ....................................................................... 23 Step 3: Configure Excel Services Global Settings .................................................................................... 25 Step 4: Configure the workbook to use the unattended service account .............................................. 26 Step 5: Configure Workbook objects to be published ............................................................................ 28 Step 6: Perform a test publication of the workbook .............................................................................. 30 Optional: Display the PivotChart in a Dashboard ................................................................................... 33 References .............................................................................................................................................. 36 How to Setup Word Automation Services .................................................................................................. 39 Overview ................................................................................................................................................. 39 Step 1: Configure..................................................................................................................................... 39 Step 2: Enable ......................................................................................................................................... 40 Step 3: Activate ....................................................................................................................................... 42 Step 4: Test ............................................................................................................................................. 43 Optional: Remove the new page link from Global and Current Navigation ........................................... 45

iv

Summary ................................................................................................................................................. 45 References .............................................................................................................................................. 45 How to Setup Access Services ..................................................................................................................... 47 Overview ................................................................................................................................................. 47 Step 1: Install Silverlight on clients ......................................................................................................... 47 Step 2: Enable ASP.NET Session State Service ........................................................................................ 47 Step 3: Configure Access Services ........................................................................................................... 50 Optional: Publish the Northwind Traders sample Access application .................................................... 51 Optional: Remove a published Access database application ................................................................. 53 References .............................................................................................................................................. 54 Troubleshooting .......................................................................................................................................... 55 An error occurred during the "Generate Key" process........................................................................... 55 Cannot logon with credentials obtained from Secure Store Provider.................................................... 56 Access Denied by Business Data Connectivity ........................................................................................ 57 Error: Specified method is not supported .............................................................................................. 61 Convert Document option does not appear on the Word document's context menu .......................... 62

v

This page intentionally blank

vi

SharePoint 2010 BI Services

1 Introduction If you would like to learn how to implement the SharePoint Server 2010 business intelligence (BI) services in a step by step manner, then this book is for you. In this book, you will learn how to setup the essential BI services available with SharePoint Server 2010.

What this book covers This book presents step-by-step tutorials for setting up, configuring and testing the essential BI services, including: Business Connectivity Services, Excel Services, Word Automation Services and Access Services.

Each and every click, text entry and selection is identified and listed for you. No steps are left out. Key screen shots are included to provide visual feedback that you are on the right track.

What you need to use this book Before you get started, you do need to have essential hardware resources. The author recommends that your machine meet the following minimum requirements for the best build experience: •

8 GB RAM

•

Hardware-based VM acceleration

•

40 GB disk space

•

DVD Writer

It will also be helpful to have an Internet connection. Note: you’ll need the DVD writer to install the downloaded trial versions to a DVD installation disk.

You will also need a virtualization environment, such as VMware, Hyper-V, Virtual Box, Virtual PC etc. and to have created a virtual SharePoint Server 2010 farm for development and testing.

How this book is structured Chapter 1: Introduction Chapter 2: How to Setup Business Connectivity Services

1

Introduction Chapter 3: How to Setup Excel Services Chapter 4: How to Setup Documentation Conversion from Word to HTML Chapter 5: How to Setup Access Services Chapter 6: Troubleshooting

2

SharePoint 2010 BI Services

2 How to Setup Business Connectivity Services Overview This chapter walks you through the process of setting up SharePoint 2010 Secure Store and Business Connectivity Services (BCS) to access line of business (LOB) data hosted in the same domain as the SharePoint farm. It builds upon the excellent articles previously written on this subject by Marco Cadario, Jardalu, iKarstein, and Kirk Evans, and of course the excellent Microsoft Developer Network articles that focus on SharePoint 2010 business connectivity. Working through this tutorial creates read access to a table in a database. The LOB data that the BCS in this tutorial will connect to is the Northwind database hosted on the farm's backend SQL Server instance.

The steps involved here are performed on a small, two-tier development SharePoint 2010 farm hosted on Windows 2008 R2 SP1 and employing NTLM authentication. A Secure Store Service has not previously been manually configured on this machine, but one was setup automatically during the SharePoint Server 2010 initial installation and configuration. The tools that will be used in this tutorial include: •

Active Directory User and Computers snap-in,

•

SQL Server 2008 management studio, and

•

SharePoint Server 2010 Central Administration.

Setting up Secure Store and BCS to access LOB data involves four main steps: 1. Provisioning accounts and security groups 2. Creating a new Secure Store Service target application 3. Creating a new external content type 4. Setting BCS object permissions 5. Testing

3

How to setup Business Connectivity Services Each of these steps will be presented in this chapter. Step 1 will be presented only in summary form, as Active Directory and SQL Server are not the focus of this tutorial. Steps 2, 3 and 4 will be presented in detail, where each task is specifically described, click by click. Steps for solving common problems encountered whilst setting up a Secure Store and BCS applications are presented in the Troubleshooting section, at bottom.

Step 1: Provision LOB accounts and security groups 1) Create a new account, contoso\sp_bcs, in Active Directory that will be used to run the Secure Store application pool:

2) Create a new security group, BCS1, the members of which will have access to the LOB data:

4

SharePoint 2010 BI Services 3) Add members to this security group as desired.

4) Register the account with SharePoint Server 2010 as a managed account:

5) Grant the account access to the LOB database:

5

How to setup Business Connectivity Services 6) This completes Step 1.

Step 2: Create new Secure Store Service target application 1) In Central Administration, navigate to the Manage Service Applications page. Go: Application Management > Manage service applications.

2) On the Manage Service Applications page, scroll down until you find Secure Store Service.

You’ll see two Secure Store Service items. One is the Secure Store Service Application and the other is the Secure Store Service Application Proxy. Click on the one that is the Secure Store Service Application.

3) Click Secure Store Service. Your browser is navigated to the Secure Store Service Application: Secure Store Service page.

If you have not previously created a secure store service application, you will see a warning message listed.

4) On the Edit ribbon, click the Generate New Key button. The Generate New Key dialog appears:

5) Enter a pass phrase that meets the security requirements. The pass phrase we’ll use is P@ssPhrase1.

6) Click OK. The dialog closes, and the focus is returned to the Secure Store Service application page:

6

SharePoint 2010 BI Services 7) On the Edit ribbon, click the New button. The Specify Settings page of the Create New Secure Store Target Application wizard appears.

8) On this page, configure the fields as desired. For this tutorial, the following configuration was implemented: a) Target Application ID: BCS1. b) Display Name: BCS1-spdev11-AW. c) Contact Email: [your admin email]. d) Target Application Type: Group. e) Target Application Page URL: None.

9) Click Next. The Specify the Credentials page of the wizard appears.

10) Enter the field names as desired. For this tutorial, the following configuration was used: a) BCS1 Windows User Name - Windows User Name - Not Masked. b) BCS1 Windows Password - Windows Password - Masked.

11) Click Next. The Specify the membership settings page is presented.

12) Enter the accounts and groups as desired. For this tutorial, the following configuration was implemented:

7

How to setup Business Connectivity Services a) Target Application Administrators: contoso\Administrator; contoso\Stephan.Bren. b) Members: contoso\BCS1.

13) Click OK. The Create New Secure Store Target Application wizard page closes, and the browser is navigated back to the Secure Store Service page, now listing the newly created target application:

14) Select this target application, and, on the Edit ribbon, click the Set Credentials button. The Set Credentials for Secure Store Target Application (Group) dialog appears.

15) Enter the information as desired. For this tutorial, the following configuration was used: a) Credential Owners: contoso\Administration; contoso\Stephan.Bren; contoso\sp_bcs1. b) BCS1 Windows User Name: contoso\sp_bcs1. c) BCS1 Windows Password: P@ssw0rd. d) Confirm BCS1 Windows Password: P@ssw0rd.

8

SharePoint 2010 BI Services

16) Click OK. The dialog closes, and the focus returns to the Secure Store Service page: 17) This completes Step 2.

Step 3: Create new external content type 1) Open SharePoint Designer 2010.

2) Connect to the SharePoint Server 2010 farm.

3) In the Navigation pane, select External Content Types.

4) On the External Content Types ribbon, click the New External Content Type button. The Results pane updates to display the New External content type pane:

9

How to setup Business Connectivity Services

5) In the External Content Type Information section, edit the fields as so: a) Name: Northwind. b) Display Name: Northwind. c) Version: [default]. d) Identifiers: [default]. e) Office Item Type: Generic List. f)

Offline Sync for external list: Enabled.

6) Click where it states: Click here to discover external data sources an... The Operations Designer pane is displayed:

10

SharePoint 2010 BI Services

7) Click the Add Connection button. A small popup appears from which you can select the connection type.

8) Select SQL Server, and then click OK. The popup closes and the SQL Server Connection dialog appears.

11

How to setup Business Connectivity Services 9) Enter the configuration information. For this tutorial, the configuration is the following: a) Database Server: spdev11. b) Database Name: Northwind. c) Name (optional): Northwind. d) Select Connect with Impersonated Windows Identity. e) Secure Store Application ID: BCS1.

This is the same name that you assigned to the Secure Store Service target application, back in Step 2: 10) Click OK. The SQL Server Connection dialog closes and a progress bar appears momentarily: 11) When the progress bar closes, the focus is returned to the Operation Designer pane, now displaying a closed tree item, Northwind, in the Data Source Explorer tab.

12) Expand the tree. The various object groups associated with this database are revealed. Expand the Tables object group and then select the Customers table: 13) Then expand the Customers table object:

14) Right-click on the Customers table item in the tree. A popup menu is displayed.

15) Select New Read List Operation. The Operations Properties page of the Read List wizard dialog is displayed:

12

SharePoint 2010 BI Services

16) Click Next. The Filter Parameters Configuration page is displayed.

17) Click Next. Leave the CustomerID data source element configuration settings as default, but for all the other fields, enable the Show in Picker setting:

18) Click Finish. The dialog closes and the focus changes back to the Operation Designer page: 19) Look in the External Content Type Operations section: the read list operation is listed.

13

How to setup Business Connectivity Services

Note also the warning. To resolve this warning, repeat steps 3.12 through 3.17, but this time choosing New Read Item Operation from the popup menu. In other words: a) On the Data Source Explorer tab, scroll down to the Customers table. b) Right-click on the Customers table object. c) Select New Read Item Operation from the popup menu. d) Repeat the configuration steps for the Read Item wizard. Once the Read Item operation has been configured, you will see two operations listed in the External Content Type Operations section: 20) On the SharePoint Designer UI, select the External Content Types ribbon, and then click the Summary View button. The results pane is updated to display the Summary Results tab. Note that now, the Identifiers field has been populated with the CustomerID(String) field, and the available fields are listed in the Fields section:

14

SharePoint 2010 BI Services 21) Click the Save button at the top right of the SharePoint Designer UI. This saves the BCS configuration back to the farm.

22) Again on the External Content Types ribbon, click the Create Lists & Form button.

The Create List and Form dialog appears.

23) The settings are configured as follows: a) List Name: Northwind_Customers. b) Read Item Operations: CustomersRead Item. c) System Instance: Northwind. d) List Description: [blank] 24) Click OK. The dialog closes, and the focus returns to the Summary View. Note that now there is an entry in the External Lists section, at right:

25) On the SharePoint Designer UI, in the Navigation pane at right, select Lists and Libraries.

26) In the Lists and Libraries panel, now appearing at right, note that there is a new group added, External Lists, and an item in this group, Northwind_Customers, and a new View is also listed:

15

How to setup Business Connectivity Services

27) Close SharePoint Designer.

28) This completes Step 3.

Step 4: Set Object Permissions 1) Launch Central Administration.

2) Navigate to the Manage Service Applications page, and then click on the Business Data Connectivity Service application. The Business Data Connectivity Service page is displayed.

There are two Business Data Connectivity Service entries in this list, one is the application and the other is the application proxy. To distinguish them, look in the Type column.

3) Select Northwind from the list.

4) On the Edit ribbon, click the Set Object Permissions button.

The Set Object Permissions dialog appears.

5) In this dialog, add the domain administrator account and the Contoso\BCS1 security group to the users and groups to be granted permissions.

6) Then configure each of these with all of the permissions listed:

16

SharePoint 2010 BI Services

7) Click Close. The dialog closes, and the focus returns to the Business Data Connectivity Service page.

8) This completes Step 4.

Step 5: Test 1) Open a new browser, and then connect (as domain administrator) to the site, spdev12. Note that the Northwind_Customers list appears in the Lists group of quick links.

2) Click the Northwind_Customers link. The Northwind_Customers list is shown.

17

How to setup Business Connectivity Services

3) This completes Step 5.

References TechNet SharePoint 2010 •

Configure the Secure Store Service (SharePoint Server 2010)

•

Secure Store service cmdlets (SharePoint Server 2010)

Developer Network SharePoint 2010 •

Configuring the Secure Store Service

SharePoint Administrator and Developer Blogs •

How to create and configure Business Connectivity Services on SharePoint 2010

•

Secure Store Service - Configuration (SharePoint 2010)

•

How To: Create, Configure, Consume SharePoint 2010 Secure Store in Business Connectivity Services

•

Walkthrough: Create a simple BCS connection with SharePoint Designer 2010

•

Setting Up BCS and the Secure Store Service in SharePoint 2010

18

SharePoint 2010 BI Services Al's Tech Tips •

SharePoint 2010: An error occurred during the Generate Key process

•

Cannot logon with credentials obtained from Secure Store Provider

Developer Network SharePoint 2010 •

Consuming External Data Using SharePoint Server 2010 Business Connectivity Services and an Excel 2010 Add-In

•

Configuring the Secure Store Service

MSDN Magazine •

Using Business Connectivity Services in SharePoint 2010

19

How to setup Business Connectivity Services

20

SharePoint 2010 BI Services

3 How to Setup Excel Services Overview This chapter walks through the process of configuring SharePoint Server 2010 farm Excel Services to use Secure Store Services and an unattached service account, when presented published Excel 2010 workbooks and workbook objects that connect to external data sources. Accomplishing this involves the following tasks: 1) Configuring a data access account; 2) Configuring the farm Secure Store target application for the unattended service account; 3) Configuring the farm's Excel Services Global Settings; 4) Configuring a workbook to use the unattended service account when published and connecting to external data sources; 5) Configuring this workbook to publish only user-defined workbook objects to Excel Services; and finally 6) Testing the implementation This procedure assumes that you already have a workbook created and connected to an external data source; that you have already created a PivotTable; and that you have created a PivotChart in this workbook. It also assumes that you have configured and started Excel Services on the farm. This tutorial employs the AdventureWorks database to present Excel Services.

Preparation •

Identify an Active Directory service account that will serve as the Excel services unattended service account. This must be a domain account. It may be an existing farm services account or a new one.

For this procedure, an existing farm services account, contoso\sp_app, will be used.

•

Identify the target external data source to which Excel needs access. For this tutorial, the full version of the AdventureWorks database, version 2008R2, will be used.

21

How to setup Excel Services

•

Identify a name that you will assign to the Secure Store service application.

•

Create or identify a document library on the farm to which you want to publish a workbook.

Step 1: Configure the data access account 1) Launch SQL Server Management Studio.

2) In the console tree, expand Security.

3) Right-click on Logins, and then click New Login.

4) Add the AD account, in this case, contoso\sp_app, and then select Windows Authentication.

5) Select the User Mapping page; select the target database (in this case AdventureWorks Full); and then add the db_datareader role to this account:

6) Click OK. 7) This completes Step 1.

22

SharePoint 2010 BI Services

Step 2: Configure a new Secure Store target application 1) Connect to Central Administration using a farm administrator account (this is critical).

2) Go: Security > General Security > Configure service accounts.

3) From the Credential Management dropdown, select Service Application Pool - SharePoint Web Services Default.

4) Verify that Excel Services Application (Excel Services Application Web Service Application) is listed along with other service applications.

5) Note down the account name associated with this service. For this procedure, the service account is: contoso\sp_app.

6) Click Cancel.

7) Go: Application Management > Manage service applications. The Manage Service Applications page is displayed. Look for Secure Store Service:

8) Click on Secure Store Service. The Secure Store Service page is displayed.

9) On the Edit ribbon, click the New button. The Create New Secure Store Target Application Target Application Settings page is displayed.

10) Configure the new Secure Store Target Application. For this tutorial, the following configuration was used: a) Target Application ID: ExcelServicesUnattended. b) Display Name: ExcelServicesUnattended. c) Contact Email: Stephan.bren@contoso.com.

23

How to setup Excel Services d) Target Application Type: Group:

11) Click Next. The Create New Secure Store Target Application Add Field page is displayed:

12) Leave all settings default, and then click Next. The Create New Secure Store Target Application Specify the membership settings page is displayed.

13) Enter user accounts who for administration and the unattended account that will be mapped to this secure store application:

14) Click OK. The browser is returned to the Secure Store Service Target Application page, now displaying the new Target Application name:

24

SharePoint 2010 BI Services

15) Check the new target application item listed, and then, on the Edit ribbon, click the Set Credentials button. The Set Credentials for Secure Store Target Application (Group) dialog appears.

16) In this dialog, enter the same domain account and password that was used in Step 1:

For this tutorial, these accounts are the same, but they need not be.

17) Click OK. 18) This completes Step 2.

Step 3: Configure Excel Services Global Settings 1) From Central Administration, go: Application Management > Manage Service Applications. Look for Excel Services Application.

2) Click Excel Services Application.

3) Click Global Settings, and then scroll down to the External Data section.

4) In the Application ID field, enter the same name that was assigned to the Target Application ID, back in step 2. For this tutorial, the Target Application ID is ExcelServicesUnattended:

25

How to setup Excel Services

5) Click OK. 6) This completes Step 3.

Step 4: Configure the workbook to use the unattended service account 1) Open the Excel 2010 workbook that you want to publish and that is connected to an external data source.

2) On the Data ribbon, click the Connections button. appears:

The Workbook Connections dialog

3) Click the Properties button. The Connection Properties dialog appears:

26

SharePoint 2010 BI Services

4) Check off all of the options that you see, and then select the Definitions tab:

The Command Text and Command Type are custom. For additional information on these, see the Notes section, below. The SQL that you see there is the following:

27

How to setup Excel Services

5) Click the Authentication Settings button. The Excel Services Authentication Settings dialog appears.

6) Select None:

Selecting None here is essential. By selecting None, Excel Services uses the unattended service account identity to establish the connection to the external data source 7) Click OK. The Excel Services Authentication Settings dialog closes.

8) Click OK again. The Connection Properties dialog closes.

9) Click OK one more time. The Workbook Connections dialog closes.

10) This completes this Step 4.

Step 5: Configure Workbook objects to be published 1) Select the PivotChart that you want to be published.

2) On the Layout ribbon, click the Properties button.

28

SharePoint 2010 BI Services

The small Chart Name box appears just below the button.

3) Enter a defined name for this chart object. For this tutorial, the defined name for the chart will be MyChart:

Incidentally, this particular chart already has filters applied: the date range has between filtered to between 7/3/2005 and 7/30/2005. This filtering is not fixed and can be modified even after publication to the farm. We'll see how later.

4) Press the Enter key. The little dialog closes, and now when you select the chart, the defined name for the chart will appear in the Name box.

5) Select the File tab. The File options are displayed.

6) On the File tab, select the Save & Send option, and then select Save to SharePoint:

7) Click the Publish Options button. The Publish Options dialog is displayed.

29

How to setup Excel Services 8) On the Show tab, from the dropdown, select items in the Workbook; then check the MyChart and PivotTable2 objects listed below: Selecting specific items will impact the user's online experience when viewing the published workbook later on. Selection of the appropriate objects here is important: if you want to only present the PivotChart, then only select the chart object and nothing else. If you want to publish the chart and also enable users to interact with the chart, you must select the chart object and the its data source, which in this case is the PivotTable2 object. The reason being that published PivotCharts cannot be made directly interactive. The way to work around this is to provide access to the underlying chart data, and the filter methods associated with that data. We'll see this later once the workbook objects are published

9) Click OK. The dialog closes.

10) This completes Step 5. All configuration has now been completed and it remains to test the implementation.

Step 6: Perform a test publication of the workbook 1) Double-click where it states, Browse for a location. A Save As dialog appears:.

2) Enter the URL to the document library identified or created at the beginning of this tutorial. For this tutorial, the URL is: http://spdev12/AdventureWorks/Workbooks/

30

SharePoint 2010 BI Services

3) Press the Enter key. After a few moments, the target SharePoint document library will be shown on the Save As dialog:

4) Press the Enter key again. The dialog closes. After a few moments, a new browser instance is launched that displays the published workbook chart:

31

How to setup Excel Services

Note that it displays the same filtered view that was previously configured in the local workbook copy itself.

5) To change the filter and interact with the chart, look for the View dropdown, in the upper right corner, and then select PivotTable2 from this dropdown. The view changes to display the PivotChart's data:

32

SharePoint 2010 BI Services

6) Change the filter settings as desired, and then return to the PivotChart (in this case MyChart) object to see the affect.

7) This completes Step 6.

Optional: Display the PivotChart in a Dashboard 1) Navigate to the target site dashboard page.

2) On the Page ribbon, click the Edit Page button. The page enters edit mode:

3) Click where it states, Add a Web Part, in the web part zone to which you want to add the image.

The Browse ribbon gets the focus and displays tools for finding and selecting a web part.

4) In the Categories panel, click Business Data, and then in the Web Parts panel, select Excel Web Access:

33

How to setup Excel Services

5) Click the Add button. The Browse button is updated, and the page body is updated to display the added web part in edit mode:

6) Click the link, Click here to open the tool pane. The Excel Web Access toolpane is displayed.

7) Click the ellipsis box next to the Workbook field.

34

SharePoint 2010 BI Services

8) The Select an Asset -- Webpage Dialog appears.

You can navigate to any asset (workbook) within the site collection - not just to assets within the current site.

9) In this dialog, navigate to the location of the published workbook. For this tutorial, the workbook is located in the Workbooks document library in the AdventureWorks site:

35

How to setup Excel Services 10) Click OK. A progress indicator appears for a few moments, and then the dialog closes, with the workbook path now displayed in the Workbook field on the Excel Web Access toolpane:

11) Click OK. The toolpane closes, and the PivotChart is now displayed in the web part: 12) To expand the web part so as to eliminate the scroll bars, from the title toolbar, just above the web part, select the Edit Web Part option. The Excel Web Access toolpane opens.

13) Scroll down to the Appearance section, and expand this section.

14) Select Yes for both the Height and Width fields; 15) enter 475 for height and 16) enter 580 for width; and then 17) select None from the Chrome Type dropdown: 18) Click OK, and then on the Page ribbon, click the Stop Editing button; then select the Browse tab. The PivotTable is displayed as it would appear to any user: 19) To remove the PivotChart toolbar (File, Data, Find, etc), go back to the Toolpane, and then select None from the Type of Toolbar dropdown in the Title Bar section. You might also want to uncheck All Workbook Interactivity in the Navigation and Interactivity section. After making these changes, the PivotChart displays as a simple image: 20) This completes the Optional step.

References

36

•

The data connection uses Windows Authentication and user credentials could not be delegated.

The following connections failed to refresh: PowerPivot Data

•

This workbook contains one or more queries that refresh external data

•

AdventureWorks2008R2-Full Database Backup

•

Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010)

SharePoint 2010 BI Services

•

Refresh connected (imported) data

•

View a named item in Excel Services

•

Excel Web Access Web Part custom properties

•

Using Excel Services to share pieces and parts of Excel workbooks

•

Using charts and PivotChart reports in a workbook in the browser

•

SharePoint 2010: Creating dashboards and Charts for SharePoint Lists using PerformancePoint and Excel Services from Start to Finish

•

Excel Chart Updating with Pivot table

37

How to setup Excel Services

38

SharePoint 2010 BI Services

4 How to Setup Word Automation Services Overview This tutorial walks through the process of enabling document conversion from Word to HTML using Word Automation Services. The procedure developed for this tutorial was performed on the following farm topology: two-tier SharePoint 2010 farm, hosted on Windows Server 2008 R2 and employing SQL Server 2008 R2 for the database tier. Domain controller, DNS, SQL Server instances collocated on same server. This tutorial employs NTLM authentication. All servers hosted within VMware Workstation 7.X. All references used to guide development of this tutorial are provided in the References section.

Step 1: Configure 1) Launch Central Administration under an account that is a member of the Farm Administrators SharePoint group.

2) Go: Central Administration > Service Applications > Manage services on server. The browser is navigated to the Services on Server page 3) Look for the following two services: •

Document Conversions Launcher Service.

•

Document Conversions Load Balancer Service.

If you used the Farm Configuration Wizard, after initial installation, these will most likely already be created. However, by default, they will be stopped.

4) Click the Document Conversions Load Balancer Service first. The browser is navigated to the Load Balancer Service Settings page:

39

How to setup Word Automation Services

5) Configure as desired, and then click OK. The browser is navigated back to the Services on Server page. If this service is not started, start it. The Load Balancer service must be configured and started before configuring and starting the Launcher service, as the Launcher service depends upon the Load Balancer settings.

6) Click the Document Conversions Launcher Service second. The browser is navigated to the Launcher Service Settings page:

7) Configure as desired. Note that the available servers listed in the Load Balancer server dropdown depends on properly configuring and starting the Load Balancer Service.

8) Click OK. The browser is navigated back to the Services on Server page.

9) Start the Document Conversions Launcher Service. Both services will now appear as stated in the Services on Server page:

10) This completes Step 1.

Step 2: Enable 1) Go: Central Administration > General Application Settings > External Service Connections > Configure document conversions. The browser is navigated to the Configure Document Conversions page:

40

SharePoint 2010 BI Services

2) In the Web Application section, select the web application from the dropdown list.

3) In the Enable Document Conversions section, select Yes. 4) In the Load Balancer Server section, select the Load Balance Server.

5) In the Conversion Schedule section, select the desired schedule. At this point, the page looks like this:

6) In the Converter Settings section, click Customize From Word Document to Web Page (docx to html). The browser is navigated to the Document Converter Settings page:

41

How to setup Word Automation Services 7) In the Converter Settings section, ensure that Make this document converter... has been checked, and edit other settings as desired:

8) Click OK. The browser is navigated back to the Configure Document Conversions page.

9) Exit Central Administration.

10) This completes Step 2

Step 3: Activate 1) Connect to the web application identified in step 2.2, above.

2) Go: Site Actions > Site Settings > Site Collection Administration > Site collection features. The browser is navigated to the Features page.

3) Scroll down to near the bottom, where you'll find the SharePoint Server Publishing Infrastructure feature:

By default, this feature is not activated.

4) Click the Activate button next to this feature. After a few moments, the button will change to state Deactivate and a blue status message will appear next to it, indicating Activated.

5) Now go: Site Actions > Site Settings > Site Actions > Manage site features. The browser is navigated to the site settings Features page.

6) Scroll down until you find the SharePoint Server Publishing feature:

42

SharePoint 2010 BI Services

7) Click the Activate button next to this feature. After a few moments, the button will change to state Deactivate and a blue status message will appear next to it, indicating Activated.

8) This completes Step 3.

Step 4: Test 1) Upload a Word document to a document library in some web application site. It doesn't matter which, as Word Automation services are set for the Web Application level and not at the site collection level. For example, the Word document here was loaded to the default Shared Documents folder:

2) Open the file's context menu, point to Convert Document, and then point to From Word Document to Web Page:

3) Click this menu option. The browser is then navigated to the Create Page From Document page.

4) In the Page Title and Description section, enter a title and a description. As the title is entered, it will be used to create an initial URL Name. This name can be changed independently of the Title.

For this example, an underscore was used in place of a blank to avoid HTML encoding:

43

How to setup Word Automation Services

5) Click Create. A busy indicator will appear momentarily. After a few moments, the browser is navigated to the new page, displaying the word document in HTML format:

6) This page was created by default in the Pages library, which is created when you activate the Publishing feature:

7) This completes testing.

44

SharePoint 2010 BI Services

Optional: Remove the new page link from Global and Current Navigation 1) To remove the newly converted Word document's link from Global and Current navigation, go: Site Actions > Site Settings > Look and Feel > Navigation. The browser is navigated to the Navigation Settings page.

2) In the Global Navigation and Current Navigation sections, uncheck the Show pages checkbox setting:

3) Click OK. The browser is navigated back to the Site Settings page, with the new page links no longer appearing in the navigation bar.

4) This completes this optional step

Summary This tutorial has presented the steps for enabling document conversion from Word to HTML in SharePoint 2010. For additional detail on the topics discussed in this tutorial, see the references below.

References •

Build a SharePoint Server 2010 Two-Tier Dev Environment on Windows 2008 R2

•

Configure Document Conversions Load Balancer and Launcher Services (SharePoint Server 2010)

•

Enable document converters for a Web application

•

Enable the Document Conversion Service

•

Converting Word Documents to PDF using SharePoint Server 2010 and Word Services

•

Word Automation Services Troubleshooting (SharePoint Server 2010)

45

How to setup Word Automation Services

46

SharePoint 2010 BI Services

5 How to Setup Access Services Overview This tutorial walks through the process of implementing SharePoint 2010 Access Services, step-by-step.

Implementing Access services requires that the administrator work with several different technologies, including Silverlight, Session State, SharePoint Services and Service Applications, and Microsoft Office products. Implementing Access services will involve several key steps: 1) Install Silverlight: Microsoft Silverlight us used to generate many of the Access Online user interfaces, such as New and Edit forms.

2) Enable ASP.NET Session State Service: SharePoint Server 2010 uses ASP.NET session state to persist user data between successive requests. It is needed when working with online implementations of Microsoft Access, Excel and other office tools.

3) Configure Access services: configure service applications.

Step 1: Install Silverlight on clients •

Install Microsoft Silverlight on all client machines that will be connecting to the online Access database application.

Step 2: Enable ASP.NET Session State Service 1) In Central Administration, go: Application Management > Service Applications > Manage service applications.

2) Verify that the SharePoint Server ASP.NET Session State Service is not listed.

3) On the SharePoint Server 2010 host machine, open the SharePoint 2010 Management Shell.

4) Execute the following command: Enable-SPSessionStateService –DefaultProvision

47

How to setup Access Services

5) In Windows Explorer, navigate to the Web.Config file of the web application that will be used to host Access webs: C:\Inetpub\wwwroot\wss\VirtualDirectories\

6) Edit the Web.Config file by setting enableSessionState to True.

7) Save the file. Changes to the web application Web.Config file automatically trigger a restart of the application.

8) To manually restart IIS, use iisreset:

48

SharePoint 2010 BI Services

9) In Central Administration, go: Application Management > Service Applications > Manage service applications.

10) Verify that the SharePoint Server ASP.NET Session State Service is now listed:

Failure to enable ASP.NET Session State Service will not stop Microsoft Access database applications from being successfully published to SharePoint Server 2010. However, when you attempt to actually connect to the web-enabled version, you may experience the following error:

If this error does occur, check to make sure that the ASP.NET Session State Service has been started.

11) This completes step 1.

49

How to setup Access Services

Step 3: Configure Access Services 1) Install Reporting Services Add-In: •

Logon to the SharePoint Server 2010 host.

•

Install the SQL Microsoft SQL Server 2008 R2 Reporting Services Add-in for SharePoint 2010

2) Provision Service Account: •

Use the sp_app domain account.

3) Register service account as a managed account: a) In Central Administration, go: Security > General Security > Configure managed accounts. b) Add domain service account to list of managed accounts

4) Start Access Services: a) In Central Administration, go: Application Management > Manage services on server. b) Look for the Access Database Service in the list

50

SharePoint 2010 BI Services c) Click Start if the service is not already started.

In a default small farm two-tier installation, this service will already have been started.

5) Create Access Service Application: a) In Central Administration, go: Application Management > Manage Service Applications. b) Look for Access Services in the list:

c) Create a new Access Service Application if one is not already created.

In a default small farm two-tier installation, this service application will already have been created.

Optional: Publish the Northwind Traders sample Access application 1) Download the Northwind sample template, Northwind.accdt, for Office 2010.

2) Double-click the template to create the runnable database application, Database.accdb.

3) Change the filename to Northwind.accdb, and then save. The application will open and prompt you to login:

51

How to setup Access Services 4) On the ribbon, click the File tab, select Save and Publish, and then select Publish to Access Services:

5) Click the Run Compatibility Checker, and then view the Compatibility Checker results:

6) Enter the Server URL and enter a custom name for the new site that will be created for this Access application.

7) Click Publish to Access Services, and then logon when requested.

8) After a minute or two, a success prompt appears.

9) Click the URL to view the online application. After a minute, a browser will open to the site.

52

SharePoint 2010 BI Services

10) This completes the test.

Optional: Remove a published Access database application You can delete this using three different methods.

•

Method 1: From the Access database view: 1) At the top of the online Access database view, click Options, and then click Settings.

2) In the Settings group of links, click Delete this Site.

•

Method 2: From the site's Site Settings: 1) In the browser address bar, modify the URL by appending, "_layouts/settings.aspx" to the URL: ...[server]/[Access database site]/_layouts/settings.aspx 2) In the Site Actions group, click Delete this site.

•

Method 3: From the root site:

53

How to setup Access Services 1) Navigate to the root site.

2) From Site Actions, click Site Settings.

3) In the Site Administration group, click Sites and Workspaces.

4) In the list of Sites, click the "X" associated with the site you want to delete, and the click Delete on the ribbon.

References

54

•

Microsoft MSDN Blogs: Using Session State in SharePoint 2010

•

MSDN Developer Network: Session State Management in SharePoint

•

Microsoft TechNet: Manage the State Service (SharePoint Server 2010)

•

MSDN Developer Network: Working with Web.config Files

•

MSDN Developer Network: Editing ASP.NET Configuration Files

•

MSDN Developer network: Managing Changes to Configuration Settings

•

TechNet: Set up and configure Access Services (SharePoint Server 2010)

•

Microsoft Download Center: Microsoft® SQL Server® 2008 R2 Reporting Services Add-in for Microsoft SharePoint® Technologies 2010

•

Microsoft Office 2010: Build and publish an Access database to SharePoint

•

Microsoft Office: Sample Access databases that you can download and adapt

•

Microsoft Office: Northwind sales web database

•

Microsoft Office: Desktop sales pipeline

•

Institute for Informatics Georg-August-Universität Gottingen: The MONDIAL Database

•

What About SharePoint: Set up and configure Access Services (SharePoint Server 2010)

•

Nick Patel: Access Services in SharePoint 2010 - All You Need to Know

•

Microsoft Developer Network: Creating Web Databases with Access 2010 and Access Services

SharePoint 2010 BI Services

6 Troubleshooting An error occurred during the "Generate Key" process You are trying to configure a new Secure Store service target application on a SharePoint 2010 farm using Central Administration. You are logged in as the domain administrator or under an account that has domain administration privileges or is a member of the farm administration group for your SharePoint 2010 instance. This is a default installation, where all services and service applications were created using the wizard. You are attempting to create a new pass phrase as part of configuring a new secure store service target application. After entering the new pass phrase, and clicking OK, you see the following error message:

To resolve this problem, perform the following troubleshooting steps: 1) Check whether the account you were currently logged in as (domain administrator, etc) was a member of the Farm administrators group. •

If it isn’t, add it, and then again attempt to configure a new target application.

2) Log in under the farm administration account (in this case Contoso\sp_farm), and then try to configure a new target application. Log out from this account and log back in under the domain administrator account (Contoso\administrator), and navigate to the Secure Store Service application page.

55

Troubleshooting

•

This error message will confirm that the problem involves permissions.

3) To probe this further, check the Secure Store Service database role of the account you were currently logged in as (e.g., domain administrator) against that of the farm administration account.

4) Check the Secure Store Service database properties: •

Note that the farm administrator account is the owner of the database files.

For additional information on this error, see the following: •

Secure Store Service: Cannot generate a Secure Store Service key

•

Secure Store Service Error

•

Secure Store service cmdlets (SharePoint Server 2010)

Cannot logon with credentials obtained from Secure Store Provider You are working in SharePoint Designer 2010 and connected to the farm trying to add a new SQL Server connection in order to configure a new BCS service application. At the SQL Server Connection dialog:

you enter the appropriate information, select Connect with Impersonated Windows Identity, enter the Secure Store Service target application ID, and click OK. You then experience the following error:

56

SharePoint 2010 BI Services

This experience is repeatable. You then attempt to add the connection by selecting Connect with User's Identity, and click OK. You then experience the following error:

This result is repeatable The second error message indicates the problem and its resolution: use an instance of SharePoint Designer 2010 that is installed to a machine within the same domain as the SharePoint Server 2010 farm.

For additional information, see: ERROR - Cannot connect to the SQL Server database defined in data source connection AdventureWorks.

Access Denied by Business Data Connectivity You have successfully created both a new Secure Store Service application and a new Business Data Connectivity Service application. The new BCS application connects to an external line of business database hosted on SQL Server and located within the same Active Directory domain as the SharePoint Server 2010 farm. You successfully created an application page for a list within this LOB database. You then navigate to this application page and experience the following error:

This likely involves a permissions issue. If this is a new BCS application, verify that you have set the appropriate object permissions on the new BCS application. Use the following checklist to troubleshoot:

57

Troubleshooting In SharePoint 2010 Central Administration: Verify that you and other users and security groups have been granted appropriate BCS object permissions: 1) Go: Application Management > Manage Service Applications > [your business data service application]:

2) Next, select the appropriate list item (check it), and then click the Set Object Permissions button on the Edit ribbon. You can also hover the cursor over the name of the list item to expose the dropdown, and then select Set Permissions:

3) In the Set Object Permissions dialog, verify that all necessary accounts and groups are listed here and that they have been assigned all of the permissions that you see in the box below.

In SQL Server Management Studio: Verify that the impersonation account has been added to the target database logins and that it has been granted the appropriate database roles: 1) Open SQL Server Management Studio.

2) Expand [server name] > Security > Logins, and then verify that the impersonation account is listed:

58

SharePoint 2010 BI Services

3) Double-click the impersonation account to view its properties.

4) Select the User Mapping page. Verify that the impersonation account has been mapped to the appropriate database and assigned the appropriate roles:

In Active Directory Users and Computers: Verify that users and security groups have been created and that users needing access to the LOB database have been made members of the appropriate security group: 1) On the Domain Controller, go: Start > Administrative Tasks > Active Directory Users and Computers.

59

Troubleshooting 2) Expand the console tree: Active Directory Users and Computers > [yourdomain] > Users.

3) Verify that the impersonation account is listed:

4) Verify that the security group to be used for LOB database access is listed:

5) Double-click on any account experiencing problems trying to view LOB data in SharePoint.

6) Select the Member Of tab and verify that the user account has been made a member of the appropriate security group dedicated to this LOB database access

60

SharePoint 2010 BI Services To learn more about this issue, see these references: •

Access denied by Business Data Connectivity

•

"Access denied by Business Data Connectivity" error message when you discover, delete, or save a new LOB system in SharePoint Designer 2010

•

Access denied by Business Data Connectivity when trying to add myself to the BDC Metadata Store

•

SharePoint 2010: Access Denied By Business Data Connectivity Error in Using External Content Type List

•

Access denied by business data connectivity error

•

SharePoint BCS SSS Help Needed

•

Access denied by Business Data Connectivity – Solution

Error: Specified method is not supported You attempted to delete a Microsoft Access database application from a SharePoint Server 2010 site, but experienced the following error:

This error may occur after installing a service pack or cumulative update.

To resolve this, run the SharePoint 2010 Products Configuration Wizard.

To learn more about this issue, see these references: •

SharePoint TechCenter Blog: Please Help: Correlation ID - Specified method is not supported

•

JonTheNerd: SharePoint 2010: Unable to delete site/web after SP1

61

Troubleshooting

Convert Document option does not appear on the Word document's context menu 1) Go: Central Administration > General Application Settings > External Service Connections > Configure document conversions. The Configure Document Conversion page is displayed.

2) Verify the following settings: a) Enable Document Conversions: Yes. b) Load Balancer Server: [set to a valid load server and not to None]

62