0

I'm using olap4j 1.2.0 to connect to Microsoft Analysis Services.

My code is

Class.forName("org.olap4j.driver.xmla.XmlaOlap4jDriver");

OlapConnection olapConnection = DriverManager.getConnection(
"jdbc:xmla:Server=http://myserver:1234/OLAP/msmdpump.dll;Catalog=mycatalog")
.unwrap(OlapConnection.class);

CellSet cellSet = olapConnection.createStatement().executeOlapQuery("<MDX query>");

So, when I use Excel, query takes about a second. I take MDX query string from Excel (using macros) and put it into "MDX query" in my code. And execution takes about 145 seconds (depending on the date filter and the size of obtained data).

As I suppose, Excel uses a cache of MSAS, but olap4j doesn't. I know, that olap4j uses its own cache - when I execute 2 identical olap queries, the second query executes very fast (~300 ms).

The question is "is there any way to tell olap4j to use MSAS cache?"

I guess that Excel uses OLEDB to connect to MSAS and the cache policy is specified in connection string (or provided by OLEDB driver by default, I don't know), but I didn't find any similar parameter in XmlaOlap4jDriver.

Sorry for my not very good English and thanks in advance.

fightlight
  • 946
  • 1
  • 11
  • 12
  • My educated guess is that Excel is caching the query, not even sending the request to the server, to the extend of my knowledge MSAS doesn't cache a full query (other vendors do ;-) ). – ic3 Jun 28 '16 at 08:53
  • ic3, thanks for the fast response :) This is interesting info. Then I don't understand why Excel works so fast and olap4j - so slow (my only explanation of this is above). And when Excel executes the FIRST query, it executes as fast as the next one. – fightlight Jun 28 '16 at 10:08
  • MSAS has some internal cache, but it should work with any provider. Are you sure it's the same request ? (you'll have to trace MDX requests in MSAS) – ic3 Jun 28 '16 at 13:23
  • I'm using Excel macro by Marco Russo for obtaining a MDX query http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx It's a good idea to trace MDX requests in MSAS, but I don't have appropriate access to the MSAS server. I'll try to reach an agreement with our admins and write back later. – fightlight Jun 28 '16 at 16:05
  • I've analyzed the trace-file and have found a lot of 'Discover' requests. The query itself is executing at the beginning and executes very fast. So my initial assumption was wrong. olap4j 1.3.0 acts the same way. So the first query will be always slow and there is no other solution, I guess. Thank you for your help! – fightlight Jun 29 '16 at 09:48

0 Answers0