The NEXT thing to try!!

Icon

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

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

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:DATASOURCE_NAME></mx:DATASOURCE_NAME>
</mx:Object>
 
<mx:Object id="cubesRequest" type="{XMLAService.DISCOVER_CUBES}">
    <mx:DATASOURCE_NAME></mx:DATASOURCE_NAME>
    <mx:CATALOG_NAME></mx:CATALOG_NAME>
</mx:Object>
 
<mx:Object id="discoverRequest" type="{XMLAService.DISCOVER_CUBE}">
    <mx:DATASOURCE_NAME></mx:DATASOURCE_NAME>
    <mx:CATALOG_NAME></mx:CATALOG_NAME>
    <mx:CUBE_NAME></mx:CUBE_NAME>
</mx:Object>

The declaration of XMLAService looks like:

<local:XMLAService id="hs" url="http://localhost/olap/msmdpump.dll" 
    xmlaResult="xmlaServiceResultHandler(event)"/>

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;
    hs.send();
}
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" 
dataSource="NISHEET03" 
catalog="Analysis Services Tutorial" 
name="Adventure Works DW"
serviceURL="http://localhost/olap/msmdpump.dll"/>

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);
    cube.refresh();
}

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)
        Alert(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 (error.id) 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
Advertisements

Filed under: flex, OLAP

19 Responses

  1. […] About Talking with Analysis Services from Flex […]

  2. Scott says:

    This looks really cook. I was about to try and implement this myself. We are going to try and hook it up to Mondrian…

    Any chance you will publish the flex code anytime soon..

    Thanks,
    Scott

  3. Kevin says:

    I’ve been trying to get this to work. I get the following error:

    The prefix “local” for element “local:XMLAService” is not bound.

    I’m new to Flex and we’re using SQL Server’s Analysis Services. Maybe you could help me out. =D

  4. Phil Jeary says:

    I have a few questions regarding this that i’d love to get some input on;

    Where was your source of information? I tried to find how to connect flex 3 to msmdpump.dll but could not find any details.

    What does it look like on screen? Does the user get all of the dimensions and measures to choose from etc?

    Is it possible to connect to the olap server and get results for a pre-determined set of dims and measures without letting the user change anything of it, so it just shows a graph?

    Regards,
    Phil

  5. Madhav says:

    Hi,

    The sample didn’t work.

    I’m using Analysis Service 2005 and the configured it as described here. I’m getting the XML error page as expected when I open the URL http://localhost/OlapTest/msmdpump.dll

    All the mxml file have same URI reference as above. When I start the XMLAServiceSample.swf file in my browsers on the same machine where MSAS is configured, and click “DISCOVER DATASOURCES” button, then it takes more than 5 minutes. There is no item in the combo box after the ‘discover datasources’ completes.

    When I finally try to execute the query I get the following error:

    text = “Error #2048: Security sandbox violation: file:///D:/RRM/XMLAServiceSample.swf cannot load data from http://dev-gem1/olap/msmdpump.dll.”
    type = “securityError”

    Could you tell me if some configuration is wrong?

    -Thanks,
    Madhav

  6. Amar says:

    I have tested the AnalysisBridge component against Mondrian OLAP Engine and it works great. I was wondering if the source code is available under Open Source license?

    Please let me know.
    -Thanks
    Amar

  7. Shivakanth says:

    Hey Amar,
    i’m trying to do something similar to what you’ve achieved,can you please let me know what are the steps involved in connecting Mondrian using the AnalysisBridge,thanks in advance.

    cheers,
    Shiv.

  8. shyam says:

    Hi
    My intention is too display result in flex ui and i dont want to have huge load on flex ui so i want to use mondrian in middle and expose its services as xmla so that i can grab those services through flex is this possible.?

  9. shyam says:

    Do the above code only works only for msas cant it work for mondrian..?

  10. shyam says:

    Hi Amar can you expalin the code that how you connect to mondrian ?

  11. shyam says:

    hai Every at Last after several corrections and R&D Atlast i Suceeded in Diaplying the Result in Flex and Mondrian.

  12. ak says:

    hi shyam,

    can you please help me with setting up mondrian for flex. It would be extremely helpful for me.
    TIA

  13. saindou says:

    salut j’ai repris le tuto FlexAnalysisservicesBridge,mais je suis coincé par le namespace fc utilisé (xmlns:fc=”http://www.adobe.com/2006/fc”),j’ai l’erreur suivante(au niveau de la balise fc irresolvable.
    si quelqu’uun pourrait m’aider à debloquer ah mon Dieu.J’utilise flexBuilder3 avec BlazeDs,j’ai configurer le http sous win xp,et j’utilise sqlserver2008 anslysis

  14. André Filipe says:

    Hey… I’m having serious problems with namespaces.
    In your example, you declare ASCube and XMLAService members of fc namespace but there is no entities like that in “http://www.adobe.com/2006/fc”. In your quotes you declare this members like ASCube:ASCube and local:XMLAService, which gives error messages as well.

    I mean, the article is neat, it’s good but it’s confusing when it comes to the namespacing.

  15. Arturas says:

    Nice, we’ve done it in a little different way, check out how it is working in our flex pivot table component at http://www.flexmonster.com

  16. pp says:

    Hi,

    How do you make the request if who need to send the user and password?

    I’m new in flex and i need to connect to Analysis Services. The pump is already created.

    Thanks

  17. rei says:

    great we can import, now how to export the results to excel or flat file ;?

  18. sen says:

    Hi, Can you help me to connect SSAS Olap cube from Flex?
    I tried based on your demo but no luck.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: