Flex OLAP aggregating strings.

I recently got many queries about support for String aggregation in Flex OLAP. The current implementation available out of the box doesn't support this.

Here are the changes to source files required to support this. The files need to be unzipped into the fbpro directory and the datavisulization.swc needs to be build using the steps mentioned here.

The result would look something like this.

The sample project has a StringAggreator implementation which can be used as a starting point to build your own logic into the StringAggregator. The sample one appends the strings to each other.

Here is the sample project.

Of-course I might have missed something while testing. So let me know if you find any bugs !

CalculatedMeasures in Flex OLAP

I wrote a small sample which shows how a Custom Aggregator can be written and used to simulate a Calculated measure in Flex OLAP. Of-course, this solution may not work for all requirements but can be used atleast in some scenarios. The source is here.

Using Flex OLAP/OLAPDataGrid for doing Pivoting computations

Satish has developed a new PivotComponent using OLAPDataGrid and OLAP which can be used to perform pivot analysis of data.

The sample can be found here. Drag and drop one or more dimensions into the column and row fields. The results would get displayed as soon as the drop happens. Drag and drop more dimensions into column, row or slicer fields. Use the drop down attached to dimension button to pick specific members of the dimension to do slicing. The sample uses OLAPChart and OLAPDataGrid extensions to display the OLAPResult generated.

Description and source

Custom Aggregator sample for Flex OLAP

Here is a sample which shows a percent custom aggregator used for OLAP. The sample shows computation of percentage of values to the total value. The total value is assumed to be known before hand.

The PercentAgg class implements the IOLAPCustomAggregator interface. The constructor takes the total value as input and uses it to compute the percentage of values in the computeEnd and computeObjectEnd functions.

The sample also shows how OLAPAttribute class can be extended to OLAPTotal to replace the "(All)" value with a custom name "Total".

The sample also shows how OlapDataGrid can be created in AS and itemRendererProviders can be supplied to it and values displayed in ODG can be styled. Here is the complete source.

Writing a Custom Aggregator for Flex OLAP

Writing a custom aggregator for OLAP involves implementing the IOLAPCustomAggregator interface. It is easy to write a custom one based on the default ones. The default SUM, AVARAGE, MIN, MAX and COUNT aggregators source can be found in the mx.olap.aggregators directory.

The SUM, MIN, MAX and COUNT aggregators are all simple and acutally doesn't require any special handling as they are linear. The AVARAGE aggregator is special because avarage of avarages wouldn't give the correct result and hence we need to maintain proper information to arrive at the correct result.

The following sample shows how this is achieved.

IOLAPCustomAggregator has 6 functions can they can be divided into two sets.

1. computeBegin,computeLoop and computeEnd. These functions are called to initialize, compute and return the simple aggregation value.

Suppose 1..8 are the input values and they need to avaraged as two different sets 1..5 and 6..8. The sequence of function calls is one computeBegin, one or many calls to computeLoop and one call to computeEnd. 1. computeBegin can be used to prepare for a fresh computation. 2. computeLoop is the place where the acutal computation takes place. This function would get called repeatedly to add new input values to the aggregation. 3. computeEnd is supposed to return the value of the computation. This is called when cube decides that no new input values would get added to this aggregation and it is ready to receive the final value.

2. computeObjectBegin, computeObjectLoop, computeObjectEnd. These functions are called to initialize, compute and return the aggregation of aggregations.

The second set of calls would be, one call to computeObjectBegin, one or many calls to computeObjectLoop (depending on the number of aggregated values) and one call to computeObjectEnd.

As shown in the image these functions are supposed to compute the avarage of avarages. As the first set of functions saved the sum and count values separately the task is easy. We need just compute the sum of both these values from each avarage object.

In computeObjectBegin the first value to start the computation is passed. In computeObjectLoop the the computation is carried forward with additional values. In computeObjectEnd the result of the computation should be returned. Hope this helps!

Introducing OLAPTimeDimension for OLAP

In this sample though the data contained date information we didn't use it while building the OLAP cube. The reason was simple. The default OLAP implmentation in Flex 3 cannot handle dates in that format. It requires the date to be broken down into its components like year and month. If half year and quarter information is required that needs to be added to the flat data. That is a great pain point.

Here is OLAPTimeDimension to the rescue. It can be introduced as any other dimension in the OLAPCube schema and then configured to return year, half year, quarter, month and day in any combination.

