Analysis Services Execute DDL Task

Article
02/28/2023

Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory

The Analysis Services Execute DDL task runs data definition language (DDL) statements that can create, drop, or alter mining models and multidimensional objects such as cubes and dimensions. For example, a DDL statement can create a partition in the Adventure Works cube, or delete a dimension in Adventure Works DW Multidimensional 2012, the sample Analysis Services database included in SQL Server.

The Analysis Services Execute DDL task uses an Analysis Services connection manager to connect to an instance of Analysis Services or an Analysis Services project. For more information, see Analysis Services Connection Manager.

Integration Services includes a number of tasks that perform business intelligence operations, such as processing analytic objects and running data mining prediction queries.

For more information about related business intelligence tasks, click one of the following topics:

DDL Statements

The DDL statements are represented as statements in Analysis Services Scripting Language (ASSL), and framed in an XML for Analysis (XMLA) command.

ASSL is used to define and describe an instance of Analysis Services and the databases and database objects it contains. For more information, see Analysis Services Scripting Language (ASSL for XMLA).
XMLA is a command language that is used to send action commands, such as Create, Alter, or Process, to an instance of Analysis Services. For more information, see XML for Analysis (XMLA) Reference.

If the DDL code is stored in a separate file, the Analysis Services Execute DDL task uses a File connection manager to specify the path of the file. For more information, see File Connection Manager.

Because DDL statements can contain passwords and other sensitive information, a package that contains one or more Analysis Services Execute DDL tasks should use the package protection level EncryptAllWithUserKey or EncryptAllWithPassword. For more information, see Integration Services (SSIS) Packages.

DDL Examples

The following three DDL statements were generated by scripting objects in the Adventure Works DW Multidimensional 2012, the Analysis Services database included in SQL Server.

The following DDL statement deletes the Promotion dimension.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <Object>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
        <DimensionID>Dim Promotion</DimensionID>  
    </Object>  
</Delete>

The following DDL statement processes the Adventure Works DW Multidimensional 2012 cube.

<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 Multidimensional 2012</DatabaseID>  
      </Object>  
      <Type>ProcessFull</Type>  
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
    </Process>  
  </Parallel>  
</Batch>

The following DDL statement creates the Forecasting mining model.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <ParentObject>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
        <MiningStructureID>Forecasting</MiningStructureID>  
    </ParentObject>  
    <ObjectDefinition>  
        <MiningModel xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
            <ID>Forecasting</ID>  
            <Name>Forecasting</Name>  
            <Algorithm>Microsoft_Time_Series</Algorithm>  
            <AlgorithmParameters>  
                <AlgorithmParameter>  
                    <Name>PERIODICITY_HINT</Name>  
                    <Value xsi:type="xsd:string">{12}</Value>  
                </AlgorithmParameter>  
            </AlgorithmParameters>  
            <Columns>  
                <Column>  
                    <ID>Amount</ID>  
                    <Name>Amount</Name>  
                    <SourceColumnID>Amount</SourceColumnID>  
                    <Usage>Predict</Usage>  
                </Column>  
                <Column>  
                    <ID>Model Region</ID>  
                    <Name>Model Region</Name>  
                    <SourceColumnID>Model Region</SourceColumnID>  
                    <Usage>Key</Usage>  
                </Column>  
                <Column>  
                    <ID>Quantity</ID>  
                    <Name>Quantity</Name>  
                    <SourceColumnID>Quantity</SourceColumnID>  
                    <Usage>Predict</Usage>  
                </Column>  
                <Column>  
                    <ID>Time Index</ID>  
                    <Name>Time Index</Name>  
                    <SourceColumnID>Time Index</SourceColumnID>  
                    <Usage>Key</Usage>  
                </Column>  
            </Columns>  
            <Collation>Latin1_General_CS_AS_KS</Collation>  
        </MiningModel>  
    </ObjectDefinition>  
</Create>

The following three DDL statements were generated by scripting objects in the Adventure Works DW Multidimensional 2012, the Analysis Services database included in SQL Server.

The following DDL statement deletes the Promotion dimension.

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <Object>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
        <DimensionID>Dim Promotion</DimensionID>  
    </Object>  
</Delete>

