X-XMLA: vii. Partition Management with XMLA

In the 7th article of this series, I will discuss how to manage your partitions with XMLA.   We will be focused on the partition structures as opposed to partition processing which was covered in the previous post.

Creating or altering a partition

I have already discussed object creation with XMLA in part 3 of this series.  Remember that when altering the partition you need to have all of the partition definition you wish to keep as well as what you are changing in the XMLA.  If you have a subset of the definition, SSAS will assume you want to remove the missing parts.

Adding a partition to an existing measure group

One area where a partition is different from other objects is that you can add a partition to an existing measure group.  This is often done to improve processing performance or to match the underlying data architecture (especially if table partitioning is already in place for the source tables).  Often the partitions are based on date and correspond to the date the data is loaded.  For instance, you may have separate partitions for each year.  So at the beginning of the next year you need to add a partition.  In this case, you create a new partition with a specific data source definition for that partition.

If the measure group only has one partition that is based on a single table, you may need to change the source definition so you can properly add a new partition.  There are two common ways to manage the data sources to support partitions.  The first is to create views that map to the partition structure in the cube and then the data source for each partition can point directly to a particular table or view.  The second way is to specify a query definition that usually points at one table and filters the data based on one or more keys.

In either case, the XMLA is constructed as a Create command with the ParentObject specifying the measure group that the partition will be added to.  The Object definition contains the ID, Name, Source and other properties that make up the partition.  The example below shows the definition for adding a partition with that covers the month of January using a filter in the QueryDefinition for the Source.

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>
        <CubeID>Adventure Works DW</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    </ParentObject>
    <ObjectDefinition>
        <Partition 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
            <ID>Internet_Sales_Jan2011</ID>
            <Name>Internet_Sales_Jan2011</Name>
            <Source xsi:type="QueryBinding">
                <DataSourceID>Adventure Works DW</DataSourceID>
                <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110101 AND 20110131</QueryDefinition>
            </Source>
            <StorageMode>Molap</StorageMode>
            <ProcessingMode>Regular</ProcessingMode>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <AggregationStorage>MolapOnly</AggregationStorage>
                <Source xsi:type="ProactiveCachingInheritedBinding">
                    <NotificationTechnique>Server</NotificationTechnique>
                </Source>
            </ProactiveCaching>
            <EstimatedRows>60398</EstimatedRows>
            <AggregationDesignID>Internet Sales</AggregationDesignID>
        </Partition>
    </ObjectDefinition>
</Create>

Merging partitions

In some cases, you will find the need to merge partitions.  In our example above we added a partition for the month of January.  However, as the your partition count becomes larger there is benefit to merging partitions into historical or archive partitions.    Or you may even decide that merging will help with maintenance or processing.  In either case, the logic is fairly simple.  You have one or more source partitions being merged into target partition.  The XMLA for this is pretty straightforward.

There are a couple of nuances to be aware of including the fact that the structure and the aggregation designs need to be the same to support merging.  If you plan to merge, you should take this into account when building partitions and applying aggregation designs.  Furthermore, if you use partition slices you will likely need to modify the target’s slice after the merge is account for the new data.

Here is the code required to merge two source partitions into a target partition.  The result will be one partition.

<MergePartitions xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Sources>
    <Source>
      <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
      <PartitionID>Internet_Sales_Jan2011</PartitionID>
    </Source>
    <Source>
        <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
        <CubeID>Adventure Works DW</CubeID>
        <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
        <PartitionID>Internet_Sales_Feb2011</PartitionID>
    </Source>
  </Sources>
  <Target>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales</PartitionID>
  </Target>
</MergePartitions>

As you can see in the example above, all that you need to specify is the full address for each partition and identify which are the sources and which one is the target.

Setting the Slice Property

Before digging into the XMLA a couple of notes on the data slice in cubes.  First, slices are not required for MOLAP partitions, however, they can help with query performance.  While in current versions of SSAS (2005 and later), the system will determine the slice based on the data, it is not always accurate.  Keep in mind that a slice does not affect processing or the source data for a partition.  That is determined by the Source attribute and related definition.

Refer to the following blog posts Jesse Orosz and Mosha Pasumanky for more information as the details around the value of assigning scope which is beyond the scope of this post:

http://jesseorosz.wordpress.com/?s=slices

http://sqlblog.com/blogs/mosha/archive/2008/10/14/get-most-out-of-partition-slices.aspx

