The NEXT thing to try!!

Icon

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

Request:

  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.

Reply:

  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

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

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
       4:  
       5:  If Err.Number <> 0 Then
       6:     Response.Write Err.Description
       7:  else    
       8:     cnn.DefaultDatabase = defaultDB
       9:  
      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. cel.open 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
    else 
        rowDepth = 0
    end If
     
    cellIndex = 0
     
    Response.Write "<TABLE>"
     
        Response.Write "<TR>"
        For iCount = 1 To rowDepth
                Response.Write "<TD rowSpan="& colDepth &">  </TD>"
        Next
     
        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>"
        Next
     
        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>"
            Next
            Response.Write "</TR>"
        Next
     
       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>"
            Next
            For iCount = 1 To cel.Axes(0).Positions.Count
                 if cel(cellIndex) Then
                     Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"
                 else
                     Response.write "<TD class=data>NaN</TD>"
                end If
                 cellIndex = cellIndex + 1
            Next
            Response.Write "</TR>"
            Next
      else
         For iCount = 1 To cel.Axes(0).Positions.Count
             if cel(cellIndex) Then
                 Response.write "<TD class=data>" & cel(cellIndex).FormattedValue & "</TD>"
             else
                  Response.write "<TD class=data>NaN</TD>"
             end If
             cellIndex = cellIndex + 1
          Next
      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]

Output:

asresult

Filed under: OLAP

My AIR apps wishlist

I have been constantly keeping myself updated with all the apollo apps coming up. Since the alpha release of apollo lot of Desktop applications have been built on top of apollo. Here is a list of few of the applications built using AIR. I particularly liked Fresh, DiggTop, SearchCoders, AirTalkr and AirPress, reason being that they fall into the category of things I do most of the time (I am not a flickr and youtube fan, and I hardly visit these sites). I believe apollo has got capabilities to provide a good compettion to those old .NET based windows apps, and replace the traditional desktop experience.

With the apollo getting matured, I have came up with a wishlist of apps/features, I would love to see in the future. Here it goes:

  1. A mail notification app which can handle atleast IMAP.
  2. A yakuake kind of terminal window, in general a way to wrap windows application in apollo so that I can customize it’s layout based on my needs.
  3. An Object Dock kind of fish eye component, which acts as a quick launch bar in windows. Similarly, If I want a Alt-F2 kind of utility(which is there in linux to run any application) or may be in windows there is a Launchy utility. As of now this is not possible, because AIR doesnt allow executing an external application.
  4. An expose like effect it an application. Something which can allow pushing some basic effects and layouting of currently active WINDOWS apps.

Beside this I really wish if we can get rid of Adobe’s right click philosphy and have good support of customizing the context menu, atleast for AIR, it makes sense to do this (Because AIR is not just flash!!!). I strongly thing with out context based context menu any application will lag that experience which most of the world is used to (Linux and Windows users). Say AIRPress, while writing a blog w/o the right click it becomes little uneasy to do things like spelling correction, and some basic formatting stuff similar to what we get in Windows Live Writer (and that is why I prefer it). Well Live Writer also has got other features like playing with the inserted images. It also allows inserting maps


Adobe has now release beta2. Here one can read the list of features added in this release.

Filed under: flex

Well old news now, Gutsy Gibbon is out

As per the schedule, Canonical (a commercial sponsor of ubuntu) has released Ubuntu 7.10 (Gutsy Gibbon). The release new describing the features introduced can be read here. A detailed description of the features can be read here.

One can download the iso from the following link. You can also request free CDs from shipit.

Along with this the release schedule for the next release Hardy Heron (Ubuntu 8.04) is also up and can be seen here. The plan is have the next release on 24th April. Well the count down has begun.

Filed under: linux, ubuntu

Putting Scrollable Code in wordpress

<div style=”overflow: auto; height: 350px; width: 100%; background-color: #aaaaaa “>
<pre style=”border-top: 2px solid #aaaaaa; border-bottom: 2px solid #aaaaaa; width: 100%;font-family: ‘Courier New’,Courier,Fixed”>

<– Your Code goes here — >

</pre></div>

