Mondrian OLAP connection management

Question

What is the recommended pattern for managing Mondrian database connections?

I am using Mondrian as a library in a Scala/Play Framework web application. For example:

var connection
try {
  connection = DriverManager.getConnection(connection_string).unwrap(classOf[OlapConnection])
  val result = connection.createStatement.executeOlapQuery(mdx)
  // ... use the result ...
} finally {
  if (connection) connection.close
}

Is calling close in a finally block the right approach?

How do I configure a connection pool?

What is the recommended approach for terminating a long running query? Is it possible to monitor the progress of query?

Answer 1

Calling close() in a finally block makes sure that the connection gets really closed, so it's the right thing to do for any resource.

I would write it like

val connection = DriverManager.getConnection(connection_string).unwrap(classOf[OlapConnection])
try {
    [...]
} finally {
    connection.close
}

to get rid of the var. But this is still "imperative style", so I would use

def withResource[T <: { def close() }, R](resource: T)(code: (T) => R): R = {
  try {
    code(resource)
  } finally {
    import scala.language.reflectiveCalls
    resource.close()
  }
}

together with

withResource(DriverManager.getConnection(...)) {
  conn =>
    [...]
}

to get rid of the try/catch which clutters up your code. What is more, you cannot forget to close. This is applicable to any class which offers a close() method. If you put the withResource() method in a trait, you can mix it in your classes.

As for the connection pool, there is another thread here.

As for long running OLAP queries ... they are supposed not to run very long. Experience with Essbase or Palo show that these queries are next to "real time". If you drill down the only problem might be the large amount of data to be transferred to the client. While you read the result, you could use the incoming data as a means to implement a progress display. OLAP databases are incredibly fast. Anyway you could put the query in a background thread, so that the code is non-blocking, but there should be no need to do this with OLAP. Just pipe the data into your frontend as fast as possible, that's the way the clients (Excel plugins) work.