Créer un pool de connexion Olap avec Olap4J et DBCP

Olap4J est un projet qui a (entre autre) pour but d'exposer les connexions et les requètes Olap comme des requêtes JDBC. Cela présente beaucoup d'avantage comme par exemple le fait de se retrouver avec des api que l'on connait déjà bien ou de pouvoir utiliser les efforts déjà fait sur les pool de connexion comme C3P0 ou common DBCP.

J'ai rencontré quelques difficultés en utilisant DBCP 1.2.1, et vous devrez pour des raisons de "legacy" souvent travailler avec cette version de la librairie. A la suite je présente le code que l'on pourrait utiliser avec dbcp 1.4

	//inutile depuis le jdk 1.6 "jdbc:mondrian" provoque la registration du driver
	//si celui-ci est dans le classpath, c'est pareil pour jdbc:mysql
	//le driver mysql est automatiquement chargé depuis le classpath
	//donc a décommenter si on est en dessous du jdk 1.6
	//Class.forName("mondrian.olap4j.MondrianOlap4jDriver");
	//Class.forName("com.mysql.jdbc.Driver");
	//le pool de connexion, PoolableConnectionFactory sera en charge des entrées et
	//sortie des connexion dans le pool ces deux classes appartiennent à common-pool
	GenericObjectPool connectionPool = new GenericObjectPool(null);
	//la fabrique de connexion mondrian, cette classe sera passé à PoolableConnectionFactory
	//pour creer des connexion et les faire entrer dans le connectionPool
	ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
	"jdbc:mondrian:Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;"
	+ "Catalog=demo/FoodMart.xml;"
	+ "Role='California manager'", new Properties());
	PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
	connectionFactory, connectionPool, null, null, false, true);
	//je triche pour obtenir l'accès à la connection décorée (ie la olap connection)
	//DataSource dataSource = new PoolingDataSource(connectionPool);
	PoolingDataSource dataSource = new PoolingDataSource(connectionPool);
	//si je ne fais pas cela connection.getInnermostDelegate(); renvoie null
	//attention cette méthode n'est pas accessible sur l'interface DataSource
	dataSource.setAccessToUnderlyingConnectionAllowed(true);
	Connection conn = dataSource.getConnection();
	//ce down cast est necessaire pour obtenir le proxy de base avec #getInnermostDelegate()
	DelegatingConnection connection = (DelegatingConnection) conn;
	//j'aurai ontenu org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to org.olap4j.OlapWrapper
	//avec OlapWrapper wrapper = (OlapWrapper) conn;
	OlapWrapper wrapper = (OlapWrapper) connection.getInnermostDelegate();
	OlapConnection olapConnection = wrapper.unwrap(OlapConnection.class);
	OlapStatement statement = olapConnection.createStatement();
	CellSet cellSet = statement
	.executeOlapQuery("SELECT {[Measures].[Unit Sales]} ON COLUMNS,\n"
	+ " {[Product].Members} ON ROWS\n" + "FROM [Sales]");
	System.out.println(cellSet);

view raw OlapConnexionPool-DBCP1.2.1.java hosted with ❤ by GitHub

	GenericObjectPool connectionPool = new GenericObjectPool(null);
	ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
	"jdbc:mondrian:Jdbc=jdbc:mysql://localhost/foodmart;JdbcUser=foodmart;JdbcPassword=foodmart;"
	+ "Catalog=demo/FoodMart.xml;"
	+ "Role='California manager'", new Properties());
	PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
	connectionFactory, connectionPool, null, null, false, true);
	DataSource dataSource = new PoolingDataSource(connectionPool);
	Connection connection = dataSource.getConnection();
	//OlapWrapper wrapper = (OlapWrapper) connection;
	OlapConnection olapConnection = connection.unwrap(OlapConnection.class);
	OlapStatement statement = olapConnection.createStatement();
	CellSet cellSet = statement
	.executeOlapQuery("SELECT {[Measures].[Unit Sales]} ON COLUMNS,\n"
	+ " {[Product].Members} ON ROWS\n" + "FROM [Sales]");
	System.out.println(cellSet);

view raw OlapConnexionPool-DBCP1.4.java hosted with ❤ by GitHub

Getting the number of weeks in a year

	function nbWeekInAYear ($year) {
	$nbWeek = date('W',mktime(0, 0, 0, 12, 31, $year));
	if ($nbWeek==1) {
	$nbWeek = 52;
	}
	return $nbWeek;
	}
	echo nbWeekInAYear(2009)."\n"; //53
	echo nbWeekInAYear(2010)."\n"; //52
	echo nbWeekInAYear(2011)."\n"; //52
	echo nbWeekInAYear(2012)."\n"; //52
	echo nbWeekInAYear(2013)."\n"; //52
	echo nbWeekInAYear(2014)."\n"; //52
	echo nbWeekInAYear(2015)."\n"; //53
	echo nbWeekInAYear(2016)."\n"; //52

view raw numberOfWeekInAYear.php hosted with ❤ by GitHub

Why using memcached instead of a local cache solution

I decide to have a look to memcached and see what  it is all about. I found this small self-explenatory story in their wiki. I finally understood that memcached let you use the ram of other servers in your network. Actually you query this distributed cache through TCP-IP

Memcached has an algorithm that choose the server based on the key input.

But it supposes you buy new servers, connect them to your network. And if you need to increase your cache to, let's say 1 Gb, you need to buy a new server ! This caching solution look expensive to me and do not compete with a local cache. It is true that accessing data in the RAM is much faster than accessing data on a local storage, but there is the overhead of the TCP connections.. ...

On his blog  Artur Ejmond compares APC (Which is a local cache solution) and memcached. The result is clear APC is much faster and much cheaper.

Then why using memcached instead of a local cache solution ?

I can see two reasons  :

1. You have more than one client for the cache and you want all of them to share the same cache
2. Theres's a lot of write on the cache and your disk risk to bottleneck

I know that many major sites like wikipedia or twitter use memcached, but I would be happy to hear why you choose memcached over a local cache solution.