PerformancePoint Monitor & Analyze UDF Project (MAUDF)

RELEASED: 1.0 Beta (Jun 03, 2008)

Mon, 02 Jun 2008 22:52:34 GMT

!!! Initial release
This is the beta release of the MAUDF project.

UPDATED RELEASE: 1.0 Beta (Jun 03, 2008)

Mon, 02 Jun 2008 22:52:34 GMT

!!! Initial release
This is the beta release of the MAUDF project.

UPDATED RELEASE: 1.0 Beta (Jun 03, 2008)

Mon, 02 Jun 2008 22:49:38 GMT

!!! Initial release
This is the beta release of the MAUDF project.

UPDATED WIKI: FAQs

NickBarclay — Mon, 02 Jun 2008 10:32:02 GMT

FAQs

Q: Can the UDFs be used to alter any data in the PPSMonitoring database?
A: No. At present the UDFs only read data that is stored in the database. No data modification functionality has been created.

Q: Can I access the value of a particular KPI in a cell of a scorecard using MAUDFs?
A: No. The MAUDFs only access element metadata, not the data they extract from their source systems.

UPDATED WIKI: Home

NickBarclay — Mon, 02 Jun 2008 10:31:01 GMT

MAUDF Project

The MAUDF project is a series of CLR table-valued User Defined Functions that facilitate access to metadata for elements published to a specific PerformancePoint Server 2007 Monitoring and Analytics (M&A) instance. The aim of the MAUDF project is to make otherwise hard-to-get-at metadata more accessible through these functions. Because the data exposed is tabular this provides an easier way for system administrators and developers to surface useful information contained within a particular Monitoring server using just T-SQL.

UDF List

UDF	Description
ElementMetadata	Element level metadata common to all elements e.g. name, description, display folder
ElementMemberships	Lists each membership domain account the assigned privileges for an element
ElementCustomProperties	Lists information for all custom properties that exist on an element
KpiMetrics	Lists metadata for each actual and target metric within a kpi
KpiTargetThresholds	Lists detailed threshold settings for each target metric within a kpi
ScorecardMetadata	High level property settings for published scorecards
ScorecardKpis	List of all kpis referenced within a scorecard
ScorecardAnnotations	Lists all annotations and associated comments associated with a scorecard
ReportMetadata	High level metadata for published reports
IndicatorMetadata	High level metadata for published indicators
IndicatorLevels	Lists properties for each level defined within an indicator
DataSourceMetadata	High level metadata for published data sources
DashboardItems	Lists metadata for each scorecard, report and filter item referenced within a dashboard

Setup

FAQs

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:29:54 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY property set to ON so the CLR code can run

-- Check if PPSMonitoring has TRUSTWORTHY set to ON
SELECT
  is_trustworthy_on 
FROM sys.databases 
WHERE [name] = 'PPSMonitoring'
 
-- if the result above is 0 set it ON
ALTER DATABASE PPSMonitoring
SET TRUSTWORTHY ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<YourMonitoringServerName>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you can change the name when setting the server up but the default name is PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:28:58 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY property set to ON so the CLR code can run

-- Check if PPSMonitoring has TRUSTWORTHY set to ON
SELECT
  is_trustworthy_on 
FROM sys.databases 
WHERE [name] = 'PPSMonitoring'
 
-- if the result above is 0 set it ON
ALTER DATABASE PPSMonitoring
SET TRUSTWORTHY ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<YourMonitoringServerName>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you may have renamed it but by default its name will be PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:28:15 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY property set to ON so the CLR code can run

-- Check if PPSMonitoring has TRUSTWORTHY set to ON
SELECT
  is_trustworthy_on 
FROM sys.databases 
WHERE [name] = 'PPSMonitoring'
 
-- if the result above is 0 set it ON
ALTER DATABASE PPSMonitoring
SET TRUSTWORTHY ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<MYSERVERNAME>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you may have renamed it but by default its name will be PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:27:52 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY property set to ON

-- Check if PPSMonitoring has TRUSTWORTHY set to ON
SELECT
  is_trustworthy_on 
FROM sys.databases 
WHERE [name] = 'PPSMonitoring'
 
-- if the result above is 0 set it ON
ALTER DATABASE PPSMonitoring
SET TRUSTWORTHY ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<MYSERVERNAME>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you may have renamed it but by default its name will be PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:23:48 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY property set to ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<MYSERVERNAME>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you may have renamed it but by default its name will be PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: Setup

NickBarclay — Mon, 02 Jun 2008 10:22:44 GMT

Setup Instructions

Follow these steps to create the MAUDF objects within a PPSMonitoring database

Enable the CLR

In order to enable the CLR on the SQL Server where the PPSMonitoring database is located run the following T-SQL code in SSMS.

