Applies to Data Academy 5.5+
In Data Academy 5.6+, CUBE-it gives you a powerful new ability to use XMLA Scripts to carry out additional post-processing of your cubes and cube spaces.
This gives you the ultimate power to do ANYTHING with your cubes, while at the same time maintaining ALL of the meta-data in Data Academy, allowing you to use TRANS-it to truck cubes from 'dev' to 'live' for example, without loosing your enhanced settings.
Scripts are executed when a Cube Space is Generated not when it is Run!
The scripting language is XMLA - this is a standard feature of Analysis Services.
Cube Space Level Scripts
Cube Space level scripts are run after all of the cubes and dimensions have been built and initially-processed - it is the final step in the 'Generation' process. They are shown under the 'Scripts' tab on the Cube Space Details screen.
The following Cube Space level script creates a security role called 'AccountTeam':
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject><DatabaseID>MY_DATABASE_NAME</DatabaseID></ParentObject>
<ObjectDefinition>
<Role>
<ID>AccountTeam</ID>
<Name>AccountTeam</Name>
<Description>Account Team</Description>
</Role>
</ObjectDefinition>
</Create>
Note that you need to explicitly specify the database name in the script!
Cube \ Dimension Level Scripts
Cube level scripts are run after the cube or dimension has been built but before it is initially-processed. They are shown under the 'Scripts' tab on the Cube \ Dimension Details screen.
The following cube level script adds a Perspective called 'Perspective2' to the cube 'Test Cube'.
<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<ParentObject>
<DatabaseID>CubeTests</DatabaseID>
<CubeID>Test Cube</CubeID>
</ParentObject>
<ObjectDefinition>
<Perspective>
<ID>Perspective2</ID>
<Name>PES2</Name>
<Dimensions>
<Dimension>
<CubeDimensionID>Customer Region</CubeDimensionID>
<Attributes>
<Attribute>
<AttributeID>Customer Name</AttributeID>
</Attribute>
<Hierarchies>
<Hierarchy>
<HierarchyID>Customer Region</HierarchyID>
</Hierarchy>
</Hierarchies>
</Dimension>
</Dimensions>
<MeasureGroups>
<MeasureGroup>
<MeasureGroupID>General</MeasureGroupID>
<Measures>
<Measure>
<MeasureID>Quantity</MeasureID>
</Measure>
</Measures>
</MeasureGroup>
</MeasureGroups>
</Perspective>
</ObjectDefinition>
</Create>
Merge Scripts
Analysis Services doesn't allow you to add some classes of sub-object to an existing cube. In such cases, it is necessary to re-script the entire cube - in order to add an Action, for example.
Data Academy includes the feature to merge a script 'snippet' with the built cube's default script, allowing you to script-in just the Action, as shown below.
<MyScript xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Actions>
<Action xsi:type="StandardAction">
<ID>GoDA</ID>
<Name>GoToDataAcademy</Name>
<TargetType>AttributeMembers</TargetType>
<Target>[Product].[Product]</Target>
<Type>Url</Type>
<Expression>"http://www.DataAcademy.com/"</Expression>
</Action>
</Actions>
</MyScript>
Note that although we are essentially dealing with a 'snippet' here, the namespacing must be correct and it must be consistent with that of the document with which it is being merged.
The name of the root, document element is not important - it is called MyScript in the above example, but it called be called anything at all, as it is only a container for the actual script we want to merge-in!
Merge Injection Points
The merge works by scripting-off the cube \ dimension programatically, and merging the INNER XML of the DOCUMENT ELEMENT* of your script with the real-time, generated cube \ dimension script.
The script we are merging to is a standard cube \ dimension 'Alter' script, which you can view in Management Studio when you right-click on the cube and select 'Script [Object] as -> Alter...'
By default, in the case shown above, the 'Actions' node is merged into the cube script under the 'Cube' node.
However, it is not always the case that you want to merge at this point, so the provision is made for a 'Merge Injection Point' which is an x-path expression identifying the parent node into which the INNER XML of the DOCUMENT ELEMENT* of your script is merged.
The default merge injection point is displayed in the UI, and is as follows:
/default:Alter/default:ObjectDefinition/default:Cube
In this simple example, we are just drilling down from the root node, equivilent to:
Alter -> ObjectDefinition -> Cube
(When specifying the Merge Injection Point, note that we must EXPLICITLY reference the default namespace - hence the "default:" prefixes!)
This merges into the Cube node, but if we wanted to inject our script into a different node, we would have to modify the injection point accordingly.
Replace-Merge Injection Points
To replace an existing node in the default XMLA script, you will need to prepend "#R" to your Injection Point expression. For example, to replace the Measure 'Unit Price':
#R/default:Alter/default:ObjectDefinition/default:Cube/default:MeasureGroups/default:MeasureGroup[1]/default:Measures/default:Measure[default:ID='Unit Price']
This will replace the identified node with the FIRST CHILD of the DOCUMENT ELEMENT of your script - any subsequent children will be ignored.
---------------
* The INNER XML of the DOCUMENT ELEMENT is the node BENEATH MyScript in the example shown - this is why the name of the root document element doesn't matter - it is just a container, allowing you to specify any required namespacing!