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:
- Create a base directory anywhere.
- 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\
- Open your IIS Server manager (Run ‘inetmgr’)
- Create a new application pool (say OLAP)
- 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.
- Choose the application pool created in step 4 for this virtual directory.
- You now need to add dll as in application mapping and make it point to the %virtual directory path%/msmdpump.dll
- 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):
- DISCOVER_DATASOURCES – discover all the available datasources.
- DISCOVER_CATALOGS – for a given datasource, discover all the available catalogs.
- 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.
- Querying the AdventureWorks cube and displaying the result in OLAPDataGrid. – link
- Querying with an erroneous query and showing the error-link
Filed under: flex, OLAP
CubeBrowser exposes cube propery to build an IHierarchicalCollectionView to build the required view.
The NEXT thing to try!! 