foodmart.xml

基于mondrian 开源框架进行OLAP多维分析

<?xml version="1.0"?>
<Schema name="FoodMart">
<!--
  == $Id: //open/mondrian-release/3.0/demo/FoodMart.xml#4 $
  == This software is subject to the terms of the Common Public License
  == Agreement, available at the following URL:
  == http://www.opensource.org/licenses/cpl.html.
  == Copyright (C) 2000-2002 Kana Software, Inc.
  == Copyright (C) 2002-2007 Julian Hyde and others.
  == All Rights Reserved.
  == You must accept the terms of that agreement to use this software.
  -->

<!-- Shared dimensions -->

  <Dimension name="Store">
    <Hierarchy hasAll="true" primaryKey="store_id">
      <Table name="store"/>
      <Level name="Store Country" column="store_country" uniqueMembers="true"/>
      <Level name="Store State" column="store_state" uniqueMembers="true"/>
      <Level name="Store City" column="store_city" uniqueMembers="false"/>
      <Level name="Store Name" column="store_name" uniqueMembers="true">
        <Property name="Store Type" column="store_type"/>
        <Property name="Store Manager" column="store_manager"/>
        <Property name="Store Sqft" column="store_sqft" type="Numeric"/>
        <Property name="Grocery Sqft" column="grocery_sqft" type="Numeric"/>
        <Property name="Frozen Sqft" column="frozen_sqft" type="Numeric"/>
        <Property name="Meat Sqft" column="meat_sqft" type="Numeric"/>
        <Property name="Has coffee bar" column="coffee_bar" type="Boolean"/>
        <Property name="Street address" column="store_street_address" type="String"/>
      </Level>
    </Hierarchy>
  </Dimension>

  <Dimension name="Store Size in SQFT">
    <Hierarchy hasAll="true" primaryKey="store_id">
      <Table name="store"/>
      <Level name="Store Sqft" column="store_sqft" type="Numeric" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Store Type">
    <Hierarchy hasAll="true" primaryKey="store_id">
      <Table name="store"/>
      <Level name="Store Type" column="store_type" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Time" type="TimeDimension">
    <Hierarchy hasAll="false" primaryKey="time_id">
      <Table name="time_by_day"/>
      <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"
          levelType="TimeYears"/>
      <Level name="Quarter" column="quarter" uniqueMembers="false"
          levelType="TimeQuarters"/>
      <Level name="Month" column="month_of_year" uniqueMembers="false" type="Numeric"
          levelType="TimeMonths"/>
    </Hierarchy>
    <Hierarchy hasAll="true" name="Weekly" primaryKey="time_id"
          defaultMember="[Time.Weekly].[All Time.Weeklys].[1997]">
      <Table name="time_by_day"/>
      <Level name="Year" column="the_year" type="Numeric" uniqueMembers="true"
          levelType="TimeYears"/>
      <Level name="Week" column="week_of_year" type="Numeric" uniqueMembers="false"
          levelType="TimeWeeks"/>
      <Level name="Day" column="day_of_month" uniqueMembers="false" type="Numeric"
          levelType="TimeDays"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Product">
    <Hierarchy hasAll="true" primaryKey="product_id" primaryKeyTable="product">
      <Join leftKey="product_class_id" rightKey="product_class_id">
        <Table name="product"/>
        <Table name="product_class"/>
      </Join>
<!--
      <Query>
        <SQL dialect="generic">
SELECT *
FROM "product", "product_class"
WHERE "product"."product_class_id" = "product_class"."product_class_id"
        </SQL>
      </Query>
      <Level name="Product Family" column="product_family" uniqueMembers="true"/>
      <Level name="Product Department" column="product_department" uniqueMembers="false"/>
      <Level name="Product Category" column="product_category" uniqueMembers="false"/>
      <Level name="Product Subcategory" column="product_subcategory" uniqueMembers="false"/>
      <Level name="Brand Name" column="brand_name" uniqueMembers="false"/>
      <Level name="Product Name" column="product_name" uniqueMembers="true"/>
-->
      <Level name="Product Family" table="product_class" column="product_family"
          uniqueMembers="true"/>
      <Level name="Product Department" table="product_class" column="product_department"
          uniqueMembers="false"/>
      <Level name="Product Category" table="product_class" column="product_category"
          uniqueMembers="false"/>
      <Level name="Product Subcategory" table="product_class" column="product_subcategory"
          uniqueMembers="false"/>
      <Level name="Brand Name" table="product" column="brand_name" uniqueMembers="false"/>
      <Level name="Product Name" table="product" column="product_name"
          uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Warehouse">
    <Hierarchy hasAll="true" primaryKey="warehouse_id">
      <Table name="warehouse"/>
      <Level name="Country" column="warehouse_country" uniqueMembers="true"/>
      <Level name="State Province" column="warehouse_state_province"
          uniqueMembers="true"/>
      <Level name="City" column="warehouse_city" uniqueMembers="false"/>
      <Level name="Warehouse Name" column="warehouse_name" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

<!-- Sales -->
<Cube name="Sales" defaultMeasure="Unit Sales">
  <Table name="sales_fact_1997">
<!--
    <AggExclude name="agg_l_03_sales_fact_1997" />
    <AggExclude name="agg_ll_01_sales_fact_1997" />
    <AggExclude name="agg_pl_01_sales_fact_1997" />
    <AggExclude name="agg_l_05_sales_fact_1997" />
