Subversion Repositories SmartDukaan

Rev

Rev 4033 | Rev 5200 | 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>
5161 anupam.sin 9 
				<Level approxRowCount="3" name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
2401 ankur.sing 10 
				</Level>
3542 vikas 11 
				<Level approxRowCount="4" name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
2401 ankur.sing 12 
				</Level>
3542 vikas 13 
				<Level approxRowCount="8" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
2401 ankur.sing 14 
				</Level>
3542 vikas 15 
				<Level approxRowCount="20" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
2401 ankur.sing 16 
				</Level>
17 
			</Hierarchy>
18 
		</Dimension>
5161 anupam.sin 19 
		<Dimension type="TimeDimension" foreignKey="booking_date_id" name="BookingDate">
20 
			<Hierarchy name="BookingDate" hasAll="true" allMemberName="All" primaryKey="date_id">
21 
				<Table name="datedim">
22 
				</Table>
23 
				<Level approxRowCount="3" name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
24 
				</Level>
25 
				<Level approxRowCount="4" name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
26 
				</Level>
27 
				<Level approxRowCount="8" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
28 
				</Level>
29 
				<Level approxRowCount="20" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
30 
				</Level>
31 
			</Hierarchy>
32 
		</Dimension>
2401 ankur.sing 33 
		<Dimension type="StandardDimension" foreignKey="pincode" name="Geography">
34 
			<Hierarchy name="Territory" hasAll="true" allMemberName="All" primaryKey="pincode">
35 
				<Table name="pincodesB">
36 
				</Table>
3542 vikas 37 
				<Level approxRowCount="25" name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2401 ankur.sing 38 
				</Level>
3542 vikas 39 
				<Level approxRowCount="10" name="City" column="city" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2401 ankur.sing 40 
				</Level>
41 
				<Level name="Pincode" column="pincode" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
42 
				</Level>
43 
			</Hierarchy>
44 
		</Dimension>
45 
		<Dimension type="StandardDimension" foreignKey="status" name="OrderStatus">
46 
			<Hierarchy name="StatusDim" hasAll="true" allMemberName="All" primaryKey="status">
47 
				<Table name="orderstatus">
48 
				</Table>
5161 anupam.sin 49 
				<Level name="isValid" column="isValid" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2401 ankur.sing 50 
				</Level>
5161 anupam.sin 51 
				<Level name="StatusGroup" column="statusGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2401 ankur.sing 52 
				</Level>
5161 anupam.sin 53 
				<Level name="StatusSubGroup" column="statusSubGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
54 
				</Level>
55 
				<Level name="StatusName" column="statusName" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
56 
				</Level>
2401 ankur.sing 57 
			</Hierarchy>
58 
		</Dimension>
59 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Product">
5161 anupam.sin 60 
			<Hierarchy name="Product Name" hasAll="true" allMemberName="All" primaryKey="id">
2401 ankur.sing 61 
				<Table name="item">
62 
				</Table>
63 
				<Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
64 
				</Level>
4029 vikas 65 
				<Level name="Product" column="id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
66 
					<NameExpression>
67 
						<SQL dialect="generic">
68 
							concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;), &#39;(&#39;, IFNULL(color, &#39;&#39;), &#39;)&#39;)						</SQL>
69 
					</NameExpression>
70 
				</Level>
71 
			</Hierarchy>
2401 ankur.sing 72 
		</Dimension>
2532 vikas 73 
		<Dimension type="StandardDimension" foreignKey="item_id" name="Category">
74 
			<Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
2553 vikas 75 
				<Table name="item">
2532 vikas 76 
				</Table>
77 
				<Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
78 
				</Level>
79 
				<Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
80 
				</Level>
2553 vikas 81 
				<Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
82 
				</Level>
2532 vikas 83 
			</Hierarchy>
84 
		</Dimension>
3481 vikas 85 
		<Dimension type="StandardDimension" foreignKey="price_range" name="Price">
86 
			<Hierarchy name="Price Range" hasAll="true" allMemberName="All" primaryKey="id">
3542 vikas 87 
				<Table name="pricerange">
3481 vikas 88 
				</Table>
4033 vikas 89 
				<Level name="Price Range" column="name" nameColumn="name" ordinalColumn="id" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2553 vikas 90 
				</Level>
91 
			</Hierarchy>
92 
		</Dimension>
2596 vikas 93 
		<Dimension type="StandardDimension" foreignKey="payment_method" name="Payment">
94 
			<Hierarchy name="Payment" hasAll="true" allMemberName="All" primaryKey="paymentmethodid">
2639 vikas 95 
				<Table name="paymentmethod">
2596 vikas 96 
				</Table>
3542 vikas 97 
				<Level approxRowCount="6" name="Gateway" column="gateway" nameColumn="gateway" ordinalColumn="gateway" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2596 vikas 98 
				</Level>
3542 vikas 99 
				<Level name="Payment Method" column="paymentmethodname" nameColumn="paymentmethodname" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2596 vikas 100 
				</Level>
