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.
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
Catalog=Chapter18");
AdomdDataAdapter da = new AdomdDataAdapter("SELECT
Shippers.[Shipper Name].MEMBERS ON COLUMNS, Time.Year.MEMBERS ON ROWS FROM
Sales WHERE Measures.[Total Sales]", conn);
DataTable dt = new DataTable();
da.Fill(dt);
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
Catalog=Chapter18");
AdomdCommand cmd = new AdomdCommand("SELECT Shippers.[Shipper Name].MEMBERS
ON COLUMNS, Geography.Country.MEMBERS ON ROWS, Time.Year.MEMBERS ON PAGES
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):
conn.Open();
cs = cmd.ExecuteCellSet();
conn.Close();
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)
{
cboPages.Items.Add(p.Members[0].Caption);
}
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):
lvwResults.Columns.Add(String.Empty);
foreach (Position p in cs.Axes[0].Positions)
{
lvwResults.Columns.Add(p.Members[0].Caption);
}
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:
lvwResults.Items.Clear();
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,
cboPages.SelectedIndex].FormattedValue);
}
}
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.
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
Catalog=Chapter18");
conn.Open();
CubeDef cube = conn.Cubes["Sales"];
foreach (Measure m in cube.Measures)
{
cboMeasures.Items.Add(m.Caption);
}
conn.Close();
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
Catalog=Chapter18");
conn.Open();
DataSet ds = conn.GetSchemaDataSet(AdomdSchemaGuid.MeasureGroups, new
object[] { "", "", "Sales" });
dgvResults.DataSource = ds.Tables[0];
conn.Close();
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.
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)
{
cboMeasureGroups.Items.Add(dr["MEASUREGROUP_CAPTION"]);
}
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";
Restrictions[6] = MDACTION_COORDINATE_MEMBER;
DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).
Tables[0];
dgvResults.DataSource = results;
MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to
navigate to action URL");
wbrMain.Navigate(results.Rows[0]["Content"].ToString());
This code relies on a few things:
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 (http://search.msn.com/results.aspx?q=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)";
Restrictions[6] = MDACTION_COORDINATE_CELL;
DataTable results = conn.GetSchemaDataSet(AdomdSchemaGuid.Actions, Restrictions).
Tables[0];
dgvResults.DataSource = results;
MessageBox.Show("Content = " + results.Rows[0]["Content"].ToString() + "\nClick OK to
See Drillthrough Data");
txtMDX.Text = results.Rows[0]["Content"].ToString();
ExecMDX(conn);
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:
DRILLTHROUGH Select
(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.
Caution
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)
{
cboCatalogs.Items.Add(d.Name);
}
The following additional code adds a new (empty) database called NewDatabase:
Database dbNew = new Database("NewDatabase");
s.Databases.Add(dbNew);
dbNew.Update();
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
DW").Dimensions.Add("NewDim");
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:
dimNew.Attributes.Add(atrNew);
dimNew.Update();
|
Important
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.
