Readme For Ascmd Command-line Utility Sample

Article
12/03/2008

New: 17 July 2006

The ascmd command-line utility enables a database administrator to execute an XMLA script, MDX query, or DMX statement against an instance of Microsoft SQL Server 2005 Analysis Services (SSAS). This command-line utility contains functionality for Analysis Services that resembles the sqlcmd utility included with SQL Server 2005. For more information, see the topic sqlcmd Utility in SQL Server 2005. The execution results of the script, query, or statement can be stored in a file together with relevant SQL Server Profiler trace information. The default install location for the ascmd command-line utility is as follows:

<system_drive>\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Administrator\ascmd

Scenarios

The following scenarios give examples of using the ascmd command-line utility.

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool and the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts or run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that uses the third-party tool. The solution is a command-line utility to run an XMLA script. The utility is then called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. After compilation, the database administrator can use the ascmd command-line utility to execute XMLA scripts that process partitions and dimensions.

Backing Up an OLAP Database from a Third-Party Tool

Another database administrator at the same company is required to automate the back-up of an Analysis Services database. Again, because the scheduling software that the company is using is not a SQL Server tool, the task has to be run from the command line. The database administrator generates the appropriate XMLA script (using SQL Server Management Studio). Then, the third-party scheduling software uses the ascmd command-line utility to run the XMLA script to backup the OLAP database.

Using XMLA During an Installation

A developer for an independent software vendor is required to integrate the execution of an XMLA script directly into the installation of the firm's product. The developer must run an XMLA script and retrieve status (and trace events) to know that the Analysis Services database was created correctly. The developer can do this by using the ascmd command-line utility.

Languages

C#, the language that ascmd itself is coded in.
Batch file commands, which start the ascmd command-line utility.

Prerequisites

To effectively use the ascmd command-line utility, you should have some or all of the following software installed:

Microsoft SQL Server 2005 Analysis Services (SSAS)
An instance of Analysis Services must be installed and running, because the ascmd command-line utility is used to connect to an instance of Analysis Services and execute MDX queries, XMLA scripts, and DMX statements.

SQL Server Management Studio and Business Intelligence Development Studio
These two work environments provide supporting infrastructure for you to complete any task related to Analysis Services. For any given task, you can approach implementation through the user interface or programmatically.

Analysis Management Objects (AMO)
AMO is required to execute the ascmd command-line utility on a computer that does not have Analysis Services installed. AMO can be installed from the SQL Server 2005 Feature Pack, which can be downloaded from the Microsoft Download Center at https://www.microsoft.com/downloads.

.NET Framework 2.0
The .NET Framework 2.0 is required for the ascmd command-line utility to run. It can be downloaded from the Microsoft Download Center at https://www.microsoft.com/downloads.

Microsoft Visual Studio 2005 or .NET Framework SDK 2.0
We recommended that you use Visual Studio 2005 when you are building or customizing the ascmd sample application. If you do not have Visual Studio 2005 available, use the .NET Framework SDK 2.0. The .NET Framework SDK includes the MSBuild.exe (see the “Compiling the Sample” installation later in this document).

The Adventure Works DW database included with SQL Server 2005
The database samples are useful for experimenting with the ascmd command-line utility. For more information, see Running Setup to Install AdventureWorks Sample Databases and Samples in SQL Server 2005 Books Online.

An updated version of AdventureWorks database is also available for download on the SQL Server Developer Center Web page.

An updated version of the SQL Server 2005 Database Engine samples is also available for download on the SQL Server Developer Center Web page.

Arguments

The following arguments are supported at the command line for ascmd.

–Ulogin_id
Is the user login ID, which is case-insensitive.

Note

the use of login_id is different for sqlcmd and ascmd. In sqlcmd, login_id represents a SQL Server login; for ascmd it represents a Windows login.

For TCP/IP access, Analysis Services only supports trusted connections. If the –U parameter is specified (together with the matching password using the –P parameter), the ascmd command-line utility logs on to the Windows operating system using the specified account and then impersonates the account when executing the XMLA script, MDX query, or DMX statement. The login ID must be in the form <domain>\<username>, and the domain must be specified. If –U is not specified, authentication is based on the Windows account of the user who is running the ascmd command-line utility.

If an http (or https) connection is specified by the –S parameter, the ascmd command-line utility does not log on to the Windows operating system. Instead, the –U and –P parameters (if present) are included as part of the connect string to the Internet Information Services (IIS) server. Depending on how IIS is configured, the –U and –P parameters can be used for basic authentication. For more information about the “UID” connect string parameter, see AdomdConnection Class in SQL Server 2005 Books Online.

–Ppassword
Is a user-specified matching password to the –U parameter. If the –U parameter is specified and the –P parameter is not specified, the password is assumed to be blank (an empty, zero-length string). If the –P parameter is specified and the –U parameter is not, the –P parameter is ignored.

Security Note:
Do not use a blank password. Use a strong password. For more information, see Strong Passwords in SQL Server 2005 Books Online. The –P parameter password is stored as clear text in the script, query, or statement file; it will be visible to anyone who can see the computer monitor or read the file itself. If you use this feature, put ACLs on the files or use other security techniques to make sure that only trusted users can read the files.

Do not use a blank password. Use a strong password. For more information, see Strong Passwords in SQL Server 2005 Books Online. The –P parameter password is stored as clear text in the script, query, or statement file; it will be visible to anyone who can see the computer monitor or read the file itself. If you use this feature, put ACLs on the files or use other security techniques to make sure that only trusted users can read the files.

–Sserver\instance or –Shttp[s]://server[:port]/virtualdirectory/msmdpump.dll
Specifies the Analysis Services instance to which the ascmd command-line utility will connect and execute. If the –P parameter is not specified, the ascmd command-line utility connects to the default instance of Analysis Services on the local computer that is running TCP (connecting to localhost) and executes the XMLA script, MDX query, or DMX statement.

