The NEXT thing to try!!


Random rants and tips about NETCF, WinCE, Emacs, Powershell etc.

Presenting Flex Analysis Services Bridge!!

In my last post, I gave an idea about how one can make their flex application talk to a Microsoft Analysis Services instance. Flex Analysis service Bridge and HTTP pump together provide a layer in between to enable communication between two.

Here is the high level diagram illustrating the design of the bridge and the workflow.Flex Analysis Services Bridge


  1. An XMLA request like DISCOVER_CUBES is sent to the XMLAService.
  2. XMLAService takes the request and uses it to create a SOAP request. The created SOAP request is then sent to the HTTP pump.
  3. HTTP pump (msmdpump.dll) then forwards the request to the Microsoft Analysis Services Instance.


  1. Microsoft Analysis Services respond to the request and send the SOAP reply to the HTTP pump.
  2. HTTP pump then forwards the reply to the bridge.
  3. Bridge then parses the reply and create an actionscript representation of the reply. XMLAService then dispatches an XMLA_RESULT event which can be listened and appropriate action can be taken. After dispatching the event XMLAService also takes care of calling the responders if any registered.

Similarly, when cube.refresh() is called from the Flex application, the bridge starts reading the cube by sending SOAP requests to the HTTP pump which in turn forward these requests to the Microsoft Analysis Services instance. The ASCube keeps parsing the replies and building the cube. It also keeps sending CUBE_PROGRESS events. Once it receives all the replies, it dispatches a CUBE_COMPLETE event.

Same workflow is followed in case of query execution.

I also explained in detail the code you need to write in your flex application in my last post. You can see, after using the bridge it requires almost zero effort to connect with a remote OLAP cube. The implementation is generic enough and is supposed to work in case of a custom XMLA provider like mondrian. The only thing you need there is a serviceURL or in other words an equivalent of HTTP pump which can accepts POST requests. Feel free to use and drop in your feedback.

You can download the code here.

Filed under: actionscript, flex, flex component, OLAP

Talking with Analysis Services from Flex

Since the release of Flex 3 beta 2 with some new features like OLAP APIs and OLAPDataGrid,  requests for integrating the OLAP work with Microsoft Analysis Services have been constant coming in. So the requirement has been to make your flex application access and query a Microsoft Analysis Services instance and display the results in an OLAPDataGrid control.

For fast runners

I have came up with a bridge which lets you access an Analysis Cube <mx:ASCube/>, query it and display the results in an OLAPDataGrid control via an HTTP pump.

I am also providing an <mx:XMLAService/> which allows you discovering your datasources, catalogs and cubes. Well that is simple and my implementation just provides a wrapper to avoid the work involved in  creating each SOAP envelopes and sending them to the server.

In the next part of this post, I am going to explain how this can be achieved in detail.

Creating an HTTP Pump for HTTP access

Analysis Services 2005 uses XML for Analysis 1.1 (XMLA) as its network protocol for communicating with the server. This communication can be configured over HTTP via an HTTP pump. You need to follow these steps to create a pump:

  1. Create a base directory anywhere.
  2. Copy the contents of %Installation folder%\OLAP\bin\isapi into this folder. In my case the location was C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin\isapi\
  3. Open your IIS Server manager (Run ‘inetmgr’)
  4. Create a new application pool (say OLAP)
  5. Create a new virtual directory (say OLAP) and make it point to the base directory you created in step 1. Also in the access permissions make sure ‘Run Scripts’ is checked.
  6. Choose the application pool created in step 4 for this virtual directory.
  7. You now need to add dll as in application mapping and make it point to the %virtual directory path%/msmdpump.dll
  8. You can configure the security settings based on the security model your Analysis services allows.