-->
    <AggExclude name="agg_c_special_sales_fact_1997" />
<!--
    <AggExclude name="agg_c_14_sales_fact_1997" />
-->
    <AggExclude name="agg_lc_100_sales_fact_1997" />
    <AggExclude name="agg_lc_10_sales_fact_1997" />
    <AggExclude name="agg_pc_10_sales_fact_1997" />
    <AggName name="agg_c_special_sales_fact_1997">
        <AggFactCount column="FACT_COUNT"/>
        <AggIgnoreColumn column="foo"/>
        <AggIgnoreColumn column="bar"/>
        <AggForeignKey factColumn="product_id" aggColumn="PRODUCT_ID" />
        <AggForeignKey factColumn="customer_id" aggColumn="CUSTOMER_ID" />
        <AggForeignKey factColumn="promotion_id" aggColumn="PROMOTION_ID" />
        <AggForeignKey factColumn="store_id" aggColumn="STORE_ID" />
<!--
        <AggMeasure name="[Measures].[Avg Unit Sales]" column="UNIT_SALES_AVG"/>
-->
        <AggMeasure name="[Measures].[Unit Sales]" column="UNIT_SALES_SUM" />
        <AggMeasure name="[Measures].[Store Cost]" column="STORE_COST_SUM" />
        <AggMeasure name="[Measures].[Store Sales]" column="STORE_SALES_SUM" />
        <AggLevel name="[Time].[Year]" column="TIME_YEAR" />
        <AggLevel name="[Time].[Quarter]" column="TIME_QUARTER" />
        <AggLevel name="[Time].[Month]" column="TIME_MONTH" />
    </AggName>
  </Table>

  <DimensionUsage name="Store" source="Store" foreignKey="store_id"/>
  <DimensionUsage name="Store Size in SQFT" source="Store Size in SQFT"
      foreignKey="store_id"/>
  <DimensionUsage name="Store Type" source="Store Type" foreignKey="store_id"/>
  <DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
  <DimensionUsage name="Product" source="Product" foreignKey="product_id"/>
  <Dimension name="Promotion Media" foreignKey="promotion_id">
    <Hierarchy hasAll="true" allMemberName="All Media" primaryKey="promotion_id" defaultMember="All Media">
      <Table name="promotion"/>
      <Level name="Media Type" column="media_type" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Promotions" foreignKey="promotion_id">
    <Hierarchy hasAll="true" allMemberName="All Promotions" primaryKey="promotion_id" defaultMember="[All Promotions]">
      <Table name="promotion"/>
      <Level name="Promotion Name" column="promotion_name" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Customers" foreignKey="customer_id">
    <Hierarchy hasAll="true" allMemberName="All Customers" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Country" column="country" uniqueMembers="true"/>
      <Level name="State Province" column="state_province" uniqueMembers="true"/>
      <Level name="City" column="city" uniqueMembers="false"/>
      <Level name="Name" column="customer_id" type="Numeric" uniqueMembers="true">
        <NameExpression>
          <SQL dialect="oracle">
"fname" || ' ' || "lname"
          </SQL>
          <SQL dialect="access">
fname + ' ' + lname
          </SQL>
          <SQL dialect="postgres">
"fname" || ' ' || "lname"
          </SQL>
          <SQL dialect="mysql">
CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)
          </SQL>
          <SQL dialect="mssql">
fname + ' ' + lname
          </SQL>
          <SQL dialect="derby">
"customer"."fullname"
          </SQL>
          <SQL dialect="db2">
CONCAT(CONCAT("customer"."fname", ' '), "customer"."lname")
          </SQL>
          <SQL dialect="luciddb">
"fname" || ' ' || "lname"
          </SQL>
            <SQL dialect="teradata">
"fname" || ' ' || "lname"
            </SQL>
          <SQL dialect="generic">
fullname
          </SQL>
        </NameExpression>
        <OrdinalExpression>
          <SQL dialect="oracle">
"fname" || ' ' || "lname"
          </SQL>
          <SQL dialect="access">
fname + ' ' + lname
          </SQL>
          <SQL dialect="postgres">
"fname" || ' ' || "lname"
          </SQL>
          <SQL dialect="mysql">
CONCAT(`customer`.`fname`, ' ', `customer`.`lname`)
          </SQL>
          <SQL dialect="mssql">
fname + ' ' + lname
          </SQL>
          <SQL dialect="derby">
"customer"."fullname"
          </SQL>
          <SQL dialect="db2">
CONCAT(CONCAT("customer"."fname", ' '), "customer"."lname")
          </SQL>
          <SQL dialect="luciddb">
"fname" || ' ' || "lname"
          </SQL>
          <SQL dialect="generic">
fullname
          </SQL>
        </OrdinalExpression>
        <Property name="Gender" column="gender"/>
        <Property name="Marital Status" column="marital_status"/>
        <Property name="Education" column="education"/>
        <Property name="Yearly Income" column="yearly_income"/>
      </Level>
    </Hierarchy>
  </Dimension>
  <Dimension name="Education Level" foreignKey="customer_id">
    <Hierarchy hasAll="true" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Education Level" column="education" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Gender" foreignKey="customer_id">
    <Hierarchy hasAll="true" allMemberName="All Gender" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Gender" column="gender" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>