Subversion Repositories SmartDukaan

Rev

Rev 2886 | Rev 3393 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
2401 ankur.sing 1 
<Schema name="SalesSchema">
2 
	<Cube name="SalesCube" cache="true" enabled="true">
2596 vikas 3 
		<Table name="sales">
2401 ankur.sing 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>
2553 vikas 11 
				<Level name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
2401 ankur.sing 12 
				</Level>
2553 vikas 13 
				<Level name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
2401 ankur.sing 14 
				</Level>
2553 vikas 15 
				<Level name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
2401 ankur.sing 16 
				</Level>
17 
			</Hierarchy>
18 
		</Dimension>
19 
		<Dimension type="StandardDimension" foreignKey="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>
35 
				<Level name="IsValid" column="isvalid" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
36 
				</Level>
37 
				<Level name="Status" column="description" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
38 
				</Level>
39 
			</Hierarchy>
40 
		</Dimension>
41 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Product">
42 
			<Hierarchy name="Product" hasAll="true" allMemberName="All" primaryKey="id">
43 
				<Table name="item">
44 
				</Table>
45 
				<Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
46 
				</Level>
47 
				<Level name="ModelNumber" column="model_number" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
48 
				</Level>
2553 vikas 49 
				<Level name="Color" column="color" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2401 ankur.sing 50 
				</Level>
51 
			</Hierarchy>
52 
		</Dimension>
2532 vikas 53 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Category">
54 
			<Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
2553 vikas 55 
				<Table name="item">
2532 vikas 56 
				</Table>
57 
				<Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
58 
				</Level>
59 
				<Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
60 
				</Level>
2553 vikas 61 
				<Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
62 
				</Level>
2532 vikas 63 
			</Hierarchy>
64 
		</Dimension>
2553 vikas 65 
		<Dimension type="StandardDimension" name="Price">
66 
			<Hierarchy name="Price" hasAll="true" allMemberName="All">
2596 vikas 67 
				<Level name="Price" table="sales" column="unit_price" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2553 vikas 68 
					<KeyExpression>
69 
						<SQL dialect="generic">
2596 vikas 70 
							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>
2553 vikas 71 
					</KeyExpression>
72 
					<NameExpression>
73 
						<SQL dialect="generic">
2596 vikas 74 
							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>
2553 vikas 75 
					</NameExpression>
76 
					<OrdinalExpression>
77 
						<SQL dialect="generic">
2596 vikas 78 
							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>
2553 vikas 79 
					</OrdinalExpression>
80 
				</Level>
81 
			</Hierarchy>
82 
		</Dimension>
2596 vikas 83 
		<Dimension type="StandardDimension" foreignKey="payment_method" name="Payment">
84 
			<Hierarchy name="Payment" hasAll="true" allMemberName="All" primaryKey="paymentmethodid">
2639 vikas 85 
				<Table name="paymentmethod">
2596 vikas 86 
				</Table>
87 
				<Level name="Gateway" column="gateway" nameColumn="gateway" ordinalColumn="gateway" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
88 
				</Level>
89 
				<Level name="Payment Method" column="paymentmethodname" nameColumn="paymentmethodname" ordinalColumn="paymentmethodname" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
90 
				</Level>
91 
			</Hierarchy>
92 
		</Dimension>
2639 vikas 93 
		<Dimension type="StandardDimension" name="Order Hour">
94 
			<Hierarchy name="Order Hour" hasAll="true" allMemberName="All">
95 
				<Level name="Order Hour" table="sales" column="order_hour" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
96 
					<KeyExpression>
97 
						<SQL dialect="generic">
98 
							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>
99 
					</KeyExpression>
100 
					<NameExpression>
101 
						<SQL dialect="generic">
102 
							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>
103 
					</NameExpression>
104 
					<OrdinalExpression>
105 
						<SQL dialect="generic">
106 
							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>
107 
					</OrdinalExpression>
108 
				</Level>
109 
			</Hierarchy>
110 
		</Dimension>
2853 vikas 111 
		<Dimension type="StandardDimension" foreignKey="customer_id" name="First Source">
2801 vikas 112 
			<Hierarchy name="Source" hasAll="true" allMemberName="All" primaryKey="id">
2834 vikas 113 
				<Table name="user">
2712 vikas 114 
				</Table>
2801 vikas 115 
				<Level name="Source" column="source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
116 
					<KeyExpression>
117 
						<SQL dialect="generic">
118 
							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; else &#39;Referer&#39; end						</SQL>
119 
					</KeyExpression>
2712 vikas 120 
					<NameExpression>
121 
						<SQL dialect="generic">
2801 vikas 122 
							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; else &#39;Referer&#39; end						</SQL>
2712 vikas 123 
					</NameExpression>
2801 vikas 124 
					<OrdinalExpression>
125 
						<SQL dialect="generic">
126 
							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; else &#39;Referer&#39; end						</SQL>
127 
					</OrdinalExpression>
2712 vikas 128 
				</Level>
2801 vikas 129 
				<Level name="Site" column="source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
130 
					<KeyExpression>
