Writeback to a Regular Dimension
NOTE This is part of a series of entries on the topic of Building Writeback Applications with Analysis Services.
In the post on writeback to a parent-child dimension, writeback was implemented using the ALTER CUBE MDX statement. If you attempt to use that statement to writeback to a regular dimension you will get an error stating that “Dimension writeback using this syntax is supported only on parent-child members.” To get around this, you’ll need to writeback to a regular dimension using one of three XMLA commands.
Before diving into those commands, it should be pointed out that this isn’t as intimidating as it might sound. The commands themselves follow some simple patterns, and once you learn these and get past the verbose inclusion of namespaces they are surprisingly straightforward. In addition, the pattern established before with opening a connection in ADO or ADO.NET using the MSOLAP provider works the same with XMLA as it does with MDX. Finally, it’s worth noting that these XMLA commands could be used in place of the ALTER CUBE MDX statement for updating a parent-child dimension as well. I focused on the ALTER CUBE statement in the previous post simply because that’s the traditional pattern used for these kinds of applications.
The Basic Structure of the XMLA Commands
To add, modify, or delete a dimension record in a regular dimension, you’ll use the Insert, Update, or Drop XMLA commands. The basic structure of these commands is as follows:
<Command xsi:type= "Command"
xmlns:xsd="https://www.w3.org/2001/XMLSchema"
xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
xmlns:ddl2="https://schemas.microsoft.com/analysisservices/2003/engine/2"
xmlns:ddl2_2="https://schemas.microsoft.com/analysisservices/2003/engine/2/2"
xmlns:ddl100_100="https://schemas.microsoft.com/analysisservices/2008/engine/100/100"
xmlns:ddl200="https://schemas.microsoft.com/analysisservices/2010/engine/200"
xmlns:ddl200_200="https://schemas.microsoft.com/analysisservices/2010/engine/200/200"
xmlns="https://schemas.microsoft.com/analysisservices/2003/engine">
<Object> … </Object>
<Command Specific Elements/>
</Command>
In place of the word Command, you will use Insert, Update or Drop. Be sure to employ the appropriate command name for the opening and closing tags of the XMLA statement, but also use the command name with the xsi:type attribute. In addition, be sure to follow the sentence case pattern – first letter in upper case and the remaining in lower case - used in the sample above.
The Object element in these commands identifies the database and dimension being operated on and is consistent across all three commands:
<Object>
<Database>Writeback</Database>
<Cube> $Project</Cube>
<Dimension>Project</Dimension>
</Object>
Notice in this element that a cube is identified. For the cube element, provide the name of the database-level dimension (not a cube dimension) you are updating preceded by a dollar-sign. If you are curious as to why this is, the short answer is that you are modifying a dimension object that exists at the Analysis Services database level. At that level, a dimension is thought of as its own cube.
In addition to these elements, the commands will make use of one or more additional elements, namely the Attributes and Where elements, depending on the specific command employed. These will be addressed in the context of the specific commands in the following sections.
Adding a Dimension Member
To add a dimension member, use the Insert command. This command follows the basic pattern described above and employs the additional Attributes element.
The Attributes element identifies one or more attributes for the newly added dimension entry as demonstrated in this sample:
<Attributes>
<Attribute>
<AttributeName>Project</AttributeName>
<Name>Project 06</Name>
<Keys>
<Key>Project 06</Key>
</Keys>
</Attribute>
<Attribute>
<AttributeName>Owner</AttributeName>
<Name>Owner 02</Name>
<Keys>
<Key>Owner 02</Key>
</Keys>
</Attribute>
</Attributes>
As you can see in this sample, an entry in the Project dimension has two attributes, Project and Owner, as identified by the AttributeName element under each Attribute element. For each attribute, we identify a name and key value. If an attribute has a multi-part key, each key value is specified in the order in which they are defined for the attribute, but as each attribute here has a single key, only one Key element is employed under each Keys element.
The Attribute element has many more sub-elements so that you are not limited to the items identified here. For more information on what can be prescribed to an attribute through the Attribute element, please refer to this entry in Books Online.
Dropping a Dimension Member
To drop a dimension member, use the Drop XMLA command. As with the Insert command, Drop follows the basic pattern discussed at the top of this entry, but its one command-specific element is the Where element.
The Where element behaves very much like a WHERE clause in a SQL statement. Think of it as specifying matching criteria which can identify one or more entries in a dimension. In the Where element, one or more attributes are identified by attribute name and key value. If multiple attributes are supplied, these are considered using OR logic.
In this example, one attribute is specified so that this Where element can be thought of as behaving as a SQL WHERE clause with Project = ‘Project 06’ as its logic:
<Where>
<Attribute>
<AttributeName>Project</AttributeName>
<Keys>
<Key>Project 06</Key>
</Keys>
</Attribute>
</Where>
If you would like to ensure that only one row is identified through a Where element, specify criteria for nothing more than your leaf-level attribute.
Modifying a Dimension Member
Dimension members are modified using the Update XMLA statement. This statement follows the basic structure identified at the top of this post and makes use of both the Attributes and Where elements.
As with the Drop command, the Where element when used with the Update command identifies the dimension members you wish to affect. The Attributes element, structured same as it is with the Insert command, identifies the new values for these members.
Executing the XMLA Commands
I’ve said a few times that the XMLA commands are executed using the same pattern you’ve used the MDX statements. You simply open a connection using the MSOLAP provider, execute the statement, and then close the connection. But given that XMLA is so different from MDX, I want to make sure I am being crystal clear about this.
Here is a code sample, written in VBA, that demonstrates this pattern. The MyStatement variable can be assigned either the ALTER CUBE MDX statement or one of the three XMLA commands addressed here and it will work the same:
'Add reference to Microsoft ActiveX Data Objects 6.0 Library
'MyStatement is either ALTER CUBE or one of the XMLA DDL statements but defined somewhere else
'1. Establish Connection to Analysis Services Database
Dim cn As New ADODB.Connection
cn.Open "Provider=MSOLAP;Data Source=localhost;Initial Catalog=Writeback Demo;"
'2. Execute Command & Commit Transaction
cn.Execute MyStatement
'3. Wrap Up
cn.Close
Set cn = Nothing
NOTE This has been a very basic exploration of how to employ the Insert, Update and Drop XMLA commands. For more information on these, please check out this entry in Books Online.