Assuming you need to set the slice or if you need to change it due to a merge here is the XMLA to alter a partition’s slice property.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales_Feb2011</PartitionID>
  </Object>
  <ObjectDefinition>
    <Partition 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <ID>Internet_Sales_Feb2011</ID>
      <Name>Internet_Sales_Feb2011</Name>
      <Source xsi:type="QueryBinding">
        <DataSourceID>Adventure Works DW</DataSourceID>
        <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110102 AND 20110228</QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <Slice>{[Date].[Calendar].[Month].&amp;[2011]&amp;[2]}</Slice>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <AggregationStorage>MolapOnly</AggregationStorage>
        <Source xsi:type="ProactiveCachingInheritedBinding">
          <NotificationTechnique>Server</NotificationTechnique>
        </Source>
      </ProactiveCaching>
      <EstimatedRows>60398</EstimatedRows>
      <AggregationDesignID>Internet Sales</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Alter>

If we were to merge the January and February partitions we created previously, we could change the slice definition to include both months.

<Slice>{[Date].[Calendar].[Month].&amp;[2011]&amp;[1],[Date].[Calendar].[Month].&amp;[2011]&amp;[2]}</Slice>

Setting the Storage Location

Partitions also support the ability to change the storage location for each partition further capitalizing on hardware investments such as SSDs and low cost disks.  This is set in the XMLA using the StorageLocation element with a valid path.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>Adventure Works DW 2008R2 SE2</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales_Jan2011</PartitionID>
  </Object>
  <ObjectDefinition>
    <Partition 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
      <ID>Internet_Sales_Jan2011</ID>
      <Name>Internet_Sales_Jan2011</Name>
      <Source xsi:type="QueryBinding">
        <DataSourceID>Adventure Works DW</DataSourceID>
        <QueryDefinition>SELECT * 
                        FROM dbo.FactInternetSales 
                        WHERE OrderDateKey BETWEEN 20110101 AND 20110131</QueryDefinition>
      </Source>
      <StorageMode>Molap</StorageMode>
      <ProcessingMode>Regular</ProcessingMode>
      <StorageLocation>C:\Data\SSASOptimized</StorageLocation>
      <ProactiveCaching>
        <SilenceInterval>-PT1S</SilenceInterval>
        <Latency>-PT1S</Latency>
        <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
        <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
        <AggregationStorage>MolapOnly</AggregationStorage>
        <Source xsi:type="ProactiveCachingInheritedBinding">
          <NotificationTechnique>Server</NotificationTechnique>
        </Source>
      </ProactiveCaching>
      <EstimatedRows>60398</EstimatedRows>
      <AggregationDesignID>Internet Sales</AggregationDesignID>
    </Partition>
  </ObjectDefinition>
</Alter>

As you can see, there are many partition management operations that can be done using XMLA.  As you work to develop a management strategy keep in mind that these operations can be scripted for automated execution when managing your Analysis Services solution.

X-XMLA: vi. Processing and Out-of-Line Bindings in XMLA

In this post, I will progressively go through processing a partition from full process, to incremental process, and finally to incremental process with out-of-line bindings.  This will provide you with additional samples to examine the results of partition processing with XMLA.