101 
			</Hierarchy>
102 
		</Dimension>
3542 vikas 103 
		<Dimension type="StandardDimension" foreignKey="order_hour" name="Order Hour">
104 
			<Hierarchy name="Order Hour" hasAll="true" allMemberName="All" primaryKey="hour_id">
105 
				<Table name="orderhour">
106 
				</Table>
107 
				<Level name="Hour Range" column="hour_range" ordinalColumn="hour_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
2639 vikas 108 
				</Level>
3542 vikas 109 
				<Level name="Hour" column="hour_id" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
3393 vikas 110 
				</Level>
2639 vikas 111 
			</Hierarchy>
112 
		</Dimension>
2853 vikas 113 
		<Dimension type="StandardDimension" foreignKey="customer_id" name="First Source">
3707 vikas 114 
			<Hierarchy name="First Source" hasAll="true" allMemberName="All" primaryKey="id">
2834 vikas 115 
				<Table name="user">
2712 vikas 116 
				</Table>
3707 vikas 117 
				<Level name="Source" column="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
118 
					<KeyExpression>
119 
						<SQL dialect="generic">
4029 vikas 120 
							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>
3707 vikas 121 
					</KeyExpression>
122 
					<NameExpression>
123 
						<SQL dialect="generic">
4029 vikas 124 
							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>
3707 vikas 125 
					</NameExpression>
126 
				</Level>
3437 vikas 127 
				<Level name="Site" column="source_url" nameColumn="source_url" ordinalColumn="source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2712 vikas 128 
				</Level>
129 
			</Hierarchy>
130 
		</Dimension>
2834 vikas 131 
		<Dimension type="StandardDimension" name="Promotion">
132 
			<Hierarchy name="Promotion" hasAll="true" allMemberName="All">
133 
				<Level name="CouponCode" table="sales" column="coupon_code" nameColumn="coupon_code" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
134 
				</Level>
135 
			</Hierarchy>
136 
		</Dimension>
2853 vikas 137 
		<Dimension type="StandardDimension" name="Session Source">
138 
			<Hierarchy name="Session Source" hasAll="true" allMemberName="All">
3707 vikas 139 
				<Level name="Source" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
140 
					<KeyExpression>
141 
						<SQL dialect="generic">
4029 vikas 142 
							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>
3707 vikas 143 
					</KeyExpression>
144 
					<NameExpression>
145 
						<SQL dialect="generic">
4029 vikas 146 
							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>
3707 vikas 147 
					</NameExpression>
148 
				</Level>
3542 vikas 149 
				<Level name="Site" table="sales" column="session_source_url" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2853 vikas 150 
				</Level>
151 
			</Hierarchy>
152 
		</Dimension>
3393 vikas 153 
		<Dimension type="StandardDimension" name="Affiliate">
154 
			<Hierarchy name="Affiliate" hasAll="true" allMemberName="All">
155 
				<Level name="Affiliate" table="sales" column="aff_name" nameColumn="aff_name" ordinalColumn="aff_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2853 vikas 156 
				</Level>
157 
			</Hierarchy>
158 
		</Dimension>
3484 vikas 159 
		<Dimension type="StandardDimension" foreignKey="days_to_purchase" name="Days To Purchase">
160 
			<Hierarchy name="Days To Purchase" hasAll="true" allMemberName="All" primaryKey="id">
3542 vikas 161 
				<Table name="daystopurchaserange">
3484 vikas 162 
				</Table>
163 
				<Level name="Days To Purchase" column="id" nameColumn="name" ordinalColumn="id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
2853 vikas 164 
				</Level>
165 
			</Hierarchy>
166 
		</Dimension>
5161 anupam.sin 167 
		<Measure name="Quantity" column="quantity" datatype="Numeric" formatString="##,###" aggregator="sum" visible="true">
2401 ankur.sing 168 
		</Measure>
2596 vikas 169 
		<Measure name="Total Sales" column="total_amount" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
170 
		</Measure>
4029 vikas 171 
		<Measure name="Transfer Price" datatype="Numeric" formatString="##,##,###" aggregator="sum" visible="true">
172 
			<MeasureExpression>
173 
				<SQL dialect="generic">
174 
					transfer_price*quantity				</SQL>
175 
			</MeasureExpression>
2596 vikas 176 
		</Measure>
177 
		<CalculatedMember name="Profit" caption="Profit" formula="[Measures].[Total Sales]-[Measures].[Transfer Price]" dimension="Measures" visible="true">
2553 vikas 178 
		</CalculatedMember>
5161 anupam.sin 179 
		<CalculatedMember name="Average Selling Price" caption="Average Selling Price" formula="[Measures].[Total Sales]/[Measures].[Quantity]" dimension="Measures" visible="true">
180 
		</CalculatedMember>
2401 ankur.sing 181 
	</Cube>
182 
</Schema>