–ddatabase
Specifies the database against which an MDX query or DMX statement will execute. The –d parameter is ignored when the ascmd command-line utility executes an XMLA script, because XMLA scripts have the database name embedded within the XMLA script.

–tquery-timeout
Specifies the number of seconds before the execution of an XMLA script, MDX query, or DMX statement times out. The ascmd command-line utility adds the TIMEOUT =<query-timeout> clause to the connect string.

–tcconnect-timeout
Specifies the number of seconds before the ascmd connection to the Analysis Services instance times out. The ascmd command-line utility adds the CONNECT TIMEOUT = <connect-timeout> clause to the connect string.

–iinput-file
Identifies the file that contains the XMLA script, MDX query, or DMX statement. You must specify a value for either the –i or the –Q parameter when you use the ascmd command-line utility. If you specify no –i or**–Q** parameter, or specify both of these parameters, an error is generated.

Note

Unlike the sqlcmd command-line utility (which can handle multiple input files), the ascmd command-line utility can only process one input file at a time. If you have more than one input file, each one must be called and executed separately.

The input file specified with either the –i or the –Q parameter must be a valid XML structure and special characters must be HTML-encoded. For example, when you use an ampersand (&) in your text, it must be encoded as &. So [Product].&1922] will be encoded as [Product].&[1922]. Likewise, a less-than sign (<) must be encoded as <, a greater-than sign (>) as >, and double quotation marks (") as ". This is important for MDX queries and DMX statements because the syntax of member keys uses the ampersand character (&).

Note

If the input text does not look like it is an XMLA script, that is, it does not start with a valid XMLA command such as <Statement> or <Create> (see the full list later in this document), then the ascmd command-line utility assumes that the text is a <Statement> and HTML encodes the text for you and wraps it in a <Statement> … </Statement> XML element tag. This is done as a convenience so that executing MDX queries and DMX statements are easier. If you want to use <Statement> elements and write the HTML text yourself, you can do that. Any valid XMLA command is accepted by the ascmd command-line utility.

An input file can contain multiple batches, separated by GO commands. Each batch within an input file can contain an XMLA script, an MDX query, or a DMX statement. Each GO command must appear on a single line. When a GO command is found, the system sends the input preceding the GO command to the server. An implied GO command is at the end of the input stream. The generated output file is formatted by wrapping the returned XML streams with a <multiple-batches> element. See Scenario 11 for an example of an input file that contains multiple batches.

Each batch executes and succeeds or fails in its own right. The return status of each batch is recorded in the output file, which you must parse to determine the success or failure of each batch.

–ooutput-file | NUL | NUL:filename
Identifies the file that receives (in XML) the results of the XMLA script or the cellset return by the MDX query or DMX statement. If the specified file already exists, the existing file is automatically overwritten. File names that contain spaces must be enclosed in quotation marks (""). If the file name is not valid, an error message is generated, and the ascmd command-line utility exits.

The ascmd command-line utility does not support concurrent writing of multiple ascmd processes to the same file; if this is tried, the file output will be corrupted or incorrect.

If the specified output file is NUL or NUL:filename, the execution results are discarded unless the –T parameter is used to specify a trace file, in which case the execution results are stored in the trace file. Specifying a NUL output file and with the –T parameter is most useful when specifying a Duration trace level with the –Tl parameter.

For example, you could create a series of MDX queries and execute them with the ascmd command-line utility, ignore the output (which might be very large), record the query durations into a trace file, and then load the query duration values in the trace file into a database. This lets you evaluate performance variations over time. Alternatively, you could use the Duration-result trace level with the –Tl parameter to include both the duration and the execution result in the trace file.

Note

The ascmd command-line utility supports international encoding. Input and output files use UTF-8 encoding with byte-order markers enabled. If your text editor does not support UTF-8 and you have international characters in your MDX query, in your XMLA script, or in your DMX statement, you can use Notepad to convert the input file into UTF-8 format. To convert the input file to UTF-8, open the file in Notepad, on the File menu select Save As, and in the Encoding box, select UTF-8. Then you can use the file together with the –i parameter. Output and trace files (–o and –T) are always written with UTF-8 encoding and byte-order markers to ensure that Unicode characters are preserved.

–Ttrace-file
Identifies a file that receives Analysis Services trace events from the ascmd command-line utility executing the XMLA script, MDX query, or DMX statement. If the file already exists, it is automatically overwritten (except for the trace files that are created by using the –TlDuration and –TlDuration-result parameter settings). File names that contain spaces must be enclosed in quotation marks (""). If the file name is not valid, an error message is generated, and the ascmd command-line utility exits.

The ascmd command-line utility does not support concurrent writing of multiple ascmd processes to the same file; if this is tried, the file output will be corrupted or incorrect. If the –T parameter is not specified, the trace output is not captured and the –Tf, –Tl, Td and –Tt parameters are ignored.

Note

the –T parameter is unavailable when you are using http or https access. You must use an ordinary client/server connection, by specifying the –S parameter.

–xcextended-connect-string
Specifies an extended connect string that is inserted directly into the connect string, without any value checking. The string should not contain any leading or trailing semi-colons (;). For example, the following extended connect string changes the network packet size used between the server and the ascmd process from 4096 to 16384 and also requests that the client locale be set to en-US (US English):

-xc "Packet Size=16384;LocaleIdentifier=1033"

The default for the ascmd command-line utility is not to add any extended connect string information. Although many of the options of the ascmd command-line utility can be implemented as extended connect string setting (for example by setting Database=<database name> directly), we recommend that you use the standard ascmd options when you can and only use extended connect string settings when you have no other mechanism available.

–Tftext | csv
Specifies the file format for the –T parameter (if this parameter is specified). The default value is csv. The available options are as follows:
- For text, the file is written in a text format. Examples of the format are as follows:
  <current time> <event-class>.<event-subclass>, [name=value]
- For csv, the file is written in comma-separated format. The default column delimiter is | (pipe, or vertical bar); use the –Td parameter to change the default delimiter for a csv file. The first line in the file specifies column headings for the values.

–Tddelim-char
Specifies a single character as the trace file delimiter when specifying csv as the format for the trace file that use the –Tf parameter. Default is | (pipe, or vertical bar).

–Tttrace-timeout
Specifies the number of seconds the Analysis Services engine waits before ending the trace (if you specify the –T parameter). The trace is considered finished if no trace messages have been recorded during the specified time period. The default trace time-out value is 5 seconds.

–Tltrace-level
Specifies what data is collected and recorded in the trace file. This parameter has the following five possible values:
- High – records all trace events - this is the default setting.
- Medium – records all trace events except the ProgressReportCurrent and Notification events.
- Low – records only those trace events that contain "End" or "Error" in the event.
- Duration – records no trace events, but instead determines the duration of the execution of the script, query, or statement by the ascmd process. Writes a single entry into the trace file that includes the current time, duration, execution text, database, and server name.
- Duration-result – records the same information as the Duration setting and also records the result of the execution in the last column of the trace file.
Note

The trace files generated with the Duration and Duration-result settings are not overwritten with each execution (as is the case with trace files generated with the High, Medium and Low settings). Instead, with the Duration and Duration-result settings, if an existing trace file exists, it is opened and new values are appended to the end of the file. If the trace file does not already exist, it is created.

–Q*"cmdline query or script"*
Specifies the actual script, query, or statement directly on the command line instead of in a file.

Note

The sqlcmd command-line utility supports an additional ways to specify the input query (using the –q parameter). Unfortunately, because that option reads from sysinput, you cannot write it unless you add more language constructs. For example, sqlcmd uses “go” and “exit” to control sysinput commands. This additional way to specify query input is not supported by the ascmd command-line utility.

–v var=value...
Specifies additional scripting variables. Each variable is a var = value pair. If the value contains embedded spaces or control characters, it must be enclosed in double-quotes (").For example,

-v maxparallel=4 option= "degree of freedom"

You can specify zero, one, or more than one var = value pairs.

–? or /?
Displays the syntax summary of the ascmd command-line utility options.

Key Encryption and Compiling the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.

-- or --

Open a Microsoft .NET Framework command prompt. Click Start, point to All Programs, point to Microsoft .NET Framework SDK 2.0, and then click SDK Command Prompt.
Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

Note

To determine the folder where samples are located, click the Start button, point to All Programs, point to Microsoft SQL Server, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\100\Samples.
At the command prompt, run the following command to generate the key file:

sn -k SampleKey.snk

Important

For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

Compilation of the sample can be done by using one of the following two approaches.

Using Visual Studio 2005 – compile the sample by using the provided Visual Studio solution in the <install_path>\Samples\Analysis Services\Administrator\ascmd\cs folder.
Using MSBuild, which is included in the.NET Framework SDK 2.0, compile the sample by running the following commands at the command prompt:
```
cd Analysis Services\Administrator\ascmd\CS\ascmd
msbuild ascmd.csproj /nologo /v:quiet /p:Configuration=Debug;Platform=<platform>
```

Note

Microsoft Visual Studio is fully supported on x86 and x64-based computers, but is not supported on Itanium-based computers. Once the ascmd command-line utility is compiled, the ascmd command-line utility can be executed on any x86, x-64, or Itanium-based computer.

In the previous code, the <platform> parameter value can be x86 for 32-bit computers, x64 for x64-based computers, or Itanium for IA-64-based computers. It is a best practice to compile the appropriate version of the ascmd command-line utility because performance might be decreased when executing 32-bit code on a 64-bit computer.

Note

If you are compiling the ascmd command-line utility on a computer whose architecture is different from the target computer (for example compiling the ascmd command-line utility on a 32-bit computer using either the x64 or Itanium parameter value, then you will receive three warning messages that indicate that three separate system DLLs are not available ("…targets a different processor"). This is typical and expected. After you compile the ascmd command-line utility, copy the compiled executable to your target server and execute it from the target server (where the appropriate DLLs are available).

Using Scripting and Environment Variables

The ascmd command-line utility supports system-reserved and user-defined scripting variables that you can use in XMLA scripts, MDX queries, and DMS statements. Values for these variables can be populated by specifying values for environment variables or by specifying values for command-line parameters.

The following rules apply to user-defined scripting variables and environment variables:

A variable can contain any number of lowercase characters, uppercase characters, digits, dashes (-), or underscores (_).
A variable cannot contain embedded characters or control characters, for example CR, LF, TAB.

System-Reserved Scripting Variables

System-reserved scripting variables are scripting variables that are defined by the ascmd command-line utility to hold the values associated with each command-line parameter. In some cases, environment variables can also be used to hold the values for these system-reserved scripting variables. For system-reserved scripting variables that can be populated or derived from both environment variables and command-line parameters, the value specified for the command-line parameter (if it is specified) overwrites any specified environment variable value.

The following table describes the system-reserved scripting variables, the associated command-line parameters, and where applicable, the associated environment variables.

Note

There are three system-reserved scripting variables that can only be set by using a command-line parameter (the –i, –o, and –T parameters). There is no corresponding ASCMD environment variable that you can use to populate the system-reserved scripting variable that corresponds to those three command-line parameters.

System-Reserved Scripting Variable	Parameter	Environment Variable (if any)
ASCMDUSER	–U	ASCMDUSER
ASCMDDOMAIN	–U	ASCMDUSER
ASCMDPASSWORD	–P	ASCMDPASSWORD
ASCMDSERVER	–S	ASCMDSERVER
ASCMDINSTANCE	–S	ASCMDSERVER
ASCMDHTTPCONNECTION	–S	ASCMDSERVER
ASCMDDBNAME	–d	ASCMDDBNAME
ASCMDINPUTFILE	–i
ASCMDOUTPUTFILE	–o
ASCMDQUERYTIMEOUT	–t	ASCMDQUERYTIMEOUT
ASCMDCONNECTTIMEOUT	–tc	ASCMDCONNECTTIMEOUT
ASCMDTRACEFILE	–T
ASCMDTRACEFORMAT	–Tf	ASCMDTRACEFORMAT
ASCMETRACEDELIM	–Td	ASCMDTRACEDELIM
ASCMDTRACELEVEL	–Tl	ASCMDTRACELEVEL
ASCMDTRACETIMEOUT	–Tt	ASCMDTRACETIMEOUT
ASCMDEXTENDEDCONNECTION	–xc	ASCMDEXTENDEDCONNECTSTRING

Notice that in some cases in the previous table, multiple system-reserved scripting variables are derived from a single parameter or environment variable. In the following example, three system-reserved scripting variables are derived from the ASCMDSERVER environment variable setting.

C:\>SET ASCMDSERVER=http://myserver/my_virtual_dir/msmdpump.dll

The previous SET statement specifying a value for the ASCMDSERVER environment variable sets the following values for the following three system-reserved scripting variables:

ASCMDSERVER="http://myserver/my_virtual_dir/msmdpump.dll"
ASCMDINSTANCE=""
ASCMDHTTPCONNECTION="true"

In a following example, the same three system-reserved scripting variables are populated with different values by using a different SET statement:

C:\>SET ASCMDSERVER=myserver\myinstance

The previous SET statement specifying a value for the ASCMDSERVER environment variable sets values for the following three system-reserved scripting variables:

ASCMDSERVER="myserver"
ASCMDINSTANCE="myinstance"
ASCMDHTTPCONNECTION="false"

Using System-Reserved Scripting Variables at the Command Prompt

If an environment variable exists that matches a system-reserved scripting variable (matching is case-insensitive), the environment variable’s value is used as the default value for the system-reserved scripting variable, and for the associated command-line parameter. For example, you can use the following SET statement was executed to set the ASCMDDBNAME environment variable:

C:\>SET ASCMDDBNAME="Adventure Works DW"

In this case, “Adventure Works DW” will be used as the default database (–d parameter) when you execute the ascmd command-line utility (unless you specify a different value at the command-line).

Using System-Reserved Scripting Variables in Scripts, Queries, or Statements

System-defined scripting variables can also be used in an XMLA script, an MDX query, or a DMX statement. The following examples illustrate sample command-line invocations of the ascmd command-line utility that use scripting variables. More examples appear later in this document to illustrate usage scenarios.

C:\>ascmd -S <server name> -i process.xmla -v cube=<CubeID>

process.xmla (simplified)

<Batch>
    <Parallel>
         <Process>
             <Object>
                  <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
                  <CubeID>($CUBE)</CubeID>
            . . .
         </Process>
    </Parallel>
</Batch>

User-Defined Scripting Variables

A user-defined scripting variable is a scripting variable that is defined by using the –v parameter at the command line, or is defined as an environment variable. When the ascmd command-line utility encounters a variable in an XMLA script, an MDX query, or a DMX statement, and the variable has not been populated using the –v parameter, the utility checks for an environment variable of the same name and uses that variable's value. If the ascmd command-line utility does not find a matching environment variable, the scripting variable is eliminated by replacing it with a blank string ("").

The following rules apply to user-defined scripting variables defined by using the –v parameter at the command line:

Leading and trailing spaces are removed from the “value” section of a variable.
The variable cannot start with the string “ascmd”.

Using MDX, XMLA, and DMX in Input Files

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 2005 Books Online. The following examples show how to structure MDX queries, DMX statements, and XMLA scripts.

Important

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 virtually 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 type 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, then 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 allows 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="https://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. Notice, however, that the Property List portion 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 rather than a cellset, and the flattened rowset could be more easily loaded into a SQL relational database because it is now formatted as a table.

ASCMD Scenario Examples

The following scenarios demonstrate uses of the ascmd command-line utility.

Scenario 1: Querying an Analysis Services Cube

In this scenario, you create an input file that contains an MDX query (the query.mdx file) that contains user-defined scripting variable (cube) in the MDX query. You then call this input file from the ascmd command-line utility and specify a value for this variable at the command-line by using the –v parameter.

query.mdx file:

Format 1:

<Statement>
/* THIS IS AN MDX COMMENT */
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM $(cube)
WHERE [Customer].[Country].&amp;[United States]
</Statement>

Format 2:

/* THIS IS AN MDX COMMENT */
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM $(cube)
WHERE [Customer].[Country].&[United States]

Command-line example:

C:\>ascmd -S myserver -d "Adventure Works DW" -i query.mdx -o result.xml -v cube="[Adventure Works]"

Notice that using Format 1, the key for the United States is handled by replacing the MDX "&" (which indicates that it is the member key and not the name) with & (as required for HTML encoding) and that the <Statement> element is specified. Notice that using Format 2, neither the HTML encoding nor the <Statement> element is needed. This is because the input text does not start with a valid XMLA command and that the ascmd command-line utility therefore assumes that the input text is a Statement and automatically HTML encodes the input and wraps it in a <Statement> element before execution.

Scenario 2: Backing Up a Database in an Untrusted Domain

In this scenario, you back up a database on a server in an untrusted domain by using the ascmd command-line utility. Because the database is in an untrusted domain, this scenario requires http access. In this scenario, the remote server (called "myserver") has both Internet Information Services (IIS) and Analysis Services running, and has an IIS virtual directory named "olapadmin", which is configured to use BASIC authentication. Additionally, the remote server has a local account called "olapadmin" with appropriate backup permissions. You specify the database name, access method, username, password, and backup file at the command line by using ascmd command-line parameters, and specify an XMLA input file (backup.xmla) that contains the scripting variables for the database and backup file.

backup.xmla file:

<Backup xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
   <Object>
      <DatabaseID>$(ascmddbname)</DatabaseID>
   </Object>
   <File>$(backupfile).abf</File>
</Backup>

Command-line example:

C:\>ascmd -S https://myserver/msolap90/msmdpump.dll -U myserver\olapadmin -P #1PWD -d "Adventure Works DW" -i backup.xmla -v backupfile="AdvWorks"

Notice that in the command-line example, https is used so that the password is encrypted when it is sent over the network to the remote server.

Scenario 3: Processing Multiple Partitions

In this scenario, you process multiple partitions using the ascmd command-line utility. You use scripting variables in the XMLA processing script (process.xmla) to specify the degree of parallelism, the database and cube names, and the process type. This XMLA script also demonstrates the use of comments in an XMLA script. When you call the process.xmla processing script from the ascmd command-line utility, you specify the server and database name, an output file for XMLA results, a trace file for trace events, the trace level, and the degree of parallelism within a batch bat (process.bat). The trace file will contain the same events and information as SQL Server Profiler would return if an administrator was monitoring the system during the processing.

process.xmla file:

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
   <Parallel maxparallel="$(MAXPARALLEL)">
   <!-- SEE ABOVE FOR HOW MANY PARITIONS PROCESSED IN PARALLEL -->
      <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Object>
            <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
            <CubeID>$(ASCMDDBNAME)</CubeID>
            <!-- Just so happens CubeID=DatabaseID=Database name :-) -->
            <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
            <PartitionID>Internet_Sales_2001</PartitionID>
         </Object>
         <Type>$(PROCESSTYPE)</Type>
         <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
      </Process>
      <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Object>
            <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
            <CubeID>$(ASCMDDBNAME)</CubeID>
            <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
            <PartitionID>Internet_Sales_2002</PartitionID>
         </Object>
         <Type>$(PROCESSTYPE)</Type>
         <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
      </Process>
      <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Object>
            <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
            <CubeID>$(ASCMDDBNAME)</CubeID>
            <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
            <PartitionID>Internet_Sales_2004</PartitionID>
         </Object>
         <Type>$(PROCESSTYPE)</Type>
         <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
      </Process>
      <Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <Object>
            <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
            <CubeID>$(ASCMDDBNAME)</CubeID>
            <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
            <PartitionID>Internet_Sales_2003</PartitionID>
         </Object>
         <Type>$(PROCESSTYPE)</Type>
         <WriteBackTableCreation>UseExisting</WriteBackTableCreation>
      </Process>
   </Parallel>
</Batch>

process.bat file:

@echo off
call :generate-timestamp
ascmd -S myserver -d "Adventure Works DW" -i process.xmla
         -o process.xml -T process-%timestamp%.csv -Tl medium 
         -v maxparallel=4 processtype=ProcessFull
if ERRORLEVEL 1 goto errseen
goto :EOF
:errseen
echo ** Error seen in processing
goto :EOF

:generate-timestamp
set now_date=%date%
set now_time=%time%
set now_Year=%now_date:~10,4%
set now_Month=%now_date:~4,2%
set now_Day=%now_date:~7,2%
set now_Hour=%now_time:~0,2%
set now_Min=%now_time:~3,2%
if "%now_Hour:~0,1%"==" " set now_Hour=0%now_Hour:~1,1%
set timestamp=%now_year%%now_month%%now_day%_%now_hour%%now_min%
goto :EOF

Notice that the batch file uses a timestamp in the output file so that multiple runs can be recorded at the same time.

Scenario 4: Creating a New Database on a Server

In this scenario, you use the ascmd command-line utility to call an XMLA script file (create.xmla) to create a new database on a server. The database name is defined in the XMLA script using a user-defined scripting variable, and value for this variable is defined at the command line using the –v parameter.

create.xmla file:

The file was created from SQL Server Management Studio. To create your own file, right-click the database and from the Script menu, click Create.

<Create xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
      <ObjectDefinition>
            <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                  <ID>$(dbname)</ID>
                  <Name>$(dbname)</Name>
                  <Description>A Unified Dimensional Model that encompasses the Adventure Works data warehouse.</Description>
                  <Language>1033</Language>
                  <Collation>Latin1_General_CI_AS</Collation>
                  <DataSourceImpersonationInfo>
                     <ImpersonationMode>Default</ImpersonationMode>
                  </DataSourceImpersonationInfo>
                  <Dimensions>
                        <Dimension>
                              <ID>Dim Promotion</ID>
                              <Name>Promotion</Name>
                              <Annotations>
 . . .

Command-line example:

C:\>ascmd -S myserver -i create.xmla -v dbname="My Adventure Works DW"

In the previous XMLA script, you could also use scripting variables to configure objects such as the connect string to a data source, the server and database name that are used in the data source, or field names in the data source view.

Scenario 5: Creating a Cache Warmer Application

In this scenario, you use a batch file (cache_warmer.bat) to call the ascmd command-line utility to call several MDX queries that warm the Analysis Services data cache. For example, you might call this batch file by using SQL Server Agent daily at 2:00 A.M. or after your night batch load. In the batch file, you set environment variables for the server, database, and cube names. Because the server and database names specified as environment variables exactly match the names of system-reserved scripting variables, they become the default values for the –S and –d command-line parameters. The user-defined scripting variable for the cube name is used in all the MDX queries.

query1.mdx file:

Files: query1.mdx to query6.mdx in the format of query1.txt

<Statement>
SELECT [Measures].[Internet Sales Amount] ON COLUMNS
FROM $(cube)
WHERE [Customer].[Country].&amp;[United States]
</Statement>

Create additional query files by replacing [United States] with the other countries in Adventure Works: [Australia], [Canada], [France], [Germany], or [United Kingdom].

cache_warmer.bat file:

set ascmdserver=myserver
set ascmddbname=Adventure Works DW
set cube=[Adventure Works]

set QUERYDIR=..\queries
set OUTPUTDIR=..\queries
echo -------------------------
set f=
for %%f in (%QUERYDIR%\*.mdx) do (
    call :query %%f
            if ERRORLEVEL 1 goto :EOF
)
echo -------------------------
echo Done.
goto :EOF

:query
echo Query: %1
echo ---------
ascmd -T %OUTPUTDIR%\querylog.txt -Tl duration 
         -Tf text -o %OUTPUTDIR%\%~n1.xml -i %1
echo Errorlevel: %ERRORLEVEL%
echo -------------------------
if ERRORLEVEL 1 goto :errseen
goto :EOF

:errseen
echo -------------------------
echo   ******
echo   ****** ERROR SEEN ******
echo   ******   Exiting    ******
goto :EOF

Scenario 6: Creating a Validation Procedure

In this scenario, you use the ascmd command-line utility to call several MDX query files (similar to the previous scenario) at the end of a nightly ETL run. You use the –Tl duration parameter to record the duration of each MDX query into a trace file together with directing the MDX script output to a nul file (–oNUL). You could also use the –Tl duration parameter together with recording the execution results into a trace log. Using the ascmd command-line utility in this manner lets you track the length of time required for each MDX query, and to compare these results daily to ensure that values in the same range are being returned. If duration results for a given day are significantly out of range, this might indicate that the results of the ETL run have to be backed out.

Command-line example:

C:\>ascmd -i %queryfile% -o NUL -T querylog.csv -Tl duration

Scenario 7: Automating the Building and Training of a Data Mining Model

In this scenario, you use the ascmd command-line utility to call a series of DMX statement as follows:

A DMX statement that creates a mining structure (Bike Buyer Structure.DMX) and uses the environment variables to set the server and database names.
A DMX statement (Clustering_Model.dmx) that adds a clustering mining model to the structure.
A DMX statement (DT_Model.dmx) that adds a decision tree mining model to the structure.
A DMX statement (Process Bike Buyer Structure.dmx) to process the mining structure and mining models.

When you have the mining structure in place, you can use the ascmd command-line utility to call several DMX statements that query the mining structure using different mining models.

Create the mining structure

Bike Buyer Structure.dmx file:

<Statement>
CREATE MINING STRUCTURE [Bike Buyer]
(
    [Customer Key] LONG KEY,
    [Age]LONG DISCRETIZED(Automatic,10),
    [Bike Buyer] LONG DISCRETE,
    [Commute Distance] TEXT DISCRETE,
    [Education] TEXT DISCRETE,
    [Gender] TEXT DISCRETE,
    [House Owner Flag] TEXT DISCRETE,
    [Marital Status] TEXT DISCRETE,
    [Number Cars Owned]LONG DISCRETE,
    [Number Children At Home]LONG DISCRETE,
    [Occupation] TEXT DISCRETE,
    [Region] TEXT DISCRETE,
    [Total Children]LONG DISCRETE,
    [Yearly Income] DOUBLE CONTINUOUS
)
</Statement>

Command-line example:

C:\>set ascmdserver=myserver

C:\>set ascmddbname=Adventure Works DW

C:\>ascmd -i "Bike Buyer Structure.dmx"

Add a clustering mining model to the structure

Clustering_Model.dmx file:

<Statement>
ALTER MINING STRUCTURE [Bike Buyer]
ADD MINING MODEL [Clustering]
USING Microsoft_Clustering 
</Statement>

Command-line example:

C:\>ascmd -i "Clustering_Model.dmx"

Add a decision tree mining model to the structure

DT_Model.dmx file

<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>

Command-line example:

C:\>ascmd -i "DT_Model.dmx"

Process the mining structure and mining models

Process Bike Buyer Structure.dmx file:

<Statement>
INSERT INTO MINING STRUCTURE [Bike Buyer]
(
    [Customer Key],
    [Age],
    [Bike Buyer],
    [Commute Distance],
    [Education],
    [Gender],
    [House Owner Flag],
    [Marital Status],
    [Number Cars Owned],
    [Number Children At Home],
    [Occupation],
    [Region],
    [Total Children],
    [Yearly Income]
)
OPENQUERY([$(ASCMDDBNAME)],
    'SELECT CustomerKey, Age, BikeBuyer,
             CommuteDistance,EnglishEducation,
             Gender,HouseOwnerFlag,MaritalStatus,
             NumberCarsOwned,NumberChildrenAtHome, 
             EnglishOccupation,Region,TotalChildren,
             YearlyIncome 
      FROM dbo.vTargetMail')
</Statement>

Command-line example:

C:\>ascmd -i "DT_Model.dmx"

Query the structure using the decision tree mining model

SELECT_DRILLTHROUGH.dmx file:

<Statement>
SELECT * 
FROM [Decision Tree].CASES
</Statement>

BATCH_PREDICTION.dmx file:

<Statement>
SELECT
   TOP 10
   t.[LastName],
   t.[FirstName],
   [Decision Tree].[Bike Buyer],
   PredictProbability([Bike Buyer])
From
   [Decision Tree]
PREDICTION JOIN
 OPENQUERY([$(ASCMDDBNAME)],
      'SELECT
         [LastName],
         [FirstName],
         [MaritalStatus],
         [Gender],
         [YearlyIncome],
         [TotalChildren],
         [NumberChildrenAtHome],
         [Education],
         [Occupation],
         [HouseOwnerFlag],
         [NumberCarsOwned]
      FROM
         [dbo].[ProspectiveBuyer]
      ') AS t
ON
   [Decision Tree].[Marital Status] = t.[MaritalStatus] AND
   [Decision Tree].[Gender] = t.[Gender] AND
   [Decision Tree].[Yearly Income] = t.[YearlyIncome] AND
   [Decision Tree].[Total Children] = t.[TotalChildren] AND
   [Decision Tree].[Number Children At Home] = t.[NumberChildrenAtHome] AND
   [Decision Tree].[Education] = t.[Education] AND
   [Decision Tree].[Occupation] = t.[Occupation] AND
   [Decision Tree].[House Owner Flag] = t.[HouseOwnerFlag] AND
   [Decision Tree].[Number Cars Owned] = t.[NumberCarsOwned]
WHERE [Decision Tree].[Bike Buyer] =1
ORDER BY PredictProbability([Bike Buyer]) DESC
</Statement>

SELECT_DISCRETE.dmx file:

<Statement>
SELECT DISTINCT [Bike Buyer] 
FROM [Decision Tree]
</Statement>

Command-line example:

C:\>ascmd -i SELECT_DRILLTHROUGH.dmx

C:\>ascmd -i BATCH_PERDICTION.dmx

C:\>ascmd -i SELECT_DISCRETE.dmx

Scenario 8: Clearing the Analysis Services Data Cache

In this scenario, you use the ascmd command-line utility to call an XMLA script (ClearCache.xmla) that clears the Analysis Services data cache between performance runs when doing performance studies. The ClearCache.xmla file contains scripting variables for the database and cube names. This XMLA script is called by a batch file (ClearCache.bat) that specifies the server and instance name, the database name, the input file name, the output file name, and the cube name.

ClearCache.xmla file:

<Batch xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
   <ClearCache>
               <Object>
                      <DatabaseID>$(ASCMDDBNAME)</DatabaseID>
                      <CubeID>$(CUBE)</CubeID>
               </Object>
       </ClearCache>
</Batch>

ClearCache.bat file:

@echo off
ascmd -S myserver\myinstance -d "Adventure Works DW" -i ClearCache.xmla
         -o ClearCache.xml -v cube="Adventure Works DW"

if ERRORLEVEL 1 goto :errseen
goto :EOF

:errseen
echo **** Error seen ****
echo ********************
goto :EOF

Scenario 9: Determining who is currently connected to your server

In this scenario, you use the ascmd command-line utility to retrieve the list of active connections on the server. An application might use this information to delay processing until specific users are disconnected, or to send an e-mail to the operators if anyone has a current connection (other than the connection for the nightly batch run).

connections.xmla file:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>DISCOVER_CONNECTIONS</RequestType>
   <Restrictions />
   <Properties>
      <PropertyList>
         <Content>Data</Content>     <!-- Only the data; no schema -->
      </PropertyList>
   </Properties>
</Discover>

Command-line example:

C:\>ascmd -S myserver -i connections.xmla -o current_connections.xml

Scenario 10: Is a partition processed and, if so, when was it last processed

In this scenario, you use the ascmd command-line utility to determine if a partition has been processed and when it was processed. This information can easily be retrieved because it is stored as a property of the partition object. Thus a DISCOVER_XML_METADATA request can be used to retrieve this information.

connections.xmla file:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
   <RequestType>DISCOVER_XML_METADATA</RequestType>
   <Restrictions>
      <RestrictionList>
        <DatabaseID>$(DatabaseID)</DatabaseID>
        <CubeID>$(CubeID)</CubeID>
        <MeasureGroupID>$(MeasureGroupID)</MeasureGroupID>
        <PartitionID>$(PartitionID)</PartitionID>
      <!-- Ask for just this object referenced -->
      <ObjectExpansion>ReferenceOnly</ObjectExpansion>
      </RestrictionList>
   </Restrictions>
   <Properties>
      <PropertyList>
         <Content>Data</Content>     <!-- Only the data; no schema -->
      </PropertyList>
   </Properties>
</Discover>

Scenario 11: Using the GO Command to Perform a Writeback Operation

In this scenario, you use the ascmd command-line utility to break the writeback into two pieces: change the data and then commit it. Writeback requires the use of the GO command because the two MDX statements required for a writeback operation (the Update Cube and the Commit Transaction statements) must be issued one after the other within the same transaction. MDX does not support issuing them within the same batch.

For this scenario, you need to modify the Adventure Works DW database to support writeback. The existing database does not currently have an example of a cube that supports writeback. To create and verify a cube that supports writeback, perform the following steps:

To define a new cube called “Writeback”
Open Business Intelligence Development Studio.
On the File menu, point to Open then click Analysis Services Database.
In the Connect to Database dialog box, type your server name in the Server text box, select the Adventure Works DW database in the Database list, and then click OK.
In the Solution Explorer pane, right-click Cubes and then click New Cube.
In the Cube Wizard, click Next on the Welcome to the Cube Wizard page, select Build the cube using a data source, clear the Auto build check box, and then click Next.
Select Adventure Works DW in the Available data source views list on the Select Data Source View page and then click Next.
On the Identify Fact and Dimension Tables page, select the Fact check box for the FactSalesQuota table and the Dimension check box for the dbo.DimTime and dbo.DimEmployee table, and then click Next.
dbo.DimTime (dimension is called Date)dbo.DimEmployeeFactSalesQuota (just using the “Sales Amount Quota” measure)
On the Review Shared Dimensions page, select Date and Employee in the Available Dimensions list, click > to add these dimensions to the Cube dimensions list and then click Next.
On the Select Measures page, clear the Fact Sales Quota check box, select the Sales Amount Quota check box, and then click Next.
On the Completing the Wizard page, change the cube name to Writeback and then click Finish.
To enable writeback for the Fact Sales Quota measure group
In cube designer, select the Partitions tab.
Right-click Fact Sales Quota partition in the partition list and then click Writeback Settings.
In the Enable Writeback - Fact Sales Quota dialog box, review the default writeback table name and then click OK to create this table and enable writeback for this partition.
Notice that two partitions now appear: one for the fact table; one for the writeback table.
To process the Writeback cube
Right-click Writeback in the Cubes node in Solution Explorer and click Process.
Click Yes when prompted to save changes.
In the Process Cube - Writeback dialog box, click Run.
If you expand the processing commands, you will see the CREATE TABLE SQL statement used to create the writeback relational table.
When processing completes, verify that the process succeeded in the Status box, and then click Close.
Click Close again to close the Process Partition - WriteTable_Fact Sales Quota dialog box.
Close Business Intelligence Development Studio.
To verify that writeback is working
Open SQL Server Management Studio.
Connect to your server, and then in Object Explorer, expand Databases, right-click Adventure Works DW, point to New Query and then click MDX.

In the MDX query window, execute the following MDX query to return the current sales quote for Q1FY2002 and Stephen Y. Jiang:

/* Employee 272 is [Stephen Y. Jiang]*/
SELECT [Measures].[Sales Amount Quota] ON COLUMNS
FROM [Writeback]
WHERE ([Employee].[Employee].[Stephen Y. Jiang],[Date].[Calendar].[Calendar Quarter].[Q1 CY 2002])

Modify the cell to return $2,200 by issuing the following MDX statement:

UPDATE CUBE [Writeback]
SET ([Employee].[Employee].[Stephen Y. Jiang],
[Date].[Calendar].[Calendar Quarter].[Q1 CY 2002]) = 2200

Commit the transaction by executing the following MDX statement:
```
COMMIT TRANSACTION
```
At this point, you can examine the “dbo.WriteTable_Fact Sales Quota” table in the Adventure Works DW relational database to see what writeback has actually done for the cell. If you do, you will notice that it is the delta (-88800) that is written to this relational table. The original fact table is unchanged.

writeback.mdx file:

/* What is the existing value? */
SELECT [Measures].[Sales Amount Quota] ON COLUMNS
FROM [Writeback]
WHERE ([Employee].[Employee].&[272],
[Date].[Calendar].[Calendar Quarter].&[2002]&[1])
GO
/* Update the cube with a new value */
UPDATE CUBE [Writeback]
SET ([Employee].[Employee].&[272],
[Date].[Calendar].[Calendar Quarter].&[2002]&[1]) = 33000 /* some different value */
GO
/* Commit it */
Commit Transaction
GO
/* See what the updated value is */
SELECT [Measures].[Sales Amount Quota] ON COLUMNS
FROM [Writeback]
WHERE ([Employee].[Employee].&[272],
[Date].[Calendar].[Calendar Quarter].&[2002]&[1])
GO

Command-line example:

C:\>ascmd -S myserver -d "Adventure Works DW" -i writeback.mdx -o writeback_result.xml -v cube="[Writeback]"

Writeback_result.xml:

<multiple-batches>
   <return xmlns="urn:schemas-microsoft-com:xml-analysis">
      <root xmlns= . . .>
         <...metadata about the result set...>
<CellData xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset">
  <Cell CellOrdinal="0">
     <Value xsi:type="xsd:double" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">0</Value> 
     <FmtValue>2200</FmtValue> 
  </Cell>
</CellData>
      </root>
   </return>
   <return xmlns="urn:schemas-microsoft-com:xml-analysis">
      <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" /> 
   </return>
   <return xmlns="urn:schemas-microsoft-com:xml-analysis">
      <root xmlns="urn:schemas-microsoft-com:xml-analysis:empty" /> 
   </return>
   <return xmlns="urn:schemas-microsoft-com:xml-analysis">
      <root xmlns= . . .>
         <...metadata about the result set...>
<CellData xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset">
  <Cell CellOrdinal="0">
     <Value xsi:type="xsd:double" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">0</Value> 
     <FmtValue>33000</FmtValue> 
  </Cell>
</CellData>
      </root>
   </return>
</multiple-batches>

Notice that there are two empty result sets in the middle for the UPDATE CUBE statement and the COMMIT TRANSACTION statement.

Release	History
12 December 2006	Changed content: Documented a new feature - multiple batch support. Added example demonstrating the use of the new feature.
17 July 2006	Changed content: Documented two new features - custom XMLA requests and auto detection of command type in the input stream. Added updated examples demonstrating the use of the new features.

Share via

Readme For Ascmd Command-line Utility Sample

Scenarios

Processing a Partition from a Third-Party Tool

Backing Up an OLAP Database from a Third-Party Tool

Using XMLA During an Installation

Languages

Prerequisites

Arguments

Key Encryption and Compiling the Sample

To generate a strong name key file

Using Scripting and Environment Variables

System-Reserved Scripting Variables

Using System-Reserved Scripting Variables at the Command Prompt

Using System-Reserved Scripting Variables in Scripts, Queries, or Statements

process.xmla (simplified)

User-Defined Scripting Variables

Using MDX, XMLA, and DMX in Input Files

MDX Example:

DMX Example:

XMLA Example:

Discover Example:

Execute Example:

ASCMD Scenario Examples

Scenario 1: Querying an Analysis Services Cube

query.mdx file:

Command-line example:

Scenario 2: Backing Up a Database in an Untrusted Domain

backup.xmla file:

Command-line example:

Scenario 3: Processing Multiple Partitions

process.xmla file:

process.bat file:

Scenario 4: Creating a New Database on a Server

create.xmla file:

Command-line example:

Scenario 5: Creating a Cache Warmer Application

query1.mdx file:

cache_warmer.bat file:

Scenario 6: Creating a Validation Procedure

Command-line example:

Scenario 7: Automating the Building and Training of a Data Mining Model

Create the mining structure

Bike Buyer Structure.dmx file:

Add a clustering mining model to the structure

Clustering_Model.dmx file:

Add a decision tree mining model to the structure

DT_Model.dmx file

Command-line example:

Process the mining structure and mining models

Process Bike Buyer Structure.dmx file:

Command-line example:

Query the structure using the decision tree mining model

SELECT_DRILLTHROUGH.dmx file:

BATCH_PREDICTION.dmx file:

SELECT_DISCRETE.dmx file:

Command-line example:

Scenario 8: Clearing the Analysis Services Data Cache

ClearCache.xmla file:

ClearCache.bat file:

Scenario 9: Determining who is currently connected to your server

connections.xmla file:

Command-line example:

Scenario 10: Is a partition processed and, if so, when was it last processed

connections.xmla file:

Scenario 11: Using the GO Command to Perform a Writeback Operation

writeback.mdx file:

Command-line example:

Writeback_result.xml:

See Also

Other Resources

Help and Information

Additional resources