Friday, June 27, 2008

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.

21 comments:

SSAS-Info.com said...

Link to this post added to our website in the [SSAS Articles]/[Local Cube] section:
Complete OLAP Infrastructure without Microsoft Analysis Services, Parts 1-3

Anonymous said...

Hi! Great article. It helped me a lot. But could you describe, how exactly works ResourceManager class? It cut a piece of xmla script, and combine it with a stub?

Wiktor Zychla said...

the ResourceManager is really basic. it just reads the file content from embedded resources.

instead you can read it from file like this:

string cubescript = ( new StreamReader( "cube.xmla" ) ).ReadToEnd();

the script has to be correctly embedded inside the stub - without the stub the execution of the generated script has no effect.

Anonymous said...

Thanks for answer. But I've got one question. Can we connect to 'cubedemo.cub' from a OWC PivotTable controll in VS2005? Because, I've tried that already, but there is an error -

"The query could not be processed:
o The following system error occurred: The process cannot access the file because it is being used by another process."

We can open it only with using excel?

Wiktor Zychla said...

local cubes can be opened by a SINGLE process at the same time. this is not a big issue, since the application server uses a single process but many threads, however if you play with your cube, you have to remember to open it with one tool at the same time.

Anonymous said...

Hi,

I am not able to have more then single connection with a local cube.

Also only i am able to access the local cube file.

please help.

Thanks

Wiktor Zychla said...

could you describe the scenario more precisely?

Anonymous said...

Hi,

I have one localcube file in my Desktop application MDI App. I am using it cube as datasource in MDI Childrens with Dundas Olap Charts and with ADOMD.NET to populate datagrid in VB.NET.

My Problem is:- i am able to have only one connection with my local cube but i want to have many as i have MDI app.

dundas chart is using ADOMDNetprovider with which connection remains active as long as form is there.

please help

Wiktor Zychla said...

please write an email to me in two weeks when I am back from summer holidays. I will take a look at your issue.

MilPhilner said...

enjoyed your article,
ever deployed a cube via xmla and it not preserve your datasource credentials?

MilPhilner said...

ok, a little more focus to my question:

anyway to do the deploy but encrypt the pw in the connection,
I think BIDS does this with an encrypted file for the pw but how to do it w/ the command line style deployment?

Anonymous said...

When I try this, my app fails at conn.Open(), inner exception says the .cub file can't be opened. Anyone else run into this?

I'm running the app in XP x64, trying to build local cub file from SSAS running on SQL 2k5 on Win 2003.

I only mention this because I saw elsewhere someone reported issues trying to use local cubs w/ 64-bit OS.

Anonymous said...

Nevermind...I got past the "can't open cub file" problem by installing 64-bit versions of ADOMD via the "Feature Pack for SQL Server."

Anonymous said...

thanks for the excellent article. I followed the steps and installed pre-requisites but when I try it, I get "A connection cannot be made. Make sure the server is running" when trying to open connection. I am running on XP, source is Studio 2008 using VB (tried both target framework 3.5 and 2.0). I included a reference for adomdclient but I am not sure how the rest of the pre-requisites are used.

Wiktor Zychla said...

What is your ADOMD connection string? Aren't you trying to connect to Microsoft Analysis Services instead of connecting to static *.cub file?

Fernando said...

Good article, helped-me a lot, I sugest an update, I couldnt create the cube before I added the node Teste Olap inside the node object in the wrapper, caused me a lot of trouble.

cheers

Fernando said...

ops, the text didnt show what I mean, I mean a child node of the Object node of the wrapper by the name of CubeID, with the name of the cube itself...

Anonymous said...

Useful article.

To create offline cubes, you suggested to create an application and add a reference to the Microsoft.AnalysisServices.AdomdClient library which is a part of the ADOMD.NET. Do you know under which license ADOMD.NET is available?

Wiktor Zychla said...

I doubt there's a specific license, most probably it's free as it's downloadable from MSDN site.

Anonymous said...

Hi,
I have tried your methods and they worked out great. But, I need a way to dynamically create the XMLA File as I need to put in filters on dimensions and/or members of dimensions to be included in the cube. I need to do this using c#. Any help you can provide would be greatly appreciated.

Gilmar de Oliveira said...

hello your article is excellent.
I wonder if it is possible to create the offline cube based on a role?