131 
						<SQL dialect="generic">
132 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
133 
					</KeyExpression>
134 
					<NameExpression>
135 
						<SQL dialect="generic">
136 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
137 
					</NameExpression>
138 
					<OrdinalExpression>
139 
						<SQL dialect="generic">
140 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
141 
					</OrdinalExpression>
2712 vikas 142 
				</Level>
143 
			</Hierarchy>
144 
		</Dimension>
2834 vikas 145 
		<Dimension type="StandardDimension" name="Promotion">
146 
			<Hierarchy name="Promotion" hasAll="true" allMemberName="All">
147 
				<Level name="CouponCode" table="sales" column="coupon_code" nameColumn="coupon_code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
148 
				</Level>
149 
			</Hierarchy>
150 
		</Dimension>
2853 vikas 151 
		<Dimension type="StandardDimension" name="Session Source">
152 
			<Hierarchy name="Session Source" hasAll="true" allMemberName="All">
153 
				<Level name="Source" table="sales" column="session_source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
154 
					<KeyExpression>
155 
						<SQL dialect="generic">
156 
							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; else &#39;Referer&#39; end						</SQL>
157 
					</KeyExpression>
158 
					<NameExpression>
159 
						<SQL dialect="generic">
160 
							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; else &#39;Referer&#39; end						</SQL>
161 
					</NameExpression>
162 
					<OrdinalExpression>
163 
						<SQL dialect="generic">
164 
							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; else &#39;Referer&#39; end						</SQL>
165 
					</OrdinalExpression>
166 
				</Level>
167 
				<Level name="Site" table="sales" column="session_source" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
168 
					<KeyExpression>
169 
						<SQL dialect="generic">
170 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
171 
					</KeyExpression>
172 
					<NameExpression>
173 
						<SQL dialect="generic">
174 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
175 
					</NameExpression>
176 
					<OrdinalExpression>
177 
						<SQL dialect="generic">
178 
							SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(session_source,&#39;://&#39;,-1),&#39;/&#39;,1),&#39;www.&#39;,-1)						</SQL>
179 
					</OrdinalExpression>
180 
				</Level>
181 
			</Hierarchy>
182 
		</Dimension>
183 
		<Dimension type="TimeDimension" foreignKey="customer_id" name="First Source TIme">
184 
			<Hierarchy name="First Source TIme" hasAll="true" allMemberName="All" primaryKey="id" primaryKeyTable="user">
185 
				<Join leftKey="source_start_date_id" rightKey="date_id">
186 
					<Table name="user">
187 
					</Table>
2886 vikas 188 
					<Table name="datedim" alias="datedim2">
2853 vikas 189 
					</Table>
190 
				</Join>
2886 vikas 191 
				<Level name="Year" table="datedim2" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
2853 vikas 192 
				</Level>
2886 vikas 193 
				<Level name="Quarter" table="datedim2" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
2853 vikas 194 
				</Level>
2886 vikas 195 
				<Level name="Month" table="datedim2" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
2853 vikas 196 
				</Level>
2886 vikas 197 
				<Level name="Date" table="datedim2" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
2853 vikas 198 
				</Level>
199 
			</Hierarchy>
200 
		</Dimension>
201 
		<Dimension type="TimeDimension" foreignKey="session_start_date_id" name="Session Time">
202 
			<Hierarchy name="Session Time" hasAll="true" allMemberName="All" primaryKey="date_id">
2886 vikas 203 
				<Table name="datedim" alias="datedim3">
2853 vikas 204 
				</Table>
205 
				<Level name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
206 
				</Level>
207 
				<Level name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
208 
				</Level>
2886 vikas 209 
				<Level name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
2853 vikas 210 
				</Level>
2856 vikas 211 
				<Level name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
2853 vikas 212 
				</Level>
213 
			</Hierarchy>
214 
		</Dimension>
3222 vikas 215 
		<Dimension type="StandardDimension" name="Affiliate">
216 
			<Hierarchy name="Affiliate" hasAll="true" allMemberName="All">
217 
				<Level name="Affiliate" table="sales" column="aff_name" nameColumn="aff_name" ordinalColumn="aff_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
218 
				</Level>
219 
			</Hierarchy>
220 
		</Dimension>
2401 ankur.sing 221 
		<Measure name="Quantity" column="quantity" datatype="Integer" formatString="##,###" aggregator="sum" visible="true">
222 
		</Measure>
2596 vikas 223 
		<Measure name="Average Selling Price" column="total_amount" datatype="Numeric" formatString="##,###" aggregator="avg" visible="true">
2517 vikas 224 
		</Measure>
2596 vikas 225 
		<Measure name="Total Sales" column="total_amount" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
226 
		</Measure>
227 
		<Measure name="Transfer Price" column="transfer_price" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
228 
		</Measure>
229 
		<CalculatedMember name="Profit" caption="Profit" formula="[Measures].[Total Sales]-[Measures].[Transfer Price]" dimension="Measures" visible="true">
2553 vikas 230 
		</CalculatedMember>
2401 ankur.sing 231 
	</Cube>
232 
</Schema>