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

Comments on this post

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
Great!
I did the same approach but with AMO and script task in SSIS.
Left by Daniel on Jul 31, 2007 2:35 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
This sounds great for me! if we want to do a little more complicated: Is there a way to get all the XMLA script from a SSAS Cube already deployed on a server using AMO?
Left by Cedric on Oct 21, 2008 1:36 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
Yes, you can use the scripter object that is built into AMO to generate XMLA scripts.

The following will generate the script for a single cube.


# Scripting out a cube to XMLA

# -----------------------------------------

# Author: Darren Gosbell

# http://geekswithblogs.net/darrengosbell

# Date : 21 Oct 2008

# -----------------------------------------


$svrName = "localhost\sql08"
$sourceDB = "Adventure Works DW 2008"
$sourceCube = "Adventure Works"

# load the AMO library (redirect to null to 'eat' the output from assembly loading process)
[System.Reflection.Assembly]::LoadwithpartialName("Microsoft.AnalysisServices") > $null
# connect to the AS Server
$svr = New-Object Microsoft.AnalysisServices.Server
$svr.Connect($svrName)

# get a reference to the database
$db= $svr.Databases.Item($sourceDB)
# get a reference to the cube
$cub = $db.Cubes.FindByName($sourceCube)

# setup the scripter object
$sb = new-Object System.Text.StringBuilder
$sw = new-Object System.IO.StringWriter($sb)
$xmlOut = New-Object System.Xml.XmlTextWriter($sw)
$xmlOut.Formatting = [System.Xml.Formatting]::Indented
$scr = New-Object Microsoft.AnalysisServices.Scripter

# create an array of MajorObjects to pass to the scripter
$x = [Microsoft.AnalysisServices.MajorObject[]] @($cub)

$scr.ScriptCreate($x,$xmlOut,$false)

$sb.ToString() > c:\data\tmpCube2.xmla

# clean up any disposeable objects
$sw.Close()
$svr.Disconnect()
$svr.Dispose()


Left by Darren Gosbell on Oct 21, 2008 6:09 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
Thanks a lot darren it will help me a lot !!

Left by Cedric on Oct 21, 2008 5:39 PM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
Hi Darren,

How to process multiple partitions in parallel using AMO.
Left by SQL on Jun 19, 2012 4:33 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
@Cedric - You wrap the multiple process commands in a Batch element. The Batch element has a Parallel attribute that you can set to true.
Left by Darren Gosbell on Jun 19, 2012 6:45 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
Hi,
Can you help me to get dimension attributes xmla script for already deployed dimension on a server in C#. Thanks in advance
Left by jyothi on Dec 02, 2015 11:13 PM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
@jyothi - The c# should look almost identical to the PowerShell. It's just a matter of doing some minor syntax adjustments. Maybe something like the following:

var svrName = "localhost";
var sourceDB = "AdventureWorks";
var sourceDim = "Date";

var svr = new Microsoft.AnalysisServices.Server();
svr.Connect(svrName);

// get a reference to the database
var db = svr.Databases.Item(sourceDB);
// get a reference to the cube
var dim = db.Cubes.FindByName(sourceDim);

// setup the scripter object
var sb = new System.Text.StringBuilder();
var sw = new-Object System.IO.StringWriter(sb);
var xmlOut = new System.Xml.XmlTextWriter(sw);
xmlOut.Formatting = System.Xml.Formatting.Indented;
var scr = new Microsoft.AnalysisServices.Scripter();

// create an array of MajorObjects to pass to the scripter
var x = new Microsoft.AnalysisServices.MajorObject[]();
x.Add(dim);

scr.ScriptCreate(dim,xmlOut,false);

// at this point the string builder object "sb" should countain the XMLA
// script and you can send that whereever you like
Left by Darren Gosbell on Dec 03, 2015 10:56 AM

# re: Generating XMLA - referencing Objects by name

Requesting Gravatar...
@Darren Gosbell: Thanks a lot
Left by jyothi on Dec 03, 2015 6:31 PM

Your comment:

 (will show your gravatar)