Mondrian 4 测试的简单demo

# 简述
整理此文章方便大家做测试（手写Mondrian Schema自测），不必每次都向saiku上传文件了

# 关键文件
* foodmart4.xml  测试对应的schema文件
* foodmart-sql.zip    测试对应的数据库脚本

# maven项目：
## pom文件
```
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.firewarm</groupId>
    <artifactId>testMondrian</artifactId>
    <packaging>war</packaging>
    <version>0.0.1-SNAPSHOT</version>
    <name>testMondrian Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <repositories>
        <repository>
            <id>mine</id>
            <name>public Releases</name>
            <layout>default</layout>
            <url>http://nexus.liuyingguang.cn:8081/nexus/content/groups/public/</url>
        </repository>
        <repository>
            <id>mine-meteorite-bi-release</id>
            <name>public Releases</name>
            <layout>default</layout>
            <url>http://nexus.liuyingguang.cn:8081/nexus/content/repositories/meteorite-bi-release/</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
            <groupId>org.pentaho</groupId>
            <artifactId>mondrian</artifactId>
            <version>4.5.0.2-208</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.9</version>
        </dependency>
    </dependencies>
    <build>
        <finalName>testMondrian</finalName>
    </build>
</project>
```

## foodmart4.xml文件
```
<?xml version='1.0'?>
<Schema name='FoodMart' metamodelVersion='4.0'>

<PhysicalSchema>
        <Table name='salary'/>
        <Table name='salary' alias='salary2'/>
        <Table name='department'>
            <Key>
                <Column name='department_id'/>
            </Key>
        </Table>
        <Table name='employee'>
            <Key>
                <Column name='employee_id'/>
            </Key>
        </Table>
        <Table name='employee_closure'>
            <Key>
                <Column name='employee_id'/>
            </Key>
        </Table>
        <Link source='employee' target='employee_closure'>
            <ForeignKey>
                <Column name='employee_id'/>
            </ForeignKey>
        </Link>
        <Table name='position'>
            <Key>
                <Column name='position_id'/>
            </Key>
        </Table>
        <Link source='position' target='employee'>
            <ForeignKey>
                <Column name='position_id'/>
            </ForeignKey>
        </Link>
        <Table name='promotion'>
            <Key>
                <Column name='promotion_id'/>
            </Key>
        </Table>
        <Table name='store'>
            <Key>
                <Column name='store_id'/>
            </Key>
        </Table>
        <Link source='store' target='employee'>
            <ForeignKey>
                <Column name='store_id'/>
            </ForeignKey>
        </Link>
        <Table name='product' keyColumn='product_id'/>
        <Table name='product_class' keyColumn='product_class_id'/>
        <Link target='product' source='product_class'>
            <ForeignKey>
                <Column name='product_class_id'/>
            </ForeignKey>
        </Link>
        <Table name='time_by_day'>
            <Key>
                <Column name='time_id'/>
            </Key>
        </Table>
        <Table name='customer'>
            <Key>
                <Column name='customer_id'/>
            </Key>
            <ColumnDefs>
                <CalculatedColumnDef name='full_name' type='String'>
                    <ExpressionView>
                        <SQL dialect='oracle'>
                            <Column name='fname'/>  || ' ' || <Column name='lname'/>
                        </SQL>
                        <SQL dialect='access'>
                            <Column name='fname'/>  + ' ' + <Column name='lname'/>
                        </SQL>
                        <SQL dialect='postgres'>
                            <Column name='fname'/>  || ' ' || <Column name='lname'/>
                        </SQL>
                        <SQL dialect='mysql'>
                            CONCAT(<Column name='fname'/>, ' ', <Column name='lname'/>)
                        </SQL>
                        <SQL dialect='mssql'>
                            <Column name='fname'/> + ' ' + <Column name='lname'/>
                        </SQL>
                        <SQL dialect='derby'>
                            <Column name='fullname'/>
                        </SQL>
                        <SQL dialect='db2'>
                            CONCAT(CONCAT(<Column name='fname'/>, ' '), <Column name='lname'/>)
                        </SQL>
                        <SQL dialect='luciddb'>
                            <Column name='fname'/>  || ' ' || <Column name='lname'/>
                        </SQL>
                        <SQL dialect='generic'>
                            <Column name='fullname'/>
                        </SQL>
                    </ExpressionView>
                </CalculatedColumnDef>
            </ColumnDefs>
        </Table>
        <Table name='sales_fact_1997'>
            <ColumnDefs>
                <CalculatedColumnDef name='promotion_sales'>
                    <ExpressionView>
                        <SQL dialect='access'>
                            Iif(<Column table='sales_fact_1997' name='promotion_id'/> = 0, 0,
                            <Column table='sales_fact_1997' name='store_sales'/>)
                        </SQL>
                    <SQL dialect='generic'>
                        case when <Column table='sales_fact_1997' name='promotion_id'/> = 0 then 0
                        else <Column table='sales_fact_1997' name='store_sales'/> end
                    </SQL>
                    </ExpressionView>
                </CalculatedColumnDef>
            </ColumnDefs>
        </Table>
        <Table name='inventory_fact_1997'>
            <ColumnDefs>
                <CalculatedColumnDef name='warehouse_profit'>
                    <ExpressionView>
                        <SQL dialect='generic'>
                            <Column table='inventory_fact_1997' name='warehouse_sales'/>
                            -
                            <Column name='warehouse_cost'/>
                        </SQL>
                    </ExpressionView>
                </CalculatedColumnDef>
            </ColumnDefs>
        </Table>
        <Table name='warehouse'>
            <Key>
                <Column name='warehouse_id'/>
            </Key>
        </Table>

<Cube name='Sales' defaultMeasure='Unit Sales'>
        
        <Annotations>
            <Annotation name='caption.de_DE'>Verkaufen</Annotation>
            <Annotation name='caption.fr_FR'>Ventes</Annotation>
            <Annotation name='caption+fr_FR'>Ventes</Annotation>
            <Annotation name='description.fr_FR'>Cube des ventes</Annotation>
            <Annotation name='description.de'>Cube Verkaufen</Annotation>
            <Annotation name='description.de_AT'>Cube den Verkaufen</Annotation>
        </Annotations>

<CalculatedMembers>
            <CalculatedMember name='Profit' dimension='Measures'>
            <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
                <CalculatedMemberProperty name="FORMAT_STRING" expression="Iif(([Measures].[Store Sales]) < 10000, '|(#,##0.00 €)|style=red', '|#,##0.00 €|style=green')"/>
            </CalculatedMember>
            <CalculatedMember name='Profit last Period' dimension='Measures'
                    formula='COALESCEEMPTY((Measures.[Profit], [Time].[Time].PREVMEMBER),    Measures.[Profit])'
                    visible='false'>
                <CalculatedMemberProperty name='FORMAT_STRING' value='$#,##0.00'/>
            </CalculatedMember>
            <CalculatedMember name='Profit Growth' dimension='Measures'
                    formula='([Measures].[Profit] - [Measures].[Profit last Period]) / [Measures].[Profit last Period]'
                    visible='true' caption='Gewinn-Wachstum'>
                <CalculatedMemberProperty name='FORMAT_STRING' value='0.0%'/>
            </CalculatedMember>
        </CalculatedMembers>
    </Cube>

<Cube name='Sales 2'>
        <Dimensions>
            <Dimension source='Time'/>
            <Dimension source='Product'/>
            <Dimension name='Gender' table='customer' key='Id'>
                <Attributes>
                    <Attribute name='Gender' keyColumn='gender'/>
                    <Attribute name='Id' keyColumn='customer_id'/>
                </Attributes>
            </Dimension>
        </Dimensions>
        <MeasureGroups>
            <MeasureGroup name='Sales' table='sales_fact_1997'>
                <Measures>
                    <Measure name='Sales Count' column='product_id' aggregator='count' formatString='#,###'>
                        <CalculatedMemberProperty name='MEMBER_ORDINAL' value='1'/>
                    </Measure>
                    <Measure name='Unit Sales' column='unit_sales' aggregator='sum'    formatString='Standard'>
                        <CalculatedMemberProperty name='MEMBER_ORDINAL' value='2'/>
                    </Measure>
                    <Measure name='Store Sales' column='store_sales' aggregator='sum'  formatString='#,###.00'>
                        <CalculatedMemberProperty name='MEMBER_ORDINAL' value='3'/>
                    </Measure>
                    <Measure name='Store Cost' column='store_cost' aggregator='sum'    formatString='#,###.00'>
                        <CalculatedMemberProperty name='MEMBER_ORDINAL' value='6'/>
                    </Measure>
                    <Measure name='Customer Count' column='customer_id' aggregator='distinct-count' formatString='#,###'>
                        <CalculatedMemberProperty name='MEMBER_ORDINAL' value='7'/>
                    </Measure>
                </Measures>
                <DimensionLinks>
                    <ForeignKeyLink dimension='Time' foreignKeyColumn='time_id'/>
                    <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/>
                    <ForeignKeyLink dimension='Gender' foreignKeyColumn='customer_id'/>
                </DimensionLinks>
            </MeasureGroup>
        </MeasureGroups>
        <CalculatedMembers>
            <CalculatedMember
                    name='Profit'
                    dimension='Measures'>
                <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
                <CalculatedMemberProperty name='FORMAT_STRING' value='$#,##0.00'/>
                <CalculatedMemberProperty name='MEMBER_ORDINAL' value='4'/>
            </CalculatedMember>
            <CalculatedMember
                    name='Profit last Period'
                    dimension='Measures'
                    formula='COALESCEEMPTY((Measures.[Profit], [Time].[Time].PREVMEMBER),    Measures.[Profit])'
                    visible='false'>
                <CalculatedMemberProperty name='MEMBER_ORDINAL' value='5'/>
            </CalculatedMember>
        </CalculatedMembers>
    </Cube>

</DimensionLinks>
            </MeasureGroup>

</MeasureGroups>

</Cube>

<CalculatedMembers>
            <CalculatedMember name='Employee Salary' dimension='Measures'
                              formatString='Currency'
                              formula='([Employees].currentmember.datamember, [Measures].[Org Salary])'/>
            <CalculatedMember name='Avg Salary' dimension='Measures'
                              formatString='Currency'
                              formula='[Measures].[Org Salary]/[Measures].[Number of Employees]'/>
        <CalculatedMember name="Test" hierarchy="[Store].[Stores]" formula="[Store].[Stores].[All Stores]"/>
        </CalculatedMembers>
        <CalculatedMember hierarchy="[Actual VS Budget].[Type]" name="Actual">
            <Formula>
                [Store].[Stores].[All Stores]
            </Formula>
        </CalculatedMember>
    </Cube>

<MeasureGroups>
            <MeasureGroup name='Warehouse' table='inventory_fact_1997'>
                <Measures>
                    <Measure name='Store Invoice' column='store_invoice' aggregator='sum'/>
                    <Measure name='Supply Time' column='supply_time' aggregator='sum'/>
                    <Measure name='Warehouse Cost' column='warehouse_cost' aggregator='sum'/>
                    <Measure name='Warehouse Sales' column='warehouse_sales' aggregator='sum'/>
                    <Measure name='Units Shipped' column='units_shipped' aggregator='sum' formatString='#.0'/>
                    <Measure name='Units Ordered' column='units_ordered' aggregator='sum' formatString='#.0'/>
                    <Measure name='Warehouse Profit' column='warehouse_profit' aggregator='sum' datatype='Numeric'/>
                </Measures>
                <DimensionLinks>
                    <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/>
                    <ForeignKeyLink dimension='Time' foreignKeyColumn='time_id'/>
                    <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/>
                    <ForeignKeyLink dimension='Warehouse' foreignKeyColumn=''>
                        <ForeignKey>
                            <Column name='warehouse_id'/>
                        </ForeignKey>
                    </ForeignKeyLink>
                </DimensionLinks>
            </MeasureGroup>
        </MeasureGroups>
        <NamedSets>
            <NamedSet name='Top Sellers'>
                <Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])</Formula>
            </NamedSet>
        </NamedSets>

</Cube>

<Cube name='Store'>
        <Dimensions>
            
            <Dimension name='Store Type' key='Store Type'>
                <Attributes>
                    <Attribute name='Store Type' table='store' keyColumn='store_type' hasHierarchy='true'/>
                </Attributes>
            </Dimension>
            <Dimension source='Store'/>
            <Dimension name='Has coffee bar'>
                <Attributes>
                    <Attribute name='Has coffee bar' table='store' datatype='Boolean' keyColumn='coffee_bar' hasHierarchy='true'/>
                </Attributes>
            </Dimension>
        </Dimensions>

<Role name='No HR Cube'>
        <SchemaGrant access='all'>
            <CubeGrant cube='HR' access='none'/>
        </SchemaGrant>
    </Role>
    <Role name='No Cubes'>
    <SchemaGrant access='none'/>
    </Role>
</Schema>
```

