I've often wanted to be able access metadata within a monitoring server with greater ease. Querying the PPSMonitoring database is difficult because the more interesting pieces of information pertaining to a particular element are locked away within the SerializedXML column of the FCObjects table. Sure we can access the data in the SerializedXML column with XPath / XQuery but I really wanted to get stuck into some CLR stuff and figured UDFs would provide the greatest reuse, flexibility and query-ability.
I've created a series of CLR table-valued UDFs and created a home for it on Codeplex, a project that I have named MAUDF (taking a bit of inspiration from the ASSP project). Using the CLR the MAUDFs connect to the PPS Monitoring web service and extract pertinent metadata and return the results in a tabular format.
Why UDFs?
The use of table-valued UDFs enables element metadata to be queried, JOINED and (CROSS | OUTER) APPLY-ed in T-SQL in as if they were tables, opening up the world of M&A metadata to a much wider audience.
Using the UDFs
Some of the UDFs do not require a parameter value, simply executing these functions returns a tabular result set that can be used as if it were a table like the example below that calls MAUDF.ElementMetadata().
-- Get metadata for all elements with a display folder of "PPS Demo"
SELECT *
FROM MAUDF.ElementMetadata()
WHERE DisplayFolder = 'PPS Demo'
ORDER BY ElementType
The remaining UDFs take a single parameter: the GUID that identifies the element in question. We can supply that value by using APPLY, all we need is an anchor table from which to reference the element IDs. In the example below we use the MAUDF.ElementMetadata() function to provide the anchor record set.
-- Get the custom property details for KPIs that have custom properties defined
SELECT
ElementName AS ElementName
,PropertyType
,PropertyName
,PropertyDescription
,PropertyValue
FROM MAUDF.ElementMetadata() e
CROSS APPLY MAUDF.ElementCustomProperties(ElementID) cp
WHERE e.ElementType = 'KPI'
I have also created a view named MAUDF.vFCObjects which is based on the data in the FCObjects table. The view definition simply adds a textual ElementType column. The same query shown above be duplicated using the MAUDF.vFCObjects view as an anchor for the CROSS APPLY operation.
SELECT
ElementName AS ElementName
,PropertyType
,PropertyName
,PropertyDescription
,PropertyValue
FROM MAUDF.vFCObjects f --using vFCObjects as an anchor for CROSS APPLY
CROSS APPLY MAUDF.ElementCustomProperties(ElementID) cp
WHERE f.ElementType = 'KPI' The main difference between the using an MAUDF or vFCObjets view is query performance. Because vFCObjects is based on a physical table with indexes you can immediately limit the number of rows that are made available to the CROSS APPLY thereby increasing performance. The MAUDF.ElementMetadata() function must first generate an entire result set for all published elements and then this is reduced based on the contents of the WHERE clause, which can take a bit longer.
There are different situations where one query structure will out-perform the other but not by too much. The aim of providing both is flexibility and choice.
MAUDF Schema
All UDFs and supporting objects are created within the PPSMonitoring database in their own schema, cunningly named "MAUDF".
Sample Queries
Below are some more sample queries. All these and more are contained in the Codeplex project. Some of the samples use the no-parameter-UDFs as an anchor to CROSS APPLY to, others use the vFCObjects view. The choice is yours as to which suits your requirements.
-- Which elements has "NT AUTHORITY\Authenticated Users" been granted access to?
SELECT
f.ElementTypeName
,f.ElementName
,m.MembershipRole
FROM MAUDF.vFCObjects f
CROSS APPLY MAUDF.ElementMemberships(ElementID) m
WHERE MembershipLogin = 'NT AUTHORITY\Authenticated Users'
ORDER BY f.ElementTypeName
-- Get the count of each report type published to the server
SELECT
ReportType
,COUNT(*) ReportCount
FROM MAUDF.ReportMetadata()
GROUP BY ReportType
ORDER BY COUNT(*) DESC
-- Get all Scorecard annotations
SELECT
f.ElementName
,CommentTitle
,CommentMessage
,AnnotationKpiMetricName
,AnnotationTuples
,FilterSlice
,AnnotationKpiID
FROM MAUDF.ScorecardMetadata() s
CROSS APPLY MAUDF.ScorecardAnnotations(s.ScorecardID) sa
INNER JOIN MAUDF.vFCObjects f
ON s.ScorecardID = f.ElementID
ORDER BY s.ScorecardID, AnnotationID, CommentCreatedDate
As a Report Source
Once you can easily access element metadata in a tabular format the creation of SSRS reports becomes far easier.
For example, a common request from many customers relates to the display of threshold banding numbers set for a particular KPI target. The SSRS report depicted below displays the threshold band numbers and the indicator icons associated with them each target in a KPI. The report takes a KpiID as a parameter. The report can then easily be incorporated into a dashboard by creating a filter link from a scorecard to the report passing the KpiID.
The SQL code to return the result set for this report is also contained in the samples.
Where do I get it?
The MAUDF setup instructions, source code, release binaries, samples, documentation and more can be accessed from the MAUDF Codeplex project.
I'm looking forward to hearing feedback and any new ideas anyone may have. Please note that this is the first time I have written anything seriously in C# (or any other .NET language for that matter).
Thanks very much to Darren Gosbell for his early alpha testing and feedback.
