xmla / mdx script to define measure display folder

Question

I have an SSAS DB that was automatically generated by an application i use. This DB has a lot of measures and I would like to define 'display folder' to the measures so the cube will be more user friendly in Excel. I want an xmla/mdx ( script that will define to display folder for the measures. One way i found is to manually do that one time in visual studio and then generate a full 'alter cube' xmla scripts that applies the current definitions and run it every time after the cube is generated. The problem is I need a shorter scripts that will only define the display folder (the full 'alter cube' script is very long and this creates other problems for me.

Answer 1

I am not absolutely sure, but I think you cannot use an XMLA ALTER statement to change anything more detailed than a complete measure definition. Possibly the best approach for your problem would be a small .net program that uses AMO. I use C# below for the sample code, but you could also use VB.net. The way AMO works is:

1. You connect to the server with code like this:

   Server server = new Server(); server.Connect("Data source=servername\\instancename;Initial catalog=Adventure Works DW 2008 SE");

2. You navigate through the hierarchical structure from the database to the object you want to change (in your case a measure), similar to this:

   MeasureCollection measures = sever.Databases.FindByName("Adventure Works DW 2008 SE").Cubes.Find("YourCubeName").Measures; -- choose the measure(s) you need

3. You change some properties of this object (which only changes the object structure in RAM).
4. You call the Update() method on any object of the object hierarchy (which in your case could be the cube object), which writes any structural changes you did an any object below the object on which you call Update back to the server.
5. You disconnect from the server (possibly via the C# using construct on the server object).

See http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver(v=sql.105).aspx for the documentation of AMO.