## 简单java代码测试：

```
import java.io.PrintWriter;

import mondrian.olap.Connection;
import mondrian.olap.DriverManager;
import mondrian.olap.Query;
import mondrian.olap.Result;

/**
 *
 * @author Liuyg
 * @mail lyg210@msn.cn
 * @version
 * @time May 18, 2015
 *
 */
public class Test {
    @SuppressWarnings("deprecation")
    public static void main(String[] args) {
        Connection connection = DriverManager
                .getConnection(
                        "Provider=mondrian;Jdbc=jdbc:mysql://192.168.100.216:3306/foodmart?user=root&password=111111;Catalog=res:foodmart4.xml;",
                        null);
        Query query = connection
                .parseQuery("SELECT NON EMPTY {Hierarchize({[Measures].[Store Sqft]})} ON COLUMNS,NON EMPTY {Hierarchize({[Store Type].Members})} ON ROWS FROM [Store]");
        Result result = connection.execute(query);
        PrintWriter pw = new PrintWriter(System.out);
        result.print(pw);
        pw.flush();
    }
}
```

## 返回结果示例：
```
Axis #0:
{}
Axis #1:
{[Measures].[Store Sqft]}
Axis #2:
{[Store Type].[Store Type].[All Store Type]}
{[Store Type].[Store Type].[Deluxe Supermarket]}
{[Store Type].[Store Type].[Gourmet Supermarket]}
{[Store Type].[Store Type].[Mid-Size Grocery]}
{[Store Type].[Store Type].[Small Grocery]}
{[Store Type].[Store Type].[Supermarket]}
Row #0: 571,596
Row #1: 146,045
Row #2: 47,447
Row #3: 109,343
Row #4: 75,281
Row #5: 193,480

```

> by 刘迎光@萤火虫工作室  
> OpenBI交流群：495266201  
> MicroService 微服务交流群：217722918    
> mail: liuyg#liuyingguang.cn  
> 博主首页（==防止爬虫==）：http://blog.liuyingguang.cn

Lighting@刘迎光

Navigation

Recent Posts

友情链接