WebSVN – SmartDukaan – /trunk/pentaho/schema/SalesSchema.xml

<Schema name="SalesSchema">
        <Cube name="SalesCube" cache="true" enabled="true">
                <Table name="sales">
                </Table>
                <Dimension type="TimeDimension" foreignKey="date_id" name="OrderDate">
                        <Hierarchy name="OrderDate" hasAll="true" allMemberName="All" primaryKey="date_id">
                                <Table name="datedim">
                                </Table>
                                <Level approxRowCount="3" name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="4" name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="8" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="20" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="pincode" name="Geography">
                        <Hierarchy name="Territory" hasAll="true" allMemberName="All" primaryKey="pincode">
                                <Table name="pincodesB">
                                </Table>
                                <Level approxRowCount="25" name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="10" name="City" column="city" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Pincode" column="pincode" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="status" name="OrderStatus">
                        <Hierarchy name="StatusDim" hasAll="true" allMemberName="All" primaryKey="status">
                                <Table name="orderstatus">
                                </Table>
                                <Level name="isValid" column="isValid" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusGroup" column="statusGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusSubGroup" column="statusSubGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusName" column="statusName" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="item_id" name="Product">
                        <Hierarchy name="Product Name" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="item">
                                </Table>
                                <Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Product" column="catalog_item_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))                                           </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))                                           </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Color" column="color" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="item_id" name="Category">
                        <Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="item">
                                </Table>
                                <Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="price_range" name="Price">
                        <Hierarchy name="Price Range" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="pricerange">
                                </Table>
                                <Level name="Price Range" column="name" nameColumn="name" ordinalColumn="id" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="payment_method" name="Payment">
                        <Hierarchy name="Payment" hasAll="true" allMemberName="All" primaryKey="paymentmethodid">
                                <Table name="paymentmethod">
                                </Table>
                                <Level approxRowCount="6" name="Gateway" column="gateway" nameColumn="gateway" ordinalColumn="gateway" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Payment Method" column="paymentmethodname" nameColumn="paymentmethodname" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="order_hour" name="Order Hour">
                        <Hierarchy name="Order Hour" hasAll="true" allMemberName="All" primaryKey="hour_id">
                                <Table name="orderhour">
                                </Table>
                                <Level name="Hour Range" column="hour_range" ordinalColumn="hour_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Hour" column="hour_id" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="customer_id" name="First Source">
                        <Hierarchy name="First Source" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="user">
                                </Table>
                                <Level name="Source" column="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when source_type = &#39;Paid&#39; then &#39;Paid&#39; when source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when source_type = &#39;Paid&#39; then &#39;Paid&#39; when source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Site" column="source_url" nameColumn="source_url" ordinalColumn="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Promotion">
                        <Hierarchy name="Promotion" hasAll="true" allMemberName="All">
                                <Level name="CouponCode" table="sales" column="coupon_code" nameColumn="coupon_code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Session Source">
                        <Hierarchy name="Session Source" hasAll="true" allMemberName="All">
                                <Level name="Source" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when session_source_type = &#39;Paid&#39; then &#39;Paid&#39; when session_source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when session_source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when session_source_type = &#39;Paid&#39; then &#39;Paid&#39; when session_source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when session_source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Site" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Affiliate">
                        <Hierarchy name="Affiliate" hasAll="true" allMemberName="All">
                                <Level name="Affiliate" table="sales" column="aff_name" nameColumn="aff_name" ordinalColumn="aff_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="days_to_purchase" name="Days To Purchase">
                        <Hierarchy name="Days To Purchase" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="daystopurchaserange">
                                </Table>
                                <Level name="Days To Purchase" column="id" nameColumn="name" ordinalColumn="id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="pickupStoreId" name="PickupType">
                        <Hierarchy name="PickupType" hasAll="true" allMemberName="All">
                                <Level name="PickupType" table="sales" column="pickupStoreId" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when pickupStoreId is null then &#39;HomeDelivery&#39;  else &#39;Store Pickup&#39; end                                            </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when pickupStoreId is null then &#39;HomeDelivery&#39;  else &#39;Store Pickup&#39; end                                            </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="StoreId" table="sales" column="pickupStoreId" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Measure name="Quantity" column="quantity" datatype="Numeric" formatString="###,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Total Sales" column="total_amount" datatype="Numeric" formatString="###,###,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Transfer Price" datatype="Numeric" formatString="###,###,###" aggregator="sum" visible="true">
                        <MeasureExpression>
                                <SQL dialect="generic">
                                        transfer_price*quantity                         </SQL>
                        </MeasureExpression>
                </Measure>
                <CalculatedMember name="Profit" caption="Profit" formula="[Measures].[Total Sales]-[Measures].[Transfer Price]" dimension="Measures" visible="true">
                </CalculatedMember>
                <CalculatedMember name="Average Selling Price" caption="Average Selling Price" formula="[Measures].[Total Sales]/[Measures].[Quantity]" dimension="Measures" visible="true">
                </CalculatedMember>
        </Cube>
        <Cube name="SalesBookingCube" cache="true" enabled="true">
                <Table name="sales">
                </Table>
                <Dimension type="TimeDimension" foreignKey="booking_date_id" name="BookingDate">
                        <Hierarchy name="BookingDate" hasAll="true" allMemberName="All" primaryKey="date_id">
                                <Table name="datedim" alias="bookingdatedim">
                                </Table>
                                <Level approxRowCount="3" name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="4" name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="12" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="20" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="pincode" name="Geography">
                        <Hierarchy name="Territory" hasAll="true" allMemberName="All" primaryKey="pincode">
                                <Table name="pincodesB">
                                </Table>
                                <Level approxRowCount="25" name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level approxRowCount="10" name="City" column="city" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Pincode" column="pincode" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="status" name="OrderStatus">
                        <Hierarchy name="StatusDim" hasAll="true" allMemberName="All" primaryKey="status">
                                <Table name="orderstatus">
                                </Table>
                                <Level name="isValid" column="isValid" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusGroup" column="statusGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusSubGroup" column="statusSubGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="StatusName" column="statusName" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="item_id" name="Product">
                        <Hierarchy name="Product Name" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="item">
                                </Table>
                                <Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Product" column="catalog_item_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))                                           </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))                                           </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Color" column="color" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="item_id" name="Category">
                        <Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="item">
                                </Table>
                                <Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="price_range" name="Price">
                        <Hierarchy name="Price Range" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="pricerange">
                                </Table>
                                <Level name="Price Range" column="name" nameColumn="name" ordinalColumn="id" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="payment_method" name="Payment">
                        <Hierarchy name="Payment" hasAll="true" allMemberName="All" primaryKey="paymentmethodid">
                                <Table name="paymentmethod">
                                </Table>
                                <Level approxRowCount="6" name="Gateway" column="gateway" nameColumn="gateway" ordinalColumn="gateway" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Payment Method" column="paymentmethodname" nameColumn="paymentmethodname" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="order_hour" name="Order Hour">
                        <Hierarchy name="Order Hour" hasAll="true" allMemberName="All" primaryKey="hour_id">
                                <Table name="orderhour">
                                </Table>
                                <Level name="Hour Range" column="hour_range" ordinalColumn="hour_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Hour" column="hour_id" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="customer_id" name="First Source">
                        <Hierarchy name="First Source" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="user">
                                </Table>
                                <Level name="Source" column="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when source_type = &#39;Paid&#39; then &#39;Paid&#39; when source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when source_type = &#39;Paid&#39; then &#39;Paid&#39; when source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Site" column="source_url" nameColumn="source_url" ordinalColumn="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Promotion">
                        <Hierarchy name="Promotion" hasAll="true" allMemberName="All">
                                <Level name="CouponCode" table="sales" column="coupon_code" nameColumn="coupon_code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Session Source">
                        <Hierarchy name="Session Source" hasAll="true" allMemberName="All">
                                <Level name="Source" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when session_source_type = &#39;Paid&#39; then &#39;Paid&#39; when session_source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when session_source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when session_source_type = &#39;Paid&#39; then &#39;Paid&#39; when session_source_url like &#39;saholic.com%&#39; then &#39;Direct&#39; when session_source_url like &#39;google.co%&#39; then &#39;Search&#39; else &#39;Referer&#39; end                                         </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="Site" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Affiliate">
                        <Hierarchy name="Affiliate" hasAll="true" allMemberName="All">
                                <Level name="Affiliate" table="sales" column="aff_name" nameColumn="aff_name" ordinalColumn="aff_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="days_to_purchase" name="Days To Purchase">
                        <Hierarchy name="Days To Purchase" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="daystopurchaserange">
                                </Table>
                                <Level name="Days To Purchase" column="id" nameColumn="name" ordinalColumn="id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="pickupStoreId" name="PickupType">
                        <Hierarchy name="PickupType" hasAll="true" allMemberName="All">
                                <Level name="PickupType" table="sales" column="pickupStoreId" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when pickupStoreId is null then &#39;HomeDelivery&#39;  else &#39;Store Pickup&#39; end                                            </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when pickupStoreId is null then &#39;HomeDelivery&#39;  else &#39;Store Pickup&#39; end                                            </SQL>
                                        </NameExpression>
                                </Level>
                                <Level name="StoreId" table="sales" column="pickupStoreId" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Measure name="Quantity" column="quantity" datatype="Numeric" formatString="###,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Total Sales" column="total_amount" datatype="Numeric" formatString="###,###,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Transfer Price" datatype="Numeric" formatString="##,##,##,##,###" aggregator="sum" visible="true">
                        <MeasureExpression>
                                <SQL dialect="generic">
                                        transfer_price*quantity                         </SQL>
                        </MeasureExpression>
                </Measure>
                <CalculatedMember name="Profit" caption="Profit" formula="[Measures].[Total Sales]-[Measures].[Transfer Price]" dimension="Measures" visible="true">
                </CalculatedMember>
                <CalculatedMember name="Average Selling Price" caption="Average Selling Price" formula="[Measures].[Total Sales]/[Measures].[Quantity]" dimension="Measures" visible="true">
                </CalculatedMember>
        </Cube>
</Schema>
Subversion Repositories SmartDukaan

(root)/trunk/pentaho/schema/SalesSchema.xml – Rev 6919