Code that goes within these tags looks like this. If the lines are long, then it will automatically insert horizontal scrollbars. Check out the vertical and horizontal scrollbars.

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

Some extra lines for vertical scrollbar to appear

I checked the approach for Firefox-2.0.0.7 and IE6 and it was working fine. There is one problem, that after saving the post if you want to re-edit the post, wordpress would have modified the code. So it is advisable to put these tags just before publishing your post.

Filed under: Blogging

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

Adobe Flex 3 beta 2 is out, Linux FB? WOW!!

Well the old news first, Adobe has released Flex 3 beta 2 with whole lot of improvements and few new features. With this release, flex community has got oppurtinity to explore the capabilities Adobe flex 3 will be offering in their final release. Read the release notes here.

October 2 was a big day for Adobe Flex, when Ram showcased about FlexBuilder Linux at MAX 2007. Read swaroop’s post (the developer behind the success). An excerpt from his post:

Now presenting the alpha of the Flex Builder IDE on Linux! (this was announced at the Adobe MAX 2007 conference). You can now create Flex projects, write code with intellisense-like hinting, compile and debug all within an IDE based on Eclipse, on Linux. You heard it right, it’s officially supported by Adobe

Read the corresponding slashdot article and discussion by mikepotter.

This will surely help to create a buzz in the linux developers community. After reading the discussion which is going I realized many people haven’t even heard about flex and somebody interpreted it as “Flex builder is a fast lexical scanner generator builder”. Now when they have an option to build RIA applications in linux also (using an IDE), many people will surely give flex a try. Adobe is offering it FREE and so the price concern is also not there.

I am just waiting for the time when Adobe will give an AIR (apollo) for linux and the linux flex builder will start supporting development of apollo applications. They have promised it on labs. I personally consider Apollo as a huge step by Adobe and having it on linux will make developing desktop applications very easier. Once this thing is achived I am sure Adobe Flex will get to see a large growth in the flex community.

Filed under: flex, linux

AIRPress, blogging on wordpress

Airpress, a nice kool little AIR app for blogging in wordpress. With the offline edition mode, it fits very well with the requirements of a blogging Desktop application like Windows Live Writer.
With all the super kool effects and UI, it just looks great. Drag and drop support for inserting images and other content is also helpful. I am myself writing this post from AirPress itself.

In my first attempt with the tiny app I found few issues:

  1. It doesn’t allows something like editing html so the corruption made by the UI can’t be corrected.
  2. Drag and drop operation on an image can be undone.
  3. You can’t place the text around an image in smarter ways.
  4. And last but not the least don’t even attempt drag and drop of a swf, it starts playing the swf in the player. May be a FOL only. But I think drag drop of only limited filetypes should be allowed. Anyways, wordpress will not accept the other filetypes.
  5. Undo functionality doesn’t work properly.
  6. And ya, I found an RTE also while dragging an HTML file
ArgumentError: The width of an HTMLControl can not be greater than 2880.at flash.html::HTMLControl/flash.html:HTMLControl::validateAndConvertDimension()[C:\Documents and Settings\acrorel\Local Settings\Temp\aslibc-28157\HTMLControl.as:1045]at flash.html::HTMLControl/set width()[C:\Documents and Settings\acrorel\Local Settings\Temp\aslibc-28157\HTMLControl.as:816]at org.airpress.component.media::HTMLMedia/::normalizeSize()
at MethodInfo-3991()
at flash.events::EventDispatcher/flash.events:EventDispatcher::dispatchEventFunction()
at flash.events::EventDispatcher/dispatchEvent()[C:\Documents and Settings\acrorel\Local Settings\Temp\aslib-28157\EventDispatcher.as:209]
at flash.html::HTMLControl/flash.html:HTMLControl::onCompleteTimer()[C:\Documents and Settings\acrorel\Local Settings\Temp\aslibc-28157\HTMLControl.as:2101]
at flash.utils::Timer/flash.utils:Timer::_timerDispatch()
at flash.utils::Timer/flash.utils:Timer::tick()[C:\Documents and Settings\acrorel\Local Settings\Temp\aslibc-28157\Timer.as:158]

Filed under: Blogging, flex

Follow

Get every new post delivered to your Inbox.