SP_CONFIGURE 'clr enabled', 1
GO
RECONFIGURE
GO

Set TRUSTWORTHY ON

Ensure that the PPSMonitoring database has its TRUSTWORTHY http://msdn.microsoft.com/en-us/library/ms187861.aspx property set to ON

Create MAUDF Objects

Files required

MAUDF.dll
MAUDF.XmlSerializers.dll
CreateMAUDF.sql

Procedure

Create a directory (e.g. C:\MAUDF) and drop the MAUDF.dll and MAUDF.XmlSerializers.dll files into it
Open the MAUDFCreate.sql file in SSMS
Edit the value in the @AssemblyPath variable to point towards the directory where the MAUDF.dll and MAUDF.XmlSerializers.dll files are located.

SET @AssemblyPath = 'C:\MAUDF'

Edit the value in the @WebServiceURL variable to point towards the web service URL for the PerformancePoint instance in question.

SET @WebServiceURL = 'http://<MYSERVERNAME>:40000/webservice/pmservice.asmx'

Ensure the PPSMonitoring database is selected (you may have renamed it but by default its name will be PPSMonitoring)
Run the script to register the assemblies and create the appropriate objects within the database

UPDATED WIKI: DataSourceMetadata

NickBarclay — Mon, 02 Jun 2008 10:20:32 GMT

DataSourceMetadata

Parameter

N/A

Grain

One record per DataSource published to the server

Result Set

Field Name	Data Type	Description
DataSourceID	UNIQUEIDENTIFIER	Uniquely identifies a DataSource
DataSourceType	NVARCHAR(MAX)	Type of data source e.g. Tabular, Multidimensional
DataSourceClass	NVARCHAR(MAX)	Class of datasource e.g. ADOMD.NET, SqlTabularDataSource, ExcelServicesWorkbook
MinutesToCache	INT	Cache setting in minutes
ServerName	NVARCHAR(MAX)	Name of the server where the data source is located
DatabaseName	NVARCHAR(MAX)	Name of the database that the data source is querying. This is blank for tabular data sources such as ODBC, Excel Workbook
CubeName	NVARCHAR(MAX)	Name of the OLAP cube the data source is querying. This is also used to contain the name of a table or view used by SqlTabular data sources

UPDATED WIKI: IndicatorMetadata

NickBarclay — Mon, 02 Jun 2008 10:19:56 GMT

IndicatorMetadata

Parameter

N/A

Grain

One record for each indicator published to the server

Result Set

Field Name	Data Type	Description
IndicatorID	UNIQUEIDENTIFIER	Uniquely identifies an Indicator
IndicatorType	NVARCHAR(MAX)	“Centered” or “Standard”
CountLevels	INT	Number of levels defined 2-10

UPDATED WIKI: ReportMetadata

NickBarclay — Mon, 02 Jun 2008 10:19:11 GMT

ReportMetadata

Parameter

N/A

Grain

One record for each report published to the server

Result Set

Field Name	Data Type	Description
ReportID	UNIQUEIDENTIFIER	Uniquely identifies a Report
ReportType	NVARCHAR(MAX)	Type of report e.g. Url, OLAPGrid, TrendAnalysisChart etc.
CountEndPoints	INT	Number of endpoints the report item makes available to accept inbound filter links when contained in a dashboard

UPDATED WIKI: ScorecardMetadata

NickBarclay — Mon, 02 Jun 2008 10:18:12 GMT

ScorecardMetadata

Parameter

N/A

Grain

One record per Scorecard element published to the server

Result Set

Field Name	Data Type	Description
ScorecardID	UNIQUEIDENTIFIER	Uniquely identifies a Scorecard
TemplateType	NVARCHAR(MAX)	Template used to create the scorecard e.g. General
AllowAnnotations	BIT	Does the scorecard allow annotations to be created
ShowAnnotationFlags	BIT	Are the annotations existence marked with red flags
InvalidCellDisplay	NVARCHAR(MAX)	Default text to be shown for invalid cells
CountKpis	INT	Number of Kpis referenced in the scorecard
CountEndPoints	INT	Number of end points available when the scorecard is embedded in a dashboard (inbound filter links)
CountBeginPoints	INT	Number of begin points available when the scorecard is embedded in a dashboard (outbound filter links)
CountAnnotations	INT	Number of annotations that have been created on the scorecard

UPDATED WIKI: ElementMetadata

NickBarclay — Mon, 02 Jun 2008 10:17:36 GMT

ElementMetadata

Parameters

N/A

Grain

One record per element published to the server

Result Set

