Invoke-ASCmd

Enables a database administrator to execute an XMLA script, Multidimensional Expressions (MDX) query, or DataMining Extensions (DMX) statement against an instance of Microsoft SQL Server Analysis Services.

Enables a database administrator to execute an XMLA script, Multidimensional Expressions (MDX) query, or Data Mining Extensions (DMX) statement against an instance of Microsoft SQL Server Analysis Services. The cmdlets execution of these queries or scripts within input files.The script that is passed is an XAML command and following commands are supported. 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.

Parameters
-Query <string>

  • This value is required
  • Accepts pipeline input ByValue

Specifies the actual script, query, or statement directly on the command line instead of in a file.

-InputFile <string>

  • This value is required

Identifies the file that contains the XMLA script, MDX query, or DMX statement.You must specify a value for either the -InputFile or the -Query parameter when you use the Invoke-AsCmd. If you specify no -InputFile or -Query parameter, or specify both of these parameters, an error is generated.

-Server <string>

  • Default value is localhost

Specifies the Analysis Services instance to which the cmdlet will connect and execute. If not server name is specified, the cmdlet will try to connect to localhost. For default instances, only specify the computer name: “MyComputer”. For named instances, use the format “ComputerNameInstanceName” For IIS Server, use the format http[s]://server[:port]/virtualdirectory/msmdpump.dll.

-Database <string>

Specifies the database against which an MDX query or DMX statement will execute. The database parameter is ignored when the cmdlet executes an XMLA script, because the database name is embedded in the XMLA script.

-Credentials <PSCredential>

If this parameter is specified, the user name and password passed will be used to connect to specified Analysis Server instance. If no credentials are specified default windows account of the user who is running the tool will be used.

-ConnectionTimeout <int>

  • Default value is 30

Specifies the number of seconds before the connection to the Analysis Services instance times out. The timeout value must be an integer between 0 and 65534. If 0 is specified, connection attempts do not time out.

-QueryTimeout <int>

  • Default value is 30

Specifies the number of seconds before the queries time out. If a timeout value is not specified, the queries do not time out. The timeout must be an integer between 1 and 65535.

-Variable <string[]>

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-quotation marks. Use a PowerShell array to specify multiple variables and their values.

-TraceFile <string>

Identifies a file that receives Analysis Services trace events while 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 -TraceLevel:Duration and -TraceLevel:DurationResult 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.

-TraceFileFormat <string>

  • Default value is csv

Specifies the file format for the -TraceFile parameter (if this parameter is specified). The available options are text or csv. The default value is “csv”.

csv

text

-TraceFileDelimiter <string>

Specifies a single character as the trace file delimiter when you specify csv as the format for the trace file that use the -TraceFileFormat parameter. Default is | (pipe, or vertical bar).

-TraceTimeout <int>

  • Default value is 5

Specifies the number of seconds the Analysis Services engine waits before ending the trace (if you specify the -TraceFile 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.

-TraceLevel <TraceLevelOption>

  • Default value is High

Specifies what data is collected and recorded in the trace file. Possible values are High, Medium, Low, Duration, DurationResult.

High

Medium

Low

Duration

DurationResult

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug,ErrorAction, ErrorVariable, WarningAction, WarningVariable,OutBuffer, PipelineVariable, and OutVariable.

Inputs

PSObject

Outputs

string

Examples
  1. The above XMLA script displays the list of databases present in local Analysis Server:
    C:PS> Invoke-ASCmd -Server:localhost -Query:"DBSCHEMA_CATALOGS"
    
  2. The above MDX statement displays the bottom 10 customers with the lowest sales in 2008 that were not null:
    C:PS> Invoke-ASCmd -Database:"Adventure Works DW 2008R2" -Query:"SELECT {[Measures].[Internet Sales Amount]} ON COLUMNS, BOTTOMCOUNT(NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer]), ( [Measures].[Internet Sales Amount] ) ), 10, ( [Measures].[Internet Sales Amount] )) ON ROWS FROM [Adventure Works] WHERE ( [Date].[Calendar].[Calendar Year].&[2008] )"
    
  3. The above DMX statement returns information about the mining model schema rowset for the Forecasting mining model

    :

    C:PS> Invoke-ASCmd -Database:"Adventure Works DW 2008R2" -Query:"SELECT MODEL_CATALOG, MODEL_NAME, ATTRIBUTE_NAME, NODE_NAME FROM [Forecasting].CONTENT"
    

    The above DMX statement returns information about the mining model schema rowset for the Forecasting mining model

  4. The above Discover XMLA query returns available data sources for the Analysis server and information required to connect to them:
    C:PS> Invoke-ASCmd -Database:"Adventure Works DW 2008R2" -Query:"DISCOVER_DATASOURCESver>"
    
  5. Retrieves the list of active connections on the server:
    C:PS> Invoke-ASCmd -InputFile:"C:MyFolderDiscoverConnections.xmla"
    

    In the above example DiscoverConnections.xmla should have the following XMLA script.

    DISCOVER_CONNECTIONSData

Additional Notes