MDX Queries for Notification Services

The result set of a multidimensional expression (MDX) query can have multiple dimensions. However, this event provider requires that its MDX queries return a flat row set, which can in turn be submitted as a set of events into a Notification Services application.

The MDX query must return a result set with columns that map to the event class fields. The first MDX result column should map to the first event class field, the second result column should map to the second event class field, and so on.

MDX queries for the Analysis Services event provider can be static. Static means that the complete query is known when you create the application. Or, they can be dynamic. Dynamic means that the elements of the ROWS axis or elements of the MDX query slicer are obtained by executing Transact-SQL queries. The results of the Transact-SQL queries are used to generate the final MDX query by using an XSL transform.

Static MDX Queries

When you know the exact query required to obtain data for your Notification Services application, you can write a static query. Notification Services will run this exact query on the quantum schedule specified in the application definition.

Consider the following event class definition:

<EventClass>
    <EventClassName>KPIEvents</EventClassName>
    <Schema>
        <Field>
            <FieldName>Employee</FieldName>
            <FieldType>varchar(100)</FieldType>
        </Field>
        <Field>
            <FieldName>ProfitMargin</FieldName>
            <FieldType>varchar(10)</FieldType>
        </Field>
        <Field>
            <FieldName>OrderQuantity</FieldName>
            <FieldType>int</FieldType>
        </Field>
        <Field>
            <FieldName>SalesAmount</FieldName>
            <FieldType>money</FieldType>
        </Field>
    </Schema>
</EventClass>

The following is an example of a static MDX query that returns an appropriate result that maps to this event class:

SELECT
      {[Measures].[Reseller Gross Profit Margin], 
      [Measures].[Reseller Order Quantity], 
      [Measures].[Reseller Sales Amount]} ON COLUMNS,
      {[Employee].[Employees].[Ken J. Snchez], 
      [Employee].[Employees].[Amy E. Alberts]} ON ROWS
FROM [Adventure Works] 
WHERE [Date].[Calendar].[CY 2002]

This query selects three measures from the Adventure Works cube. These measures are the columns in the result set. The query is further restricted to select rows that pertain to only two employees, Ken J. Sánchez and Amy E. Alberts.

There are multiple tuples on the Order Time dimension. Therefore, the query further restricts, or "slices," the query to show only the year 2002.

The final result set is a table that shows the Reseller Gross Profit Margin, Reseller Order Quantity, and Reseller Sales Amount values for the selected employees for the year 2002.

When Notification Services runs this query, the two-dimensional result set is submitted as an event batch to an event class.

Dynamic MDX Queries

For a Notification Services application, you might only want to select data for employees who are subscribed to the application. However, because employees can add and delete subscriptions, you will not have a fixed list for a static query when you build the application.

You could write a static query that returns a record for each employee, whether subscribed or not. However, if a cube has tens of thousands of employees, a static query would return a row for each employee. This is a large event batch that contains irrelevant data.

You can make significant performance improvements to the application by writing a dynamic MDX query. Consider a subscription class that uses the following schema:

<SubscriptionClass>
    <SubscriptionClassName>KPISubscriptions</SubscriptionClassName>
    <Schema>
        <Field>
            <FieldName>DeviceName</FieldName>
            <FieldType>nvarchar(255)</FieldType>
            <FieldTypeMods>NOT NULL</FieldTypeMods>
        </Field>
        <Field>
            <FieldName>SubscriberLocale</FieldName>
            <FieldType>nvarchar(10)</FieldType>
            <FieldTypeMods>NOT NULL</FieldTypeMods>
        </Field>
        <Field>
            <FieldName>EmployeeFullName</FieldName>
            <FieldType>varchar(100)</FieldType>
        </Field>
        <Field>
            <FieldName>ProfitMargin</FieldName>
            <FieldType>varchar(10)</FieldType>
        </Field>
    </Schema>
    ...
</SubscriptionClass>

First, you write a Transact-SQL query that selects a list of items of interest, such as employees:

SELECT EmployeeFullName as Employee
FROM NSKPISubscriptionsView

This query assumes that the middle initial is included with the first name.

The Analysis Services event provider obtains the result of this Transact-SQL query in XML form, where each column name selected is returned as an attribute of a row element:

<rows>
    <row Employee="Ken J. Snchez" />
    <row Employee="Amy E. Alberts" />
    <row Employee="Terry Lee Duffy" />
    <row Employee="Brian S. Welcker" />
</rows>

You then write an XSL transform that formats the result list into the complete MDX query. For example, the transformation can write the known parts of the query on match="rows". Then, for each row, transform the value into an item in the ON ROWS specification of the MDX query. To transform the XML, use the following XSL transform:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" />
<xsl:template match="rows">
SELECT 
    { [Measures].[Total Product Cost], 
       KPIValue([Gross Profit Margin]), 
       KPIGoal([Gross Profit Margin]), 
       KPIStatus([Gross Profit Margin]), 
       KPITrend([Gross Profit Margin]) } ON COLUMNS,
    { <xsl:apply-templates select="row"/> } ON ROWS
FROM [Adventure Works] 
WHERE [Date].[Calendar Time].[2002]
</xsl:template>
<xsl:template match="row"> 
    <xsl:apply-templates select="@Employee" />
    <xsl:if test="not(position()=last())">,
    </xsl:if> 
</xsl:template>
<xsl:template match="@Employee">[Employee].[Employees].[<xsl:value-of select="."/>]</xsl:template>
</xsl:stylesheet>

The result of this transform is a complete MDX query:

SELECT
      {[Measures].[Reseller Gross Profit Margin], 
      [Measures].[Reseller Order Quantity], 
      [Measures].[Reseller Sales Amount]} ON COLUMNS,
    { [Employee].[Employees].[Ken J. Snchez],
       [Employee].[Employees].[Amy E. Alberts],
       [Employee].[Employees].[Brian S. Welcker] } ON ROWS
FROM [Adventure Works] 
WHERE [Date].[Calendar].[CY 2002]

The Analysis Services event provider uses this query, generated dynamically every time that the event provider runs, to query the cube for event data.

In this XSL transform, the slicer [Date].[Calendar].[CY 2002] is hard-coded into the transform. A Transact-SQL query can be passed to the Analysis Services event provider using the SlicerQuery argument to dynamically create the WHERE clause.

The results of the row query and slicer query are combined into a single XML result:

<rows>
    <row Employee="Ken J. Snchez" />
    <row Employee="Amy E. Alberts" />
    <row Employee="Brian S. Welcker" />
</rows>
<slicers>
    <slicer Calendar="CY 2002"/>
</slicers>

Your XSL transform is applied to this XML to generate the dynamic MDX query.

See Also

Concepts

Defining the Analysis Services Event Provider

Other Resources

Analysis Services Event Provider
Defining Event Providers
Standard Event Providers
Key Concepts in MDX (MDX)

Help and Information

Getting SQL Server 2005 Assistance