Complete OLAP infrastructure without Microsoft Analysis Services, part 3

In previous part of this tutorial we've built an analytical database using Microsoft Analysis Services. We've been able to browse the database using SQL Management Studio and execute MDX queries on it.

In this part of the tutorial we will:

• build an offline static cube (*.cub file) from the relational database using C# and ADOMD.NET
• query the static cube using Microsoft Excell as static cube browser
• query the static cube with MDX queries using C# and ADOMD.NET

Building static cubes from relational data using ADOMD.NET

This step occured to be the most difficult one. It seems that building static offline cubes with the PivotTable Services technology I've mentioned before is well known and used. However, the same approach fails with ADOMD.NET since the ADOMD.NET API does not directly correspond do the PivotTable Services API. It turns out that completely different approach must be taken. The approach will use XMLA (Xml for Analysis) together with ADOMD.NET as primary tools.

What you should do is to write an XMLA script which would create the database and execute this script as command on the static offline cube the ADOMD.NET is connected to.

The question is: how to obtain the correct XMLA script?

The answer is: since XMLA is just an XML, you can write the script manually. However, having the Analysis Services elsewhere you can:

• connect to the Analysis Services using Management Studio
• select the database in the Object Explorer
• invoke Script Database/CREATE To action available from the context menu
• save the script elsewhere

Note, that the XMLA script will be rather unreadable and long. The script generated for the analytical database we've built for our tutorial is 57kB long!

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

    <ObjectDefinition>

        <Database xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2">

            <ID>CubeDemo</ID>

            <Name>CubeDemo</Name>

            <Language>1033</Language>

            <Collation>Polish_CI_AS</Collation>

            <DataSourceImpersonationInfo>

                <ImpersonationMode>Default</ImpersonationMode>

            </DataSourceImpersonationInfo>

            <Dimensions>

                <Dimension>

                    <ID>PERSON</ID>

                    <Name>PERSON</Name>

                    <Annotations>

                        <Annotation>

                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:DiagramLayout</Name>

                        </Annotation>

                        <Annotation>

                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowFriendlyNames</Name>

                            <Value>true</Value>

                        </Annotation>

                        <Annotation>

                            <Name>http://schemas.microsoft.com/DataWarehouse/Designer/1.0:ShowRelationshipNames</Name>

                            <Value>false</Value>

                        </Annotation>

                        <Annotation>

... a lot of XMLA code follows

What is really important is one specific section of the script:

... a lot of XMLA stuff above

<DataSources>

      <DataSource xsi:type="RelationalDataSource">

          <ID>Cube Source</ID>

          <Name>Cube Source</Name>

          <ConnectionString>Provider=SQLNCLI.1;Data Source=WZYCHLA\SQL2005;Integrated Security=SSPI;Initial Catalog=CubeSource</ConnectionString>

          <ImpersonationInfo>

              <ImpersonationMode>ImpersonateServiceAccount</ImpersonationMode>

          </ImpersonationInfo>

          <Timeout>PT0S</Timeout>

      </DataSource>

  </DataSources>

