<Schema name="CZS-sales">

<Dimension name="Geographical Area">

<Hierarchy hasAll="true" primaryKey="store_id" primaryKeyTable="store">

<Join leftKey="region_id" rightKey="region_id">

<Table name="store" />

<Table name="region" />

</Join>

<Level name="Country" table="region" column="sales_country" type="String"

uniqueMembers="true" levelType="Regular" hideMemberIf="Never" />

<Level name="Region" table="region" column="sales_region" type="String"

uniqueMembers="false" levelType="Regular" hideMemberIf="Never" />

<Level name="State" table="store" column="store_state" type="String"

uniqueMembers="true" levelType="Regular" hideMemberIf="Never" />

<Level name="City" table="store" column="store_city" type="String"

uniqueMembers="false" levelType="Regular" hideMemberIf="Never" />

<Level name="Store Name" table="store" column="store_name" type="String"

uniqueMembers="true" levelType="Regular" hideMemberIf="Never">

<Property name="Store Type" column="store_type" type="String" />

<Property name="Store Manager" column="store_manager" type="String" />

<Property name="Store Sqft" column="store_sqft" type="Numeric" />

<Property name="Street address" column="store_street_address"

type="String" />

</Level>

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

<Level name="Product Family" table="product_class" column="product_family"

type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never" />

<Level name="Product Department" table="product_class"

column="product_department" type="String" uniqueMembers="false"

levelType="Regular" hideMemberIf="Never" />

<Level name="Product Category" table="product_class" column="product_category"

type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never" />

<Level name="Product Subcategory" table="product_class"

column="product_subcategory" type="String" uniqueMembers="false"

levelType="Regular" hideMemberIf="Never" />

<Level name="Brand Name" table="product" column="brand_name" type="String"

uniqueMembers="false" levelType="Regular" hideMemberIf="Never" />

<Level name="Product Name" table="product" column="product_name" type="String"

uniqueMembers="true" levelType="Regular" hideMemberIf="Never" />

</Hierarchy>

</Dimension>

<Cube name="Sales" cache="true" enabled="true">

<Table name="sales_fact_2006" />

<DimensionUsage source="Geographical Area" name="Geographical Area"

foreignKey="store_id" />

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

<Measure name="Unit Sales" column="unit_sales" formatString="Standard"

aggregator="sum" />

<Measure name="Store Cost" column="store_cost" formatString="#,###.00"

aggregator="sum" />

<Measure name="Store Sales" column="store_sales" formatString="#,###.00"

aggregator="sum" />

</Cube>

</Schema>

<Roles>

<Role name="ROLE_SALES_MANAGER">

<SchemaGrant access="none">

<CubeGrant cube="Sales" access="all">

<HierarchyGrant hierarchy="[Geographical Area]" access="custom"

topLevel="[Geographical Area].[state]"

bottomLevel="[Geographical Area].[City]">

<MemberGrant member="[Geographical Area].[%{Country}].[%{Region}].[%{State} access="all"/>

</HierarchyGrant>

<HierarchyGrant hierarchy="[Product]" access="custom"

topLevel="[Product].[Product Family]"

bottomLevel="[Product].[Product Department]">

<MemberGrant member="[Product].[Electronics].[%{ProductDepartment}]" access="all"/>

</HierarchyGrant>

</CubeGrant>

</SchemaGrant>

</Role>

<Role name="ROLE_SALES_REP">

<SchemaGrant access="none">

<CubeGrant cube="Sales" access="all">

<HierarchyGrant hierarchy="[Geographical Area]" access="custom"

topLevel="[Geographical Area].[City]"

bottomLevel="[Geographical Area].[City]">

<MemberGrant member="[Geographical Area].[%{Country}].[%{Region}].[%{State}].[%{Cities}]" access="all"/>

</HierarchyGrant>

<HierarchyGrant hierarchy="[Product]" access="custom"

topLevel="[Product].[Product Family]"

bottomLevel="[Product].[Product Department]">

<MemberGrant member="[Product].[Electronics].[%{ProductDepartment}]" access="all"/>

</HierarchyGrant>

</CubeGrant>

</SchemaGrant>

</Role>

</Roles>