Oracle OLAP

Oracle's Java SDK for OLAP technology has a lot of room for improvement. I came to this conclusion after completing reporting project requring I decided to design a framework utilizing OLAP beans and query techniques making an easier and simplier Java API using Java generics. I have used Oracle's development example that uses the channel, product, time and customer dimensions. Here is an example of utilizing this API at a high level. I think a simple interface like the one below to the OLAP structure is much easier to understand and utilize.

OlapCriteria<String> oc = new OracleOlapCriteria("SALES");
oc.addCriteria("CHANNEL", new String[] { "PRIMARY::TOTAL_CHANNEL::1", "PRIMARY::CHANNEL::2"});
oc.addCriteria("CUSTOMER", new String[] { "SHIPMENTS::TOTAL_CUSTOMER::1", "SHIPMENTS::REGION::8"});
oc.addCriteria("TIME", new String[] { "CALENDAR_YEAR::QUARTER::10", "CALENDAR_YEAR::MONTH::34", "CALENDAR_YEAR::MONTH::35"});
oc.addCriteria("PRODUCT", new String[] { product });
OlapBean<String,Double> ob = (OlapBean<String,Double>)os.query(oc);

Now as you can see, this creates a OlapBean object. This bean structure is essentially the same structure as the multidimensional cube used in the OLAP data tables. It is accessed by a multidimensional key. If the cube has 4 dimensions, then by plugging in a 4 dimensioned key into the bean, the value at that location within the cube is given. I used Apache Velocity to traverse and display the bean as shown below.

<!-- Dimensions
  0 - channel
  1 - cust
  2 - time
  3 - product
#foreach($chan in $olap.getDim(0))
 chan: $chan.desc
 <table border="1">
    #foreach ($time in $olap.getDim(2))
     <td>time: $time.desc </td>
   #foreach ($cust in $olap.getDim(1))
     <td>cust: $cust.desc </td>
     #foreach ($time in $olap.getDim(2))
       #foreach ($prod in $olap.getDim(3))
       #set($key1 = [$chan.name, $cust.name, $time.name, $prod.name])

Notice how I created the key as an array of dimensional values. I then just simply called the getData function to access the value.

the OlapBean class stores data as a simple has. As you can see below the OlapBean uses a hash structure to store the data. A simple key-value pair stores the Multi-Dimensional data. The complexity comes with the key as shown later.

