FoodMart 4

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' keyColumn='the_year' levelType='TimeYears' hasHierarchy='false'>
                <Annotations><Annotation name="AnalyzerDateFormat">[yyyy]</Annotation></Annotations>
            </Attribute>
            <Attribute name='Quarter' levelType='TimeQuarters' hasHierarchy='false'>
                <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[Qq]</Annotation></Annotations>
                <Key>
                    <Column name='the_year'/>
                    <Column name='quarter'/>
                </Key>
                <Name>
                    <Column name='quarter'/>
                </Name>
            </Attribute>
            <Attribute name='Month' levelType='TimeMonths' hasHierarchy='false'>
                <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[Qq].[mm]</Annotation></Annotations>
                <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'>
                <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[ww]</Annotation></Annotations>
                <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'>
                <Annotations><Annotation name="AnalyzerDateFormat">[yyyy].[Qq].[mm].[dd]</Annotation></Annotations>
                <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='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>
        </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'/>
                    <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' value='$#,##0.00'/>
            </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='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]'/>
        </CalculatedMembers>
    </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>

</Schema>