Field Name	Data Type	Description
ElementID	UNIQUEIDENTIFIER	Uniquely identifies the element
ElementType	NVARCHAR(MAX)	Type of element e.g. KPI, Scorecard, Indicator
ElementName	NVARCHAR(MAX)	Name of the element
ElementDescription	NVARCHAR(MAX)	Description of the element
Owner	NVARCHAR(MAX)	Owner of the element
DisplayFolder	NVARCHAR(MAX)	Display folder path e.g. Sales\NE Region
CreatedBy	NVARCHAR(MAX)	Domain\Username of the element creator
CreatedDate	DATETIME	Date and time the element was created
Version	INT	Current version number
CountMemberships	INT	Number of memberships defined
CountCustomProperties	INT	Number of custom properties defined

UPDATED WIKI: Home

NickBarclay — Sat, 31 May 2008 04:05:25 GMT

MAUDF Project

UDF List

UDF	Description
ElementMetadata	Element level metadata common to all elements e.g. name, description, display folder
ElementMemberships	Lists each membership domain account the assigned privileges for an element
ElementCustomProperties	Lists information for all custom properties that exist on an element
KpiMetrics	Lists metadata for each actual and target metric within a kpi
KpiTargetThresholds	Lists detailed threshold settings for each target metric within a kpi
ScorecardMetadata	High level property settings for a scorecard
ScorecardKpis	List of all kpis referenced within a scorecard
ScorecardAnnotations	Lists all annotations and associated comments associated with a scorecard
ReportMetadata	High level metadata for a report
IndicatorMetadata	High level metadata for an inindicator
IndicatorLevels	Lists properties for each level defined within an indicator
DataSourceMetadata	High level metadata for a data source
DashboardItems	Lists metadata for each scorecard, report and filter item referenced within a dashboard

Setup

FAQs

UPDATED WIKI: Home

NickBarclay — Sat, 31 May 2008 04:04:28 GMT

MAUDF Project

UDF List

UDF	Description
ElementMetadata	Information common to all elements e.g. name, description, display folder
ElementMemberships	Lists each membership domain account the assigned privileges for an element
ElementCustomProperties	Lists information for all custom properties that exist on an element
KpiMetrics	Lists metadata for each actual and target metric within a kpi
KpiTargetThresholds	Lists detailed threshold settings for each target metric within a kpi
ScorecardMetadata	High level property settings for a scorecard
ScorecardKpis	List of all kpis referenced within a scorecard
ScorecardAnnotations	Lists all annotations and associated comments associated with a scorecard
ReportMetadata	High level metadata for a report
IndicatorMetadata	High level metadata for an inindicator
IndicatorLevels	Lists properties for each level defined within an indicator
DataSourceMetadata	High level metadata for a data source
DashboardItems	Lists metadata for each scorecard, report and filter item referenced within a dashboard

Setup

FAQs

UPDATED WIKI: Home

NickBarclay — Sat, 31 May 2008 04:03:30 GMT

MAUDF Project

UDF List

UDF	Description
ElementMetadata	Information common to all elements e.g. name, description, display folder
ElementMemberships	Domain accounts and their assigned privileges for an element
ElementCustomProperties	Lists information for all custom properties that exist on an element
KpiMetrics	Lists metadata for each actual and target metric within a kpi
KpiTargetThresholds	Lists detailed threshold settings for each target metric within a kpi
ScorecardMetadata	High level property settings for a scorecard
ScorecardKpis	List of all kpis referenced within a scorecard
ScorecardAnnotations	Lists all annotations and associated comments associated with a scorecard
ReportMetadata	High level metadata for a report
IndicatorMetadata	High level metadata for an inindicator
IndicatorLevels	Lists properties for each level defined within an indicator
DataSourceMetadata	High level metadata for a data source
DashboardItems	Lists metadata for each scorecard, report and filter item referenced within a dashboard

Setup

FAQs

UPDATED WIKI: Home

NickBarclay — Sat, 31 May 2008 03:59:38 GMT

MAUDF Project

UDF List

UDF	Description
ElementMetadata	Information common to all elements e.g. name, description, display folder
ElementMemberships	Domain accounts and their assigned privileges for an element
ElementCustomProperties	Lists all custom property information that exists for an element
KpiMetrics	Actual and target metric metadata for a kpi
KpiTargetThresholds	Detailed threshold settings for each target metric within a kpi
ScorecardMetadata	High level property settings for a scorecard
ScorecardKpis	List of all kpis referenced within a scorecard
ScorecardAnnotations	All annotations and associated comments created on a scorecard
ReportMetadata	High level metadata for a report
IndicatorMetadata	High level metadata for an indicator
IndicatorLevels	Individual level properties for an indicator
DataSourceMetadata	High level metadata for a data source
DashboardItems	Item level metadata for each scorecard, report and filter located within a dashboard