Section 20.4. SSAS Languages

20.4. SSAS Languages

SSAS provides the following languages used to query and manipulate data and schemas:

Multidimensional Expressions (MDX)

Used to refine, work with, and retrieve data from multidimensional objects

Data Mining Extensions (DMX)

Used to create and work with data-mining models

XML for Analysis (XMLA)

Open-standard, SOAP-based protocol for accessing standard multidimensional data sources, including SSAS data sources, on the Web

Analysis Services Scripting Language (ASSL)

Used to create, manage, and deploy SSAS objects

The following subsections describe the SSAS languages.

20.4.1. Multidimensional Expressions (MDX)

A relational database represents two-dimensional data defined by columns and rows. The intersection of a column and row identifies a field containing a single data value. T-SQL statements query relational data by using SELECT queries, specifying columns to retrieve and limiting values retrieved by using a WHERE clause.

MDX is a statement-based scripting language used to define, retrieve, and manipulate multidimensional objects and data. Although MDX is similar to T-SQL, it is not an extension to T-SQL. The MDX language provides the following:

  • A Data Manipulation Language (DML) used to retrieve and manipulate data from multidimensional objects

  • A Data Definition Language (DDL) used to create, alter, and drop multidimensional objects

  • A scripting language used to manage scope, context, and control flow within MDX scripts

  • Operators and functions, both built-in and user-defined, for manipulating data retrieved from multidimensional objects

Each MDX expression has a SELECT clause to request data, a FROM clause to identify the data source, and a WHERE clause to filter data. These elements, together with other keywords, are used to extract multidimensional data from cubes and manipulate the data retrieved through a set of built-in or user-defined functions. MDX also provides a DDL to manage SSAS objects.

MDX returns the results of a query against a cube in a structure called a cellset, which is analogous to the result set returned by T-SQL statements issued against relational data sources. A cube contains a collection of cells, and the intersection point of a member from each dimension of the cube defines a cell. A cell contains one measure together with its properties, such as the data type and format. A tuple is an expression that contains an ordered collection of one member of each dimension uniquely identifying a cell. A set is an ordered collection of tuples .

A SELECT statement is used in MDX to retrieve data from cubes. A simple SELECT statement contains a SELECT clause and a FROM clause with an optional WHERE clause.

The following example executes an MDX SELECT query against the Adventure Works cube in the Adventure Works DW database. Right-click that database in Object Explorer and select New Query MDX from the context menu. Execute the following query to retrieve a cellset from the Adventure Works cube:

     SELECT {[Measures].[Sales Amount], [Measures].[Gross Profit Margin]} ON COLUMNS,         {[Product].[Product Model Categories].[Category]} ON ROWS     FROM [Adventure Works]     WHERE ([Sales Territory Country].[United States]) 

Results are shown in Figure 20-2.

Figure 20-2. Results for MDX query example

The preceding example defines two query axesSales Amount and Gross Profit Margin as columns and [Product].[Product Model Categories].[Category] as a rowand restricts the data returned from the measure to the United States.

In T-SQL, the fields specified in a SELECT statement, together with a WHERE clause, are used to limit the data retrieved by a query. In MDX, the SELECT clause specifies the dimensions and members returned, which are referred to as the query axis dimensions. The WHERE clause restricts the data returned in the cellset to specific dimensions and member criteria, which are referred to as the slicer axis dimensions.

The SELECT clause determines the query axesthe edgesof the cellset returned by an MDX query. Each axis dimension is associated with a number, starting with 0 and incrementing sequentially with no breaks. The first five axes can be referred to by the aliases COLUMNS, ROWS, PAGES, SECTIONS, and CHAPTERS. If you specify more than two dimensions in the query, you will not be able to see those dimensions in the Results window in SQL Server Management Studio.