A more detail tutorial to configure HTTP access can be found here. After successfully creating the pump, you should see an xml with some error written in it, when run in a browser (http://localhost/OLAP/msmdpump.dll).


XMLAService is wrapper written in Actionscript to retrieve information about the Analysis services instance configured above. It accepts three type of requests (xmlaRequest):

  1. DISCOVER_DATASOURCES – discover all the available datasources.
  2. DISCOVER_CATALOGS – for a given datasource, discover all the available catalogs.
  3. DISCOVER_CUBES – for a given catalog, find all the available cubes.

XMLAService internally converts these requests into SOAP requests and also returns the result as an Array of the resource names requested. A request object could be one of these:

<mx:Object id="catalogsRequest" type="{XMLAService.DISCOVER_CATALOGS}">
<mx:Object id="cubesRequest" type="{XMLAService.DISCOVER_CUBES}">
<mx:Object id="discoverRequest" type="{XMLAService.DISCOVER_CUBE}">

The declaration of XMLAService looks like:

<local:XMLAService id="hs" url="http://localhost/olap/msmdpump.dll" 

where xmlaResult is the event it dispatches after successfully retrieving and parsing the results. For sending a request say to discover all the available datasources one needs to write:

private function getDataSources():void
    hs.xmlaRequest = dataSourcesRequest;
private function xmlaServiceResultHandler(event:XMLAEvent):void
    if(event.requestType == XMLAService.DISCOVER_DATASOURCES)
        dataSources = event.xmlaResult as Array;

Once we have discovered the resources available and decided upon the cube we wish to connect to we can proceed with accessing our Analysis Services Cube.

Analysis Services Cube

An ASCube can be declared like this:

<ASCube:ASCube id="cube" 
catalog="Analysis Services Tutorial" 
name="Adventure Works DW"

After declaring the cube, one can trigger the cube discover process by calling cube.refresh(). Discovering a cube works by sending SOAP requests to discover all the cube elements (dimensions, hierarchies, attributes and levels) in a breadth first manner. Thus one needs to write the following code to trigger this.

private function initCube():void
    cube.addEventListener("complete", completeHandler);
    cube.addEventListener("progress", showProgress);

Querying the cube and displaying the results.

Once the complete event is dispatched, the AScube is ready to be queried. One can query the cube created above as follows:

private function executeQuery():void
    var token:AsyncToken = cube.execute(new ASQuery(query));
    token.addResponder(new AsyncResponder(resultHandler, faultHandler));
private function resultHandler(result:Object, token:Object = null):void
    odg.dataProvider = result as IOLAPResult;
private function faultHandler(info:Object, token:Object = null):void
    if(info && info.message)

Once the results are retrieved, they can be displayed using the OLAPDataGrid control by setting the dataProvider property. The ASCube implementation also takes care of the query errors. The error string returned by the Analysis Server can be obtained in the faultHandler from info.message. The info object also contains the error code ( of the error which could be useful in some situations.

I tried this workflow on AdventureWorks sample (a free sample provided by Microsoft) and it takes some 7s to read the cube structure. The implementation is in alpha phase and requires improvements based on the feedback I get. I will be posting the code tomorrow (well, it is almost ready!!). As of now I am putting some snapshots of my sample application.

  1. Querying the AdventureWorks cube and displaying the result in OLAPDataGrid. – link
  2. Querying with an erroneous query and showing the error-link

Filed under: flex, OLAP

Accessing Microsoft Analysis services using ASP

Oh!! I wasn’t aware that ADOMD makes it so easy. Using ADOMD class Library, I have created a small asp script which can make a connection with the Microsoft Analysis services and run a MDX query. The APIs also allows catching an error i.e the error while attempting an invalid MDX can also be displayed.

To start with we need to prepare Analysis services instance for connection. What this means is the instance should allow access to IUSR_MACHINENAME. This can be achieved by adding a role in the catalog you wish to connect to. Secondly, you should know the connection string which should be used for connection to the instance. For a local instance, most probably it will be

“Data Source=localhost;Provider=MSOLAP;”

Once this is done we are ready to connect to the AS instance.

  1. Declare the connection properties i.e the connection string and the default database
       1: connString = "Data Source=localhost;Provider=MSOLAP;"
       2: defaultDB = "Analysis Services Tutorial"
  2. Connect with the Microsoft Analysis services instance, using the connection properties used above
       1: 'Create a connection to the Adventure Works OLAP Database
       2:  Set cnn = Server.CreateObject("ADODB.Connection")
       3:  cnn.Open  connString
       5:  If Err.Number <> 0 Then
       6:     Response.Write Err.Description
       7:  else    
       8:     cnn.DefaultDatabase = defaultDB
      10: 'Access the Adventure Works cube catalog
      11:  Set cat = Server.CreateObject("ADOMD.Catalog")
      12:  Set cat.ActiveConnection = cnn
  3. After step 2, we have established a connection with the Analysis services instance, and we can now use the cnn connection object to query the server
       1: Set cel = Server.CreateObject("ADOMD.Cellset")
       2: cel.Source = strMDX
       3: cel.ActiveConnection = cnn
       4: cel.Open
  4. above has triggered an MDX query (i.e. strMDX here), the result of which can be read from cel (CellSet Object). I wrote a small routine to show the returned set in an HTML table
  5. Set colAxis = cel.Axes(0)
    Set rowAxis = cel.Axes(1)
    Set firstColPos = colAxis.Positions(0)
    colDepth = colAxis.Positions(0).Members.Count
    if rowAxis Then
       Set firstRowPos = rowAxis.Positions(0)
       rowDepth = firstRowPos.Members.Count
        rowDepth = 0
    end If
    cellIndex = 0
    Response.Write "<TABLE>"
        Response.Write "<TR>"
        For iCount = 1 To rowDepth
                Response.Write "<TD rowSpan="& colDepth &">  </TD>"
        For Each pos In colAxis.Positions
         Response.Write "<TD class=headerStyle><b>"
         Set m = pos.Members(0)
         Response.Write pos.Members(0).Caption & "</b></TD>"
        Response.Write "</TR>"
        For cCount = 2 To colDepth
            Response.Write "<TR>"
            For Each pos In colAxis.Positions
             Response.Write "<TD class=headerStyle><b>"
             Response.Write pos.Members(cCount-1).Caption & "</b></TD>"
            Response.Write "</TR>"
       if rowAxis Then
        For Each pos In rowAxis.Positions
            Response.Write "<TR>"
            For iCount = 1 To rowDepth
                Response.Write "<TD class=headerStyle><b>" & pos.Members(iCount-1).Caption & "</b></TD>"
            For iCount = 1 To cel.Axes(0).Positions.Count
                 if cel(cellIndex) Then
                     Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"
                     Response.write "<TD class=data>NaN</TD>"
                end If
                 cellIndex = cellIndex + 1
            Response.Write "</TR>"
         For iCount = 1 To cel.Axes(0).Positions.Count
             if cel(cellIndex) Then
                 Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"
                  Response.write "<TD class=data>NaN</TD>"
             end If
             cellIndex = cellIndex + 1
      end If
    Response.Write "</TABLE>"

The full source for the OLAP Application can be downloaded from here.

Here output which you get when querying for

Select ( [Order Date].[CalendarYear].members) ON COLUMNS, ([Customer].[English Education].Members) ON ROWS From [Adventure Works DW] Where [Measures].[Sales Amount]



Filed under: OLAP

Exploring an IOLAPCube

Leveraging the capabilities of AdvancedDataGrid, I have implemented the IHierarchicalCollectionView and created a cube browser component.

CubeBrowser provides a tree kind of view for exploring the cube elements viz dimensions, hierarchies, attributes, levels and members.

CubeBrowser exposes cube propery to build an IHierarchicalCollectionView to build the required view.

<local:CubeBrowser cube=”{myOLAPCube}”/>

Click here to see a demo application.

It also uses the drag-drop feature in AdvancedDataGrid provides, and by enabling dragEnabled propery one can drag and drop a particular item in a list. I have overridden the default drag and drop behaviour and so that the dragged items are added under “items” rather than “treeItems”. This is particularly useful in case one wants to build a query editor kind of thing.

Click here to get the source for the component.

Filed under: flex, OLAP

Adobe flex offers an in-memory OLAP Cube

With the announcement of beta 2 release of flex 3, Adobe provides support for multidimensional analysis of data. The new OLAP APIs and OLAPDataGrid, Flex 3 is offering it is now possible to do multidimensional analysis of your data and display it in a cross tab fashion in OLAPDataGrid.

The idea here is to have an in-memory representation of an OLAP Cube which can be queried using OLAP query APIs. The query APIs look similar to the well know MDX query language but query support is through APIs rather than a query language like MDX. The query result can also be shown using an OLAPDataGrid control. OLAPDataGrid control itself is highly customizable and one can use custom styles, formatters and renderers to suite your needs.

What about remote olap servers like Microsoft Analysis services, mondrian? Ahh, good question!!!the OLAP API’s are very generic and one can get the functionality by implementing the interfaces provided. If that sound like too much to do, believe me its not. I will be posting a sample very soon for interfacing with Microsoft Analysis services.

If you are finding this interesting and want to find out how to achive this, read the write up here. There is also a captivate video which demonstrates how to create your first cube.

So my suggestion is do give it a try!!

Flex 3 beta 2 can be download from the following link

Filed under: flex, OLAP

About the author

25, Male, living in Hyderabad Mobile Developer, working for Microsoft IDC

View Nisheet Jain's profile on LinkedIn

RSS Mobile Dev Blog

  • An error has occurred; the feed is probably down. Try again later.