In a recent article dedicated to Integration Services on this forum, we started exploring their ability to interact with other Business Intelligence-related features of the SQL Server 2008 R2 product line, focusing in particular on Analysis Services. After describing the most elementary principles of this technology (which you might want to review before reading further), we provided a comprehensive overview of Analysis Services Processing Task. Unfortunately, despite considerable range of configurable options, its applicability is relatively limited, allowing you essentially only to update Analysis Services objects, typically following changes to the underlying data they reference. Now it is time to present its more versatile sibling known as Analysis Services Execute DDL Task.
The term DDL designates Data Definition Language, which implies an action involving creating data structures. In the context of Analysis Services Execute DDL Task, this is a bit of a misnomer because its capabilities are much more diverse, extending to such areas as backups, restores, deletions, modifications, or processing (which, in essence, mirrors the basic functionality facilitated by the Analysis Services Processing Task). This is accomplished by employing Analysis Services Scripting Language (ASSL), which, in turn, relies on three complementing mechanisms:
- Data Definition Language (DDL) protocol describes Analysis Services objects on which a desired operation will be performed, as documented in Analysis Services Scripting Language Reference.
- A command language provides specifics of such operations, referred to as XMLA (eXtensible Markup Language for Analysis), which you can become familiar with by reading through XML for Analysis Reference.
- Simple Object Access Protocol (SOAP), which facilitates communication with a target instance of Analysis Services by implementing Discover and Execute methods. The first extracts metadata of Analysis Services objects that constitutes their definition, while the latter triggers invocation of ASSL commands.
If you want to determine the Data Definition Language-based description of an arbitrary Analysis Services object, launch View Code command from its context-sensitive menu in the Solution Explorer of Business Intelligence Development Studio. Similarly, if your intention is to identify the format of an XMLA command to carry out a specific action on a given object, you can take advantage of capabilities built into SQL Server Management Studio. More specifically, after you connect to the target instance of Analysis Services and display its content in the Object Explorer window, you are able to create scripts for object-specific actions by using features incorporated into their graphical representation. For example, the Script Database entry included in the context-sensitive menu of the Analysis Services database contains three sub-menu options CREATE To, ALTER To, and DELETE To, allowing you to capture the corresponding XMLA syntax into a New Query Editor Window, File, or Clipboard (the same procedure applies to Data Sources, Data Source Views, Cubes, Dimensions, and Mining Structures). In the same manner, you can use the Script button in a toolbar of dialog boxes displayed after selecting Back Up…, Restore…, Detach…, Process, or Delete entries appearing in the context-sensitive menu of individual Analysis Services objects within Object Explorer.
Following this brief introduction into ASSL, let’s step through an example demonstrating sample use of Analysis Services Execute DDL Task. We will use for this purpose the Adventure Works DW 2008R2 database, available as a self-extracting executable for download from the CodePlex website (for more details regarding its deployment, refer to our previous article). Assuming that the local SQL Server 2008 R2 instance includes Analysis Services installation with the newly deployed sample database, launch Business Intelligence Development Studio and create a new project based on Integration Services template. Drag Analysis Services Execute DDL Task from the Toolbox onto the Designer surface and select Edit… command from its context-sensitive menu to launch its Editor window. Set Name and Description values in the General section to indicate the task’s purpose and switch to the next one labeled DDL. Start by defining a new Connection. Point to the target Analysis Services instance by adjusting Data Source and choose appropriate authentication method (alternatively, you have an option of leveraging a Business Intelligence Development Studio solution that contains a project based on the Analysis Services template, as described in our previous article). Use Test Connection button to verify that the target instance is accessible and confirm your changes by clicking on the OK command button. Next, you need to set the value of Source Type parameter, which determines the way of specifying ASSL command – Direct Input, File Connection, or Variable. Your choice affects format of the third parameter, which contains specifics of the location you designate. For the sake of simplicity, we will use Direct Input, although, in general, you would probably want to opt for one of the other two in order to make your package more flexible.
At this point, we need to construct an XML-formatted ASSL command that will trigger execution of an action we want to implement. Fortunately, as long as its target is an existing object, this is relatively easy to accomplish. As we have explained above, you can generate desired syntax by employing functionality incorporated into SQL Server Management Studio. For example, let’s assume that our goal is to trigger processing of the Account dimension. Once you right click on its visual representation in the Object Explorer (under Dimensions subfolder), you will be presented with a context sensitive menu containing Process entry. Selecting it will trigger display of Process Dimensions – Account dialog box, where you can customize processing options and dimension key errors configuration. All changes you make are reflected in the corresponding XML-based representation of the command appearing in the Query window displayed by clicking on Script button at the top of the page, which will take the form similar to the following:
<Batch >
<Parallel>
<Process xmlns_xsd=”http://www.w3.org/2001/XMLSchema” xmlns_xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns_ddl2=”http://schemas.microsoft.com/analysisservices/2003/engine/2″
xmlns_ddl2_2=”http://schemas.microsoft.com/analysisservices/2003/engine/2/2″
xmlns_ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100″
xmlns_ddl200=”http://schemas.microsoft.com/analysisservices/2010/engine/200″
xmlns_ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200″>
<Object>
<DatabaseID>Adventure Works DW 2008R2</DatabaseID>
<DimensionID>Dim Account</DimensionID>
</Object>
<Type>ProcessUpdate</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Simply copy its content to the DDL Statements window of the Analysis Services Execute DDL Task Editor, confirm your decision by clicking twice on the OK command button, and execute the package. You would follow the same procedure if you wanted to modify, delete, backup, or restore arbitrary object (creating new ones is a bit more challenging, since it requires defining their structure in the data definition language syntax). Obviously, creating a new Analysis Services object will require a bit more effort and getting more intimately familiar with the ASSL syntax (available, as mentioned earlier, at Analysis Services Scripting Language Reference).
In conclusion, Analysis Services Execute DDL Task offers significant benefit when automating repeatable maintenance tasks (such as processing or backup) of Analysis Services objects. You can configure them (as part of an SSIS package) in a manner that allows you to designate target objects via SSIS variables, which values are assigned dynamically (for example by a Script or SQL Query task), providing extra level of flexibility.
