Analyzing the Subversion logs from the Rails project with mx:OLAPCube 6
I started playing with Anthony Eden’s ActiveWarehouse and followed his excellent tutorial on how to analyze the Ruby On Rails Svn Commit Log with the ActiveWarehouse framework. Of course this made me want to try to do the same with the new mx:OLAPCube and mx:OLAPDataGrid provided by Flex 3 as part of the DataVisualization components. Let me just say this…I am not done playing with either the Flex OLAPCube nor the Rails ActiveWarehouse framework as these are pretty complex beasts. Both of these frameworks are overlapping and complementary. There are overlapping as both can digest raw data and perform aggregation of that data. They are complementary in the sense that a server side warehouse needs a good visualization front-end. Maybe the OLAPCube and OLAPDataGrid can be this front-end. In my initial trials I haven’t come up with a compelling way to integrate both, but by using some simple SQL I could extract the data from the ActiveWarehouse and pass it to the OLAPCube.
Before going on you may want to read Anthony’s blog and check his presentation on Data Warehouses with ActiveWarehouse. I didn’t find much information on the Flex OLAPCube besides these: Feature_Introductions:_OLAPDataGrid on Adobe’s labs, Flex 3: Feature Introduction Video for OLAP Support, and these Flex examples.
So I create the following sample application. You can try it out here. Note it’s pretty slow, it takes up to a minute to aggregate 10000 values. The Flex team mentioned they didn’t optimize this component yet. I can confirm this. But I may also have messed something up as these are only my initial steps with that component. The application displays the Author dimension with the Author Name as rows and the Time dimension with the Year and Quarter as columns. The facts is the File Change count during that period. Flex calls the “facts” a measure.
Run the applicaiton
To extract the data from the ActiveWarehouse I created this SQL to join the facts table with all the dimensions table. I need to find out if the ActiveWarehouse doesn’t just return this data in xml format by using it’s build-in classes.
def report_as_xml
sql = <<-EOSQL
SELECT
date.calendar_year,
date.calendar_quarter,
date.calendar_month_name,
author.name,
file_revision_facts.file_changed AS `file_changed`
FROM
file_revision_facts
JOIN date_dimension as date
ON file_revision_facts.date_id = date.id
JOIN author_dimension as author
ON file_revision_facts.author_id = author.id
WHERE
date.calendar_year > '2005'
EOSQL
@@xml ||= ActiveRecord::Base.connection.select_all(sql).to_xml(:dasherize => false)
render :text => @@xml
endIn Flex the OLAPCube can be loaded with the XML
var data:ICollectionView = new ArrayCollection(result.records.record); // is Array
cube.dataProvider = data;
cube.addEventListener(CubeEvent.CUBE_COMPLETE, creationCompleteHandler);
cube.refresh(); Once the cube is loaded you can slice and dice it in many ways by using an OLAPQuery. I still need to figure out all the possibilities which are offered.
[Bindable]
private var cubeResult:IOLAPResult;
private function creationCompleteHandler(event:CubeEvent):void
{
//Cube was created, let's query it
var query:OLAPQuery = new OLAPQuery;
// TIME DIMENSION
var yearSet:IOLAPSet = new OLAPSet;
yearSet.addElements(cube.findDimension("Time").findAttribute("Year").members);
var quarterSet:IOLAPSet = new OLAPSet;
quarterSet.addElements(cube.findDimension("Time").findAttribute("Quarter").members);
//year-quarter
var newTimeSet:IOLAPSet = yearSet.crossJoin(quarterSet);
// AUTHOR DIMENSION
var authorSet:IOLAPSet = new OLAPSet;
authorSet.addElements(cube.findDimension("Author").findAttribute("Name").members);
// ROW/COLUMNS
var rowAxis:IOLAPQueryAxis = query.getAxis(OLAPQuery.ROW_AXIS);
rowAxis.addSet(authorSet.hierarchize(true));
var colAxis:IOLAPQueryAxis = query.getAxis(OLAPQuery.COLUMN_AXIS);
colAxis.addSet(newTimeSet.hierarchize(true));
// QUERY CUBE
var token:AsyncToken = cube.execute(query);
token.addResponder(new AsyncResponder(displayResult, olapFaultHandler));
}
private function displayResult(result:Object, token:Object=null):void
{
cubeResult = result as IOLAPResult;
}The cube result is the dataProvider of the Cube which in it’s simplests form can be defined as follows:
<mx:OLAPDataGrid id="olapGrid" dataProvider="{cubeResult}" />I’ve then added a change listener for the grid to create the dataProvider for the ColumnChart.
[Bindable]
private var chartData:Array;
private function gridSelectionChanged():void {
if (!(olapGrid.selectedItem is OLAPAxisPosition)) return;
var rowIndex:Number = olapGrid.selectedIndex;
var axis:IOLAPQueryAxis = cubeResult.query.getAxis(OLAPQuery.COLUMN_AXIS);
var columnLength:Number = cubeResult.getAxis(OLAPQuery.COLUMN_AXIS).positions.length;
var newChartData:Array = [];
for (var i:int=0;i<columnLength;i++) {
var tuple:OLAPTuple = axis.tuples[i];
var key:String = tuple.explicitMembers.toArray().join(",");
if (key.indexOf("(All)") > -1) continue; newChartData.push({key:key, value:cubeResult.getCell(rowIndex, i).value});
}
chartData = newChartData;
}<mx:ColumnChart id="chart" width="100%" height="30%" dataProvider="{chartData}">
<mx:series>
<mx:ColumnSeries yField="value" />
</mx:series>
<mx:horizontalAxis>
<mx:CategoryAxis categoryField="key" />
</mx:horizontalAxis>
</mx:ColumnChart>This are my first tribulations with both frameworks. Over the next few month I will have to dive more deeply into the possibilities which are offered. Thanks to both teams as this is pretty cool.
Enjoy! Daniel.
Hi, This is a very interesting use case indeed! Flexteam has been building working on a few samples that should make it much more easier for you to consume OLAP data in charts which we hope to post some time in the near future. We have also improved the query performance since the last beta and would like to run your sample to see the current performance. Could you log a bug here for us to track? http://bugs.adobe/flex/ (Flex Data visualization components project)
Cheers Ram
Hi Ram,
Shoot me an email to daniel[at]onrails.org I’ll send you the source code. I mostly use FlexBuilder so if you have a later beta version than 2, I can try that out. Now my question is…what bug do you want me to file? Missing facilities to bind grid to chart? I could add a couple of more issues, like selecting rows, columns, and cells. Selecting using a tuple or parital tuple..many more use cases. But the Cube is pretty cool…looking forward for the next version.
Ideally there would be an OLAPChart or OLAPChartDataProvider to feed the same data to the OLAPGrid and chart, but right now you need to do some marshaling.
Hi,
I’m trying to do the same as you did in this example. Having problems accessing to the aggregated data calculated by the olapdatagrid for drawing the chart with them. More than having problems is that I don’t know how to get them (I already have the olapdatagrid working).
Don’t know if you still have the code for it. If you do and you don’t mind to share it, I’d appreciate if you could send it to me.
Many thanks, Rafa
Hi,
I’m trying to do the same as you did in this example. Having problems accessing to the aggregated data calculated by the olapdatagrid for drawing the chart with them. More than having problems is that I don’t know how to get them (I already have the olapdatagrid working).
Don’t know if you still have the code for it. If you do and you don’t mind to share it, I’d appreciate if you could send it to me.
Many thanks, Rafa
Hey Rafa, I wouldn’t mind sharing the code but I checked this morning and didn’t find it. Send me your email just in case I find it again.