Subversion Repositories SmartDukaan

Rev

Rev 3222 | Rev 3437 | Go to most recent revision | Blame | Compare with Previous | Last modification | View Log | RSS feed 

<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 name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
                                </Level>
                                <Level name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
                                </Level>
                                <Level name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
                                </Level>
                                <Level 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 name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level 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="Status" column="description" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="item_id" name="Product">
                        <Hierarchy name="Product" 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="ModelNumber" column="model_number" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                </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" name="Price">
                        <Hierarchy name="Price" hasAll="true" allMemberName="All">
                                <Level name="Price" table="sales" column="unit_price" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when unit_price&#60;2000 then 0 when unit_price &#60; 5000 then 2000 when unit_price &#60; 10000 then 5000 when unit_price &#60; 15000 then 10000 when unit_price &#60;20000 then 15000 when unit_price &#60; 50000 then 20000 else 50000 end                                              </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when unit_price&#60;2000 then &#39;0 - 2000&#39; when unit_price &#60; 5000 then &#39;2000 - 5000&#39; when unit_price &#60; 10000 then &#39;5000 - 10000&#39; when unit_price &#60; 15000 then &#39;10000 - 15000&#39; when unit_price &#60;20000 then &#39;15000 - 20000&#39; when unit_price &#60; 50000 then &#39;20000 - 50000&#39; else &#39;&#62; 50000&#39; end                                            </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        case when unit_price&#60;2000 then 0 when unit_price &#60; 5000 then 2000 when unit_price &#60; 10000 then 5000 when unit_price &#60; 15000 then 10000 when unit_price &#60;20000 then 15000 when unit_price &#60; 50000 then 20000 else 50000 end                                              </SQL>
                                        </OrdinalExpression>
                                </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 name="Gateway" column="gateway" nameColumn="gateway" ordinalColumn="gateway" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                                <Level name="Payment Method" column="paymentmethodname" nameColumn="paymentmethodname" ordinalColumn="paymentmethodname" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" name="Order Hour">
                        <Hierarchy name="Order Hour" hasAll="true" allMemberName="All">
                                <Level name="Order Hour Range" table="sales" column="order_hour" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when order_hour&#60;6 then 0 when order_hour&#60;12 then 6 when order_hour&#60;15 then 12 when order_hour&#60;18 then 15 else 18 end                                               </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when order_hour&#60;6 then &#34;0am - 6am&#34; when order_hour&#60;12 then &#34;6am - 12pm&#34; when order_hour&#60;15 then &#34;12pm - 15pm&#34; when order_hour&#60;18 then &#34;15pm - 18pm&#34; else &#34;18pm - 24pm&#34; end                                         </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        case when order_hour&#60;6 then 0 when order_hour&#60;12 then 6 when order_hour&#60;15 then 12 when order_hour&#60;18 then 15 else 18 end                                               </SQL>
                                        </OrdinalExpression>
                                </Level>
                                <Level name="Order Hour" table="sales" column="order_hour" ordinalColumn="order_hour" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Dimension type="StandardDimension" foreignKey="customer_id" name="First Source">
                        <Hierarchy name="Source" hasAll="true" allMemberName="All" primaryKey="id">
                                <Table name="user">
                                </Table>
                                <Level name="Source" column="source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when source is null then &#39;Unknown&#39; when source like &#39;DIRECT%&#39; then &#39;Direct&#39; when source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when source is null then &#39;Unknown&#39; when source like &#39;DIRECT%&#39; then &#39;Direct&#39; when source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        case when source is null then &#39;Unknown&#39; when source like &#39;DIRECT%&#39; then &#39;Direct&#39; when source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </OrdinalExpression>
                                </Level>
                                <Level name="Site" column="source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </OrdinalExpression>
                                </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" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when session_source is null then &#39;Unknown&#39; when session_source like &#39;DIRECT%&#39; then &#39;Direct&#39; when session_source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when session_source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when session_source is null then &#39;Unknown&#39; when session_source like &#39;DIRECT%&#39; then &#39;Direct&#39; when session_source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when session_source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        case when session_source is null then &#39;Unknown&#39; when session_source like &#39;DIRECT%&#39; then &#39;Direct&#39; when session_source like &#39;ORGANIC%&#39; then &#39;Organic&#39; when session_source like &#39;PAID%&#39; then &#39;Paid&#39; else &#39;Referer&#39; end                                             </SQL>
                                        </OrdinalExpression>
                                </Level>
                                <Level name="Site" table="sales" column="session_source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)                                              </SQL>
                                        </OrdinalExpression>
                                </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" name="Days To Purchase">
                        <Hierarchy name="Days To Purchase" hasAll="true" allMemberName="All">
                                <Level name="Days To Purchase" table="sales" column="first_source_start_date_id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
                                        <KeyExpression>
                                                <SQL dialect="generic">
                                                        case when session_start_date_id-first_source_start_date_id&#62;30 then &#39;&#62; month&#39; when session_start_date_id-first_source_start_date_id&#62;15 then &#39;&#62; 15 days&#39; when session_start_date_id-first_source_start_date_id&#62;7 then &#39;&#62; 7 days&#39; when session_start_date_id-first_source_start_date_id&#62;2 then &#39;&#62; 2 days&#39; when session_start_date_id-first_source_start_date_id&#62;1 then &#39;&#62; 1 day&#39; else &#39;same day&#39; end                                               </SQL>
                                        </KeyExpression>
                                        <NameExpression>
                                                <SQL dialect="generic">
                                                        case when session_start_date_id-first_source_start_date_id&#62;30 then &#39;&#62; month&#39; when session_start_date_id-first_source_start_date_id&#62;15 then &#39;&#62; 15 days&#39; when session_start_date_id-first_source_start_date_id&#62;7 then &#39;&#62; 7 days&#39; when session_start_date_id-first_source_start_date_id&#62;2 then &#39;&#62; 2 days&#39; when session_start_date_id-first_source_start_date_id&#62;1 then &#39;&#62; 1 day&#39; else &#39;same day&#39; end                                               </SQL>
                                        </NameExpression>
                                        <OrdinalExpression>
                                                <SQL dialect="generic">
                                                        session_start_date_id-first_source_start_date_id                                                </SQL>
                                        </OrdinalExpression>
                                </Level>
                        </Hierarchy>
                </Dimension>
                <Measure name="Quantity" column="quantity" datatype="Integer" formatString="##,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Average Selling Price" column="total_amount" datatype="Numeric" formatString="##,###" aggregator="avg" visible="true">
                </Measure>
                <Measure name="Total Sales" column="total_amount" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
                </Measure>
                <Measure name="Transfer Price" column="transfer_price" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
                </Measure>
                <CalculatedMember name="Profit" caption="Profit" formula="[Measures].[Total Sales]-[Measures].[Transfer Price]" dimension="Measures" visible="true">
                </CalculatedMember>
        </Cube>
</Schema>