<mx:OLAPCube id="salesCube" >

 <mx:OLAPDimension name="SalesData" >
   <mx:OLAPAttribute name="Company" dataField="company" />
   <mx:OLAPAttribute name="Region" dataField="region" />
   <mx:OLAPAttribute name="Market" dataField="market" />
   <mx:OLAPAttribute name="Product" dataField="product" />
    
   <mx:OLAPHierarchy name="Region-Market-Store" >
    <mx:OLAPLevel attributeName="Company" />
    <mx:OLAPLevel attributeName="Region" />
    <mx:OLAPLevel attributeName="Market" />
    <mx:OLAPLevel attributeName="Product" />
   </mx:OLAPHierarchy>
 </mx:OLAPDimension>
  
 <local:OLAPTimeDimension name="Years" dataField="date" />
  
 <mx:OLAPMeasure name="Revenue" dataField="revenue" />
  
</mx:OLAPCube>

Using this we get the following result where in we can query revenue for different years.

It can be easily extended to query quarters and months by setting includeQuarter and includeMonth to true on OLAPTimeDimension. The source is here.

Simple uses of OLAP

In Flex 3 OLAP APIs were introduced. And here is a sample showing how it can be used in simple data processing.

The sample shows how a companys product revenue over regions and markets can be compared. Click the "Create cube" button to process the input data which looks like this

<row>
  <company>Fiction</company>
  <region>Asia</region>
  <market>Digital publishing</market>
  <product>Frames</product>
  <date>3/31/2005</date>
  <revenue>10</revenue>
</row>

and build the OLAP cube. Clicking on the "Query Revenue with Product by Region" button would display the result in OLAPDataGrid. The query returns the total revenue comparing product by region across all years (2005 and 2006 in the sample data case). The last row and last column display the totals by product and totals by region respectively.

Now different markets can be selected from the ComboBox and revenues from different markets can be compared.

The source can be found here.

Creating OLAPCube in AS

In Flex 3 OLAP APIs were introduced and there have been many queries about how to create a OLAPCube in AS instead of defining it in MXML. Here is a sample :

private var salesCube:OLAPCube;

private function createCube():void
{
 salesCube = new OLAPCube();

 var dim1:OLAPDimension = new OLAPDimension("SalesData");

 //add attributes to the dimension
 var attr1:OLAPAttribute = new OLAPAttribute("Region");
 attr1.dataField = "region";

 var attr2:OLAPAttribute = new OLAPAttribute("Market");
 attr2.dataField = "market";

 var attr3:OLAPAttribute = new OLAPAttribute("Store");
 attr3.dataField = "store";

 var attr4:OLAPAttribute = new OLAPAttribute("LineOfBusiness");
 attr4.dataField = "line_of_business";

 var attr5:OLAPAttribute = new OLAPAttribute("Model");
 attr5.dataField = "model";

 dim1.attributes = new ArrayCollection([ attr1, attr2, attr3, attr4, attr5 ]);

 //add a user defined hierarchy   
 var regionHierarchy:OLAPHierarchy = new OLAPHierarchy("Region-Market-Store");

 //define the levels of the hierarchy
 var level1:OLAPLevel = new OLAPLevel();
 level1.attributeName = "Region" ;

 var level2:OLAPLevel = new OLAPLevel();
 level2.attributeName = "Market" ;

 var level3:OLAPLevel = new OLAPLevel();
 level3.attributeName = "Store" ;

 var level4:OLAPLevel = new OLAPLevel();
 level4.attributeName = "LineOfBusiness" ;

 var level5:OLAPLevel = new OLAPLevel();
 level5.attributeName = "Model" ;

  //add levels to the hierarchy
  regionHierarchy.levels = new ArrayCollection([ level1, level2, level3, level4, level5 ]);

  //add hierarchy to the dim
 dim1.hierarchies = new ArrayCollection([ regionHierarchy ]);

 //more dimensions can be defined here

 var measure:OLAPMeasure = new OLAPMeasure("Revenue");
 measure.dataField = "revenue" ;

 //more measures can be defined here

 //add the dimensions and measures to the cube.
 salesCube.elements = [ dim1, measure ];
}

This creates the same OLAPCube as the following in MXML

<mx:OLAPCube id="salesCube" >

 <mx:OLAPDimension name="SalesData" >
   <mx:OLAPAttribute name="Region" dataField="region" />
   <mx:OLAPAttribute name="Market" dataField="market" />
   <mx:OLAPAttribute name="Store" dataField="store" />
   <mx:OLAPAttribute name="LineOfBusiness" dataField="line_of_business" />
   <mx:OLAPAttribute name="Model" dataField="model" />
    
   <mx:OLAPHierarchy name="Region-Market-Store" >
    <mx:OLAPLevel attributeName="Region" />
    <mx:OLAPLevel attributeName="Market" />
    <mx:OLAPLevel attributeName="Store" />
    <mx:OLAPLevel attributeName="LineOfBusiness" />
    <mx:OLAPLevel attributeName="Model" />
   </mx:OLAPHierarchy>
 </mx:OLAPDimension>
  
 <mx:OLAPMeasure name="Revenue" dataField="revenue" />
  
</mx:OLAPCube>