SQL Server 2005 : Beyond OWC: Full-On OLAP Development (part 2) - OLAP Development with ADO MD.NET

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
7/30/2012 6:08:08 PM

OLAP Development with ADO MD.NET

You’ve learned how to design a cube from scratch and extend it with calculations, KPIs, actions, and perspectives. You’ve also learned how to query the cube using Excel PivotTables and charts, both in Excel itself and from your own Windows Forms and ASP.NET applications. And we just covered in great detail how to use MDX to query your cube in a variety of ways in SQL Server Management Studio, giving you extremely precise control over what results are returned.

What we haven’t yet covered is how to execute MDX queries from within your own applications and display the results. It will be important to know how to execute queries that return data over two axes and display results quickly. It will also be important to understand how to execute more complex queries—those with three or more axes—and examine the results programmatically so that members of the third and higher axes can be enumerated in your own custom user interfaces.

With the introduction of SQL Server Analysis Services 2005, this kind of programming is almost as easy as data access programming against relational databases. That’s because Microsoft has created an ADO.NET managed data provider called ADO MD.NET, which you can use against Analysis Services databases. In fact, ADO MD.NET was introduced in May 2004 for use with SQL Server Analysis Services 2000, but using it with that version of the product was tricky. ADO MD.NET is implemented as a wrapper around XML for Analysis (XMLA), which is the native protocol of Analysis Services 2005. Analysis Services 2000 did not use XMLA natively (it instead used a protocol called PivotTable Service, or PTS), so using ADO MD.NET against Analysis Services 2000 required the installation of an XMLA wrapper (the XMLA SDK) around PTS.

Using Your ADO.NET Skills

One of the great things about ADO MD.NET is that you can treat it like any other ADO.NET provider—using Connection, Command, DataReader, DataAdapter, DataTable, and DataSet objects to execute MDX queries and handle the results. But you can also use a wide array of OLAP-specific objects to explore a cube’s metadata and carefully crawl through result sets (including those with more than two axes) axis by axis and cell by cell.

These OLAP-specific objects are essentially clones of the objects available in the old ADO MD object model (initially introduced with OLAP Services in SQL Server 7.0), which was effectively the native API for OLAP programming in SQL Server 2000 Analysis Services. ADO MD was a COM-based wrapper around OLE DB for OLAP (much as ADO was a COM wrapper around OLE DB). ADO MD.NET is thus the “fusion” of ADO MD and ADO.NET, and it is based on XMLA.

To use ADO MD.NET in your applications, simply set a reference to the Microsoft.AnalysisServices.AdomdClient managed assembly, which is listed on the .NET tab of Visual Studio’s Add Reference dialog box. If you are working on a PC that has the older ADO MD.NET provider for SQL Server 2000 Analysis Services installed, be sure to select the new version (version 9.0) as shown in Figure 6. Once you set the reference, use the namespace of the same name in your code.

Figure 6. The Add Reference dialog box in Visual Studio, with the .NET tab active and the ADO MD.NET assembly (Microsoft.AnalysisServices.AdomdClient) selected

You now have direct access to all objects in the ADO MD.NET library, including AdomdConnection, AdomdCommand, and AdomdDataAdapter. Connection strings for the AdomdConnection simply require Data Source and Initial Catalog specifiers; the CommandText property of the AdomdCommand can then be set to any MDX query and can be used to return an AdomdDataReader (or, using the AdomdDataAdapter.Fill method, a DataTable or DataSet).

Beyond this standard ADO.NET functionality, you can also use the special AdomdCommand .ExecuteCellSet method to get back an OLAP-specific result set object called a CellSet. Under “classic” ADO MD, CellSet objects were the only OLAP result set vehicle available. Although CellSet objects are not as convenient to work with as DataReader, DataTable, and DataSet objects, they have Axes and Cells collections and thus allow for better programmatic manipulation of MDX result sets, especially when more than two axes are returned.

In addition to CellSet objects, ADO MD.NET offers other unique objects that make OLAP-oriented programming easier. ADO MD.NET makes it relatively easy to create .NET code that connects to OLAP cubes and then explores their metadata, executes MDX queries against them, and displays the results. We’ll now show you how to use ADO MD.NET to write code that queries both a cube’s data and its metadata.

Executing MDX Queries

Let’s start on the data side. Executes a simple MDX query against the Sales cube, and then binds the results to a DataGridView control called dgvResults:

