ADOMD.NET - Microsoft SQL Server 2008 Analysis Services - page 651

Using Parameters in MDX Requests

The client application you write will often generate the text of MDX requests based on
user input. One part of the request usually remains constant for all requests, and the other
part varies from request to request, depending on the data the user enters.

In Analysis Services 2000, you had to create your MDX requests by concatenating strings
prepared in advance with strings entered by the user. This way of creating MDX requests
was not convenient, and sometimes it could be dangerous because a user could try to
outwit the system by entering incorrect text; for example, he could try to break security by
MDX injection.

Starting from Analysis Services 2005, there is a faster, more reliable way of operating with
data entered by the user: parametric queries. ADOMD.NET allows the client application to
use the AdomdParametersCollection specified in the AdomdCommand object to pass parame-
ters to the server. AdomdParametersCollection consists of AdomdParameter objects.
AdomdParameter implements the IDataParameter interface (as shown in Figure 33.19) and
allows you to pass parameters of any scalar type to the query.

System.Data.IDataParameter AdomdClient.AdomdParameter

FIGURE 33.19

The ADOMDDataParameter object implements the IDataParameter inter-

face.

Let's look at an example to see how parameters can be used in an application. In this
example, the application displays a certain number of the products that were the most
popular in each possible product family. This application allows the user to choose the
product family she's interested in and the number of top products that she wants to see.
To implement this, our application creates an MDX query and, instead of specifying the
product family and the number of products, embeds the following:

SELECT TopCount(Descendants(StrToMember(@productFamilyMember,
➥ CONSTRAINED), [Product].[Product Name] ), @topCount,
➥ [Measures].[Unit Sales] ) ON COLUMNS FROM [Warehouse and Sales]

The parameters must be scalar types, but they can represent different MDX objects, such
as Member or Set , so the template should contain a function that translates a scalar type—a
string—to MDX. In Listing 33.28, we use StrToMember ; but in similar cases, you could use
StrToSet or StrToTuple . You don't need to translate the numeric parameter that specifies
the number of the products because the MDX function TopCount expects a numeric value.

LISTING 33.28

Sending Parametric Queries to Analysis Services Using ADOMD.NET

using System;