Rev 4166 | Blame | Compare with Previous | Last modification | View Log | RSS feed
<Schema name="OrderFulfillmentSchema"><Cube name="OrderFulfillmentCube" cache="true" enabled="true"><Table name="orderfulfillment"></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="customer_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="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="Status" column="statusName" 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="Estimate"><Hierarchy name="Estimate" hasAll="true" allMemberName="All"><Level name="Estimate" table="orderfulfillment" column="expected_delivery_days" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when expected_delivery_days <= 5 then expected_delivery_days when expected_delivery_days > 5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when expected_delivery_days <= 5 then convert(expected_delivery_days, char) when expected_delivery_days > 5 then 'more than 5 days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Actual Delivery Days"><Hierarchy name="Actual Delivery Days" hasAll="true" allMemberName="All"><Level name="Actual Delivery Days" table="orderfulfillment" column="actual_delivery_days" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when actual_delivery_days <= 5 then actual_delivery_days when actual_delivery_days > 5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when actual_delivery_days <= 5 then convert(actual_delivery_days, char) when actual_delivery_days > 5 then 'more than 5 days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Connected Locally"><Hierarchy name="Connected Locally" hasAll="true" allMemberName="All"><Level name="Connected Locally" table="orderfulfillment" column="connected_locally" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when connected_locally <= 5 then connected_locally when connected_locally > 5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when connected_locally <= 5 then convert(connected_locally, char) when connected_locally > 5 then 'more than 5 days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Reached Destination City"><Hierarchy name="Reached Destination City" hasAll="true" allMemberName="All"><Level name="Reached Destination City" table="orderfulfillment" column="reached_destination_city" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when reached_destination_city <= 5 then reached_destination_city when reached_destination_city > 5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when reached_destination_city <= 5 then convert(reached_destination_city, char) when reached_destination_city > 5 then 'more than 5 days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="First Delivery Attempt"><Hierarchy name="First Delivery Attempt" hasAll="true" allMemberName="All"><Level name="First Delivery Attempt" table="orderfulfillment" column="first_delivery_attempt" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when first_delivery_attempt <= 5 then first_delivery_attempt when first_delivery_attempt > 5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when first_delivery_attempt <= 5 then convert(first_delivery_attempt, char) when first_delivery_attempt > 5 then 'more than 5 days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Delivery Difference"><Hierarchy name="Delivery Difference" hasAll="true" allMemberName="All"><Level name="Delivery Difference" table="orderfulfillment" column="actual_estimate_diff" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when actual_estimate_diff <0 then -1 when actual_estimate_diff =0 then 0 when actual_estimate_diff<5 then actual_estimate_diff when actual_estimate_diff>=5 then 6 else 7 end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when actual_estimate_diff <0 then 'Before Time' when actual_estimate_diff =0 then 'On Time' when actual_estimate_diff<5 then concat('Delayed by ', convert(actual_estimate_diff, char), ' days') when actual_estimate_diff>=5 then 'Delayed by 5 or more days' else '-' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Logistics Provider"><Hierarchy name="Logistics Provider" hasAll="true" allMemberName="All"><Level name="Logistics Provider" table="orderfulfillment" column="logistics_provider_id" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><NameExpression><SQL dialect="generic">case when logistics_provider_id=1 then 'Blue Dart' when logistics_provider_id=2 then 'Aramex' else 'N/A' end </SQL></NameExpression></Level></Hierarchy></Dimension><Dimension type="StandardDimension" name="Delay Cause"><Hierarchy name="Delay Cause" hasAll="true" allMemberName="All"><Level name="Delay Cause" table="orderfulfillment" column="delay_reason" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"></Level></Hierarchy></Dimension><Dimension type="StandardDimension" foreignKey="payment_method" name="Payment Mode"><Hierarchy name="Payment Mode" hasAll="true" allMemberName="All" primaryKey="paymentmethodid"><Table name="paymentmethod"></Table><Level name="Payment Mode" column="gateway" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"><KeyExpression><SQL dialect="generic">case when gateway in ('ebs', 'hdfc', 'cheque') then 'prepaid' else gateway end </SQL></KeyExpression><NameExpression><SQL dialect="generic">case when gateway in ('ebs', 'hdfc', 'cheque') then 'prepaid' else gateway end </SQL></NameExpression></Level></Hierarchy></Dimension><Measure name="Quantity" column="date_id" datatype="Integer" formatString="##,###" aggregator="count" visible="true"></Measure></Cube></Schema>