Reading SQL Analysis Services Metadata

Question

I have a number of objects in my SSAS cube, many of which were created to be invisible. For example:

CREATE MEMBER CURRENTCUBE.[Measures].[Latency Backcolor]
AS CASE
  WHEN [Average Latency] > [Web Alert] THEN 6384849
  WHEN [Average Latency] > [Web Warn] THEN 4577517
  ELSE IIF ( [measures].[Query count] > NULL, 14876123, null)
END, VISIBLE = 0;

which is not visible and:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Latency]
 AS IIF ([Measures].[Query Count] > 0, [Measures].[Total Ms] / [Measures].[Query Count], null),
      FORMAT_STRING = "#,##0.00000;-#,##0.00000",
      BACK_COLOR = [Latency Backcolor],
      VISIBLE = 1,
      DISPLAY_FOLDER = 'Overall',
      ASSOCIATED_MEASURE_GROUP = 'Fact Raw FD';

which is.

I've tried two methods for interrogating the cube. First, using the Microsoft.AnalysisServices.AdomdClient namespace. For example:

using Microsoft.AnalysisServices.AdomdClient;

var _connection = new AdomdConnection
{
    ConnectionString = "Data Source=localhost;User ID=me;Password=secret;Initial Catalog=dbname",
    ShowHiddenObjects = true
};

_connection.Open();

CubeDef _cube = _connection.Cubes["MyCube"];

// _cube.Measures

I've also tried the Microsoft.AnalysisServices namespace. For example:

using Microsoft.AnalysisServices;

Server server = new Server();
server.Connect( "Data Source=localhost;User ID=me;Password=secret" );

Database database = server.Databases.GetByName( "dbname" );

Cube cube = database.Cubes.FindByName( "MyCube" );

// cube.Dimensions
// cube.MeasureGroups[].Measures

All of the above is taken directly from working code (though reduced to the bare minimum for the purposes of asking the question). Everything works perfectly with either code, with the single exception that I can't "see" any of my hidden objects, such as Measures. I could use the raw MDX which I can retrieve from the database using the second technique. The (seriously) down side is I'd have to parse it myself which would be a real buzz-kill. There's got to be a way to get to the actual objects without having to jump through so many hoops.

Thanks!

Answer 1

As far as i know, there is no way to get hidden measures with ADOMD object models. You have to use XML/A and deal with the resulting XML.

Another solution that i would also recommend is using Perspectives rather than directly hiding measures. As you may know or guess, Perspectives is used for creating subset of the cube by hiding members. In this way you can access all measures with ADOMD object model and let your users see only members of the cube that is included in the current Perspective.