Sending XMLA to Analysis Services the easy way

I love Chris Harrington's Thin Olap site http://www.activeinterface.com/thinolap.html which has a stack of examples on sending XMLA using VBScript.

However I recently came across an easier way to send XMLA to Analysis Services using .Net. There is an assembly called Microsoft.AnalysisServices.XMLA.dll that is distributed with Analysis Services 2005, it is installed in GAC on the server or can be found in “C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies“ by default.

If you add a reference to this assembly you can then run the following C# code to send an XMLA script that was created in SMSS. I used the following code to create an entire copy of the Adventure Works database.

  1 using System; 
  2 using System.Collections.Generic; 
  3 using System.Text; 
  4 using Microsoft.AnalysisServices.Xmla; 
  5 using System.IO; 
  6  
  7 namespace xmlaCmd 
  8 { 
  9     class Program 
 10     { 
 11         static void Main(string[] args) 
 12         { 
 13             XmlaClient clnt = new XmlaClient(); 
 14             clnt.Connect(@"localhost\sql05"); 
 15             string xmla = File.ReadAllText(@"c:\data\AdvWrksDW2.xmla"); 
 16             clnt.Send( xmla, null ); 
 17             clnt.Disconnect(); 
 18         } 
 19     } 
 20 }

If I get time in the future I would like to try and build a more complete xmla client, but this will give you a basic idea of the approach.

Print | posted on Thursday, May 25, 2006 9:03 PM

Comments on this post

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
Darren,
You must be the one and only person who's discovered that class. Googling found nobody else - not even at Microsoft. But certainly a good thing to have come across. Any idea where one could find a doc page on that class? Have you seen any other samples?

Thanks,
Chris
Left by Chris Harrington on May 27, 2006 12:02 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
Chris,

Sorry I don't know where you can get any documentation on this class. I have seen only one sample which unfortunately I cannot share. However it really only did a connect, discover and disconnect. I then did a bit of poking around with reflector to see what this class could be used for. I have a few more samples that I am working up using discover and executing mdx queries. I will try and post these soon.
Left by Darren Gosbell on May 27, 2006 8:40 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
Hi Darren,

Can you tell me what the format the restriction and property string parameters to XmlaClient.Discover and XmlaClient.ExecuteStatement must be presented? I've tried all sorts and can't figure it out. I'm not in a position to use xmla script files because I'm trying to leverage an existing mdx generation engine - but I want to get the results back as xmla instead of using the adomd.net client, as we're presenting on the web and I'd like to leverage xslt to manage the presentation.

Any pointers on how to interact with the XmlaClient, would be very greatfully received.

Thanks,

Tim
Left by Tim Watson on Feb 06, 2007 9:46 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
hi,

i have a question, could you help me?
thanks.

i run the following xmla query in SQL Server Management Studio


<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_LOCATIONS</RequestType>

<Restrictions>
<RestrictionList>
<LOCATION_BACKUP_FILE_PATHNAME>C:\AnalysisDBBackup\AmoAdventureWorks.abf</LOCATION_BACKUP_FILE_PATHNAME>
</RestrictionList>
</Restrictions>

<Properties>
<PropertyList>
</PropertyList>
</Properties>

</Discover>




And get the following result:


<row>
<LOCATION_BACKUP_FILE_PATHNAME>C:\AnalysisDBBackup\AmoAdventureWorks.abf</LOCATION_BACKUP_FILE_PATHNAME>
<LOCATION_PARTITION_OBJECTPATH>VM2005.AmoAdventureWorks.Adventure Works.Sales Reasons.Fact_Internet_Sales_Reason</LOCATION_PARTITION_OBJECTPATH>
<LOCATION_PARTITION_DATASOURCEID />
<LOCATION_PARTITION_DATASOURCENAME />
<LOCATION_PARTITION_NAME>Fact_Internet_Sales_Reason</LOCATION_PARTITION_NAME>
<LOCATION_PARTITION_SIZE>2910</LOCATION_PARTITION_SIZE>
<LOCATION_CONNECTION_STRING />
<LOCATION_PARTITION_FOLDER />
</row>


what is the LOCATION_PARTITION_SIZE element units?
Left by alex on Feb 03, 2008 5:02 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
hi..


This is too good code, but i want to create analysis service using dot net or using xmla. Would you like to help me ?

My mail id is mailvipuls@yahoo.com
Left by Vipul Sharma on Oct 11, 2008 8:21 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
Can this dll communicate will oracle BI
Left by Teddy on Dec 08, 2008 10:04 PM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
@Teddy - I don't think this will work with Oracle BI. I don't have an Oracle BI instance that I can check against, but I do not think that there is anywhere to specify a provider. So I think the dll only knows how to connect to SSAS.
Left by Darren Gosbell on Dec 09, 2008 6:53 AM

# re: Sending XMLA to Analysis Services the easy way

Requesting Gravatar...
Hello Darren,

Your approach was mush helpful wit my task: I needed to cancel processing deploying/processing script while it was executing. AdomdCommand and Server.Execute were useless...

Regards,
Roman K
ceasor(a)yandex.ru

P.S. Could you please give me any information about XmlaClient methods or what is reflector at least? Thanks!!
Left by Roman_K on Dec 30, 2009 2:14 PM

Your comment:

 (will show your gravatar)