How can I update view if i change db?-answered

[shawnhy]

Hello,

I am a newbie in jaspersoft. I change a value of demodb (foodmart)in mysql, but the amalysis view remains the same. Is this normal? How should I do to make it up-to-date? Thank you!

 

Post edited by: shawnhy, at: 2007/08/10 03:31

Post edited by: shawnhy, at: 2007/08/13 05:56

[csbac]

Hi!

- restart tomcat/jboss

- wait for some time (I'm not sure how long, maybe 30 mins?), until the JPivot view is closed.

Unfortunately, calling "flush olap cache" via the menu does *not* do it, at least not in my case ...

You can see the data has been reloaded when the changes you made to the OLAP display view (drilling, slicing) are lost ...

 

Maybe one of the JS-people can help us there, why the flush olap cache does not do anything noticeable ...

 

Hope this helps,

Sebastian

[shawnhy]

Thanks Sebastian,

 

It seems that what we see via "report analysis" is just a snapshot, whcih has been seperated from real database(I am not sure about this statement)

 

I wait for a night long but nothing happens. There sould be a trigger can manually update it. Can somebody kindly help, please?

 

Sahwn

Post edited by: shawnhy, at: 2007/08/10 03:38

[zzz1]

Hi!

 

I found 2 decisions:

- restart tomcat server

- call "flush olap cache" then "log out" and login again.

[shawnhy]

Thanks a lot. I try and success.

 

Is this a kind of bug? I also get another question. Now my analysis view load lastest data every time I open it. This might be no good if the database is super large. Can I set up a schedule for it? Thanks.

 

BTW, the option "Choose Aggregate Table By Volume" and "Enable Aggregate Tables" seem involved.

[csbac]

Hi!

The OLAP cache that is involved here is, afaik, in the memory of the Mondrian or JPivot component that calculates the cube from the sql data.

Thus, it is

- dropped after a timeout

- dropped, of course, when tomcat/jboss is restarted

- should theoretically be flushable by some command, though the flush olap cache does not do it

- has nothing to do with the source database

 

If you need optimisation, you either need to use a database system that supports materialised views (oracle, db2 or such), or create tables yourself where you regularly aggregate the data ...

Of course, this only helps if the mondrian cube is defined on joining several tables ... if the fact table itself is LARGE, I'm not sure what can be done .. check the OLAP literature.

 

Yours,

Sebastian

[zzz1]

If I understand right, JS based on Mondrian.

In Mondrian FAQ http://mondrian.pentaho.org/documentation/faq.php I found:

14.1 When I change the data in the RDBMS, the result doesn't change even if i refresh the browser. Why is this?

 

Mondrian uses a cache to improve performance. The first time you run a query, Mondrian will execute various SQL statements to load the data (you can see these statements by turning on tracing). The next time, it will use the information in the cache.

 

Cache control is primitive right now. If the data in the RDBMS is modified, Mondrian has no way to know, and does not refresh its cache. If you are using the JPivot web ui and refresh the browser, that will simply regenerate the web page, not flush the cache. The only way to refresh the cache is to call the following piece of code, which flushes the entire contents:

 

mondrian.rolap.CachePool.instance().flush();

[sbirney]

This post got me wondering if there was a bug, so I tried some experiments with it.

 

The last post is correct, that the mondrian cache is flushed with the following line of code, which is what the "Flush OLAP Cache" button does:

 

mondrian.rolap.CachePool.instance().flush();

 

However, JPivot keeps its own (smaller) session based cache, so logging out after flushing the cache is necessary to clear your session if you have already loaded a particular analysis view in the current http session.

 

And, as alluded to in another post, if aggregate tables are used, they might hide changes to base data. For example, I changed sales_fact_1997 in foodmart by multiplying unit_sales by 2, then I flushed the cache and logged out, but did not see the changes until I went back and disabled aggregate tables (and then flushing the cache and logging out again). If you are using aggregate tables, the proper thing to do would be to rebuild or update those when the underlying data has been updated.

 

HTH,

Sam