AdomdConnection conn = new AdomdConnection("DataSource=localhost;Initial
AdomdDataAdapter da = new AdomdDataAdapter("SELECT
 Sales WHERE Measures.[Total Sales]", conn);
DataTable dt = new DataTable();
dgvResults.DataSource = dt;

Note that the previous code will work only if the following using directive has been placed at the top of the class file:

using Microsoft.AnalysisServices.AdomdClient;

In case you weren’t counting, we connected to, queried, and displayed results using just five lines of code, not including the using directive. Granted, we took advantage of the AdomdDataAdpater object’s ability to accept an MDX command string in its constructor and thus create an AdomdCommand object on our behalf. But the point still stands: Writing .NET OLAP code doesn’t have to be hard.

Working with More Than Two Axes

Now let’s consider a harder case: a query with three axes. In this case, we’ll need to explicitly declare and initialize AdomdConnection and AdomdCommand objects, and we’ll bring the results back as a CellSet object. We’ll insert each member of the PAGES axis in a ComboBox control called cboPages, and we’ll display all the rows and columns of the selected page (and their data) in a ListView control called lvwResults (the View property of which must be set to Details).

To start with, here’s the code that declares and initializes our AdomdConnection and AdomdCommand objects:

AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial
AdomdCommand cmd = new AdomdCommand("SELECT Shippers.[Shipper Name].MEMBERS
 FROM Sales WHERE (Measures.[Total Sales],
 Products.[Category   Name].Condiments)", conn);

Next, the code grabs the results of the three-axis MDX query in an ADO MD.NET CellSet (assuming that cs, an object of type CellSet, has already been declared at the class level):

cs = cmd.ExecuteCellSet();

Once we have the CellSet, we can iterate through axis 2 (the PAGES axis) and populate our ComboBox control with the name of each page.

foreach (Position p in cs.Axes[2].Positions)

In almost the same fashion, we can loop through the axis 1 (COLUMNS axis) positions and add an item to the ListView object’s Columns collection for each position on the axis (first adding a blank “dummy” column to accommodate the row headers that will appear):

foreach (Position p in cs.Axes[0].Positions)

Next we’ll preselect PAGE 0 by setting the SelectedIndex property of cboPages:

cboPages.SelectedIndex = 0;

In the SelectedIndexChanged event handler of cboPages, we’ll add a new item to the ListView control for each position in axis 1 (the ROWS axis), setting its text to the caption of the ROW axis. We’ll then add one SubItem to that ListViewItem for every column, fetching the FormattedValue of the corresponding cell:

foreach (Position p in cs.Axes[1].Positions)
   ListViewItem newItem = lvwResults.Items.Add(p.Members[0].Caption);
   for (int i = 0; i < cs.Axes[0].Positions.Count; i++)
      newItem.SubItems.Add(cs.Cells[i, newItem.Index,

Notice that we fetch the correct cell using a three-level coordinate, in the (column, row, page) format, where the column is determined by the inner loop’s counter value (the position in axis 0, the COLUMNS axis), the row is determined by the new ListViewItem object’s index, and the page is determined by the SelectedIndex of cboPages.

Code that implements these techniques can be found in the AdomdCommand.ExecuteCellSet region in the source code of form frmAPIs in the sample code’s Windows Forms project. Figure 7 shows the OLAP APIs form after that code has been executed.

Figure 7. The sample application’s OLAP APIs form, with a three-axis query executed and the Time.Year = All PAGES axis member displayed

Working with CellSet objects is harder than working with DataReader, DataTable, or DatsSet objects, but the results are often better. Because we grab the Caption property of each axis position/member, our column and row headers are more user friendly than the unique namebased column and row headers returned in DataReader, DataTable, and DataSet objects. 

Cell References: Coordinate-Based vs. Ordinal

Speaking of cells, they are typically referenced from a CellSet object’s Cells collection by coordinate reference. For two-axis queries, we use a (column, row) coordinate format; for three-axis coordinates, we use a (column, row, page) format. (The coordinate reference is determined by axis order.) But you can also reference cells ordinally, by supplying a single value as the index into the Cells collection. For a two-axis CellSet, the ordinal value is calculated as follows:

row number * column count + column number

Therefore, Cells[5, 2] in a CellSet with 10 columns can be also be referenced as Cells[25] (because 2 * 10 + 5 = 25). For a three-axis CellSet, the formula is as follows:

(page number * row count * column count) + (row number * column count) + (column number)


Cells[5, 2, 3] in a CellSet with 20 rows and 10 columns can also be referenced as Cells[625] (because 3 × 20 × 10 + 2 × 10 + 5 = 625). Understanding the formula for fetching cells ordinally allows you to write code that generically fetches cell values from a CellSet of any number of axes.

Cracking the Metadata

If you know how to fetch MDX result sets as DataReader, DataTable, DataSet, and CellSet objects, you can build applications that can query virtually any cube and return the results. Of course, to build these applications, you must have full knowledge of the cube’s structure and embed the MDX queries into your application code. This tends to be how we write relational applications, but it does not always work well for OLAP applications.

The whole point of OLAP is to allow users to explore their data. Just as Excel PivotTables and charts offer a Field List window where users can change their queries on the fly, your own applications might benefit from offering users a list of measures, dimensions (and their attributes and hierarchies), and KPIs. You might even want to create a high-powered front end that presents the measure groups, display folders, and other advanced metadata you used when designing your cube.

We will now discuss three distinct methods for fetching metadata in your cubes: two that use ADO MD.NET and one that uses Analysis Management Objects (AMO).

Getting Metadata from ADO MD.NET

The ADO MD.NET object model provides access to a full list of your database’s cubes and much of their metadata. This is possible because the AdomdConnection object contains a Cubes collection whose child members, in turn, contain Kpis, NamedSets, Measures, and Dimensions collections. Each object in these collections contains important details. For example, each Kpi object in the Kpis collection contains StatusGraphic and TrendGraphic properties that specify which graphics were selected when the KPI was designed. Combining this information with the data returned by the MDX KPIStatus and KPITrend functions allows you to present a rich UI for the KPI data in your cube.

Each Dimension object has a Hierarchies and an AttributeHierarchies collection (the latter containing attributes). Hierarchy objects have a Levels collection, and Level objects have a GetMembers method that returns a collection of Member objects. 

AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial
CubeDef cube = conn.Cubes["Sales"];
foreach (Measure m in cube.Measures)

Code that implements a similar technique can be found in the Measures region in the source code of form frmAPIs in the sample code’s Windows Forms project.

Schema DataSet Objects

The ADO MD.NET object model itself does not contain collections or objects for measure groups, calculated members, or actions. Instead, you can enumerate these objects by using the AdomdConnection object’s GetSchemaDataSet method, which returns a one-table DataSet containing detailed metadata. The method has several overloads, the simplest of which accepts an enumerated constant indicating the type of metadata desired and a special restriction array used to filter the metadata returned.

For example, you can use GetSchemaDataSet to get a list of measure groups quite easily. 

AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial
DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.MeasureGroups, new
 object[] { "", "", "Sales" });
dgvResults.DataSource = ds.Tables[0];

Code that implements a similar technique can be found in the Measure Groups region in the source code of form frmAPIs in the sample code’s Windows Forms project, Chapter19.

When you view the contents of the schema DataSet in the DataGridView control, you should take note of a few important things. First, notice that the third column (column 2, named CUBE_NAME) contains the cube name; this is precisely why we placed our desired cube name (“Sales”) in the third element in the inline array passed to GetSchemaDataSet. The structure of restriction arrays aligns with the columns in the schema DataSet table that they filter. Had we not specified a cube name in the restrictions array, all measure groups in the entire database would have been returned. The results of such an unfiltered query are shown in Figure 8.

Figure 8. Contents of the MeasureGroups schema DataSet bound to, and displayed in, a DataGridView control

Next, notice that the seventh and final column (column 6, named MEASUREGROUP_CAPTION) contains the actual name of the measure group. You can therefore use the following code to populate a ComboBox control called cboMeasureGroups with the measure groups in the cube:

foreach (DataRow dr in ds.Tables[0].Rows)

In addition to fetching measure groups, you can use schema DataSet objects to fetch lists of databases, calculated members, and actions (using the enumerated constants AdomdSchemaGuid.Catalogs, AdomdSchemaGuid.Members, and AdomdSchemaGuid.Actions, respectively) from a server. In fact, you can use schema DataSet objects to discover much of the same metadata that can be fetched from the ADO MD.NET object model itself. Just bear in mind that when you use ADO MD.NET, certain metadata can be fetched only using GetSchemaDataSet.

Executing Actions Using Schema DataSet Objects

GetSchemaDataSet technically returns only metadata, but sometimes this metadata is useful for more than determining a cube’s structure and contents. Specifically, if you want your application to support any actions you created when you designed your cube, GetSchemaDataSet is indispensable. 

The target of our URL is the members of the City level of the Country – State-Province – City – Postal Code hierarchy of the Geography dimension; the target of our drillthrough is the individual cells in our cube. To get specific URLs or drillthrough result sets, we can use the AdomdSchemaGuid.Actions enumerated constant and then apply our knowledge of the actions’ targets to the restriction arrays. Doing this will return specific schema DataSet objects, with tables that contain a text column called content. This column will contain a fully computed URL for the URL action and an MDX query containing the special DRILLTHROUGH command for the drillthrough action.

The following code determines the value generated by the URL action for the city of Vancouver and then navigates to that URL using a WebBrowser control.

object[] Restrictions = new object[7];
Restrictions[2] = "Sales";
Restrictions[5] = "Geography.[Country - State-Province - City -
 Postal Code].City.Vancouver";

DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).
dgvResults.DataSource = results;
MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to
navigate to action URL");



This code relies on a few things:

  • That MDACTION_COORDINATE_MEMBER has already been defined with its documented value of 4.

  • That wbrMain is a WebBrowser control.

Code that implements this technique can be found in the Actions - URL region in the source code of form frmAPIs in the sample code’s Windows Forms project.

The values we store in the restrictions array ensure that the schema DataSet returns only actions for the Geography.[Country – State-Province – City – Postal Code].City.Vancouver-level member of the Sales cube. We know that only one action has been defined for this member, so we can assume that row 0 of the DataTable object results contains that action info and that its Content column contains the specific URL for Vancouver ( The URL is displayed in a message box; when the user clicks OK, the resulting page is displayed in the WebBrowser control.

The next snippet of code works similarly, but instead of retrieving a URL it retrieves a special MDX query whose results are the drillthough data for the slice of the cube defined by sales of products in the Beverages category in Vancouver:

object[] Restrictions = new object[7];
Restrictions[2] = "Sales";
Restrictions[5] = "(Geography.[Country - State-Province - City -
 Postal Code].City.Vancouver, Products.[Category Name].Beverages)";

DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).
dgvResults.DataSource = results;
MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to
See Drillthrough Data");