You can use a calculated measure in a query by specifying it using a WITH MEMBER clause, as shown in the following example:

     WITH MEMBER [Measures].[Total Amount] AS         '[Measures].[Sales Amount] + [Measures].[Tax Amount]'     SELECT {[Measures].[Total Amount]} ON COLUMNS,         {[Product].[Product Model Categories].[Category]} ON ROWS     FROM [Adventure Works]     WHERE ([Sales Territory Country].[United States]) 

Results are shown in Figure 20-3.

Figure 20-3. Results for MDX query using WITH MEMBER clause example

20.4.2. Data Mining Extensions (DMX)

DMX is a language used to create new data-mining model structures, train models, manage models, and browse and predict against models. DMX consists of DML statements, DDL statements, functions, and operators.

The OLE DB for Data Mining specification defines a structure in which to store the definition of a mining model and a language for creating, managing, and working with data-mining models. You can use DMX with this structure to create and work with models.

There are two types of DMX statementsdata manipulation and data definition. Use DMX data-manipulation statements to browse and create predictions against existing models. Use DMX data-definition statements to create, import, export, and drop mining models and mining structures from a database.

You create a new DMX statement by right-clicking an SSAS database in Object Explorer and selecting New Query DMX from the context menu. As an example, execute the following DMX data-manipulation statement to return information about the mining model schema rowset for the Forecasting mining model:


Results are shown in Figure 20-4.

20.4.3. XML for Analysis (XMLA)

XMLA is a SOAP-based XML protocol used for universal data access to any standard multidimensional data source on the Web. XMLA lets you explore and query multidimensional data through web services.

The XMLA open specification has two methodsDiscover and Executethat handle incoming and outgoing information on an SSAS instance. The Discover method returns information and metadata from a web service. The Execute method lets you run commands against XMLA data sources.

Figure 20-4. Results for DMX data-manipulation statement example

XMLA is the native protocol for SQL Server 2005 and is used by client applications to communicate with SSAS instances. SSAS uses XMLA exclusively when communicating with client applications, and significantly extends the XMLA 1.1 specification for this purpose.

The following example shows an Execute XMLA query that is the same as the first query in the "Multidimensional Expressions (MDX)" section earlier in this chapter. Create a new XMLA query in SQL Server Management Studio by right-clicking the SSAS instance and selecting New Query XMLA from the context menu.

     <Execute xmlns="urn:schemas-microsoft-com:xml-analysis">       <Command>         <Statement>           SELECT {[Measures].[Sales Amount], [Measures].[Gross Profit Margin]}             ON COLUMNS,           {[Product].[Product Model Categories].[Category]} ON ROWS           FROM [Adventure Works]           WHERE ([Sales Territory Country].[United States])         </Statement>       </Command>       <Properties>         <PropertyList>           <Catalog>Adventure Works DW Standard Edition</Catalog>           <Format>Multidimensional</Format>           <AxisFormat>ClusterFormat</AxisFormat>         </PropertyList>       </Properties>     </Execute> 

The results are returned in the <CellData> element of the XML document partially shown in Figure 20-5.

The next example uses a Discover XMLA query to return the available data sources for the SSAS server and information required to connect to them:

     <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">       <RequestType>DISCOVER_DATASOURCES</RequestType>       <Restrictions>       </Restrictions>       <Properties>       </Properties>     </Discover> 

Figure 20-5. Partial results for XMLA Execute method example

The results are returned in the <row> element of the XML document partially shown in Figure 20-6.

Figure 20-6. Partial results for XMLA Discover method example

The RequestType parameter specifies the type of information the Discover method should return. The Restrictions parameter filters the returned result set based on column values. The Properties element specifies properties of the Discover method.

20.4.4. Analysis Services Scripting Language (ASSL)

ASSL is a dialect for SOAP messages used by client applications to communicate with SSAS instances. ASSL has two parts:

  • A DDL that defines an SSAS instance, the database, and the database objects contained in the instance. Client applications use the DDL to describe, create, alter, and deploy SSAS objects.

  • A command language that sends actions to an SSAS instance using XMLA.

For more information about ASSL, see Microsoft SQL Server 2005 Books Online.

Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton © 2008-2017.
If you may any questions please contact us: