How to: Create Data Providers for PerformancePoint Services Filters
In PerformancePoint Services in Microsoft SharePoint Server 2010, data providers retrieve data from a filter's underlying data source and define how to use the data. Most importantly, data providers specify the data values to expose in the filter control and the data that can be used as the filter's beginpoint. A data provider also stores the value that a user selects from the filter control, which is then sent to filter consumers. Data providers use two DataTable objects to organize and store data. For more information, see PerformancePoint Services Filters.
Applies to: SharePoint Server 2010
The procedures and examples in this topic are based on the SampleFilterDataProvider class from the custom objects sample. The editor is a thin Web application that enables users to modify the report's name and description. The complete code for the class is provided in the "Example" section in this topic.
Note
We recommend that you use the sample data provider as a template. The sample shows how to call objects in the PerformancePoint Services API and demonstrates best practices for PerformancePoint Services development.
You create a data provider by performing two basic procedures, as follows:
Creating and configuring the provider class
Defining the provider functionality
To create a custom provider, start by creating the provider class.
To create and configure the provider class
Install PerformancePoint Services, or copy the DLLs that your extension uses (listed in step 3) to your computer. For more information, see PerformancePoint Services DLLs Used in Development Scenarios.
In Visual Studio, create a C# class library. If you have already created a class library for your extension, add a new C# class.
Add the following PerformancePoint Services DLLs as assembly references to the project:
Microsoft.PerformancePoint.Scorecards.Client.dll
Microsoft.PerformancePoint.Scorecards.Server.dll
Depending on your extension's functionality, other project references may be required.
In your provider class, add using directives for the following PerformancePoint Services namespaces:
Depending on your extension's functionality, other using directives may be required.
Inherit from the CustomParameterDataProvider base class.
After you create and configure the provider class, you must define your provider’s functionality.
To define the provider functionality
Set the string identifier for the data provider name. This must match the key that you add to the CustomParameterDataProviders section of the web.config file when you register the extension. For more information, see How to: Manually Register PerformancePoint Services Extensions.
Override the GetId() method to return the identifier for your data provider.
Override the GetDisplayDataInternal method to define a DataTable object to store the data values from the underlying data source. The filter uses this method to populate the filter selection control. The display data table must contain the following column names:
Key The unique identifier for the record. This value cannot be null. For performance and security purposes, controls emit only a key; they do not emit values from the other columns.
Display The value that appears in the filter control.
ParentKey This value is used to arrange hierarchical data in a tree control.
IsDefault This value is used for filter persistence.
Tip
You can add more columns to extend the filter's functionality.
GetDisplayDataInternal calls the DataSourceRegistry.GetDataSource(DataSource) method to verify the data source type by name, as follows:
It references a custom data source type by using the SubTypeId property of the data source, which is the same value as the subType attribute that is registered in the PerformancePoint Services web.config file for the data source extension.
It references a native data source by using the SourceName property, which returns a field from the DataSourceNames class.
Override the GetMessageData method to store the user's selection from the filter control. The filter uses this method when it sends the user's selections to consumers.
Next step: After you create a data provider and a filter editor (including its user interface, if required), deploy the extension as described in How to: Manually Register PerformancePoint Services Extensions. For instructions about how to install and configure the sample filter extension, see the "Installing the Sample Report, Filter, and Data Source Objects" section in PerformancePoint Services Code Sample: Custom Report, Filter, and Tabular Data Source Objects.
Example
The following code example shows how a data provider retrieves values from a Web service or an Excel worksheet and returns DataTable objects for the filter's display data and message data.
Note
Before you can compile this code example, you must configure your development environment as described in To create and configure the provider class.
using System.Data;
using Microsoft.PerformancePoint.Scorecards;
using Microsoft.PerformancePoint.Scorecards.Server.Extensions;
namespace Microsoft.PerformancePoint.SDK.Samples.SampleFilter
{
// Represents the sample filter's data provider.
public class SampleFilterDataProvider : CustomParameterDataProvider
{
// This value must match the key that you register for this extension
// in the CustomParameterDataProviders section in the web.config file.
private const string dataProviderName = "SampleFilterDataProvider";
// Returns a table of all possible values (rows) for the
// filter’s beginpoints. The filter's BeginPoint property returns
// one ParameterDefinition object.
protected override DataTable GetDisplayDataInternal(ParameterDefinition parameterDefinition, RepositoryLocation parameterSourceLocation, object custom)
{
DataTable retrievedData = null;
// Get the data source.
DataSource parameterDataSource = SafeGetDataSource(parameterSourceLocation);
if (null != parameterDataSource)
{
// Verify that the data source is the sample data source
// or an Excel workbook, which are the types that the
// sample supports.
// If you modify these types of data source, you must make
// the corresponding change in the filter's editor.
if (parameterDataSource.SourceName == "WSTabularDataSource" || parameterDataSource.SourceName == DataSourceNames.ExcelWorkbook)
{
IDataSourceProvider parameterDataSourceProvider =
DataSourceRegistry.GetDataSource(parameterDataSource);
if (null != parameterDataSourceProvider)
{
var dataSourceMetadata = parameterDataSourceProvider as IDataSourceMetadata;
if (null != dataSourceMetadata)
{
// Get the data and store it in the retrievedDataSet
// variable. The -1 parameter returns all records
// from the data source.
DataSet retrievedDataSet = dataSourceMetadata.GetPreviewDataSet(-1);
// Verify that the dataset contains data.
if (retrievedDataSet != null &&
retrievedDataSet.Tables != null &&
retrievedDataSet.Tables.Count > 0 &&
retrievedDataSet.Tables[0] != null &&
retrievedDataSet.Tables[0].Columns != null &&
retrievedDataSet.Tables[0].Columns.Count > 0 &&
retrievedDataSet.Tables[0].Rows != null &&
retrievedDataSet.Tables[0].Rows.Count > 0 &&
retrievedDataSet.Tables[0].Columns.Contains(parameterDefinition.KeyColumn))
{
retrievedData = retrievedDataSet.Tables[0];
}
}
}
}
if (null != retrievedData)
{
// Name the display data table.
retrievedData.TableName = "ParamData";
// Verify that the table has the correct structure.
EnsureDataColumns(retrievedData, parameterDefinition);
bool firstRowSeen = false;
foreach (DataRow row in retrievedData.Rows)
{
// Set the ParentKeyColumn to null because the data
// does not have a hierarchical structure.
row[parameterDefinition.ParentKeyColumn] = null;
// Set the IsDefaultColumn column in the first row to true.
row[parameterDefinition.IsDefaultColumn] = !firstRowSeen;
if (!firstRowSeen)
{
firstRowSeen = true;
}
}
// Set the column visibility.
SetColumnVisibility(retrievedData);
}
}
return retrievedData;
}
// Adds the ShowColumn extended property to a column in the display data table
// and sets it to true. This exposes the column in Dashboard Designer as
// a source value for the beginpoint.
private static void SetColumnVisibility(DataTable displayData)
{
for (int i = 0; i < displayData.Columns.Count; i++)
{
if (!displayData.Columns[i].ExtendedProperties.Contains("ShowColumn"))
{
displayData.Columns[i].ExtendedProperties.Add("ShowColumn", true);
}
}
}
// Verify that all required columns are in the data table.
// The data table returned by this method is expected to contain a
// Key, ParentKey, IsDefault, Display, and an arbitrary number of
// Value columns.
// The specific column names (except for Value columns) are defined
// in the filter's ParameterDefinition object, which is referenced by
// the filter's BeginPoint property.
private static void EnsureDataColumns(DataTable dataTable, ParameterDefinition parameterDefinition)
{
if (!string.IsNullOrEmpty(parameterDefinition.KeyColumn) && !dataTable.Columns.Contains(parameterDefinition.KeyColumn))
{
dataTable.Columns.Add(parameterDefinition.KeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.DisplayColumn) && !dataTable.Columns.Contains(parameterDefinition.DisplayColumn))
{
dataTable.Columns.Add(parameterDefinition.DisplayColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.ParentKeyColumn) && !dataTable.Columns.Contains(parameterDefinition.ParentKeyColumn))
{
dataTable.Columns.Add(parameterDefinition.ParentKeyColumn);
}
if (!string.IsNullOrEmpty(parameterDefinition.IsDefaultColumn) && !dataTable.Columns.Contains(parameterDefinition.IsDefaultColumn))
{
dataTable.Columns.Add(parameterDefinition.IsDefaultColumn, typeof(bool));
}
}
// Returns the unique string identifier of the data provider.
// This value must match the key that you register for this extension
// in the CustomParameterDataProviders section in the web.config file.
public override string GetId()
{
return dataProviderName;
}
// Returns a table of rows that match the keys in the passed
// ParameterMessage object.
// This method is used by controls that accept parameters, such as
// scorecard and reports. It can also apply a Post Formula.
public override DataTable GetMessageData(RepositoryLocation providerLocation, ParameterMessage parameterMessage, RepositoryLocation parameterSourceLocation, ParameterMapping parameterMapping, object custom)
{
DataTable msgTable = null;
// The ParameterMapping object contains information about
// linked dashboard items.
// The CustomData object is optionally used to store information
// that is not stored in other properties.
DataTable displayTable = GetDisplayDataInternal(parameterMessage, parameterSourceLocation, custom);
if (null != displayTable)
{
msgTable = displayTable.Clone();
for (int i = 0;i < parameterMessage.Values.Rows.Count; i++)
{
for (int j = 0;j < displayTable.Rows.Count; j++)
{
if (!parameterMessage.Values.Rows[i][parameterMessage.KeyColumn].Equals(displayTable.Rows[j][parameterMessage.KeyColumn].ToString()))
continue;
msgTable.ImportRow(displayTable.Rows[j]);
break;
}
}
}
return msgTable;
}
}
}
Compiling the Code
Before you can compile this code example, you must configure your development environment as described in To create and configure the provider class.
Security
You must sign your DLL with a strong name. In addition, ensure that all assemblies referenced by your DLL have strong names. For information about how to sign an assembly with a strong name and how to create a public/private key pair, see How to: Create a Public/Private Key Pair.
See Also
Tasks
How to: Create Editors for PerformancePoint Services Filters
Concepts
PerformancePoint Services Filters
Other Resources
Create Custom Objects for PerformancePoint Services
Code Samples for PerformancePoint Services in SharePoint Server 2010