Introduction

This article gives some examples on how you can modify a dimension or cube using XPATH.

To alter the cube using XMLA, follow the steps below. The details can also be found in this article describing custom scripts on OLAP objects.

1. Open the SSAS database in BIDS
2. Perform the changes (create the perspective, change the property).
3. Connect to the SSAS database from SSMS
4. Right click the OLAP database in SSMS, choose Script Database As -> Create To...
5. Locate the section with your change, copy it
6. In TX, on the Cubes tab, add a new Script Action
7. Edit the cube/OLAP/dimension that this relates to
8. Select the script in the inline script dropdown

Examples

You can use the examples below by following these steps:

1. Add a script action with the contents below.
2. Add this script action as a inline script on the dimension.
3. Deploy and execute

Changing the time dimension

This script changes the time dimension from the default setting of all relations points to the key attribute "DateValue" into a setup where the relations are DateValue -> Week -> Month -> Year. Quarter is left as default pointing to the key attribute. TX does not support creating diamond shaped relationships, this is the only way to achieve that. The first action removes the existing relations the rest inserts the new relations.

<Script>
<AddNameSpace Alias="my" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine"/>
<AddNameSpace Alias="xsd" AddNameSpace="http://www.w3.org/2001/XMLSchema"/>
<AddNameSpace Alias="xsi" AddNameSpace="http://www.w3.org/2001/XMLSchema-instance"/>
<AddNameSpace Alias="ddl2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2"/>
<AddNameSpace Alias="ddl2_2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"/>
<AddNameSpace Alias="ddl100_100" AddNameSpace="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"/>
<Replace ReplaceNode="//my:AttributeRelationships" SearchType="Path" Operator="All">
</Replace>
<InsertEnd Node="//my:Attribute[my:Name ='DateValue']" SearchType="Path" Operator="All">
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Week</AttributeID>
<Name>Week</Name>
</AttributeRelationship>
</AttributeRelationships>
</InsertEnd>
<InsertEnd Node="//my:Attribute[my:Name ='Week']" SearchType="Path" Operator="All">
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Month</AttributeID>
<Name>Month</Name>
</AttributeRelationship>
</AttributeRelationships>
</InsertEnd>
<InsertEnd Node="//my:Attribute[my:Name ='Month']" SearchType="Path" Operator="All">
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Year</AttributeID>
<Name>Year</Name>
</AttributeRelationship>
</AttributeRelationships>
</InsertEnd>
</Script>

Changing the DefaultMember of an attribute on a dimension with more than one attribute

This script below changes the DefaultMember of an attribute on a dimension with more than one attribute. If there's only one attribute, this can be changed without XPATH)

<Script>
<AddNameSpace Alias="my" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine"/>
<AddNameSpace Alias="xsd" AddNameSpace="http://www.w3.org/2001/XMLSchema"/>
<AddNameSpace Alias="xsi" AddNameSpace="http://www.w3.org/2001/XMLSchema-instance"/>
<AddNameSpace Alias="ddl2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2"/>
<AddNameSpace Alias="ddl2_2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"/>
<AddNameSpace Alias="ddl100_100" AddNameSpace="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"/>
<InsertEnd Node="//my:Attribute[my:Name ='Title']" SearchType="Path" Operator="All">
<DefaultMember>[OrderDetail Elements].[Title].&amp;[Administration of Health Programs]</DefaultMember>
</InsertEnd>
</Script>

Changing the discretization method to Clusters with bucketsize 3

This script changes the discretization method to Clusters with bucketsize 3.

This script would be chosen as an inline script on the dimension.

<Script>
<AddNameSpace Alias="my" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine"/>
<AddNameSpace Alias="xsd" AddNameSpace="http://www.w3.org/2001/XMLSchema"/>
<AddNameSpace Alias="xsi" AddNameSpace="http://www.w3.org/2001/XMLSchema-instance"/>
<AddNameSpace Alias="ddl2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2"/>
<AddNameSpace Alias="ddl2_2" AddNameSpace="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"/>
<AddNameSpace Alias="ddl100_100" AddNameSpace="http://schemas.microsoft.com/analysisservices/2008/engine/100/100"/>
<AddNameSpace Alias="ddl200" AddNameSpace="http://schemas.microsoft.com/analysisservices/2010/engine/200"/>
<AddNameSpace Alias="ddl200_200" AddNameSpace="http://schemas.microsoft.com/analysisservices/2010/engine/200/200"/>
<InsertEnd Node="//my:Attribute[my:Name ='Value']" SearchType="Path" Operator="All">
<DiscretizationMethod>Clusters</DiscretizationMethod>
<DiscretizationBucketCount>3</DiscretizationBucketCount>
</InsertEnd>
</Script>

This article applies to: timeXtender 4.5, tX2012, TX2014.