Mondrian 3.14.0 服务配置说明（限功能使用验证）

Mondrian3.14.0 服务配置

准备

 #1 下载mondrian 3.14 war包

该压缩包含mondrian 3.14及tomcat8.5.9，详细见如下分享链接：

链接：https://pan.baidu.com/s/1KQopFKvYB1fzSDtAOfLKmg

提取码：9dtp

#2 初始化foodmart数据库

#1 下载mondrian

SourceForge.net里下载mondrian-3.3.0.14703

#2 执行数据库初始化，这里

java -cp D:\FoodMart\mondrian.jar;D:\FoodMart\log4j-1.2.8.jar;D:\FoodMart\commons-logging-1.0.4.jar;D:\FoodMart\eigenbase-xom.jar;D:\FoodMart\eigenbase-resgen.jar;D:\FoodMart\eigenbase-properties.jar;D:\FoodMart\mysql-connector-java-5.1.20-bin.jar;D:\FoodMart\olap4j.jar;D:\FoodMart\mysql-connector-java-5.1.19.jar mondrian.test.loader.MondrianFoodMartLoader -verbose -tables -data -indexes -jdbcDrivers="com.mysql.jdbc.Driver" -inputFile=D:\FoodMart\FoodMartCreateData.sql -outputJdbcURL="jdbc:mysql://localhost:3309/foodmart?user=root&password=root1234

# 详细见

Mondrian mysql版数据初始化https://blog.csdn.net/shenliang1985/article/details/122980468?spm=1001.2014.3001.5501

配置mondrian

#1 解压到指定的目录

# 当前是ubuntu环境，

/root/software/apache-tomcat-8.5.9/

# 2 赋予可执行权限

catalina.sh、shutdown.sh、startup.sh 赋予可执行权限

如：

chmod +x catalina.sh

#3 修改jsp里的jdbc链接信息

将apache-tomcat-8.5.9/webapps/jpivot/WEB-INF/queries下的arrows.jsp和colors.jsp里的jdbc信息修改为mysql的。详细见下：

<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost:3306/foodmart" jdbcUser="root" jdbcPassword="root1234" catalogUri="/WEB-INF/queries/FoodMart.xml" connectionPooling="false">

#4 回到Tomcat的bin目录下启动Tomcat

/root/software/apache-tomcat-8.5.9/bin

root@slave2:~/software/apache-tomcat-8.5.9/bin# ./startup.sh

验证

#1 输入访问地址

这里为localhost:8080/jpivot/

#2点击Colors in Cells

#3 选择指标和维度对应的值后点击确定 

 

#4 点击+号下钻取更多数据。

#5 SQL验证

-- 取得产品名称为'Good Imported Beer','Good Light Beer'的成本、销售额及盈利额。
SELECT B.product_name
,round(SUM(store_cost),2) store_cost
,round(sum(store_sales),2) store_sales
,round(sum(store_sales)-SUM(store_cost),2) profit
-- FROM sales_fact_1997 A
FROM agg_c_special_sales_fact_1997 A -- 从已经预加工的聚合表里关联
JOIN product B
ON A.product_id = B.product_id
	AND B.product_name IN ('Good Imported Beer','Good Light Beer')
JOIN customer C
ON A.customer_id = C.customer_id
AND C.gender ='F'
GROUP BY B.product_name

 

问题集锦

1 tag标签配置异常

核心错误信息：Unterminated <jp:mondrianQuery tag

问题分析：该问题与jsp的注释有关，xml里的注释是<!-- -->而jsp里需要写错<%-- --%>

2 无法连接X11窗口

核心错误信息：java.awt.AWTError: Can't connect to X11 window server using 'localhost:11.0' as the value of the DISPLAY variable.

解决方法：

在 tomcat bin 内的 startup.sh，第一行添加：

export CATALINA_OPTS="-Djava.awt.headless=true"

3 连接Mysql 5.7.36时出现ssh方面的异常

注：当前用的mysql驱动是mysql-connector-java-5.1.47.jar

org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL dialect
org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: org.apache.jasper.JasperException: javax.servlet.ServletException: javax.servlet.jsp.JspException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL dialect
	at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:565)
	at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466)
	at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
	at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)




[JPivot] 08 Dec 2022 13:31:05,811 ERROR [Session 29CA622CA591F60868210378A8A62B43] com.tonbeller.wcf.controller.RequestFilter#doFilter: Error handling request
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 290 milliseconds ago.  The last packet sent successfully to the server was 285 milliseconds ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)

 分析tomcat日志后发现如下关键信息:

Caused by: javax.net.ssl.SSLHandshakeException: No appropriate protocol (protocol is disabled or cipher suites are inappropriate)
    at sun.security.ssl.HandshakeContext.<init>(HandshakeContext.java:171)
    at sun.security.ssl.ClientHandshakeContext.<init>(ClientHandshakeContext.java:103)
    at sun.security.ssl.TransportContext.kickstart(TransportContext.java:220)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:433)
    at com.mysql.jdbc.ExportControlled.transformSocketToSSLSocket(ExportControlled.java:186)

解决方法：

<jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost:3306/foodmart?useSSL=false" jdbcUser="root" jdbcPassword="root1234" catalogUri="/WEB-INF/queries/FoodMart.xml" connectionPooling="false">

JDBCURL里新增useSSL=false参数，重新启动tomcat并验证。