txtMDX.Text = results.Rows[0]["Content"].ToString();


The code assumes that MDACTION_COORDINATE_CELL has already been defined with its documented value of 6. It then fetches an entire MDX query from the Content column of the DataTable object results, inserts that MDX text into the txtMDX TextBox control, and calls the sample code’s ExecMDX void function (mentioned earlier) to execute the query. Drillthrough queries happen to return tabular result sets rather than multidimensional ones. Because ExecMDX uses an AdomdDataAdapter to execute the query, the tabular result set is handled gracefully and is easily bound to the DataGridView control in our application. Code that implements this technique can be found in the Actions - Drillthrough region in the source code of form frmAPIs in the sample code’s Windows Forms project.

The drillthrough query, which is displayed in a message box before being executed, deserves some discussion before we move on. Here’s the text of the query:

 (Geography.[Country - State-Province - City - Postal Code].City.Vancouver,
 [Products].[Category Name].&[Beverages])  on 0
From [Sales] RETURN [Main].[Total Sales]

If you want, you can enter the DRILLTHROUGH query text into an MDX query window in SQL Server Management Studio and execute it there.

Notice that the query has three parts:

  • A SELECT query that consists of a single axis specifier that returns exactly one cell

  • The DRILLTHROUGH keyword (at the beginning)

  • A RETURN clause that indicates the measure(s) whose underlying fact data should be returned (at the end)

