xmla files

Question

I'm trying to create an automatic deployment for an analysis services project I've created.
I've used the deployment wizard to generate an xmla file which can deploy my database.  The file works great if I open it within the Management Studio and press the 'Execute' button.
The question is how can I execute this script from the command line (i.e. so a deployment process could do it automatically)?

Thanks,
Kobi Reiter

Answer 1

Kobi,

Have a look at the Analysis Services Deployment Wizard.  I posted a reply to another question (not exactly the same as yours but...) with some information about the wizard, the XML files it uses, and how it works.  Here's the other post:

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=68068

Dave Fackler

Answer 2

Dave,

That works.
Alternatively, you can also create an SSIS project to execute the xmla script, which has the same effect.  Both work fine.

Kobi Reiter

Answer 3

Kobi,

Yes, that will work just as well.  But keep in mind that the SSIS components will have to be installed somewhere to run the resulting package.  In some cases and environments, this may not always be an option.  The deployment wizard should exist wherever Analysis Services is installed.  So using the deployment files with the wizard will always (hopefully) be available to handle the creation of the database and processing...

Dave Fackler

Answer 4

Strangely enough there's no command line utility that executes XMLA scripts. Luckily it's easy to write one though. Here's a small C# program that does the job, XmlaExecutor.cs:

using System.IO;
using Microsoft.AnalysisServices.AdomdClient;

public class XmlaExecutor
{
   public static void Main(string[] args)
   {
      TextReader tr = File.OpenText(args[0]);
      string xmla = tr.ReadToEnd();
      tr.Close();
      AdomdConnection cn = new AdomdConnection("Data Source=localhost");
      cn.Open();
      AdomdCommand cmd = cn.CreateCommand();
      cmd.CommandText = xmla;
      cmd.ExecuteNonQuery();
      cn.Close();
   }
}

Compile it with:

csc /r:c:\Program Files\Microsoft.XML\Adomd.NET\90\Microsoft.AnalysisServices.AdomdClient.dll XmlaExecutor.cs

Then run it by saying:

XmlaExecutor myscript.xmla

Hope this helps!

Answer 5

Any idea how to catch the output that shows up in the Messages tab of SSMS? The AdomdConnection and AdomdCommand objects only appear to expose the Disposed event.

Answer 6

We (a few Microsoft folks in the product group) are developing such a command-line utility. Our current plans is that it will be in WR2 (web release 2) of the Samples.MSI in late Spring. It will be available as a sample application, similar to the Activity Viewer, in source (a C# application).

With it you can execute either an XMLA script or an MDX query. Input and output can come from either the command-line or files. It is called 'ascmd' and is patterned after sqlcmd's syntax and capabilities. Optionally you can as it to capture trace events  issued on the session (like SQL Profiler does). It runs over both tcp/ip connections and http access and it supports the new AS2K5 multi-instancing also.

If you are interested in participating in our beta, please send an email to Robert Skoglund, Microsoft Consulting Services ([e-mail]), or to me in the product group, [e-mail]. Since this is pre-release software there will be some legal release stuff you will need to agree to. Robert is handling this.

We are extremely interested in customers that will be working with the tool in unusual ways, e.g. http access, multi-instance, different domain structures, non-English character sets, etc.

_-_-_ Dave

 

Answer 7

Speaking of non-English character sets, I always have some trouble to put "é" in my dimension when I use my cube in Reporting Services.

 Unfortunately, in french, year is : "Année" and every Time Dimension has the letter "é" in its name. Analysis Services works fine, but then Reporting Services send an error until I change the "é" in "e".

PS: I have an english version of Reporting Services.

Answer 8

 

 

try this

 

http://technet.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.adomdcommand.executexmlreader.aspx

 

 

Answer 9

dart,

after one year, you responded?! :-)

Regards!!