The following DDL statement processes the Adventure Works DW Multidimensional 2012 cube.

<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 Multidimensional 2012</DatabaseID>  
      </Object>  
      <Type>ProcessFull</Type>  
      <WriteBackTableCreation>UseExisting</WriteBackTableCreation>  
    </Process>  
  </Parallel>  
</Batch>

The following DDL statement creates the Forecasting mining model.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">  
    <ParentObject>  
        <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>  
        <MiningStructureID>Forecasting</MiningStructureID>  
    </ParentObject>  
    <ObjectDefinition>  
        <MiningModel xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">  
            <ID>Forecasting</ID>  
            <Name>Forecasting</Name>  
            <Algorithm>Microsoft_Time_Series</Algorithm>  
            <AlgorithmParameters>  
                <AlgorithmParameter>  
                    <Name>PERIODICITY_HINT</Name>  
                    <Value xsi:type="xsd:string">{12}</Value>  
                </AlgorithmParameter>  
            </AlgorithmParameters>  
            <Columns>  
                <Column>  
                    <ID>Amount</ID>  
                    <Name>Amount</Name>  
                    <SourceColumnID>Amount</SourceColumnID>  
                    <Usage>Predict</Usage>  
                </Column>  
                <Column>  
                    <ID>Model Region</ID>  
                    <Name>Model Region</Name>  
                    <SourceColumnID>Model Region</SourceColumnID>  
                    <Usage>Key</Usage>  
                </Column>  
                <Column>  
                    <ID>Quantity</ID>  
                    <Name>Quantity</Name>  
                    <SourceColumnID>Quantity</SourceColumnID>  
                    <Usage>Predict</Usage>  
                </Column>  
                <Column>  
                    <ID>Time Index</ID>  
                    <Name>Time Index</Name>  
                    <SourceColumnID>Time Index</SourceColumnID>  
                    <Usage>Key</Usage>  
                </Column>  
            </Columns>  
            <Collation>Latin1_General_CS_AS_KS</Collation>  
        </MiningModel>  
    </ObjectDefinition>  
</Create>

Configuration of the Analysis Services Execute DDL Task

You can set properties through SSIS Designer or programmatically.

For more information about the properties that you can set in SSIS Designer, click the following topic:

Expressions Page

For more information about setting these properties in SSIS Designer, click the following topic:

Set the Properties of a Task or Container

Programmatic Configuration of the Analysis Services Execute DDL Task

For more information about programmatically setting these properties, click the following topic:

ASExecuteDDLTask

Analysis Services Execute DDL Task Editor (General Page)

Use the General page of the Analysis Services Execute DDL Task Editor dialog box to name and describe the Analysis Services Execute DDL task.

Options

Name
Provide a unique name for the Analysis Services Execute DDL task. This name is used as the label in the task icon.

Note

Task names must be unique within a package.

Description
Type a description of the Analysis Services Execute DDL task.

Analysis Services Execute DDL Task Editor (DDL Page)

Use the DDL page of the Analysis Services Execute DDL Task Editor dialog box to specify a connection to an Analysis Services project or an Analysis Services database and to provide information about the source of data definition language (DDL) statements.

Static Options

Connection
Select an Analysis Services project or an Analysis Services connection manager in the list, or click <New connection...> and use the Add Analysis Services Connection Manager dialog box to create a new connection.

SourceType
Specify the source type of the DDL statements. This property has the options listed in the following table:

Value	Description
Direct Input	Set the source to the DDL statement stored in the SourceDirect text box. Selecting this value displays the dynamic options in the following section.
File Connection	Set the source to a file that contains the DDL statement. Selecting this value displays the dynamic options in the following section.
Variable	Set the source to a variable. Selecting this value displays the dynamic options in the following section.

Dynamic Options

SourceType = Direct Input

Source
Type the DDL statements or click the ellipsis (...) and then type the statements in the DDL Statements dialog box.

SourceType = File Connection

Source
Select a File connection in the list, or click <New connection...> and use the File Connection Manager dialog box to create a new connection.

Related Topics: File Connection Manager

SourceType = Variable

Source
Select a variable in the list, or click <New variable...> and use the Add Variable dialog box to create a new variable.

Related Topics: Integration Services (SSIS) Variables

Share via