Generating XMLA - referencing Objects by name

Jamie Thomson has an interesting post here http://blogs.conchango.com/jamiethomson/archive/2006/06/20/4106.aspx about some of his recent experiences with Analysis Services.

One point that particularly stuck out for me an that was his criticism of having to access objects using their ID instead of their name in XMLA. This affected me recent while I was working on the PowerShell provider for AMO (which is still a work in progress). Most of the collections in AMO can be accessed using the object's ID, I did not realise this initally as a great deal of the IDs and names are the same.

I think that there are two issues here. One is that you can't set the ID from the BIDS user interface and once created they are immutable, that is, they cannot be changed. The second is that the IDs and the names are the same in a lot of circumstances, leading to increased confusion. I have to say when I first saw the generated IDs I was supprised that they are set to the same value as the original name of the object. It made sense when I heard that Microsoft were introducing immutable IDs, but would have expected that these IDs would have been set using integers or GUIDs.

Getting back to Jamie's post, he laments the fact that you cannot write XMLA that references objects by their name. What I have to offer here is a possible work around. While you cannot manually build the XMLA as a string if you do not know the ID, what you can do is to use AMO and get it to build the XMLA for you.

Below is an example, written in PowerShell which demonstrates how to generate XMLA. (PowerShell is fast becoming my prototyping language of choice, especially when combined with Karl Prosser's excellent PowerShell Analyzer)

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[Microsoft.AnalysisServices.Server]$svr = new-Object([Microsoft.AnalysisServices.Server])
$svr.Connect("Localhost\sql05")
$svr.CaptureXml = $true
[Microsoft.AnalysisServices.Database] $db = $svr.Databases.FindbyName("Adventure Works DW")
$dimAcct = $db.Dimensions.FindByName("Account")
$dimCust = $db.Dimensions.FindByName("Customer")
$dimAcct.Process()
$dimCust.Process()
$svr.CaptureLog

By setting the CaptureXML property to true, the AMO methods will not actually perform the requested action. The commands will instead be buffered as XMLA, which you can either save out to a file or send to the server as a single batch. The above code returns the following XMLA, which will process the customer and accounts dimensions one after the other. Notice how the code gets the dimensions by their name, but the XMLA is generated using the relevant ID.

<Process xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>
ProcessDefault</Type>
  <Object>
    <DatabaseID>
Adventure Works DW</DatabaseID>
    <DimensionID>
Dim Account</DimensionID>
  </Object>
</Process>
<Process xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>
ProcessDefault</Type>
  <Object>
    <DatabaseID>
Adventure Works DW</DatabaseID>
    <DimensionID>
Dim Customer</DimensionID>
  </Object>
</Process>

If you want to process the objects in parallel or within a transaction, then you can replace the final call to .CaptureLog() with a call to .ConcatenateCaptureLog() 

$svr.ConcatenateCaptureLog($true,$true,$true)

This will return the following XMLA:

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine' Transaction='true'>
<Parallel>
<Process xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>
ProcessDefault</Type>
  <Object>
    <DatabaseID>
Adventure Works DW</DatabaseID>
    <DimensionID>
Dim Account</DimensionID>
  </Object>
</Process>
<Process xmlns="
http://schemas.microsoft.com/analysisservices/2003/engine">
  <Type>
ProcessDefault</Type>
  <Object>
    <DatabaseID>
Adventure Works DW</DatabaseID>
    <DimensionID>
Dim Customer</DimensionID>
  </Object>
</Process>
</Parallel>
</Batch>

If you are not worried about doing anything with the resulting XMLA, but you want to just batch up a series of commands you can also just call .ExecuteCaptureLog() which has the same parameters as .ConcatenateCaptureLog().

Print | posted on Wednesday, June 21, 2006 8:52 PM