Use a custom MDX query for a PerformancePoint KPI

  • Article

 

Applies to: SharePoint Server 2010 Enterprise

After you have created a PerformancePoint key performance indicator (KPI) that uses Microsoft SQL Server Analysis Services (SSAS) data, you can use PerformancePoint Dashboard Designer to extend that KPI by using a custom MultiDimensional Expressions (MDX) query. For example, suppose that you want to configure a KPI that shows performance for a particular set of dimension members. One way to do this is by adding an MDX query to the KPI.

You can add an MDX query to a KPI’s Actual value, Target value, or both, provided these values use a SQL Server Analysis Services data source.

Adding custom MDX queries to PerformancePoint KPIs

When you extend a KPI by adding custom MDX queries, you begin by opening an existing KPI for editing. Then, you additionally configure that KPI by specifying an MDX query for a value, such as an Actual or Target value that is used in the KPI.

Important

Before you perform the procedures in this article, make sure that you have an existing KPI to configure. The KPI must use data that is stored in Analysis Services To create a KPI, see Create and configure a KPI by using Dashboard Designer.

Open a KPI for editing

To open a KPI for editing

  1. In Dashboard Designer, locate the Workspace Browser on the left side of the window, and then click PerformancePoint Content. The center pane displays two tabs: SharePoint and Workspace.

  2. Using the SharePoint and Workspace tabs, locate a KPI that you want to configure.

  3. Double-click the KPI that you want to configure to open it for editing.

Add an MDX query to a KPI’s Actual or Target value

When you add custom MDX queries to a KPI’s value, you use the Dimensional Data Source Mapping dialog box to select and configure a value, such as an Actual or Target value. You configure each Actual or Target value separately.

To add an MDX query to a KPI

  1. In the center pane, on the Editor tab, locate the Actual or Target value for which you want to add a custom MDX query.

  2. In the Data Mappings column, click the hypertext. A Data Source Mapping dialog box opens. You add a custom MDX query to a KPI’s value by using the Dimensional Data Source Mapping dialog box. Depending on which Data Source Mapping dialog box opens, use one of the procedures shown in the following table.

    Data Source Mapping dialog box type Procedure

    Fixed Values Data Source Mapping

    If the Fixed Values Data Source Mapping dialog box is open, you cannot add a custom MDX query to that value as it is configured. You can either select a different value, or you can specify a different (Analysis Services) data source to use for that value.

    To use a different data source, take the following steps:

    1. Click Change Source to open the Select a Data Source dialog box.

    2. Use the SharePoint Site and Workspace tabs to locate the Analysis Services data source that you want to use, and then click OK.

    3. Proceed to Step 3.

    Dimensional Data Source Mapping

    If the Dimensional Data Source Mapping dialog box is open, proceed to Step 3.

    Calculated Metrics Data Source Mapping

    If the Calculated Metrics Data Source Mapping dialog box is open, then the KPI’s Actual or Target value is based on a formula that consists of one or more other values. You can define a custom MDX query for each value that is listed in the Calculated Metrics Data Source Mapping dialog box, provided it uses an Analysis Services data source.

    1. In the Calculated Metrics Data Source Mapping dialog box, click the hypertext in the Source column for a value.

    2. Notice which kind of Data Source Mapping dialog box opens

      • If the Dimensional Data Source Mapping dialog box is open, proceed to Step 3.

      • If the Fixed Values Data Source Mapping dialog box opens, click Cancel and select a different value.

        Or, specify a different (Analysis Services) data source to use for that value. To use a different data source, take the following steps:

        1. Click Change Source to open the Select a Data Source dialog box.

        2. Use the SharePoint Site and Workspace tabs to locate the Analysis Services data source that you want to use, and then click OK.

        3. Proceed to Step 3.

    Tip

    For more information about calculated metrics, see Add calculated metrics to a PerformancePoint KPI.

  3. In the Dimensional Data Source Mapping dialog box, in the Aggregate members by section, select the Use MDX tuple formula check box.

  4. In the Use MDX tuple formula box, type the MDX query that you want to use for the KPI.

    Tip

    To view examples of MDX queries that you can use, see Extend PerformancePoint dashboards by using MDX queries.

  5. After you have specified the MDX query that you want to use, click OK to close the Dimensional Data Source Mapping dialog box.

  6. Repeat for each value for which you want to add a dimension formula.

    • If the Calculated Metrics Data Source Mapping dialog box is open, repeat Steps 2-5.

    • If no Data Source Mapping dialog box is open, repeat Steps 1-5.

  7. In the Workspace Browser, right-click the KPI, and then click Save.

See Also

Concepts

Create and configure a KPI by using Dashboard Designer
Extend a PerformancePoint KPI
Create a scorecard by using Dashboard Designer
Extend PerformancePoint dashboards by using MDX queries