有下面四个表，想用saiku可以查询出每个客户购买的产品名称和数量，用schema workbench怎么设置cube呢？

Question

/**销售表*/

create table Sale (

saleId intnot null,

proId intnull,

cusId intnull,

unitPricefloat null, --单价

number intnull, --数量

constraintPK_SALE primary key (saleId)

)

/**用户表*/

create table Customer (

cusId intnot null,

genderchar(1) null, --性别

constraintPK_CUSTOMER primary key (cusId)

)

/**产品表*/

create table Product (

proId intnot null,

proTypeIdint null,

proNamevarchar(32) null,

constraintPK_PRODUCT primary key (proId)

)

/**产品类别表*/

create table ProductType (

proTypeIdint not null,

proTypeNamevarchar(32) null,

constraintPK_PRODUCTTYPE primary key (proTypeId)

)

Answer 1

<?xml version="1.0" encoding="UTF-8" ?>
<Schema name="SaleSchema">
    <Cube name="SalesCube" >
       <!--事实表 -->
        <Table name="sale">
        </Table>
        <!--客户维 -->
        <Dimension  name="客户性别" foreignKey="cusId" >
            <Hierarchy hasAll="true" allMemberName="所有性别"  primaryKey="cusId">
                <Table name="customer">
                </Table>
                <Level name="性别"  column="gender" >
                </Level>
            </Hierarchy>
        </Dimension>
        <!--产品类别维-->
        <Dimension  name="产品类别" foreignKey="proId"  >
            <Hierarchy hasAll="true" allMemberName="所有产品"  primaryKey="proId" primaryKeyTable="product">
                <Join leftKey="proTypeId" rightKey="proTypeId">
                    <Table name="product">
                    </Table>
                    <Table name="producttype">
                    </Table>
                </Join>
                <Level name="产品类型名称"  table="producttype" column="proTypeId" nameColumn="proTypeName"  uniqueMembers="true" >
                </Level>
                <Level name="产品名称"  table="product" column="proId" nameColumn="proName"  uniqueMembers="true" >
                </Level>
            </Hierarchy>
        </Dimension>
        <!--度量指标 -->
        <Measure name="产品数量" column="number" datatype="Integer" aggregator="sum">
        </Measure>
        <Measure name="总销售额"  aggregator="sum" formatString="$#,##0.00">
            <!-- unitPrice*number所得值的列 -->
            <MeasureExpression>
                <SQL dialect="generic">(unitPrice*number)</SQL>
            </MeasureExpression>
        </Measure>
        <CalculatedMember name="平均单价"  dimension="Measures" >
            <Formula>[Measures].[总销售额] / [Measures].[产品数量]</Formula>
            <CalculatedMemberProperty name="FORMAT_STRING" value="$#,##0.00">
            </CalculatedMemberProperty>
        </CalculatedMember>
    </Cube>
</Schema>