This particular section of the script defines the connection to the relational database serving as the source for the analytical database. In real application, you read the script generated in your development environment containing the connection string to your relational datasource and dynamically change the connection to a correct one, valid in production environment! You can event connect to other data providers like Oracle or PostreSQL, just build the analytical database on the Analysis Services and check the corresponding DataSource node in the generated script (scripts for different DBMSes seem to be quite different, it's not that you only replace the connection string!)

This XMLA script, however, is still not enough to correctly create static offline cube. You have to wrap it in a stub script, defining an actual action for the analytical engine.

The wrapper script is presented below:

<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'>

CUBESCRIPTHERE

<Parallel>

  <Process xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'>

    <Object>

      <DatabaseID>CubeDemo</DatabaseID>

    </Object>

    <Type>ProcessFull</Type>

    <WriteBackTableCreation>UseExisting</WriteBackTableCreation>

  </Process>

</Parallel>

</Batch>

There's one crucial issue here: note that the XMLA script generated from the Analytical Database contains Create/ObjectDefinition/Database/Name node and the stub contains Parallel/Process/Object/DatabaseID node. Values of these nodes must match! Otherwise you'll get nothing since the actual command will try to create the database for which there'll be no definition available in the script above!

Building the offline static cube using C# and ADOMD.NET

Now, let's switch to C#, assuming that you have installed all the components I've mentioned previously. Specifically, these components are required:

• Microsoft ADOMD.NET


• Microsoft Core XML Services (MSXML) 6.0


• Microsoft SQL Server Native Client


• Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider (very, very important!)

Create a console application and add a reference to the Microsoft.AnalysisServices.AdomdClient library which is a part of the ADOMD.

Now, we are ready to create a static cube (note that the ResourceManager is my custom class which reads resources embedded in assembiles. You have to replace my ResourceManager to your own code):

AdomdConnection conn = new AdomdConnection();

/* note that the data source points to a FILE */

conn.ConnectionString = 

    string.Format( "Provider=MSOLAP;Data Source={0}", "c:\\000\\cubedemo.cub" );

try

{

    conn.Open();

    AdomdCommand cmd = new AdomdCommand();

    cmd.Connection = conn;

    /* this returns the stub XMLA */

    string xmlastub = ResourceManager.GetString( "xmlastub.txt" );

    /* this return the XMLA which contains scripted database */

    string xmlacube = ResourceManager.GetString( "xmlacube.txt" );

    /* replace the CUBESCRIPTHERE with actual script */

    cmd.CommandText = xmlastub.Replace( "CUBESCRIPTHERE", xmlacube );

    /* this does the job */

    cmd.Execute();

    Console.WriteLine( "ok" );

}

catch ( Exception ex )

{

    Console.WriteLine( ex.Message );

}

finally

{

    conn.Dispose();

}

Console.ReadLine();

And this is it! If you've installed all required components, you'll eventually end with a static cube located in the C:\000\cubedemo.cub file!

Using Microsoft Excel to browse static offline cubes

If you have the Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider installed on your client OS, you can browse the static offline cube with Microsoft Excel easily. Just invoke Open command from Excel menu, select All files filter, so that the *.cub file becomes visible and just select it. Excel will happily open the offline cube and you'll be presented with an interface allowing you to build pivot tables:

Using C#, ADOMD.NET and MDX to browse static offline cubes

Using ADOMD.NET you can connect to any analytical database and execute MDX queries on it (in fact, you can also execute XMLA queries with MDX statements embedded inside, the XMLA extends MDX by allowing MDX statements to be included inside). The schema of the code is similar to the previous case of creating cubes, with the only difference that you specify the MDX statement and read the data using AdomdDataReader or fill DataSets using AdomdDataAdapter (just like any ADO.NET provider would do).

Create a C# Windows Forms application, put a Button and a DataGridView on it and in a click event handler of the button paste the following code:

AdomdConnection conn = new AdomdConnection();

 conn.ConnectionString =

     string.Format( "Provider=MSOLAP;Data Source={0}", "c:\\000\\cubedemo.cub" );

 try

 {

     conn.Open();

     AdomdCommand cmd = new AdomdCommand();

     cmd.Connection = conn;

     /* XMLA query used previously in Management Studio */

     cmd.CommandText =

         "select " +

         "[DIVISION].[Name].MEMBERS on ROWS, " +

         "[PERSON].[Name].MEMBERS on COLUMNS " +

         "from [Cube Source] " +

         "where [Measures].[SALE Count] ";

     /* ADOMD is just an ADO.NET provider and so 

      * principles are the same

      */

     AdomdDataAdapter da = new AdomdDataAdapter( cmd );

     DataSet ds = new DataSet();

     da.Fill( ds );

     dataGridView1.DataSource = ds.Tables[0];

This yields following visual effect:

 

Other ADOMD.NET applications

The ADOMD.NET library is not developed to support static cubes only. In fact, it supports any analytical data source - you can also create or browse data in the Microsoft Analysis Services as well.

It then means that you can build an analytical layer on your application using ADOMD.NET and only choose whether to use full Microsoft Analysis Services or free static offline cubes according to project conditions.