As long as you follow these syntax rules you can execute your own ad hoc drillthrough queries whenever you like, from your own applications or from Management Studio.

As you can see, drillthroughs are just special MDX queries; drillthrough actions do not have to be defined in order for drillthrough queries to be performed. Drillthrough actions generate DRILLTHOURGH MDX queries for you and let you define at the server which specific columns should be returned, but they are a convenience only and are not strictly necessary.

The combination of MDX and ADO MD.NET provide everything you need to create full-featured OLAP applications. But several other OLAP APIs can augment the baseline functionality provided by ADO MD.NET. We’ll continue by examining another of these APIs.

Using Analysis Management Objects (AMO)

If you’re left feeling frustrated that ADO MD.NET relies so heavily on schema DataSet objects because its native object model does not provide a full picture of Analysis Services databases, you might be interested in learning about its cousin, Analysis Management Objects (AMO). AMO allows you to discover all metadata and to modify and create Analysis Services objects as well. You can use it by simply setting a reference to its assembly, Microsoft.AnalysisServices.dll.


The Microsoft.AnalysisServices.dll assembly does not appear on the .NET tab of the Add Reference dialog box in Visual Studio. You must use the Browse tab and reference the file directly. By default, the assembly is installed in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder on your development PC.

Once you add the reference, you can import the AMO namespace into your code by using the following line of code at the top of your source code file:

