foodmart.xml

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

      formula="([Employees].currentmember.datamember, [Measures].[Org Salary])"/>
  <CalculatedMember name="Avg Salary" dimension="Measures"
      formatString="Currency"
      formula="[Measures].[Org Salary]/[Measures].[Number of Employees]"/>
</Cube>

<!-- Cube with one ragged hierarchy (otherwise the same as the "Sales"
   cube). -->
<Cube name="Sales Ragged">
  <Table name="sales_fact_1997">
    <AggExclude name="agg_pc_10_sales_fact_1997"/>
    <AggExclude name="agg_lc_10_sales_fact_1997"/>
  </Table>
  <Dimension name="Store" foreignKey="store_id">
    <Hierarchy hasAll="true" primaryKey="store_id">
      <Table name="store_ragged"/>
      <Level name="Store Country" column="store_country" uniqueMembers="true"
          hideMemberIf="Never"/>
      <Level name="Store State" column="store_state" uniqueMembers="true"
          hideMemberIf="IfParentsName"/>
      <Level name="Store City" column="store_city" uniqueMembers="false"
          hideMemberIf="IfBlankName"/>
      <Level name="Store Name" column="store_name" uniqueMembers="true"
          hideMemberIf="Never">
        <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="Geography" foreignKey="store_id">
    <Hierarchy hasAll="true" primaryKey="store_id">
      <Table name="store_ragged"/>
      <Level name="Country" column="store_country" uniqueMembers="true"
          hideMemberIf="Never"/>
      <Level name="State" column="store_state" uniqueMembers="true"
          hideMemberIf="IfParentsName"/>
      <Level name="City" column="store_city" uniqueMembers="false"
          hideMemberIf="IfBlankName"/>
    </Hierarchy>
  </Dimension>

  <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">
      <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">
      <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" uniqueMembers="true">
        <KeyExpression>
          <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>
        </KeyExpression>
        <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>
  <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"/>
    </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="#,###"/>
</Cube>

<!-- a simpler version of "Sales" (with MEMBER_ORDINAL-properties) -->
<Cube name="Sales 2">
   <Table name="sales_fact_1997"/>

   <DimensionUsage name="Time" source="Time" foreignKey="time_id"/>
   <DimensionUsage name="Product" source="Product" foreignKey="product_id"/>

   <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>

   <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>

   <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].PREVMEMBER),    Measures.[Profit])"
       visible="false">
      <CalculatedMemberProperty name="MEMBER_ORDINAL" value="5"/>
   </CalculatedMember>
</Cube>

<VirtualCube name="Warehouse and Sales" defaultMeasure="Store Sales">
  <VirtualCubeDimension cubeName="Sales" name="Customers"/>
  <VirtualCubeDimension cubeName="Sales" name="Education Level"/>
  <VirtualCubeDimension cubeName="Sales" name="Gender"/>
  <VirtualCubeDimension cubeName="Sales" name="Marital Status"/>
  <VirtualCubeDimension name="Product"/>
  <VirtualCubeDimension cubeName="Sales" name="Promotion Media"/>
  <VirtualCubeDimension cubeName="Sales" name="Promotions"/>
  <VirtualCubeDimension name="Store"/>
  <VirtualCubeDimension name="Time"/>
  <VirtualCubeDimension cubeName="Sales" name="Yearly Income"/>
  <VirtualCubeDimension cubeName="Warehouse" name="Warehouse"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Sales Count]"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Cost]"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Sales]"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Unit Sales]"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit]"/>
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Profit Growth]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Store Invoice]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Supply Time]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units Ordered]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Units Shipped]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Cost]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Profit]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Warehouse Sales]"/>
  <VirtualCubeMeasure cubeName="Warehouse" name="[Measures].[Average Warehouse Sale]"/>
  <!--
  <VirtualCubeMeasure cubeName="Sales" name="[Measures].[Store Sales Net]"/>
  -->
  <CalculatedMember name="Profit Per Unit Shipped" dimension="Measures">
    <Formula>[Measures].[Profit] / [Measures].[Units Shipped]</Formula>
  </CalculatedMember>
</VirtualCube>

<!-- A California manager can only see customers and stores in California.
     They cannot drill down on Gender. -->
<Role name="California manager">
  <SchemaGrant access="none">
    <CubeGrant cube="Sales" access="all">
      <HierarchyGrant hierarchy="[Store]" access="custom"
          topLevel="[Store].[Store Country]">
        <MemberGrant member="[Store].[USA].[CA]" access="all"/>
        <MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none"/>
      </HierarchyGrant>
      <HierarchyGrant hierarchy="[Customers]" access="custom"
          topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]">
        <MemberGrant member="[Customers].[USA].[CA]" access="all"/>
        <MemberGrant member="[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>

</Schema>