Subversion Repositories SmartDukaan

Rev

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

Rev Author Line No. Line
6554 anupam.sin 1 
<Schema name="CommonOrdersScema">
2 
	<Cube name="commonOrdersCube" cache="true" enabled="true">
3 
		<Table name="commonorders">
4 
		</Table>
5 
		<Dimension type="TimeDimension" foreignKey="dateId" name="OrderDate">
6 
			<Hierarchy name="OrderDate" hasAll="true" allMemberName="All" primaryKey="date_id">
7 
				<Table name="datedim">
8 
				</Table>
9 
				<Level approxRowCount="4" name="Year" column="year" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
10 
				</Level>
11 
				<Level approxRowCount="4" name="Quarter" column="quarter" type="String" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never">
12 
				</Level>
13 
				<Level approxRowCount="10" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
14 
				</Level>
15 
				<Level approxRowCount="25" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
16 
				</Level>
17 
			</Hierarchy>
18 
		</Dimension>
19 
		<Dimension type="TimeDimension" foreignKey="firstOrderDate" name="FirstOrderDate">
20 
			<Hierarchy name="FirstOrderDate" hasAll="true" allMemberName="All" primaryKey="date_id">
21 
				<Table name="datedim">
22 
				</Table>
23 
				<Level approxRowCount="4" 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="10" name="Month" column="monthnumber" nameColumn="monthname" ordinalColumn="monthnumber" type="Integer" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
28 
				</Level>
29 
				<Level approxRowCount="25" name="Date" column="dayofmonth" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
30 
				</Level>
31 
			</Hierarchy>
32 
		</Dimension>
33 
		<Dimension type="StandardDimension" foreignKey="pincode" name="Geography">
34 
			<Hierarchy name="Territory" hasAll="true" allMemberName="All" primaryKey="pincode">
35 
				<Table name="pincodesB">
36 
				</Table>
37 
				<Level approxRowCount="25" name="State" column="state" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
38 
				</Level>
39 
				<Level approxRowCount="10" name="City" column="city" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
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>
49 
				<Level name="isValid" column="isValid" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
50 
				</Level>
51 
				<Level name="StatusGroup" column="statusGroup" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
52 
				</Level>
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>
57 
			</Hierarchy>
58 
		</Dimension>
59 
		<Dimension type="StandardDimension" foreignKey="itemId" name="Product">
60 
			<Hierarchy name="Product Name" hasAll="true" allMemberName="All" primaryKey="id">
61 
				<Table name="item">
62 
				</Table>
63 
				<Level name="Brand" column="brand" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
64 
				</Level>
65 
				<Level name="Product" column="catalog_item_id" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
66 
					<KeyExpression>
67 
						<SQL dialect="generic">
68 
							concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))						</SQL>
69 
					</KeyExpression>
70 
					<NameExpression>
71 
						<SQL dialect="generic">
