Connect cecepoint services to Oracle Data sources using business connectivity services

ArticleDirectory

• Overview
• Topology and security
• Include Oracle Data in the performancepoint dashboard through external lists (step-by-step)

Updated: February 1, August 23, 2011

This article provides links to how to use Microsoft Business connectivity services to connect performancepoint services in Microsoft SharePoint Server 2010 to an ORACLE data source. This document assumes that you have configured service applications for performancepoint services and business connectivity services on the server farm that uses Microsoft SharePoint Server 2010Program. The following is a summary of the operations you have performed in this solution.

1. Create model files and external content types to describe how Sharepoint Server connects to Oracle and retrieves inventory data.
2. Publish the list data to the Sharepoint Server.
3. Configure security.
4. Use performancepoint services to create websites, dashboards, and KPIs.
5. Create a Sharepoint list data connection for performancepoint services.

Overview

You may need to include ORACLE data to support business intelligence functions such as reports, monitoring, or analysis when performing operational procedures (such as procurement, supply, distribution, or delivery. Performancepoint services can aggregate content from different sources to view reports, scorecards, or KPIs. Business connectivity services connect to data directly or through soap or Windows Communication Foundation (WCF) web services to include external data in a dashboard, such as customers, products, employees, or sales.

For example, purchasers working for household office supplies retailers may have to determine the materials to be purchased, the quantity, and the stores to which the materials are sent once a week. In this example, you can perform analysis in the list organized by store and item. Collect data from different origins. It includes columns and indicators that highlight past material sales. It also has a column that specifies the primary data (such as the store number, store address, material description, or supplier address. Some columns can be collected from the SQL Server Analysis Services Multidimensional Dataset, while others are from the lob database that stores information about retailer inventory. The buyer will provide the quantity to be purchased in the Editable column. After that, you can publish the dashboard to the SharePoint website. The procurement process will continue there, depending on the data-driven comparison.

To use ORACLE data as a data source in performancepoint services, you must create an external content type. The external content type is similar to the SharePoint content type. It allows you to display and interact with data in the external list, Web parts, Microsoft Outlook 2010, Microsoft SharePoint Workspace 2010, and Microsoft Word 2010 clients. After describing the External System in the external content type, you can create a Sharepoint list data connection in the peformancepoint dashboard to use it as a data source. Examples of external systems that business connectivity services can connect to include SQL Server databases, SAP applications using duet enterprise for Microsoft SharePoint and SAP, Siebel CRM data, Web 2.0 services, custom applications, and sharepoint Server-based websites. Although this example relates to how to connect to Oracle data, business connectivity services allows you to connect to multiple external systems by using the following connector at runtime, as shown in the chart in the "topology and security" section.

• Preset database, WCF Service, or soap Web Service Connector
• Connector or custom connector created using. NET Framework
• Third-party connectors, such as those provided by BizTalk Server or other products of Microsoft partners

Microsoft SharePoint designer 2010 cannot be used to create external content types when other databases (such as Oracle, ole db, and ODBC) are used. Use one of the following methods.

• Create a BDC model and use Visual Studio 2010 or your favorite XML editor to describe the external content type. For more information, see create a BDC model.
• Create a web service or. net connection assembly to provide interfaces for external data displayed in the database. Use SharePoint designer 2010 to model the new service.
• Mirror the Oracle architecture table to SQL Server, and then use SharePoint designer 2010 to model SQL Server.

In this solution, you create a business connectivity services model and use the XML editor in Visual Studio 2010, which provides intelliisense.

Topology and security

The following is a topology that shows different regions for which security must be configured.

2. Your oracle connection may require explicit user ID and password parameters in the connection string.
4. In this example, the authentication mode specifies that the user's creden。 map to the account stored in the secure Store service. The credential is passed to the external system. The authentication mode provides business connectivity services with information about how to process incoming authentication.
6. The Credential set of performancepoint services and business connectivity services is stored in a dedicated secure store Service database.
8. You must run the secure Store service application and proxy to store the password of the unattended service account of the performancepoint services service application. An unattended service account is a shared domain account used to access the performancepoint services data source. Performancepoint Services uses Secure Store service to store passwords of unattended service accounts. Before using an unattended service account, make sure that the secure Store service is running. For more information, see Plan performancepoint services security (Sharepoint Server 2010 ).
10. Define a data source (such as the Sharepoint list) in the performancepoint dashboard designer and store it in a trusted Data Connection Library on SharePoint Server 2010. Microsoft SharePoint Server 2010 security model is used to protect objects stored in lists and document libraries. On top of this model, performancepoint services adds product features to the basic Sharepoint Server 2010 framework to ensure the security of data sources and dashboard content and prevent unauthorized access. The four server roles are "Administrator", "Senior reader", "data source administrator", and "creator ". In addition, the dashboard designer also sets two roles at the single project level: "Editor" and "Reader ".
12. Performancepoint Services uses the Sharepoint Server Security Model to control users' access to various functions and tasks.

Include Oracle Data in the performancepoint dashboard through external lists (step-by-step)

Procedure
For more information, see

View information about how to create external content types.

How to: create external content types (http://go.microsoft.com/fwlink? Linkid = 202504 & clcid = 0x804)

Follow the process described in "create BDC model" to create a model from scratch.

The BDC model of the external system is basically an automatically generated XML file. This file is generated when SharePoint designer 2010 is used to create an external content type for the SQL Server database, WCF or soap web service, or an existing. Net Assembly connector. To connect to Oracle, you must create your own BDC model.

Walkthrough: creating your own BDC model (http://go.microsoft.com/fwlink? Linkid = 202859 & clcid = 0x804) and related videos: Use Visual Studio 2010 to create a business data connection model for Sharepoint 2010? (Http://go.microsoft.com/fwlink? Linkid = 202860 & clcid = 0x804)

Modify the BDC model used to connect to the Oracle database. Make sure that the application definition is set using Oracle creden。 in secure Store service. Remember the syntax differences between Oracle and SQL Server parameters. For example, in Oracle, use the colon (:) instead of the @ symbol.

How to: use business connectivity services to connect to Oracle databases (http://go.microsoft.com/fwlink? Linkid = 202861 & clcid = 0x804)

To enable business connectivity services to retrieve the correct creden。 at runtime to connect to Oracle, use secure Store service to store creden。 in Sharepoint Server 2010.

For more information about business connectivity services security, see business connectivity services security operations (Sharepoint Server 2010 ).

See how to: Use secure Store service to connect to an external system (http://go.microsoft.com/fwlink? Linkid = 202862 & clcid = 0x804.

After you and other dashboard authors create one or more views to be displayed in the dashboard, you can create a dashboard page that contains these dashboard items. After setting the dashboard page, you can add projects, such as reports, score cards, and filters, to these dashboard pages.

Use the dashboard designer to create a dashboard page

For more information, see understanding the performancepoint report type (http://go.microsoft.com/fwlink? Linkid = 185512 & clcid = 0x804) and learn about performancepoint scorecard (http://go.microsoft.com/fwlink? Linkid = 185514 & clcid = 0x804 ).

Use the dashboard designer to create reports and scorecards (http://go.microsoft.com/fwlink? Linkid = 202863 & clcid = 0x804)

After creating a dashboard project (such as a page, report view, and filter), the next step is to combine your dashboard page.

Use the dashboard designer to add a dashboard project to the page

You can include the data in the Sharepoint list as the KPI data source in the report, or as a Web Report in the custom report. Create a Sharepoint list data connection report type in the dashboard designer. Data must be edited in the Sharepoint list.

Note:

Only the default list view can be used as a data source or as a fact or dimension of a KPI or filter. This view is not considered in aggregation. For example, if a column is hidden in the default view, the column cannot appear in the list preview of the dashboard designer.

Create a Sharepoint list data connection (performancepoint Services)

Other security steps include the following.

• The Oracle client must be installed on all computers connected to Oracle data on the server farm.
• You must configure the TNS network service to connect from the Sharepoint Server to Oracle.
  This is required for the external list, Web parts, and configuration file pages on the server. When the service runs on the application server, it is best to install the Oracle client on the application server.