foodmart.xml

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

  <Dimension name="Marital Status" foreignKey="customer_id">
    <Hierarchy hasAll="true" allMemberName="All Marital Status" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Marital Status" column="marital_status" uniqueMembers="true" approxRowCount="111"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Yearly Income" foreignKey="customer_id">
    <Hierarchy hasAll="true" primaryKey="customer_id">
      <Table name="customer"/>
      <Level name="Yearly Income" column="yearly_income" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <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" aggregator="sum" formatString="#,###.00">
    <MeasureExpression>
      <SQL dialect="access">
Iif("sales_fact_1997"."promotion_id" = 0, 0, "sales_fact_1997"."store_sales")
      </SQL>
      <SQL dialect="oracle">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else "sales_fact_1997"."store_sales" end)
      </SQL>
      <SQL dialect="postgres">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else "sales_fact_1997"."store_sales" end)
      </SQL>
      <SQL dialect="mysql">
(case when `sales_fact_1997`.`promotion_id` = 0 then 0 else `sales_fact_1997`.`store_sales` end)
      </SQL>
      <SQL dialect="derby">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else "sales_fact_1997"."store_sales" end)
      </SQL>
      <SQL dialect="luciddb">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else "sales_fact_1997"."store_sales" end)
      </SQL>
      <SQL dialect="db2">
(case when "sales_fact_1997"."promotion_id" = 0 then 0 else "sales_fact_1997"."store_sales" end)
      </SQL>
      <SQL dialect="generic">
(case when sales_fact_1997.promotion_id = 0 then 0 else sales_fact_1997.store_sales end)
      </SQL>
    </MeasureExpression>
  </Measure>
  <CalculatedMember
      name="Profit"
      dimension="Measures">
    <Formula>[Measures].[Store Sales] - [Measures].[Store Cost]</Formula>
    <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
  </CalculatedMember>
  <CalculatedMember
      name="Profit last Period"
      dimension="Measures"
      formula="COALESCEEMPTY((Measures.[Profit], [Time].PREVMEMBER),    Measures.[Profit])"
      visible="false">
    <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
    <CalculatedMemberProperty name="MEMBER_ORDINAL" value="18"/>
  </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>
</Cube>

<Cube name="Warehouse">
  <Table name="inventory_fact_1997"/>

  <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"/>
  <DimensionUsage name="Warehouse" source="Warehouse" foreignKey="warehouse_id"/>

  <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" aggregator="sum">
    <MeasureExpression>
      <SQL dialect="mysql">
`warehouse_sales` - `inventory_fact_1997`.`warehouse_cost`
      </SQL>
      <SQL dialect="generic">
&quot;warehouse_sales&quot; - &quot;inventory_fact_1997&quot;.&quot;warehouse_cost&quot;
      </SQL>
    </MeasureExpression>
  </Measure>
  <CalculatedMember
      name="Average Warehouse Sale"
      dimension="Measures">
    <Formula>[Measures].[Warehouse Sales] / [Measures].[Warehouse Cost]</Formula>
    <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00"/>
  </CalculatedMember>
  <NamedSet name="Top Sellers">
    <Formula>TopCount([Warehouse].[Warehouse Name].MEMBERS, 5, [Measures].[Warehouse Sales])</Formula>
  </NamedSet>    
</Cube>

<!-- Test a cube based upon a single table. -->
<Cube name="Store">
  <Table name="store"/>
  <!-- We could have used the shared dimension "Store Type", but we
     want to test private dimensions without primary key. -->
  <Dimension name="Store Type">
    <Hierarchy hasAll="true">
      <Level name="Store Type" column="store_type" uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <!-- We don't have to specify primary key or foreign key since the shared
     dimension "Store" has the same underlying table as the cube. -->
  <DimensionUsage name="Store" source="Store"/>

  <Dimension name="Has coffee bar">
    <Hierarchy hasAll="true">
      <Level name="Has coffee bar" column="coffee_bar" uniqueMembers="true"
          type="Boolean"/>
    </Hierarchy>
  </Dimension>

  <Measure name="Store Sqft" column="store_sqft" aggregator="sum"
      formatString="#,###"/>
  <Measure name="Grocery Sqft" column="grocery_sqft" aggregator="sum"
      formatString="#,###"/>

