Wednesday, March 12, 2008

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>

4 comments:

Madhav said...

Hi Sreenivas,

Couple of questions on OLAPCube structure:

As per the documentation of OLAPDimension at http://livedocs.adobe.com/flex/3/langref/mx/olap/OLAPDimension.html

..it looks like we can't define a nested dimension, i.e. a snowflake design of star schema can't be modeled. So, if I want to consume data from some OLAP server (and hence its own native cube definition which would allow me to define snowflake schema), then can I directly set the OLAPDataGrid.dataProvider to some returned value instead of first setting the result set to OLAPCube.dataProvider? I mean, can I opt not to use OLAPCube while I'm using OLAPDataGrid?

Thanks,
Madhav

Sreenivas said...

OLAPDataGrid cosumes IOLAPResult and other interfaces it doesn't care about the actual OLAPCube. But you may have to implement some interfaces to feed the data properly into ODG. You can refer to how to feed XMLA result here

http://nisheet.wordpress.com/

Cycling said...

Has anyone got this to actually work? The two concrete examples I've so far found cause the same error in OLAPMember.as (line 116) - here and here.
The fact that the second link is an Adobe flex bug report suggests this is not working. period.

Sreenivas said...

Do you mean to say the code given in the post doesn't work?

It has worked for me. I don't know whether it has worked for anyone else or not because people for whom it has worked don't leave a comment to say that it has :)

If someone has tried OLAP Cube AS creation without understanding the requirements of-course it won't work ! (the sample given in the bug report is trying to add a "Measures" dimension which is actally a in-built dimension and the step is not required. Trying to mimic what MXML syntax shows is not always guaranteed to work because the MXMLC compiler does lot of things under the hood which one needs to understand before attempting it)