Note: these examples use the Adventure Works DW 2008R2 SE sample database available on CodePlex (http://msftdbprodsamples.codeplex.com/).

Processing with XMLA

Processing is a command that can be executed in a batch and in parallel with similar tasks.  This is particularly useful when processing multiple objects that are not dependent on each other, such as dimensions.  The basic structure is as follows:

<Batch>
<Parallel>
<Process>
<Object>
~object definition~
      <Type>
<ErrorConfiguration>
<WriteBackTableCreation>

Processing Type Options

This is a list of the most commonly used types when processing SSAS objects.

• ProcessDefault:  This will determine the best option based on the condition of the object.  Be aware that if SSAS determines that a full process is required, it will do that. I would not recommend using this process type in most production situations.
• ProcessFull: This option completely rebuilds the dimension, partition, cube or even database by removing existing data and reloading the data with aggregations and indexes.  In the case of cubes, measure groups and partitions, this is the equivalent of ProcessData + ProcessIndex.
• ProcessClear: This option drops the data and related structures such as indexes and aggregations from the object.  Once this run, no data is available in the object.
• ProcessData:  This option drops the data and reloads it, but does not rebuild the aggregations or indexes. It works with cubes, measure groups, partitions, and dimensions.
• ProcessAdd: This option adds new data to a partition or dimension.  It is not available as an option when processing dimensions from SQL Server Management Studio, but is available in XMLA scripts.
• ProcessUpdate:  This options will reread the data and updates the dimension attributes.  It is only available for dimensions and will drop flexible aggregations and indexes on related partitions.
• ProcessIndex: This option rebuilds indexes and aggregations for partitions.  It requires that the partition has already been loaded with data (ProcessData).

Of these options, the most commonly used when working with XMLA in my experience have been, ProcessFull, ProcessData, ProcessAdd and ProcessIndex.  I will use each throughout the examples below and describe the cases where they have applied in my projects over the years.

Processing with XMLA

The process command in XMLA let’s you define the object to be processed, the type of processing to implement, and how to handle any writeback partitions.  (NOTE: the ErrorConfiguration options is optional.) Overall the structure is fairly simple.  Here is a simple example of XMLA will fully process the Fact Internet Sales measure group.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:       </Object>

   9:       <Type>ProcessFull</Type>

  10:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  11:     </Process>

  12:   </Parallel>

  13: </Batch>

The syntax is identical for processing dimensions.  The <Object> element identifies the object to be processed starting with the database.  Next the <Type> element specifies the processing type using the options from the list above.  Finally, the <WriteBackTableCreation> element instructs Analysis Services on how to handle a write back partition that has not been fully processed.  The default is UseExisting which will use existing table or create a new table if needed.  This is only relevant if your cube supports writeback.

Processing Affected Objects

This is a setting that is part of the Process command element.  By default it is set to True as in the example above.  This means that any objects that are affected will be processed as well.  The best example is if you process a dimension and it ends up being fully processed, this will cause all related measure groups to be fully processed as well, keeping the data in a consistent usable state.  However, if you are tightly managing your processing jobs, you may need to set this to False to reduce unexpected and blocking processing jobs.

Using Out-of-Line Binding with Incremental Processing

A great feature of XMLA is the ability to dynamically bind data and even connections when processing SSAS objects.  This is called “Out-of-Line” binding (http://msdn.microsoft.com/en-us/library/ms128523).   One of the core concepts behind out-of-line bindings is that you can specify the connection and the data source query or table at run time.  This is particularly useful when you want to do incremental processing.

This first example illustrates how to use a query to change the data being added to the partition.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:         <PartitionID>Internet_Sales</PartitionID>

   9:       </Object>

  10:       <Type>ProcessAdd</Type>

  11:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  12:     </Process>

  13:   </Parallel>

  14:   <Bindings>

  15:     <Binding>

  16:       <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

  17:       <CubeID>Adventure Works DW</CubeID>

  18:       <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

  19:       <PartitionID>Internet_Sales</PartitionID>

  20:       <Source 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xsi:type="QueryBinding">

  21:         <DataSourceID>Adventure Works DW</DataSourceID>

  22:         <QueryDefinition>Select 1 from SomeTable</QueryDefinition>

  23:       </Source>

  24:     </Binding>

  25:   </Bindings>

  26: </Batch>

The second example shows the syntax for changing a table or view in the bindings.

   1: <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

   2:   <Parallel>

   3:     <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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">

   4:       <Object>

   5:         <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

   6:         <CubeID>Adventure Works DW</CubeID>

   7:         <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

   8:         <PartitionID>Internet_Sales</PartitionID>

   9:       </Object>

  10:       <Type>ProcessAdd</Type>

  11:       <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  12:     </Process>

  13:   </Parallel>

  14:   <Bindings>

  15:     <Binding>

  16:       <DatabaseID>Adventure Works DW 2008R2 SE</DatabaseID>

  17:       <CubeID>Adventure Works DW</CubeID>

  18:       <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>

  19:       <PartitionID>Internet_Sales</PartitionID>

  20:       <Source 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" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xsi:type="TableBinding">

  21:         <DataSourceID>Adventure Works DW</DataSourceID>

  22:         <DbTableName>Fact_InternetSales</DbTableName>

  23:       </Source>

  24:     </Binding>

  25:   </Bindings>

  26: </Batch>

In the end, by using the Bindings definitions you can choose what data is getting processed into the SSAS object.  Here are some ways that I have used this technique to support my projects.

• Changed the datasource used with the object.  I have used this to point to a different data source to test whether it works with the cube without overwriting the existing datassource in the cube definition.
• Changed the table to a view for processing.  Because I use XMLA when scheduling most of SSAS process jobs, I can dynamically set the table or view as needed.
  • Changed the query definition to limit the data to new rows only. I have used this the most.  I have changed the query definition to reflect the latest data for both dimensions and partitions.  More importantly, I was able to leverage this in environments where I have not had control over the data source itself, specifically Oracle data warehouses.

Out-of-line bindings are very powerful when controlling your SSAS process options and allow you to maximize the efficiency of your jobs.

X-XMLA: v. Creating XMLA Script from Visual Studio and Deploy the Database

This will be the easiest entry in this series.  This is a reblog of an article I wrote last year.

Simple batch script to generate XMLA and deploy SSAS DB

As I was working through some issues with my cube design, I realized that I wanted to deploy my SSAS databases using XMLA in SSMS. This can be easily scripted from SSMS, but I had not deployed the database yet. As a result, I created a batch file to build the XMLA used to deploy a database. This script can be used for automated builds or even automated deployments if that fits into your model. I don’t write a lot of batch files so you may want to update the syntax as needed.  Read more…

X-XMLA: iv. Deploying Databases with XMLA

The fourth segment in this series focuses on using XMLA to deploy databases.  When you create SQL Server Analysis Services databases in Visual Studio, you often use target development server to validate the design and data with.  This blog will focus on moving that development database to other environments and the required steps to make this work.

Generating the Create Database Script

The first step is the easiest.  At this point, we are assuming that you have created a development database and have deployed it.  Generating a create script at this point is fairly straightforward.  Using the database shortcut menu, select Script Database as … CREATE To … New Query Editor Window as shown below.

This will in a CREATE script with all the metadata from the development database.

Changes Required during Initial Deployment Process

Before we move much further, a key point from the first section is that all the metadata from the development database is in this script.  There are two areas that need to be updated as this script is deployed to different environments.

Data Sources.  The data sources that are in the script currently point to development data.  In most cases, these will need to be updated to reflect the data sources used in the target environment.  For instance, you normally do not use the production data servers when developing cubes and unit testing the solution due to the performance impact on those servers.  Furthermore, many database administrators hold the connection information that is required to connect to those sources and will not give that information to developers.

Role Membership.  While we will create the roles in the development environment, we usually use development users and groups to test the security.  Once deployed, this membership will need to be updated to the correct users and groups for the target environment.

Then next two sections describe the two methods for handling these changes.  You may find a blended approach works for you as well depending on the target environment and your policies.

Applying Required Changes Post-deployment

First, you can apply these changes after the database has been deployed.  To do this, simply run the XMLA script while connected to the server in the environment you are targeting.

After running the script you can use SQL Server Management Studio to update the data sources and role members.  As shown below, in order to update the data sources, you will need to update each data sources connection string property to match the credentials and database used in that source.

You will go through a similar process with the role membership.  In this case you will go to the Properties window for each Role and Add or Remove users and groups to match the current environment.

Once this is complete, you will be able to process the cube with the current data and make sure the correct users are set up.  Just a note though, I would not process the database until the users have been set up as you will then have a database with data you did not intend to make available to developers.

Applying Required Changes Pre-deployment

The other method is to update the script prior to deployment.  In this case you will be modifying the generated script and which will not require additional changes once applied.

To update the data sources you need to search for the <DataSources> element and manually update the connection string for each data source.  Here is a sample of the XMLA you will be updating.

 1: <DataSources>

 2:     <DataSource xsi:type="RelationalDataSource">

 3:         <ID>Adventure Works DW</ID>

 4:         <Name>Adventure Works DW</Name>

 5:         <ConnectionString>Provider=SQLNCLI10.1;Data Source=ServerName;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=AdventureWorksDW2008R2</ConnectionString>

 6:         <ImpersonationInfo>

 7:             <ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

 8:         </ImpersonationInfo>

 9:         <Timeout>PT0S</Timeout>

 10:         <DataSourcePermissions>

 11:             <DataSourcePermission>

 12:                 <ID>DataSourcePermission</ID>

 13:                 <Name>DataSourcePermission</Name>

 14:                 <RoleID>Role</RoleID>

 15:             </DataSourcePermission>

 16:         </DataSourcePermissions>

 17:     </DataSource>

 18: </DataSources>

Next, you need to search for the <Roles> element.  You will be updating the <Member> elements for each role you need to update.  The primary issue with this edit is that you may need to know the Active Directory SID for each user or group you need to add as a member.  The XMLA sample below shows what you will be updating.

 1: <Roles>

 2:     <Role>

 3:         <ID>Role</ID>

 4:         <Name>Role</Name>

 5:         <Members>

 6:             <Member>

 7:                 <Name>SampleUser</Name>

 8:                 <Sid>S-0-0-0-0000000000-0000000000-0000000000-0000</Sid>

 9:             </Member>

 10:         </Members>

 11:     </Role>

 12: </Roles>

Check out the following blog entry from Derek Newton ( T | B ) for using PowerShell to retrieve an SID from AD:  http://dereknewton.com/2010/11/finding-an-active-directory-users-sid-using-powershell/

Altering the Database

Usually you will not update an entire database except during the early development cycle.  If you make enough changes you may find it beneficial to update the entire database as well.  The data source and role member changes have been described above.  However, be aware that when altering a database only the metadata in the script will be applied.  The biggest area of concern is if you dynamically add partitions and aggregations to your database that have not been applied into the development environment.  If you proceed to execute the Alter script without taking this into account you will lose all of that functionality.  As your database becomes more mature, you will likely find that targeted changes are easier to manage and have much less risk.  If you need to update the entire database, you should be sure to add the partitions, aggregations, and any other changes you may have made in production to the development database so your script contains the correct items.  I will discuss partition management in an upcoming blog in this series and will have some other ideas there that can help.

X on XMLA: iii. Basic DDL Functions in XMLA (Create, Alter, Delete)

XMLA can be used to manage the structure of your multidimensional databases.  While many developers use Visual Studio (BIDS), to deploy changes, as systems move to production or need to be more clearly managed, XMLA comes into play.

Some of the most common DDL type uses for XMLA including partition management, deploying changes, and promoting between environments.  In all of these cases, objects within Analysis Services need to be created, altered or deleted.

Before we dig into the details, I wanted to call out that the Execute method will be used and, keep in mind, that the full syntax is not required when using SQL Server Management Studio (SSMS).  (See X on XMLA: ii. Basic Structure of XMLA for more details.)  Furthermore, in SSMS, you can generate Create, Alter, and Delete XMLA by right clicking on the deployed object and choosing the Script To option.  If you have questions about syntax, definitely use this function to discover more about the syntax and the object you are working with.

The following sections are using the Sales Channel dimension from the Adventure Works sample database to illustrate the command syntax.  (This sample database is available on CodePlex).

Create

The Create command is used to create new objects in the database.  To child elements that are required are ParentObject and ObjectDefinition.  The ParentObject specifies the list of objects that are the parent.  In our illustration below, the parent object is the database.  This is true because the dimensions in Analysis Services belong to the database.  However, if we were creating a partition, the parent would be structured, database then cube then measure group.  This is true because the partition belongs to a specific measure group.  The order of the parent objects matter as they are read top to bottom in the XMLA.

Alter

The Alter command is used to modify existing objects in the database.  The Alter command uses two child elements, Object and ObjectDefinition.  The Object defines the object that is targeted.  This, like the ParentObject, is structured from top to bottom with the last object in the list as the object being targeted for alteration. 

The ObjectDefinition specifies the changes to make to the object.  The changes to the structure MUST include all of the parts of the structure you want to keep the same.  I cannot emphasize how important it is that you keep this in mind.  You cannot send a simple change via XMLA.  You must send the new version in its entirety.  This is true at all levels including the database.  Where this commonly creates some issues is in the user objects at the database level and the partitions in the measure groups.  If you have specific users at the database level that are different between environment, which you should have, you need to update the alter for each environment.  If you are modifying a measure group that you have added partitions to, you must make sure these new partitions are in your script as well.

Delete

The Delete command is used to remove existing objects in the database.  It only has one child element – Object.  This is the simplest of the commands here and only needs the proper object definition. Like the ParentObject and Object elements above, you need to have the proper order to delete the correct object.  Use caution as this command will contain the database through the targeted object.  If you execute the command at the database level you will delete your database.

Results

The execution results are not always clear.  When the query is successful you will see the following:

Not really conclusive.  Error messages are also returned in an XML format and often contain some relevant information as to why the script failed.  Be sure to read it closely as some times multiple errors are returned and the root cause may not be plainly evident.