[saiku] schema文件分析
上一篇讲到了如何在管理台配置数据源 [ http://www.cnblogs.com/avivaye/p/4877767.html ]
这次来说明下shema文件里面是怎样配置Cube和角色权限的
通过分析demo中的FoodMart.xml 可以得知XML文件中主要配置了如下的内容
|- shcema
|- <PhysicalSchema>
|- <Dimension>
| - <Cube>
|- <Role>
详细配置如下：
<?xml version='1.0'?> <Schema name='FoodMart' metamodelVersion='4.0'> <!-- 第一个关键节点：PhysicalSchema 配置物理Schema--> <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 维度--> <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'> </Attribute> <Attribute name='Quarter' levelType='TimeQuarters' hasHierarchy='false'> <Key> <Column name='the_year'/> <Column name='quarter'/> </Key> <Name> <Column name='quarter'/> </Name> </Attribute> <Attribute name='Month' levelType='TimeMonths' hasHierarchy='false'> <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'> <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'> <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='Date String' keyColumn='date_string' 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> <Hierarchy name='Date Only' hasAll='true'> <Level attribute='Date String'> <Annotations><Annotation name="SaikuDayFormatString">yyyy/mm/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 定义包含哪一些维度 Dimensions --> <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' datatype="Boolean"/> <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" expression="Iif(([Measures].[Store Sales]) < 10000, '|(#,##0.00 €)|style=red', '|#,##0.00 €|style=green')"/> </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="Actual VS Budget" hanger="true"> <Attributes> <Attribute name="Type"/> </Attributes> </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]'/> <CalculatedMember name="Test" hierarchy="[Store].[Stores]" formula="[Store].[Stores].[All Stores]"/> </CalculatedMembers> <CalculatedMember hierarchy="[Actual VS Budget].[Type]" name="Actual"> <Formula> [Store].[Stores].[All Stores] </Formula> </CalculatedMember> </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 角色权限控制配置 --> <Role name='California manager'> <SchemaGrant access='none'><!--只允许访问Sales这个Cube--> <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'><!--除了HR这个Cube其他的外都能访问--> <SchemaGrant access='all'> <CubeGrant cube='HR' access='none'/> </SchemaGrant> </Role> <Role name='No Cubes'><!--不允许访问任何Cube--> <SchemaGrant access='none'/> </Role> </Schema>
