The ascmd command-line utility supports the execution of MDX queries, XMLA scripts, and DMX statements within input files. The input script that you pass to the ascmd command-line utility is actually an XMLA Command element.
Command elements are as follows:
-
Alter
-
Backup
-
Batch
-
BeginTransaction
-
Cancel
-
ClearCache
-
CommitTransaction
-
Create
-
Delete
-
DesignAggregations
-
Drop
-
Insert
-
Lock
-
MergePartitions
-
NotifyTableChange
-
Process
-
Restore
-
RollbackTransaction
-
Statement (used to execute MDX queries and DMX statements)
-
Subscribe
-
Synchronize
-
Unlock
-
Update
-
UpdateCells
To perform commands on more than one object at a time, use the <Batch> command. To execute for MDX queries and DMX statements, use the <Statement> command. For more information, see Command Element (XMLA) in SQL Server Books Online. The following examples show how to structure MDX queries, DMX statements, and XMLA scripts.
.gif) 重要提示： |
|---|
|
Like all XML structures, commands are case sensitive. Therefore, for example, you must enclose all MDX queries in <Statement> …. </Statement> tags and the command must be “Statement”, it cannot be “statement” or “STATEMENT”. |
In addition to XMLA Commands, the ascmd command-line utility can also be used to execute custom XMLA requests to execute almost any request that can be expressed in XMLA. For example, the ascmd command-line utility can be used to issue either of the following XMLA requests:
-
Discover XMLA requests to query Analysis Services metadata. This metadata includes information about the following:
-
Objects stored in an Analysis Services database, such as the cubes defined on the server; and
-
Resources being used, such as the connections that are open on the server.
-
Execute requests that perform Commands but modify them by specifying a Property List and a Parameters List. An example of this kind of request is provided later in this document – see the Execute Example.
If the input text is not formatted as an XMLA Command, a Discover request, or an Execute request, the ascmd command-line utility assumes that the input text is a MDX query or DMX statement. In this case, the ascmd command-line utility HTML encodes the text and wraps a <Statement> … </Statement> element around it and processes it as an XMLA Command. This enables you to easily enter a MDX query or DMX statement. See Scenario 1 "Querying an Analysis Services Cube" later in this document for an example of how to use this capability.
MDX Example:
<Statement>
SELECT NON EMPTY
[Employees].Members ON ROWS,
[Measures].[Internet Gross Profit] ON COLUMNS
FROM [Adventure Works]
</Statement>
This example uses an MDX query in an XMLA Statement to return the Internet Gross Profit measure for each member of the Employees attribute hierarchy that is not empty from the Adventure Works cube.
DMX Example:
<Statement>
ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Decision Tree]
(
[Customer Key],
[Age],
[Bike Buyer] PREDICT,
[Commute Distance],
[Education],
[Gender],
[House Owner Flag],
[Marital Status],
[Number Cars Owned],
[Number Children At Home],
[Occupation],
[Region],
[Total Children],
[Yearly Income]
) USING Microsoft_Decision_Trees
WITH DRILLTHROUGH
</Statement>
This example uses a DMX query in an XMLA Statement change the [Bike Buyer] mining structure by adding a new mining model.
XMLA Example:
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
<CubeID>Adventure Works DW</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2001</PartitionID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
This example uses an XMLA Statement to fully process the Internet_Sales_2001 partition.
Discover Example:
<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
<RequestType>MDSCHEMA_CUBES</RequestType>
<Restrictions>
<RestrictionList>
<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>
</RestrictionList>
</Restrictions>
<Properties>
<PropertyList>
<Catalog>Adventure Works DW</Catalog>
<Format>Tabular</Format>
</PropertyList>
</Properties>
</Discover>
This example uses an XMLA Discover request to return what cubes are available in the Adventure Works DW database. Because Perspectives are returned to applications as if they were cubes, the returned data actually includes both cubes and perspectives.
Execute Example:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>
SELECT [Measures].MEMBERS ON COLUMNS FROM [Adventure Works]
</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>Adventure Works DW</Catalog>
<Format>Tabular</Format>
<AxisFormat>ClusterFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute>
This example uses an MDX query in an XMLA Statement. However, notice that the Property List part of the XMLA request specifies that the return format is Tabular instead of Multidimensional. The multidimensional format is the default for an XMLA Statement command. Because the return format is in tabular (rowset) format, the output file could be used by an application that understands xsd flattened rowsets instead of a cellset, and the flattened rowset could be more easily loaded into a SQL relational database because it is now formatted as a table.