PDA

View Full Version : MDX and Caching Strategy



chang33
09-30-2003, 12:45 AM
Hi,
It's me again.
I have two questions here.
1)
My database is Oracle.
I want to write a MDX with a datetime condition.
This is the sql statement.
select count(*) from CALLLOG
where TIMESTAMP > to_date('2003/09/01','yyyy/mm/dd') and
TIMESTAMP < to_date('2003/09/30','yyyy/mm/dd')
What does the MDX look like?
2)
I write a web page by using JPivot and Mondrian.
The data in web don't refresh when I modify the data in database.
What's the caching strategy of Mondrian?
Thanks.

sgwood
09-30-2003, 05:01 AM
In answer to your Question 1, check out
https://sourceforge.net/forum/message.php?msg_id=2207472.
What you need is a time dimension in the Mondrian metadata for your database.
Given the database view and Mondrian schema I had in the above post, the initial MDX query looks like:
lect {[Measures].[Actual], [Measures].[Estimated]} on columns,
{([Time].[All Periods],[Procedure Type].[All Types],[Value Type].[Costs])} on rows
from CostsByProcedure
I am not familiar with the Mondrian caching strategy.

chang33
09-30-2003, 04:25 PM
Hi,
I read the article for question 1 and I felt that was a great idea.
I'll use the idea somewhere.
Actually, what I meant in question 1 was how to put data with
a specific condition such as datetime into cube, not the whole data in the table.
I don't want to drill the datetime with the whole data.
Becasue there are million rows in the table, if I put the whole data into cube,
the loading process would take a lot of time.
According to the question 2, I want to know when the data in cube would be updated,
or is there any parameter I can configure?
Now, when I insert or update the table, the measures in pivot table wouldn't be updated
to the lastest value. It is a series problem to my system.
What should I do to solove the problem?
Thanks.

sgwood
10-01-2003, 01:15 AM
Q1:
Now I understand your question.
With the time dimension I gave before, you can do things like:
[Time].[2003].[Q3].[September]
Q2:
JPivot has a class, com.tonbeller.jpivot.olap.model.CachingOlapModel, that caches the Mondrian result. You can see it working in the server log. It is an EventListener. If you send modelChanged or structureChanged to an instance of it, it drops the result from its cache, so the next time you access it, it will hit the DB again.

avix
10-01-2003, 07:27 AM
JPivots CachingOlapModel is invalidated whenever the user navigates (i.e. when the query changes). Its mostly there to avoid Mondrian access when the user, for example, fills in a form and table/chart are visible and display the same data everytime the user submits the form.
Mondrian caches data too. To clear the cache,
use mondrian.rolap.CachePool.instance().flush().
Mondrian not only caches data, but also caches the Schema (e.g. FoodMart.xml). Its cleared by RolapSchema.flushSchema(), but thats a little difficult to get the parameters right.
Andreas

chang33
10-02-2003, 09:20 PM
I use mondrian and JPivot and write a simple web.
When tomcat server starts, the data in the web are up to date and are "all correct".
When I modify the data in Oracle database, the data in the web will be updated a moment later.
I don't know how long. I checked the mondrian.rolap.CachePool, but I couldn't figure it out.
Anyway, it will be updated.
After I check the new updated data, I find a problem. The numbers are "not correct".
Look at this.
All Time
----------------
2003
-----------------------------
Sales Count Oct | Sept
770 770 206 570
Now I only have two months data.
As you can see, 770 <> 206 + 570 .
After I restart tomcat, the data are all correct. 776 = 206 + 570.
What's the problem?
Thanks.

avix
10-02-2003, 10:07 PM
Did you call mondrian.rolap.CachePool.flush() after adding data? I would be interested if that solves your problem.
Andreas

chang33
10-03-2003, 12:57 AM
The problem is I don't know when the data will be added.
So I can't call mondrian.rolap.CachePool.flush() after data are added.
Do you have any idea that the results in the pivot table don't sum equal.
For example, 770 <> 206 + 570 (206 and 570 are up to date, but 770 is not).
Jimmy

