I have worked with Microsoft PerformancePoint Server 2007 since it was in Beta, and with all the functionality it provides, every client has asked for at least one custom control. And since most of the data used within PerformancePoint is in Analysis Services, it becomes imperative that programmers must learn to leverage data within Analysis Services Cubes.
This article demonstrates how programmers can extract metadata from Analysis Services. First you will need the Analysis Services Connection String for your server (for connection string format, visit ConnectionStrings.com).
The following snippets outline how to pull the various data elements from Analysis Services.
Required References:
1: using System;
2: using System.Collections.Generic;
3: using System.Text;
4: using Microsoft.AnalysisServices.AdomdClient;
5: using Microsoft.AnalysisServices;
List of Cube Names:
1: public static List<String> GetCubeNames(string ConnectionString, bool includeHiddenCubes)
2: {
3: List<String> names = new List<String>();
4:
5: using (AdomdConnection conn = new AdomdConnection(ConnectionString))
6: {
7: // Open ADOMD Connection
8: conn.Open();
9:
10: //Loop through every cube
11: foreach (CubeDef cube in conn.Cubes)
12: {
13: //Skip hidden cubes
14: if (!includeHiddenCubes && cube.Name.StartsWith("$"))
15: {
16: continue;
17: }
18: names.Add(cube.Name);
19: }
20:
21: // Close ADOMD Connection
22: conn.Close();
23: }
24: return names;
25: }
List of Dimensions (based on specified Cube Name):
1: public static List<String> GetDimensionNames(string ConnectionString, string CubeName)
2: {
3: List<String> names = new List<String>();
4: using (AdomdConnection conn = new AdomdConnection(ConnectionString))
5: {
6: // Open ADOMD Connection
7: conn.Open();
8:
9: //Cube
10: CubeDef cube = conn.Cubes[CubeName];
11:
12: //Dimensions
13: foreach (Microsoft.AnalysisServices.AdomdClient.Dimension dim in cube.Dimensions)
14: {
15: names.Add(dim.Name);
16: }
17:
18: // Close ADOMD Connection
19: conn.Close();
20: }
21: return names;
22: }
List of Hierarchies within a Dimension:
1: public static List<String> GetDimensionHierarchies(string ConnectionString, string CubeName, string DimensionName)
2: {
3: List<String> names = new List<String>();
4: using (AdomdConnection conn = new AdomdConnection(ConnectionString))
5: {
6: // Open ADOMD Connection
7: conn.Open();
8:
9: //Cube
10: CubeDef cube = conn.Cubes[CubeName];
11:
12: //Dimensions
13: Microsoft.AnalysisServices.AdomdClient.Dimension dim = cube.Dimensions[DimensionName];
14:
15: // Hierarchies
16: foreach (Microsoft.AnalysisServices.AdomdClient.Hierarchy hier in dim.Hierarchies)
17: {
18: names.Add(hier.Name);
19: }
20:
21: // Close ADOMD Connection
22: conn.Close();
23: }
24: return names;
25: }
Process all Cubes within a Analysis Services Database:
1: public static bool ProcessCube(string serverConnectionString, string olapDatabaseName)
2: {
3: Server server = new Server();
4: server.Connect(serverConnectionString);
5: foreach (Database db in server.Databases)
6: {
7: if (db.Name == olapDatabaseName)
8: {
9: try
10: {
11: db.Process(ProcessType.ProcessFull);
12: }
13: catch
14: {
15: return false;
16: }
17: }
18: }
19: return true;
20: }