72 
							concat(IFNULL(model_name, &#39;&#39;), &#39; &#39;, IFNULL(model_number, &#39;&#39;))						</SQL>
73 
					</NameExpression>
74 
				</Level>
75 
				<Level name="Color" column="color" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
76 
				</Level>
77 
			</Hierarchy>
78 
		</Dimension>
79 
		<Dimension type="StandardDimension" foreignKey="itemId" name="Category">
80 
			<Hierarchy name="Category" hasAll="true" allMemberName="All" primaryKey="id">
81 
				<Table name="item">
82 
				</Table>
83 
				<Level name="ParentCategory" column="parent_category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
84 
				</Level>
85 
				<Level name="Category" column="category" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
86 
				</Level>
87 
				<Level name="ProductGroup" column="product_group" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
88 
				</Level>
89 
			</Hierarchy>
90 
		</Dimension>
91 
		<Dimension type="StandardDimension" foreignKey="amount" name="Price">
92 
			<Hierarchy name="Price Range" hasAll="true" allMemberName="All" primaryKey="id">
93 
				<Table name="pricerange">
94 
				</Table>
95 
				<Level name="Price Range" column="name" nameColumn="name" ordinalColumn="id" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
96 
				</Level>
97 
			</Hierarchy>
98 
		</Dimension>
99 
		<Dimension type="StandardDimension" foreignKey="repeatFrequency" name="Days since last purchase">
100 
			<Hierarchy name="Days since last purchase" hasAll="true" allMemberName="All" primaryKey="id">
101 
				<Table name="repeatfrequency">
102 
				</Table>
103 
				<Level name="Days since last purchase" column="id" nameColumn="name" ordinalColumn="id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
104 
				</Level>
105 
			</Hierarchy>
106 
		</Dimension>
107 
		<Dimension type="StandardDimension" foreignKey="orderType" name="OrderType">
108 
			<Hierarchy name="OrderType" hasAll="true" allMemberName="All">
109 
				<Level name="OrderType" table="commonorders" column="orderType" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
110 
					<KeyExpression>
111 
						<SQL dialect="generic">
112 
							case when orderType = 0 then &#39;Physical&#39;  else &#39;Recharge&#39; end						</SQL>
113 
					</KeyExpression>
114 
					<NameExpression>
115 
						<SQL dialect="generic">
116 
							case when orderType = 0 then &#39;Physical&#39;  else &#39;Recharge&#39; end						</SQL>
117 
					</NameExpression>
118 
				</Level>
119 
			</Hierarchy>
120 
		</Dimension>
121 
		<Dimension type="StandardDimension" foreignKey="firstOrderType" name="FirstOrderType">
122 
			<Hierarchy name="FirstOrderType" hasAll="true" allMemberName="All">
123 
				<Level name="OrderType" table="commonorders" column="firstOrderType" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
124 
					<KeyExpression>
125 
						<SQL dialect="generic">
126 
							case when firstOrderType = 0 then &#39;Physical&#39;  else &#39;Recharge&#39; end						</SQL>
127 
					</KeyExpression>
128 
					<NameExpression>
129 
						<SQL dialect="generic">
130 
							case when firstOrderType = 0 then &#39;Physical&#39;  else &#39;Recharge&#39; end						</SQL>
131 
					</NameExpression>
132 
				</Level>
133 
			</Hierarchy>
134 
		</Dimension>
135 
		<Dimension type="StandardDimension" foreignKey="repeatType" name="Repeat type">
136 
			<Hierarchy name="Repeat type" hasAll="true" allMemberName="All" primaryKey="id">
137 
				<Table name="repeattype">
138 
				</Table>
139 
				<Level name="Repeat type" column="id" nameColumn="name" ordinalColumn="id" type="Integer" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
140 
				</Level>
141 
			</Hierarchy>
142 
		</Dimension>
7203 anupam.sin 143 
		<Dimension type="StandardDimension" name="isRepeat">
144 
			<Hierarchy name="isRepeat" hasAll="true" allMemberName="All">
145 
				<Level name="isRepeat" table="commonorders" column="isRepeatUser" type="String" uniqueMembers="true" levelType="Regular" hideMemberIf="Never">
146 
					<KeyExpression>
147 
						<SQL dialect="generic">
148 
							case when isRepeatUser = 1 then &#39;Repeat&#39; else &#39;One-Time&#39; end						</SQL>
149 
					</KeyExpression>
150 
					<NameExpression>
151 
						<SQL dialect="generic">
152 
							case when isRepeatUser = 1 then &#39;Repeat&#39; else &#39;One-Time&#39; end						</SQL>
153 
					</NameExpression>
154 
				</Level>
155 
			</Hierarchy>
156 
		</Dimension>
6554 anupam.sin 157 
		<Measure name="Quantity" column="quantity" datatype="Numeric" formatString="###,###" aggregator="sum" visible="true">
158 
		</Measure>
159 
		<Measure name="transactionCount" column="transactionId" datatype="Integer" formatString="###,###.###" aggregator="distinct-count" visible="true">
160 
		</Measure>
161 
		<Measure name="userCount" column="userId" datatype="Integer" formatString="###,###.###" aggregator="distinct-count" visible="true">
162 
		</Measure>
163 
	</Cube>
164 
</Schema>