Mondrian 4 测试的简单demo（Saiku简单测试Schema文件）
来源：互联网 发布：解放军纪律知乎 编辑：程序博客网 时间：2024/06/06 03:22
## 整理此文章方便大家做测试（手写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'> <!-- == This software is subject to the terms of the Eclipse Public License v1.0 == Agreement, available at the following URL: == http://www.eclipse.org/legal/epl-v10.html. == You must accept the terms of that agreement to use this software. == == Copyright (C) 2000-2005 Julian Hyde == Copyright (C) 2005-2013 Pentaho and others == All Rights Reserved. --> <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> <Table name='agg_c_special_sales_fact_1997'/> <Table name='agg_pl_01_sales_fact_1997'/> <Table name='agg_l_05_sales_fact_1997'/> <Table name='agg_g_ms_pcat_sales_fact_1997'/> <Table name='agg_c_14_sales_fact_1997'/> </PhysicalSchema> <Dimension name='Store' table='store' key='Store Id'> <Attributes> <Attribute name='Store Country' hasHierarchy='false'> <Key> <Column name='store_country'/> </Key> </Attribute> <Attribute name='Store State' keyColumn='store_state' hasHierarchy='false'/> <Attribute name='Store City' hasHierarchy='false'> <Key> <Column name='store_state'/> <Column name='store_city'/> </Key> <Name> <Column name='store_city'/> </Name> </Attribute> <Attribute name='Store Id' keyColumn='store_id' hasHierarchy='false'/> <Attribute name='Store Name' keyColumn='store_name' hasHierarchy='false'> <Property attribute='Store Type'/> <Property attribute='Store Manager'/> <Property attribute='Store Sqft'/> <Property attribute='Grocery Sqft'/> <Property attribute='Frozen Sqft'/> <Property attribute='Meat Sqft'/> <Property attribute='Has coffee bar'/> <Property attribute='Street address'/> </Attribute> <Attribute name='Store Type' keyColumn='store_type' hierarchyAllMemberName='All Store Types'/> <Attribute name='Store Manager' keyColumn='store_manager' hasHierarchy='false'/> <Attribute name='Store Sqft' keyColumn='store_sqft' hasHierarchy='false'/> <Attribute name='Grocery Sqft' keyColumn='grocery_sqft' hasHierarchy='false'/> <Attribute name='Frozen Sqft' keyColumn='frozen_sqft' hasHierarchy='false'/> <Attribute name='Meat Sqft' keyColumn='meat_sqft' hasHierarchy='false'/> <Attribute name='Has coffee bar' keyColumn='coffee_bar' hasHierarchy='false'/> <Attribute name='Street address' keyColumn='store_street_address' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Stores' allMemberName='All Stores'> <Level attribute='Store Country'/> <Level attribute='Store State'/> <Level attribute='Store City'/> <Level attribute='Store Name'/> </Hierarchy> <Hierarchy name='Store Size in SQFT'> <Level attribute='Store Sqft'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Store2' key='Store Id'> <Attributes> <Attribute name='Store Id' table='store' keyColumn='store_id' hasHierarchy='false'/> <Attribute name='Store Type' table='store' keyColumn='store_type' hierarchyAllMemberName='false'/> <Attribute name='Store Manager' table='store' keyColumn='store_manager' hasHierarchy='false'/> <Attribute name='Store Sqft' table='store' keyColumn='store_sqft' hasHierarchy='false'/> <Attribute name='Grocery Sqft' table='store' keyColumn='grocery_sqft' hasHierarchy='false'/> <Attribute name='Frozen Sqft' table='store' keyColumn='frozen_sqft' hasHierarchy='false'/> <Attribute name='Meat Sqft' table='store' keyColumn='meat_sqft' hasHierarchy='false'/> <Attribute name='Has coffee bar' table='store' keyColumn='coffee_bar' hasHierarchy='false'/> <Attribute name='Street address' table='store' keyColumn='store_street_address' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Store' allMemberName='All Path'> <Level attribute='Store Type'/> <Level attribute='Has coffee bar'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Store3' table='store' key='Store Id'> <Attributes> <Attribute name='Store Id' keyColumn='store_id' hasHierarchy='false'/> <Attribute name='Store Manager' keyColumn='store_manager' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Store' allMemberName='All Path'> <Level attribute='Store Id'/> <Level attribute='Store Manager'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Time' table='time_by_day' type='TIME' key='Time Id'> <Attributes> <Attribute name='Year' caption='Year' keyColumn='the_year' levelType='TimeYears' hasHierarchy='false'> </Attribute> <Attribute name='Quarter' levelType='TimeQuarters' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='quarter'/> </Key> <Name> <Column name='quarter'/> </Name> </Attribute> <Attribute name='Month' levelType='TimeMonths' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='month_of_year'/> </Key> <Name> <Column name='month_of_year'/> </Name> </Attribute> <Attribute name='Week' levelType='TimeWeeks' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='week_of_year'/> </Key> <Name> <Column name='week_of_year'/> </Name> </Attribute> <Attribute name='Day' levelType='TimeDays' hasHierarchy='false'> <Key> <Column name='time_id'/> </Key> <Name> <Column name='day_of_month'/> </Name> <OrderBy> <Column name='time_id'/> </OrderBy> </Attribute> <Attribute name='Month Name' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='month_of_year'/> </Key> <Name> <Column name='the_month'/> </Name> </Attribute> <Attribute name='Date' keyColumn='the_date' hasHierarchy='false'/> <Attribute name='Date String' keyColumn='the_date' hasHierarchy='false'/> <Attribute name='Time Id' keyColumn='time_id' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Time' hasAll='false'> <Level attribute='Year'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy]</Annotation></Annotations> </Level> <Level attribute='Quarter'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[Qq]</Annotation></Annotations> </Level> <Level attribute='Month'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[Qq].[mm]</Annotation></Annotations> </Level> </Hierarchy> <Hierarchy name='Weekly' hasAll='true'> <Level attribute='Year'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy]</Annotation></Annotations> </Level> <Level attribute='Week'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[ww]</Annotation></Annotations> </Level> <Level attribute='Day'> <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[ww].[dd]</Annotation></Annotations> </Level> </Hierarchy> <Hierarchy name='Date Only' hasAll='true'> <Level attribute='Date String'> <Annotations><Annotation name="SaikuDayFormatString">yyyy/mm/dd</Annotation></Annotations> </Level> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Product' key='Product Id'> <Attributes> <Attribute name='Product Family' table='product_class' keyColumn='product_family' hasHierarchy='false'/> <Attribute name='Product Department' table='product_class' hasHierarchy='false'> <Key> <Column name='product_family'/> <Column name='product_department'/> </Key> <Name> <Column name='product_department'/> </Name> </Attribute> <Attribute name='Product Category' table='product_class' hasHierarchy='false'> <Key> <Column name='product_family'/> <Column name='product_department'/> <Column name='product_category'/> </Key> <Name> <Column name='product_category'/> </Name> </Attribute> <Attribute name='Product Subcategory' table='product_class' hasHierarchy='false'> <Key> <Column name='product_family'/> <Column name='product_department'/> <Column name='product_category'/> <Column name='product_subcategory'/> </Key> <Name> <Column name='product_subcategory'/> </Name> </Attribute> <Attribute name='Brand Name' table='product' hasHierarchy='false'> <Key> <Column table='product_class' name='product_family'/> <Column table='product_class' name='product_department'/> <Column table='product_class' name='product_category'/> <Column table='product_class' name='product_subcategory'/> <Column name='brand_name'/> </Key> <Name> <Column name='brand_name'/> </Name> </Attribute> <Attribute name='Product Name' table='product' hasHierarchy='false' keyColumn='product_id' nameColumn='product_name'/> <Attribute name='Product Id' table='product' keyColumn='product_id' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Products' allMemberName='All Products'> <Level attribute='Product Family'/> <Level attribute='Product Department'/> <Level attribute='Product Category'/> <Level attribute='Product Subcategory'/> <Level attribute='Brand Name'/> <Level attribute='Product Name'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Warehouse' table='warehouse' key='Warehouse Id'> <Attributes> <Attribute name='Country' keyColumn='warehouse_country' hasHierarchy='false'/> <Attribute name='State Province' keyColumn='warehouse_state_province' hasHierarchy='false'/> <Attribute name='City' hasHierarchy='false'> <Key> <Column name='warehouse_state_province'/> <Column name='warehouse_city'/> </Key> <Name> <Column name='warehouse_city'/> </Name> </Attribute> <Attribute name='Warehouse Name' keyColumn='warehouse_name' hasHierarchy='false'/> <Attribute name='Warehouse Id' keyColumn='warehouse_id' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Warehouses' allMemberName='All Warehouses'> <Level attribute='Country'/> <Level attribute='State Province'/> <Level attribute='City'/> <Level attribute='Warehouse Name'/> </Hierarchy> </Hierarchies> </Dimension> <Cube name='Sales' defaultMeasure='Unit Sales'> <!-- Use annotations to provide translations of this cube's caption and description into German and French. Use of annotations in this manner is experimental and unsupported; just for testing right now. --> <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> <Dimensions> <Dimension source='Store'/> <Dimension source='Time'/> <Dimension source='Product'/> <Dimension name='Promotion' table='promotion' key='Promotion Id'> <Attributes> <Attribute name='Promotion Id' keyColumn='promotion_id' hasHierarchy='false'/> <Attribute name='Promotion Name' keyColumn='promotion_name' hasHierarchy='false'/> <Attribute name='Media Type' keyColumn='media_type' hierarchyAllMemberName='All Media' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Media Type' allMemberName='All Media'> <Level attribute='Media Type'/> </Hierarchy> <Hierarchy name='Promotions' allMemberName='All Promotions'> <Level attribute='Promotion Name'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name='Customer' table='customer' key='Name'> <Attributes> <Attribute name='Country' keyColumn='country' hasHierarchy='false'/> <Attribute name='State Province' hasHierarchy='false'> <Key> <Column name='country'/> <Column name='state_province'/> </Key> <Name> <Column name='state_province'/> </Name> </Attribute> <Attribute name='City' hasHierarchy='false'> <Key> <Column name='country'/> <Column name='state_province'/> <Column name='city'/> </Key> <Name> <Column name='city'/> </Name> </Attribute> <Attribute name='Name' keyColumn='customer_id' nameColumn='full_name' orderByColumn='full_name' hasHierarchy='false'/> <Attribute name='Gender' keyColumn='gender' datatype="Boolean"/> <Attribute name='Marital Status' keyColumn='marital_status' approxRowCount='111'/> <Attribute name='Education' keyColumn='education' hasHierarchy='false'/> <Attribute name='Yearly Income' keyColumn='yearly_income' hierarchyAllMemberName='All Yearly Incomes'/> </Attributes> <Hierarchies> <Hierarchy name='Customers' allMemberName='All Customers'> <Level attribute='Country'/> <Level attribute='State Province'/> <Level attribute='City'/> <Level attribute='Name'/> </Hierarchy> <Hierarchy name='Education Level'> <Level attribute='Education' name='Education Level'/> </Hierarchy> </Hierarchies> </Dimension> </Dimensions> <MeasureGroups> <MeasureGroup name='Sales' table='sales_fact_1997'> <Measures> <Measure name='Unit Sales' column='unit_sales' aggregator='sum' formatString='Standard'/> <Measure name='Store Cost' column='store_cost' aggregator='sum' formatString='#,###.00'/> <Measure name='Store Sales' column='store_sales' aggregator='sum' formatString='#,###.00'/> <Measure name='Sales Count' column='product_id' aggregator='count' formatString='#,###'/> <Measure name='Customer Count' column='customer_id' aggregator='distinct-count' formatString='#,###'/> <Measure name='Promotion Sales' column='promotion_sales' aggregator='sum' formatString='#,###.00' datatype='Numeric'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/> <ForeignKeyLink dimension='Time' foreignKeyColumn='time_id'/> <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/> <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/> <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/> </DimensionLinks> </MeasureGroup> <MeasureGroup table='agg_c_special_sales_fact_1997' type='aggregate'> <Measures> <MeasureRef name='Fact Count' aggColumn='fact_count'/> <MeasureRef name='Unit Sales' aggColumn='unit_sales_sum'/> <MeasureRef name='Store Cost' aggColumn='store_cost_sum'/> <MeasureRef name='Store Sales' aggColumn='store_sales_sum'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/> <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/> <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/> <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/> <CopyLink dimension='Time' attribute='Month'> <Column aggColumn='time_year' table='time_by_day' name='the_year'/> <Column aggColumn='time_quarter' table='time_by_day' name='quarter'/> <Column aggColumn='time_month' table='time_by_day' name='month_of_year'/> </CopyLink> </DimensionLinks> </MeasureGroup> <MeasureGroup table='agg_l_05_sales_fact_1997' type='aggregate'> <Measures> <MeasureRef name='Fact Count' aggColumn='fact_count'/> <MeasureRef name='Unit Sales' aggColumn='unit_sales'/> <MeasureRef name='Store Cost' aggColumn='store_cost'/> <MeasureRef name='Store Sales' aggColumn='store_sales'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/> <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/> <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/> <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/> <NoLink dimension='Time'/> </DimensionLinks> </MeasureGroup> <MeasureGroup table='agg_c_14_sales_fact_1997' type='aggregate'> <Measures> <MeasureRef name='Fact Count' aggColumn='fact_count'/> <MeasureRef name='Unit Sales' aggColumn='unit_sales'/> <MeasureRef name='Store Cost' aggColumn='store_cost'/> <MeasureRef name='Store Sales' aggColumn='store_sales'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Store' foreignKeyColumn='store_id'/> <ForeignKeyLink dimension='Product' foreignKeyColumn='product_id'/> <ForeignKeyLink dimension='Promotion' foreignKeyColumn='promotion_id'/> <ForeignKeyLink dimension='Customer' foreignKeyColumn='customer_id'/> <CopyLink dimension='Time' attribute='Month'> <Column aggColumn='the_year' table='time_by_day' name='the_year'/> <Column aggColumn='quarter' table='time_by_day' name='quarter'/> <Column aggColumn='month_of_year' table='time_by_day' name='month_of_year'/> </CopyLink> </DimensionLinks> </MeasureGroup> <MeasureGroup table='agg_g_ms_pcat_sales_fact_1997' type='aggregate'> <Measures> <MeasureRef name='Fact Count' aggColumn='fact_count'/> <MeasureRef name='Unit Sales' aggColumn='unit_sales'/> <MeasureRef name='Store Cost' aggColumn='store_cost'/> <MeasureRef name='Store Sales' aggColumn='store_sales'/> <MeasureRef name='Customer Count' aggColumn='customer_count'/> </Measures> <DimensionLinks> <NoLink dimension='Store'/> <CopyLink dimension='Product'> <Column aggColumn='product_family' table='product_class' name='product_family'/> <Column aggColumn='product_department' table='product_class' name='product_department'/> <Column aggColumn='product_category' table='product_class' name='product_category'/> </CopyLink> <NoLink dimension='Promotion'/> <CopyLink dimension='Customer'> <Column aggColumn='gender' table='customer' name='gender'/> <Column aggColumn='marital_status' table='customer' name='marital_status'/> </CopyLink> <CopyLink dimension='Time' attribute='Month'> <Column aggColumn='the_year' table='time_by_day' name='the_year'/> <Column aggColumn='quarter' table='time_by_day' name='quarter'/> <Column aggColumn='month_of_year' table='time_by_day' name='month_of_year'/> </CopyLink> </DimensionLinks> </MeasureGroup> </MeasureGroups> <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> <!-- a simpler version of 'Sales' (with MEMBER_ORDINAL-properties) --> <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> <Cube name='Warehouse and Sales' > <Dimensions> <Dimension source='Store2'/> <Dimension source='Store3'/> </Dimensions> <MeasureGroups> <MeasureGroup table='sales_fact_1997'> <Measures> <Measure name='Sales Count' column='product_id' aggregator='count'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Store2' foreignKeyColumn='store_id'/> <ForeignKeyLink dimension='Store3' foreignKeyColumn='product_id'/> </DimensionLinks> </MeasureGroup> </MeasureGroups> </Cube> <Cube name='HR'> <Dimensions> <Dimension name='Employee' key='Employee Id'> <Attributes> <Attribute name='Manager Id' table='employee' keyColumn='supervisor_id'/> <Attribute name='Employee Id' table='employee' keyColumn='employee_id' nameColumn='full_name' orderByColumn='employee_id'/> <Attribute name='Store Id' table='employee' keyColumn='store_id'/> <Attribute name='Store Type' table='store' keyColumn='store_type' hierarchyAllMemberName='All Store Types'/> <Attribute name='Pay Type' table='position' keyColumn='pay_type' hierarchyAllMemberName='All Pay Types'/> <Attribute name='Management Role' table='position' keyColumn='management_role'/> <Attribute name='Position Title' table='position'> <Key> <Column name='management_role'/> <Column name='position_title'/> </Key> <Name> <Column name='position_title'/> </Name> <OrderBy> <Column name='position_id'/> </OrderBy> </Attribute> <Attribute name='Marital Status' table='employee' keyColumn='marital_status'/> <Attribute name='Gender' table='employee' keyColumn='gender'/> <Attribute name='Salary' table='employee' keyColumn='salary'/> <Attribute name='Education Level' table='employee' keyColumn='education_level'/> </Attributes> <Hierarchies> <Hierarchy name='Employees' allMemberName='All Employees'> <Level attribute='Employee Id' parentAttribute='Manager Id' nullParentValue='0'> <Closure table='employee_closure' parentColumn='supervisor_id' childColumn='employee_id'/> </Level> </Hierarchy> <Hierarchy name='Position' allMemberName='All Position'> <Level attribute='Management Role'/> <Level attribute='Position Title'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension name="Actual VS Budget" hanger="true"> <Attributes> <Attribute name="Type"/> </Attributes> </Dimension> <Dimension name='Department' table='department' key='Department Description'> <Attributes> <Attribute name='Department Description' keyColumn='department_id'/> </Attributes> <Hierarchies> <Hierarchy name='Department'> <Level attribute='Department Description'/> </Hierarchy> </Hierarchies> </Dimension> <!-- Use private "Time" dimension because key is different than public "Time" dimension. --> <Dimension name='Time' table='time_by_day' type='TIME' key='Time Id'> <Attributes> <Attribute name='Year' keyColumn='the_year' levelType='TimeYears' hasHierarchy='false'/> <Attribute name='Quarter' levelType='TimeQuarters' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='quarter'/> </Key> <Name> <Column name='quarter'/> </Name> </Attribute> <!-- Use the_month as source for the name, so members look like [Time].[1997].[Q1].[Jan] rather than [Time].[1997].[Q1].[1]. --> <Attribute name='Month' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='month_of_year'/> </Key> <Name> <Column name='the_month'/> </Name> </Attribute> <Attribute name='Date' keyColumn='the_date' hasHierarchy='false'/> <Attribute name='Time Id' keyColumn='time_id' hasHierarchy='false'/> </Attributes> <Hierarchies> <Hierarchy name='Time' hasAll='false'> <Level attribute='Year'/> <Level attribute='Quarter'/> <Level attribute='Month'/> </Hierarchy> </Hierarchies> </Dimension> <Dimension source='Store'/> </Dimensions> <MeasureGroups> <MeasureGroup name='HR' table='salary'> <Measures> <Measure name='Org Salary' column='salary_paid' aggregator='sum' formatString='Currency'/> <Measure name='Count' column='employee_id' aggregator='count' formatString='#,#'/> <Measure name='Number of Employees' column='employee_id' aggregator='distinct-count' formatString='#,#'/> </Measures> <DimensionLinks> <ForeignKeyLink dimension='Time' foreignKeyColumn='pay_date' attribute='Date'/> <ForeignKeyLink dimension='Department' foreignKeyColumn='department_id'/> <ForeignKeyLink dimension='Employee' foreignKeyColumn='employee_id'/> <ReferenceLink dimension='Store' viaDimension='Employee' viaAttribute='Store Id'/> </DimensionLinks> </MeasureGroup> </MeasureGroups> <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> <!-- Cubes for compatibility with old FoodMart. Will put them in a new schema at some point. --> <Cube name='Warehouse'> <Dimensions> <Dimension source='Store'/> <Dimension source='Time'/> <Dimension source='Product'/> <Dimension source='Warehouse'/> </Dimensions> <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> <!-- A cube based on a single table. --> <Cube name='Store'> <Dimensions> <!-- We could have used the shared dimension 'Store Type', but we want to test private dimensions without primary key. --> <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> <MeasureGroups> <MeasureGroup name='Store' table='store'> <Measures> <Measure name='Store Sqft' column='store_sqft' aggregator='sum' formatString='#,###'/> <Measure name='Grocery Sqft' column='grocery_sqft' aggregator='sum' formatString='#,###'/> </Measures> <DimensionLinks> <FactLink dimension='Store'/> <FactLink dimension='Store Type'/> <FactLink dimension='Has coffee bar'/> </DimensionLinks> </MeasureGroup> </MeasureGroups> </Cube> <!--<Cube name='HR'>--> <!--<Dimensions>--> <!--<Dimension source='Time'/>--> <!--</Dimensions>--> <!--<MeasureGroups>--> <!--<MeasureGroup name='HR' table='salary'>--> <!--<Measures>--> <!--<Measure name='Org Salary' aggregator='sum' column='salary_paid'/>--> <!--</Measures>--> <!--<DimensionLinks>--> <!--<ForeignKeyLink dimension='Time' foreignKeyColumn='pay_date' attribute='Date'/>--> <!--</DimensionLinks>--> <!--</MeasureGroup>--> <!--</MeasureGroups>--> <!--</Cube>--> <Role name='California manager'> <SchemaGrant access='none'> <CubeGrant cube='Sales' access='all'> <HierarchyGrant hierarchy='[Store].[Stores]' access='custom' topLevel='[Store].[Stores].[Store Country]'> <MemberGrant member='[Store].[Stores].[USA].[CA]' access='all'/> <MemberGrant member='[Store].[Stores].[USA].[CA].[Los Angeles]' access='none'/> </HierarchyGrant> <HierarchyGrant hierarchy='[Customer].[Customers]' access='custom' topLevel='[Customer].[Customers].[State Province]' bottomLevel='[Customer].[Customers].[City]'> <MemberGrant member='[Customer].[Customers].[USA].[CA]' access='all'/> <MemberGrant member='[Customer].[Customers].[USA].[CA].[Los Angeles]' access='none'/> </HierarchyGrant> <HierarchyGrant hierarchy='[Gender]' access='none'/> </CubeGrant> </SchemaGrant> </Role> <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
### 项目文件：http://pan.baidu.com/s/1c24uLGk
by 刘迎光
OpenBI交流群：495266201
liuyg@liuyingguang.cn
博客saiku专栏：http://blog.csdn.net/column/details/openbi.html
1 0
- Mondrian 4 测试的简单demo（Saiku简单测试Schema文件）
- mondrian的schema配置文件demo
- Kafka简单测试demo
- Saiku Mondrian Schema雪花模型写法
- Mondrian Schema介绍及简单配置示例
- Storm（四）Storm简单测试demo
- Sprinmvc 简单测试Demo（二）
- Android 测试框架简单demo
- Springmvc搭建简单测试demo
- mondrian schema 文件解析
- saiku mondrian
- JPA简单测试文件
- Saiku配置Mondrian中Schema中文乱码问题解决方案
- Saiku配置Mondrian中Schema中文乱码问题解决方案
- 一个简单的上传文件测试例子
- 使用node进行简单模块化demo测试
- Kylin, Mondrian, Saiku系统的整合
- Kylin, Mondrian, Saiku系统的整合
- 关于ListView的显示问题
- The number of method references in a .dex file cannot exceed 64K
- LeetCode 121. Best Time to Buy and Sell Stock
- windows安装配置mysql-5.7.13-winx64方法
- maven如何手动导入ojdbc6.jar或ojdbc14
- Mondrian 4 测试的简单demo（Saiku简单测试Schema文件）
- python网络天气爬虫
- HDU 4408 Minimum Spanning Tree（最小生成树计数）
- 防止sql注入
- Gym 101028G-The Tower of Evil
- Canvas之绘制基本形状
- Spring中的jar包简介
- 多进程下的单实例和全局变量
- java实现愤怒的小鸟游戏之Java swing图形界面开发游戏项目愤怒的小鸟源码及实现方式详解