using Microsoft.AnalysisServices;

The AMO library is rich with objects and methods that enable you to do virtually anything against an Analysis Services database that you can do within SQL Server Management Studio or the Analysis Services designers in Visual Studio. We cannot even begin to cover the full set of capabilities of AMO, but we’ll present a few examples.

The following code populates a ComboBox control called cboCatalogs with a list of all databases on the localhost Analysis Services server:

Server s = new Server();
s.Connect("Data Source=localhost");
foreach (Database d in s.Databases)

The following additional code adds a new (empty) database called NewDatabase:

Database dbNew = new Database("NewDatabase");

The code in Listing 1 is a bit more elaborate. It adds a new dimension called NewDim, with the attribute NewAttr, to the Adventure Works cube in the Adventure Works DW database. Notice that many of the properties manipulated are the same ones that appear in the Visual Studio Properties window when a dimension or attribute object is selected in one of the Analysis Services designers.

Listing 1. Creating Objects with AMO
// Create new dimension object NewDim:
Dimension dimNew;
dimNew = s.Databases.GetByName("Adventure Works
dimNew.Type = DimensionType.Regular;
dimNew.UnknownMember = UnknownMemberBehavior.Visible;
dimNew.AttributeAllMemberName = "All";
dimNew.Source = new DataSourceViewBinding("Adventure Works DW");
dimNew.StorageMode = DimensionStorageMode.Molap;

// Create new dimension attribute NewAttr:
DimensionAttribute atrNew = new DimensionAttribute("NewAttr");
atrNew.KeyColumns.Add(new DataItem("dbo_DimPromotion",
 "EnglishPromotionCategory", System.Data.OleDb.OleDbType.WChar, 50));
atrNew.Usage = AttributeUsage.Key;
atrNew.OrderBy = OrderBy.Name;
atrNew.ID = "EnglishPromotionCategory";
atrNew.Type = AttributeType.Regular;

// Add the new attribute to the dimension:


The Adventure Works sample Analysis Services project must be installed by explicitly including the AdventureWorks Sample OLAP item in an advanced SQL Server install (it appears three levels deep under the Documentation, Samples, and Sample Databases install option). After the install of SQL Server is complete, a second step is still required: You must run the special SQLServerSamples.msi file, which by default is installed in the C:\Program Files\ Microsoft SQL Server\90\Tools\Samples folder. A shortcut to the MSI file can be found in the All Programs\Microsoft SQL Server 2005\Documentation and Tutorials\Samples branch of the Windows Start menu. After running this MSI, the Analysis Services project will be installed by default within the C:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project folder. Open the project in Visual Studio and deploy it to your server in order to query or modify the Adventure Works sample cube.

You might want to remove the new database and the dimension (from the Adventure Works cube) in SQL Server Management Studio, after you execute the code in Listing 1 and the snippet preceding it.

These examples, although brief, should give you a sense of the power and the inner workings of AMO. Through AMO object properties and child collections, you can configure virtually every facet of an Analysis Services database. This includes not only major objects such as dimensions and measures and more esoteric ones such as scripts and translations, but also specific configuration settings for proactive caching, partitioning, and security roles.

  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 2) - Using FILESTREAM Storage for Data Columns
  •  Microsoft SQL Server 2008 R2 : Using FILESTREAM Storage (part 1) - Enabling FILESTREAM Storage
  •  SQL Server 2005 : Using Excel (part 2) - Using PivotTables and Charts in Applications and Web Pages
  •  SQL Server 2005 : Using Excel (part 1) - Working Within Excel
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 2) - Modifying the Hierarchy
  •  Microsoft SQL Server 2008 R2 : Hierarchyid Data Type (part 1) - Creating a Hierarchy, Populating the Hierarchy, Querying the Hierarchy
  •  Using SQL Server 2005 Integration Services : Extensibility (part 4) - Custom Connection Managers
  •  Using SQL Server 2005 Integration Services : Extensibility (part 3) - Script Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 2) - Custom Components
  •  Using SQL Server 2005 Integration Services : Extensibility (part 1) - Script Tasks
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us