1、最近一直在研究Mondrian如何构建Schema4.0的雪花模型,和星星模型,刚开始研究了大半天都在看Schema3.0的,太坑了。这东西比较是国外研究的技术,许多api文档都英文的,国内很少资料。只能硬着头皮干。哈哈。废话不多说,直接上代码。
表关系,如下:
薪资表 -- salary
员工表 -- employee
店铺表 --store
位置表--position
部门表--department
时间维度表--time_by_day
注意:其实雪花模型已经包含星星模型,不懂得可以查查资料
Schema4.0雪花模型如下:
<Schema metamodelVersion='4.0' name='test'> <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> <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='Department2'> <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> <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> </PhysicalSchema> </Schema> |
重点维度Employee中是没有table属性,Attribute里面table的不同表名称。
salary作为实时表,employee和strore,position划分为维表。
salary和employee关系为星星模型,employee->strore->position关系为雪花模型。
注意:在PhysicalSchema里面,Link表之间的关系得描述清楚哦。
<Link source='store' target='employee'>
<ForeignKey>
<Column name='store_id'/>
</ForeignKey>
</Link>
source 来源表(主表)target 目标表(子表)
自己理解的不完全正确,可以参考。维表的度量其实在数据模型中就不体现,维表的维度作为切面层级对实时表的度量进行分析。