Performance issue in analysis view

[marasheed]

 

 

I'm attaching mondrian schema in use by me. (northstar.xml)

 

I'm executing an analysis view using MDX query

 

select 

{([Measures].AllMembers)} ON COLUMNS,

Hierarchize((([Product].[All Products].Children * [Time].[time].Members ))) ON rows 

FROM Sales

 

(generated sql queries in attached file mdxgeneratedqueries.txt)

 

 

I'm attaching the schema of database. (northstarbi.sql)

 

In database :

 

agg_lc_001_salesfact has 366326 records

salesfact has 1274937 records

location has 21877 records

member has 6287 records

product has 9019 records

TIME has 539402 records

checksfact has 1274937 records

 

Following is the configuration of machine where jasper server and mysql server are running.

AMD Phenom IIX3700e Processor 2.40 GHz

RAM 12.0 GB

Windows 7 Ultimate 64 bit

 

 

In catalina.bat I have set following parameters.

 

SET JAVA_OPTS=%JAVA_OPTS% -Xms512m -Xmx8192m -XX:MaxPermSize=800m -Djava.awt.headless=false -Dfile.encoding=UTF8 -Duser.dir="%CATALINA_HOME%" -Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false -Djava.security.auth.login.config="%CATALINA_HOME%/conf/jaas.config" -Dorg.apache.catalina.loader.WebappClassLoader.ENABLE_CLEAR_REFERENCES=false -server -XX:NewRatio=1 -XX:SurvivorRatio=20

 

 

When executed the cpu and memory usage by tomcat java process increased.

It take around 3 minutes to render the output on browser. Also browser cpu usage increased (33%) and later become normal.

If I execute these queries directly over database they are quick in generating output. 

I urgently need your help to render analysis view in 15-20 secs.

 

Attached is tomcat output (tomcatOuput.txt)

 

Another problem in drill down but in drill down query already taking a lot of time and I'm optimizing it.

 

Looking for your help.