public abstract class OlapBean<K,T> {
	public OlapBean() {
		olapData = new HashMap<MultiDimensionalKey<K>, T>();
		dimensionData = new HashMap<MdmObject, List<OlapTableObject>>(); 
	protected Map<MultiDimensionalKey<K>, T> olapData;
	protected List<MdmObject> dimension;
	protected Map<MdmObject, List<OlapTableObject>> dimensionData;
	public T getData(ArrayList<K> axis) {
		MultiDimensionalKey<K> key = new MultiDimensionalKey<K>(axis);	
		if (olapData.get(key) == null)
			return null;
		return olapData.get(key);
	public void add(MultiDimensionalKey<K> k, T value) {
		olapData.put(k, value);
	public List<OlapTableObject> getDim(Integer i) {
		if (i >= dimension.size() || dimensionData.get(dimension.get(i)) == null)
			return new ArrayList<OlapTableObject>();
		return dimensionData.get(dimension.get(i));
	public void setDimensionMetadata(List<MdmObject> dim,  List<List<OlapTableObject> > data) {
		this.dimension = dim;
		for (int i = 0; i < dimension.size(); i++)
			dimensionData.put(dimension.get(i), data.get(i));

The idea here is that the data is being stored by a multidimensional key. The data is accessed by a combination of dimensions to retrieve a specific value. Look at the implementation of the MultiDimensionalKey below. This implementation is very generic and can even apply to other requirements outside of the OLAP scope.

public class MultiDimensionalKey<T> implements Comparator<MultiDimensionalKey<T>>, Comparable<MultiDimensionalKey<T> > {
	private List<T> keys = null;
	public MultiDimensionalKey(List<T> keys) {
		this.keys = keys;
	public boolean equals(Object obj) {
		MultiDimensionalKey<T> ol = (MultiDimensionalKey<T>)obj;
		if (keys.size() != ol.keys.size())
			return false;
		for (int i = 0; i < keys.size(); i++) 
			if (!keys.get(i).equals(ol.keys.get(i)))
				return false;
		return true;
	public int compare(MultiDimensionalKey<T> o1,MultiDimensionalKey<T> o2) {
		MultiDimensionalKey<T> key1 = (MultiDimensionalKey<T>)o1;
		MultiDimensionalKey<T> key2 = (MultiDimensionalKey<T>)o2;
		if (key1.keys.size() != key2.keys.size())
			if (key1.keys.size() < key2.keys.size())
				return -1;
				return 1;
		for (int i = 0; i < key1.keys.size(); i++)
			if (key1.keys.get(i).equals(key2.keys.get(i)))
				return 0;
		return -1;
	public int compareTo(MultiDimensionalKey<T> k) {
		for (int i = 0; i < keys.size(); i++) 
			if (!keys.get(i).equals(k.keys.get(i)))
				return -1;
		return 0;
    public int hashCode() {
    	String s = new String();
    	for (int i = 0; i < keys.size(); i++) 
    		s+= keys.get(i);
    	return s.hashCode();

The meat of the OLAP logic resides on the Oracle OlapService class. Two functions in particular do most of the work. (query and createBean methods)

	public OlapBean<String, ?> query(OlapCriteria<String> oc) {
		MdmMeasure m = cxt.getMdmMeasureByName(oc.measures.get(0));
		//Source sm = m.getSource();
		List<MdmDimension> dl = m.getDimensions();
		List<String> fullOrder = new ArrayList<String>();
		for  (String o : oc.getOrder()) {
		for (MdmDimension d : dl) {
			if (!fullOrder.contains(d.getName()))
		List<MdmObject> dimension = new ArrayList<MdmObject>();
		List<Source> dimDataSourceList = new ArrayList<Source>();
		List<Source> ls = new ArrayList<Source>();
 		for (String name : fullOrder) {
			MdmPrimaryDimension mdmPd = cxt.getMdmPrimaryDimensionByName(name);
			MdmHierarchy mdmH =  mdmPd.getDefaultHierarchy();
			//MdmLevelHierarchy mdmLh = (MdmLevelHierarchy) mdmPd.getDefaultHierarchy();
			Source shortValueDesc = mdmPd.getShortValueDescriptionAttribute().getSource();
			//Source valueDesc = mdmPd.getValueDescriptionAttribute().getSource();
			List<String> selectCriteria = oc.getCriteria().get(name);
			Source s = null;
			if (selectCriteria != null && selectCriteria.size() > 0) {
				String[] stringCriteria = (String[])selectCriteria.toArray(new String[selectCriteria.size()]);
				s = ((StringSource)mdmH.getSource()).selectValues( stringCriteria );
				s = mdmPd.getSource();
 		Source ms = m.getSource();
 		for (Source s : ls) {
 			ms = ms.join(s);
 		return createBean(ms, dimension, getDimensionDataElements(dimDataSourceList));
	public OlapBean<String,?> createBean(Source s, List<MdmObject> dimension, List<List<OlapTableObject> > dimensionData) {
		CursorManagerSpecification cursorMngrSpec = cxt.getDataProvider().createCursorManagerSpecification(s);
		SpecifiedCursorManager cursorMngr = cxt.getDataProvider().createCursorManager(cursorMngrSpec);
		CompoundCursor unitsForSelCursor = (CompoundCursor)cursorMngr.createCursor();
	    ValueCursor specifiedUnitsVals = unitsForSelCursor.getValueCursor();
	    List<ValueCursor> outputs = unitsForSelCursor.getOutputs();
	    int size = outputs.size();
	    OlapBean<String,Object> bean = new OracleOlapBean<Object>();
	    do {
	        List<String> keys = new ArrayList<String>();
	        for (int j = size-1; j >= 0; j--) 
	        	keys.add( outputs.get(j).getCurrentString() );
	        MultiDimensionalKey<String> key = new MultiDimensionalKey<String>(keys);
	        bean.add(key, specifiedUnitsVals.getCurrentValue());
	    } while(unitsForSelCursor.next());
	    bean.setDimensionMetadata(dimension, dimensionData);
	    return bean;

Once up and running you should get the screen to look like the following.
OLAP Image

You can interactively allow for a user to switch between product type.
OLAP Image

I decided to re-implement the ContextExample Class while utilize Oracle's "Example" class functionality. The Class is from Oracle's java examples and I decided to utilize the oracle.olapi.examples package as much as possible.

You can access the code via CVS here

This project is set up for eclipse so if you download this project, you will need only need to modify the jdbc.properties file to direct to your oracle olap enabled database with the demo data installed.

This OLAP API layer was created to simplify OLAP calls for the developer. It is primarily designed to overlay Oracles API as a layer between the developer and Oracle's implementation. This is not meant to be a JSR-069 implementation or any other standard implementation. The purpose was to quickly implement project requirements. As far as I understand, JSR-069 is an abandoned implementation and Olap4j is not compatible with Oracle.

1 comment

Oliver Wahlen, Wed, 06/09/2010 - 03:21

Hello David,
your code is very very helpful.
Thank you a lot!

Note that I am using it with Oracle 11 and the grails framework.
Grails is a web framework quite similar to rails.
It is based on Spring, Hibernate and Sitemesh.

I will have to adapt your service and controller a bit.
It would be great if you could add some more documentation (for javadoc).

king regards,

Post new comment

Subscribe to Syndicate