"We get" OLAP cube (ADOMD.NET)

So, we continue to master the insides of OLAP cubes (previous articles on this topic: 1 , 2 , 3 ). On the agenda of the actual question: "How can you get OLAP cube program?" . I understand - theory-theory, but if the data cannot (or is very difficult) to be obtained from the code, then such technologies are worthless. Fortunately, everything is very simple here - do you know ADO.NET ? Well, this is the same thing, only called ADOMD.NET .
How is that, say you - another library that you need to study? In general, of course, you can also “get” a cube through OLEDB (I'll write something about how to do it), but then all the specific features of multidimensionality are lost, and the ADOMD.NET library itself is quite simple, which I will now show.
So let's get started.

Environment

As always, let's start with the environment. For today's recipe we need:

• Microsoft SQL Server running Analysis Services (2005/2008);
• HabraCube , created in the previous article (see below);
• Visual Studio (2005/2008) or Visual C # 2008 Express Edition
• ADOMD.NET (9.0 / 10.0)

If you do not have Habrakub, you can create it very quickly by reading the following chapter, you can skip the rest.

XMLA or quickly create habrakub

If you use Microsoft SQL Server Management Studio , you probably noticed a number of buttons for creating various queries, located at the top:
')


The first two buttons are understandable, but the following 3 most likely require explanation:

• MDX Query - creates an MDX request to fetch data from a cube (in the first article in this series there are several examples);
• DMX Query - no, this is not a tautology, DMX queries are intended for selecting data from Data Mining structures;
• We need exactly the last button of the XMLA Query - XMLA is an XML-based analytic system management language;

For example, in our case, to quickly create Habrakub, you can simply run an XMLA request by downloading it from here .

Happened? Now to fill the cube with data, download my old SQL script to create a Habra-storage of votes. And run the cube for processing - to do this, connect your Management Studio to Analysis Services , select Habracub and then the Process command in the context menu. It should be noted that, by default, the datasource for a cube is the local (local) storage of HabraDW . If you want to change the connection string, open the Data Sources folder of your cube and change the Habra DW parameters.

We continue ...

ADOMD.NET

The ADOMD.NET library can be downloaded from the Microsoft SQL Server 2008 Feature Pack page:

• X86 Package (SQLSERVER2008_ASADOMD10.msi) - 4312 KB
• X64 Package (SQLSERVER2008_ASADOMD10.msi) - 9263 KB
• IA64 Package (SQLSERVER2008_ASADOMD10.msi) - 6776 KB

Hello World, Part 1

So, the first thing you want to do is subtract the definition of a cube, that is, from which dimensions and hierarchies it consists.
Create a console C # project, add a reference to ADOMD.NET :



And use the following code:

// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=(local);initial catalog=HabraCube;" ;
mdConn.Open();

// iterate through cubes
foreach (CubeDef cube in mdConn.Cubes)
{
if (cube.Type != CubeType.Cube) continue ;
Console .WriteLine( "*** Cube: " + cube.Name);

// iterate through dimensions
foreach (Dimension dimension in cube.Dimensions)
{
Console .WriteLine( "-> Dimension: " + dimension.Name);

// iterate through hierarchies
foreach (Hierarchy hierarchy in dimension.Hierarchies)
{
Console .WriteLine( "--> Hierarchy: " + hierarchy.Name);

// iterate through levels
foreach (Level level in hierarchy.Levels)
{
Console .WriteLine( "---> Level: " + level.Name);
}
}
}
}
}

* This source code was highlighted with Source Code Highlighter .

Result:


Clear enough, isn't it?

Hello World, part 2

Now run the MDX query and subtract the result. For this we use the following code:

// prepare adomd connection
using (AdomdConnection mdConn = new AdomdConnection())
{
mdConn.ConnectionString = "provider=msolap;Data Source=(local);initial catalog=HabraCube;" ;
mdConn.Open();
AdomdCommand mdCommand = mdConn.CreateCommand();
mdCommand.CommandText = mdx; // << MDX Query

// work with CellSet
CellSet cs = mdCommand.ExecuteCellSet();

// our method supports only 2-Axes CellSets
if (cs.Axes.Count != 2) return ;

TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;

// output column headers
Console .Write( "{0,-12}" , "Item" );
for ( int col = 0; col < tuplesOnColumns.Count; col++)
{
Console .Write( "{0,-12}" , tuplesOnColumns[col].Members[0].Caption);
}
Console .WriteLine();

// output rows
for ( int row = 0; row < tuplesOnRows.Count; row++)
{
Console .Write( "{0,-12}" , tuplesOnRows[row].Members[0].Caption);

// fill columns
for ( int col = 0; col < tuplesOnColumns.Count; col++)
{
Console .Write( "{0,-12}" , cs.Cells[col, row].Value);
}
Console .WriteLine();
}
}

* This source code was highlighted with Source Code Highlighter .

We try to run the following MDX request:

SELECT
{[Measures].[Vote], [Measures].[Votes Count ]} ON COLUMNS,
[Dim Time ].[ Month Name].MEMBERS ON ROWS
FROM [HabraCube]

* This source code was highlighted with Source Code Highlighter .

Result:


The original project can be found at this link.