</Cube>

<Cube name="HR">
  <Table name="salary"/>
  <!-- Use private "Time" dimension because key is different than public
     "Time" dimension. -->
  <Dimension name="Time" type="TimeDimension" foreignKey="pay_date">
    <Hierarchy hasAll="false" primaryKey="the_date">
      <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"/>
      <!-- Use the_month as source for the name, so members look like
           [Time].[1997].[Q1].[Jan] rather than [Time].[1997].[Q1].[1]. -->
      <Level name="Month" column="month_of_year" nameColumn="the_month"
          uniqueMembers="false" type="Numeric" levelType="TimeMonths"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Store" foreignKey="employee_id" >
    <Hierarchy hasAll="true" primaryKey="employee_id"
        primaryKeyTable="employee">
      <Join leftKey="store_id" rightKey="store_id">
        <Table name="employee"/>
        <Table name="store"/>
      </Join>
      <Level name="Store Country" table="store" column="store_country"
          uniqueMembers="true"/>
      <Level name="Store State" table="store" column="store_state"
          uniqueMembers="true"/>
      <Level name="Store City" table="store" column="store_city"
          uniqueMembers="false"/>
      <Level name="Store Name" table="store" 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="Pay Type" foreignKey="employee_id">
    <Hierarchy hasAll="true" primaryKey="employee_id"
        primaryKeyTable="employee">
      <Join leftKey="position_id" rightKey="position_id">
        <Table name="employee"/>
        <Table name="position"/>
      </Join>
      <Level name="Pay Type" table="position" column="pay_type"
          uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Store Type" foreignKey="employee_id">
    <Hierarchy hasAll="true" primaryKeyTable="employee" primaryKey="employee_id">
      <Join leftKey="store_id" rightKey="store_id">
        <Table name="employee"/>
        <Table name="store"/>
      </Join>
      <Level name="Store Type" table="store" column="store_type"
          uniqueMembers="true"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Position" foreignKey="employee_id">
    <Hierarchy hasAll="true" allMemberName="All Position"
        primaryKey="employee_id">
      <Table name="employee"/>
      <Level name="Management Role" uniqueMembers="true"
          column="management_role"/>
      <Level name="Position Title" uniqueMembers="false"
          column="position_title" ordinalColumn="position_id"/>
    </Hierarchy>
  </Dimension>

  <Dimension name="Department" foreignKey="department_id">
    <Hierarchy hasAll="true" primaryKey="department_id">
      <Table name="department"/>
      <Level name="Department Description" uniqueMembers="true"
          column="department_id"/>
    </Hierarchy>
  </Dimension>
  <Dimension name="Employees" foreignKey="employee_id">
    <Hierarchy hasAll="true" allMemberName="All Employees"
        primaryKey="employee_id">
      <Table name="employee"/>
      <Level name="Employee Id" type="Numeric" uniqueMembers="true"
          column="employee_id" parentColumn="supervisor_id"
          nameColumn="full_name" nullParentValue="0">
        <Closure parentColumn="supervisor_id" childColumn="employee_id">
          <Table name="employee_closure"/>
        </Closure>
        <Property name="Marital Status" column="marital_status"/>
        <Property name="Position Title" column="position_title"/>
        <Property name="Gender" column="gender"/>
        <Property name="Salary" column="salary"/>
        <Property name="Education Level" column="education_level"/>
        <Property name="Management Role" column="management_role"/>
      </Level>
    </Hierarchy>
  </Dimension>

  <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="#,#"/>
  <CalculatedMember name="Employee Salary" dimension="Measures"
      formatString="Currency"