avix
10-03-2003, 02:38 AM
Not sure, possibly the result contains both, data from Mondrian cache (e.g. 770) which have been computed before the DB update, and data that have been computed after DB update (e.g. 206, 570).

chang33
10-06-2003, 12:21 AM
I add a button in the pivot table page.
When user feel the data are not correct, they click the button,
and then I call mondrian.rolap.CachePool.flush().
It solves my problem.
Thanks.

venkat
07-22-2008, 12:46 AM
I add a button in the pivot table page.
When user feel the data are not correct, they click the button,
and then I call mondrian.rolap.CachePool.flush().
It solves my problem.
Thanks.
Hi All,
i am facing this problem right now.How to add that button in Jpivot table?
And how to call mondrian.rolap.CachePool.flush().this line?
Give me steps..

if someone can help me...
Thanks in Advance.

Regrads,
Venkat

gustavorg
11-21-2008, 03:48 PM
If you're going to use Mondrian for a while you definitely will discover sooner or later that when you make a change in your cube (manualy or using workbench) this change is not displayed in your jpivot
query. After use the infamous IE->Tools->Internet Options->Delete files, and see how it doesnt works, you will discover that Mondria has a cache, and now your problem is how to turn it off, or, at least, how to clear it.
The google's search results states clearly that there is a way to do it, seems there is something called Mondrian cache control, there is something called mondrian.rolap.RolapSchema.clearCache(), there are segments, et cetera, et cetera. Finally (or if you're unlucky at the beggining) you will land in this post and now you are facing a new problem: how to call mondrian.rolap.CachePool.flush(), and the more cryptic problem, How to add that button in Jpivot table (thank chang33!, now we have two problems.).

Well, the problem is that if you're competent enough to try the execution of mondrian.rolap.RolapSchema.clearCache(), your friendly tomcat web app will say "nah, I dont think so". Of course, the faq of the mondrian sources say clearly that this function is deprecated and you must execute this instead:

Connection connection;
CacheControl cacheControl = connection.getCacheControl(null);
cacheControl.flushSchemaCache();

But, there is a new problem. connection needs some value to be able to call the connection.getCacheControl(null);

To make short the tale (and after try every combination of connections strings) this was my first possible winner attemp to initialize the connection variable:

Connection connection = DriverManager.getConnection("Provider=mondrian;Jdbc=jdbc:mysql://localhost/mydb?user=myuser&password=mypassword;Catalog=/WEB-INF/queries/MySchema.xml;jdbcDrivers=com.mysql.jdbc.Driver", null);

Unfortunately, this generate a lovely exception. Finally, and after inspect the mondrians sources carefully, I could find how to declarate the full path of my
xml file (I was suspecting that the saboteur here was my scheme xml file). So here you are my friends the solution to how clear the mondrian cache:

Connection connection = DriverManager.getConnection("Provider=mondrian;Jdbc=jdbc:mysql://localhost/mydb?user=myuser&password=mypassword;Catalog=file:/c:/apache-tomcat-6.0.18/webapps/mondrian/WEB-INF/queries/MySchema.xml;jdbcDrivers=com.mysql.jdbc.Driver", null);
CacheControl cacheControl = connection.getCacheControl(null);
cacheControl.flushSchemaCache();

In this solution my tomcat is installed at c:/apache-tomcat-6.0.18

I think is not needed to create a button to this so I create a jsp page (ClearCache.jsp) and put this code in it. Then just browse the page whenever you want to clear the cache. Dont forget to import the required libraries using:

<%@ page language="java"
import="mondrian.olap.*"
%>

agnas
http://www.micronosis.com



Hi All,
i am facing this problem right now.How to add that button in Jpivot table?
And how to call mondrian.rolap.CachePool.flush().this line?
Give me steps..

if someone can help me...
Thanks in Advance.

Regrads,
Venkat

Bazinou
09-08-2009, 09:07 AM
Hi

Thanks a lot for this post gustavorg !!
I was searching and searching for how clear schema cache ... :eek: