Subversion Repositories SmartDukaan

Rev

Rev 4166 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
3706 vikas 1 
<Schema name="OrderFulfillmentSchema">
2 
	<Cube name="OrderFulfillmentCube" cache="true" enabled="true">
3 
		<Table name="orderfulfillment">
4 
		</Table>
5 
		<Dimension type="TimeDimension" foreignKey="date_id" name="OrderDate">
6 
			<Hierarchy name="OrderDate" hasAll="true" allMemberName="All" primaryKey="date_id">
7 
				<Table name="datedim">
8 
				</Table>
9 
				<Level name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
10 
				</Level>
11 
				<Level name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
12 
				</Level>
13 
				<Level name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
14 
				</Level>
15 
				<Level name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
16 
				</Level>
17 
			</Hierarchy>
18 
		</Dimension>
19 
		<Dimension type="StandardDimension" foreignKey="customer_pincode" name="Geography">
20 
			<Hierarchy name="Territory" hasAll="true" allMemberName="All" primaryKey="pincode">
21 
				<Table name="pincodesB">
22 
				</Table>
23 
				<Level name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
24 
				</Level>
25 
				<Level name="City" column="city" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
26 
				</Level>
27 
				<Level name="Pincode" column="pincode" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
28 
				</Level>
29 
			</Hierarchy>
30 
		</Dimension>
31 
		<Dimension type="StandardDimension" foreignKey="status" name="OrderStatus">
32 
			<Hierarchy name="StatusDim" hasAll="true" allMemberName="All" primaryKey="status">
33 
				<Table name="orderstatus">
34 
				</Table>
5157 anupam.sin 35 
				<Level name="StatusGroup" column="statusGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
3706 vikas 36 
				</Level>
5157 anupam.sin 37 
				<Level name="StatusSubGroup" column="statusSubGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
38 
				</Level>
39 
				<Level name="Status" column="statusName" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
40 
				</Level>
3706 vikas 41 
			</Hierarchy>
42 
		</Dimension>
43 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Product">
44 
			<Hierarchy name="Product" hasAll="true" allMemberName="All" primaryKey="id">
45 
				<Table name="item">
46 
				</Table>
47 
				<Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
48 
				</Level>
49 
				<Level name="ModelNumber" column="model_number" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
50 
				</Level>
51 
				<Level name="Color" column="color" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
52 
				</Level>
53 
			</Hierarchy>
54 
		</Dimension>
55 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Category">
56 
			<Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
57 
				<Table name="item">
58 
				</Table>
59 
				<Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
60 
				</Level>
61 
				<Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
62 
				</Level>
63 
				<Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
64 
				</Level>
65 
			</Hierarchy>
66 
		</Dimension>
67 
		<Dimension type="StandardDimension" name="Estimate">
68 
			<Hierarchy name="Estimate" hasAll="true" allMemberName="All">
69 
				<Level name="Estimate" table="orderfulfillment" column="expected_delivery_days" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
70 
					<KeyExpression>
71 
						<SQL dialect="generic">
72 
							case when expected_delivery_days &#60;= 5 then expected_delivery_days when expected_delivery_days &#62; 5 then 6 else 7 end						</SQL>
73 
					</KeyExpression>
74 
					<NameExpression>
75 
						<SQL dialect="generic">
76 
							case when expected_delivery_days &#60;= 5 then convert(expected_delivery_days, char) when expected_delivery_days &#62; 5 then &#39;more than 5 days&#39; else &#39;-&#39; end						</SQL>
77 
					</NameExpression>
78 
				</Level>
79 
			</Hierarchy>
80 
		</Dimension>
81 
		<Dimension type="StandardDimension" name="Actual Delivery Days">
82 
			<Hierarchy name="Actual Delivery Days" hasAll="true" allMemberName="All">
83 
				<Level name="Actual Delivery Days" table="orderfulfillment" column="actual_delivery_days" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
84 
					<KeyExpression>
85 
						<SQL dialect="generic">
86 
							case when actual_delivery_days &#60;= 5 then actual_delivery_days when actual_delivery_days &#62; 5 then 6 else 7 end						</SQL>
87 
					</KeyExpression>
88 
					<NameExpression>
89 
						<SQL dialect="generic">
90 
							case when actual_delivery_days &#60;= 5 then convert(actual_delivery_days, char) when actual_delivery_days &#62; 5 then &#39;more than 5 days&#39; else &#39;-&#39; end						</SQL>
91 
					</NameExpression>
92 
				</Level>
93 
			</Hierarchy>
94 
		</Dimension>
5157 anupam.sin 95 
		<Dimension type="StandardDimension" name="Connected Locally">
96 
			<Hierarchy name="Connected Locally" hasAll="true" allMemberName="All">
97 
				<Level name="Connected Locally" table="orderfulfillment" column="connected_locally" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
98 
					<KeyExpression>
99 
						<SQL dialect="generic">
100 
							case when connected_locally &#60;= 5 then connected_locally when connected_locally &#62; 5 then 6 else 7 end						</SQL>
101 
					</KeyExpression>
102 
					<NameExpression>
103 
						<SQL dialect="generic">
104 
							case when connected_locally &#60;= 5 then convert(connected_locally, char) when connected_locally &#62; 5 then &#39;more than 5 days&#39; else &#39;-&#39; end						</SQL>
105 
					</NameExpression>
106 
				</Level>
107 
			</Hierarchy>
108 
		</Dimension>
109 
		<Dimension type="StandardDimension" name="Reached Destination City">
110 
			<Hierarchy name="Reached Destination City" hasAll="true" allMemberName="All">
111 
				<Level name="Reached Destination City" table="orderfulfillment" column="reached_destination_city" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
112 
					<KeyExpression>
113 
						<SQL dialect="generic">
114 
							case when reached_destination_city &#60;= 5 then reached_destination_city when reached_destination_city &#62; 5 then 6 else 7 end						</SQL>
115 
					</KeyExpression>
116 
					<NameExpression>
117 
						<SQL dialect="generic">
118 
							case when reached_destination_city &#60;= 5 then convert(reached_destination_city, char) when reached_destination_city &#62; 5 then &#39;more than 5 days&#39; else &#39;-&#39; end						</SQL>
119 
					</NameExpression>
120 
				</Level>
121 
			</Hierarchy>
122 
		</Dimension>
123 
		<Dimension type="StandardDimension" name="First Delivery Attempt">
124 
			<Hierarchy name="First Delivery Attempt" hasAll="true" allMemberName="All">
125 
				<Level name="First Delivery Attempt" table="orderfulfillment" column="first_delivery_attempt" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
126 
					<KeyExpression>
127 
						<SQL dialect="generic">
128 
							case when first_delivery_attempt &#60;= 5 then first_delivery_attempt when first_delivery_attempt &#62; 5 then 6 else 7 end						</SQL>
129 
					</KeyExpression>
130 
					<NameExpression>
131 
						<SQL dialect="generic">
132 
							case when first_delivery_attempt &#60;= 5 then convert(first_delivery_attempt, char) when first_delivery_attempt &#62; 5 then &#39;more than 5 days&#39; else &#39;-&#39; end						</SQL>
133 
					</NameExpression>
134 
				</Level>
135 
			</Hierarchy>
136 
		</Dimension>
3706 vikas 137 
		<Dimension type="StandardDimension" name="Delivery Difference">
138 
			<Hierarchy name="Delivery Difference" hasAll="true" allMemberName="All">
4166 vikas 139 
				<Level name="Delivery Difference" table="orderfulfillment" column="actual_estimate_diff" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
3706 vikas 140 
					<KeyExpression>
141 
						<SQL dialect="generic">
142 
							case when actual_estimate_diff &#60;0 then -1 when actual_estimate_diff =0 then 0 when actual_estimate_diff&#60;5 then actual_estimate_diff when actual_estimate_diff&#62;=5 then 6 else 7 end						</SQL>
143 
					</KeyExpression>
144 
					<NameExpression>
145 
						<SQL dialect="generic">
4166 vikas 146 
							case when actual_estimate_diff &#60;0 then &#39;Before Time&#39; when actual_estimate_diff =0 then &#39;On Time&#39; when actual_estimate_diff&#60;5 then concat(&#39;Delayed by &#39;, convert(actual_estimate_diff, char), &#39; days&#39;) when actual_estimate_diff&#62;=5 then &#39;Delayed by 5 or more days&#39; else &#39;-&#39; end						</SQL>
3706 vikas 147 
					</NameExpression>
148 
				</Level>
149 
			</Hierarchy>
150 
		</Dimension>
151 
		<Dimension type="StandardDimension" name="Logistics Provider">
152 
			<Hierarchy name="Logistics Provider" hasAll="true" allMemberName="All">
153 
				<Level name="Logistics Provider" table="orderfulfillment" column="logistics_provider_id" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
154 
					<NameExpression>
155 
						<SQL dialect="generic">
5157 anupam.sin 156 
							case when logistics_provider_id=1 then &#39;Blue Dart&#39; when logistics_provider_id=2 then &#39;Aramex&#39;  else &#39;N/A&#39; end						</SQL>
3706 vikas 157 
					</NameExpression>
158 
				</Level>
159 
			</Hierarchy>
160 
		</Dimension>
161 
		<Dimension type="StandardDimension" name="Delay Cause">
162 
			<Hierarchy name="Delay Cause" hasAll="true" allMemberName="All">
163 
				<Level name="Delay Cause" table="orderfulfillment" column="delay_reason" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
164 
				</Level>
165 
			</Hierarchy>
166 
		</Dimension>
3750 vikas 167 
		<Dimension type="StandardDimension" foreignKey="payment_method" name="Payment Mode">
168 
			<Hierarchy name="Payment Mode" hasAll="true" allMemberName="All" primaryKey="paymentmethodid">
4166 vikas 169 
				<Table name="paymentmethod">
3750 vikas 170 
				</Table>
171 
				<Level name="Payment Mode" column="gateway" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
172 
					<KeyExpression>
173 
						<SQL dialect="generic">
174 
							case when gateway in (&#39;ebs&#39;, &#39;hdfc&#39;, &#39;cheque&#39;)  then &#39;prepaid&#39; else gateway end						</SQL>
175 
					</KeyExpression>
176 
					<NameExpression>
177 
						<SQL dialect="generic">
178 
							case when gateway in (&#39;ebs&#39;, &#39;hdfc&#39;, &#39;cheque&#39;)  then &#39;prepaid&#39; else gateway end						</SQL>
179 
					</NameExpression>
180 
				</Level>
181 
			</Hierarchy>
182 
		</Dimension>
3706 vikas 183 
		<Measure name="Quantity" column="date_id" datatype="Integer" formatString="##,###" aggregator="count" visible="true">
184 
		</Measure>
185 
	</Cube>
186 
</Schema>