← Back to branch summary

~xrg/openobject-doc/trunk-xrg

« back to all changes in this revision

Viewing changes to source/bi/CubeXML/CubeXMLExample.rst

  • Committer: TruongSinh Tran
  • Date: 2009-07-17 18:59:45 UTC
  • Revision ID: truongsinh@vipescoserver-20090717185945-ajjp3zso6xh5jddm
[FIX]private issue

Show diffs side-by-side

added added

removed removed

Lines of Context:
source/bi/CubeXML/CubeXMLExample.rst
 
1
Creating Cube Definition using XML file
 
2
=======================================
 
3
 
 
4
.. describe:: Things to know
 
5
 
 
6
Before going through XML details its good to have an idea of all :ref:`Terminolgies <terminologies-link>`  of OLAP.
 
7
 
 
8
**Lets understand XML file in details**
 
9
 
 
10
 
 
11
**Step: 1. The first step is to specify the database it will use with parameter like name, database name , database login and database password**
 
12
 
 
13
.. code-block:: xml
 
14
 
 
15
   <record model="olap.fact.database" id="fact_databases_BI">
 
16
        <field name="name">Tiny ERP databases</field>
 
17
        <field name="db_name">Sales</field>
 
18
        <field name="db_login">postgres</field>
 
19
        <field name="db_password">postgres</field>
 
20
   </record>
 
21
 
 
22
* This will create the connection parameters needed for connecting to the database.
 
23
 
 
24
**Step: 2. Defining Schema**
 
25
 
 
26
.. code-block:: xml
 
27
 
 
28
   <record model="olap.schema" id="schema_main_sales">
 
29
        <field name="name">tinysales</field>
 
30
        <field name="state">none</field>
 
31
        <field name="database_id" ref="fact_databases_BI"/>
 
32
   </record>
 
33
 
 
34
* This will create schema name tinysales for fact_database_BI made in step 1
 
35
 
 
36
 
 
37
**Step: 3. Defining fact table to be used (In this case, sale_order_line)**
 
38
 
 
39
.. code-block:: xml
 
40
 
 
41
   <record model="olap.cube.table" id="table_sales_order_line">
 
42
        <field name="name">sale_order_line</field>
 
43
                <field name='schema_id' ref='schema_main_sales'/>
 
44
   </record>
 
45
 
 
46
**Step: 4. Making Cube on fact_table**
 
47
 
 
48
.. code-block:: xml
 
49
 
 
50
   <record model="olap.cube" id="cube_sales_order_line">
 
51
        <field name="name">sale_order_line</field>
 
52
        <field name="table_id" ref="table_sales_order_line"/>
 
53
        <field name="schema_id" ref="schema_main_sales"/>
 
54
   </record>
 
55
 
 
56
* This will create cube name sale_order_line 
 
57
 
 
58
**Step: 5. Creating Dimension product**
 
59
 
 
60
* This will used to fetch and make MDX Query on all the product 
 
61
 
 
62
.. code-block:: xml
 
63
 
 
64
   <record model="olap.dimension" id="dimension_product_template">
 
65
        <field name="name">Products</field>
 
66
        <field name="cube_id" ref="cube_sales_order_line"/>
 
67
  </record>
 
68
 
 
69
   <record model="olap.cube.table" id="table_product_template">
 
70
        <field name="name">product_product</field>
 
71
   </record>
 
72
 
 
73
**Step: 5a. Creating Hierarchy for the Dimension Product**
 
74
 
 
75
.. code-block:: xml
 
76
 
 
77
   <record model="olap.hierarchy" id="hierarchy_product_template">
 
78
        <field name="name">All Products</field>
 
79
        <field name="dimension_id" ref="dimension_product_template"/>
 
80
        <field name="primary_key_table">product_product</field>
 
81
        <field name="table_id" ref="table_product_template"/>
 
82
  </record>
 
83
 
 
84
**Step: 5b  Creating Level for the Dimension Product**
 
85
 
 
86
First,We Create Column.
 
87
 
 
88
.. code-block:: xml
 
89
 
 
90
        <record model="olap.database.columns" id="columns_product_product_default_code">
 
91
                <field name="name">default_code</field>
 
92
                <field name="column_db_name">default_code</field>
 
93
                <field name="type">varchar</field>
 
94
                <field name="table_id" ref="table_product_template>
 
95
                <field name="active">True</field>
 
96
        </record>
 
97
 
 
98
Now,Level.
 
99
 
 
100
.. code-block:: xml
 
101
 
 
102
    <record model="olap.level" id="level_product_template">
 
103
        <field name="name">default_code</field>
 
104
        <field name="column_name" ref="columns_product_product_default_code"></field>
 
105
        <field name="hierarchy_id" ref="hierarchy_product_template"/>
 
106
        <field name="table_name">res_partner</field>
 
107
        <field name="column_id_name">name</field>
 
108
   </record>
 
109
 
 
110
**Step: 6  Creating Dimension date_order up to the quarters**
 
111
 
 
112
.. code-block:: xml
 
113
 
 
114
    <record model="olap.dimension" id="dimension_sales_order">
 
115
        <field name="name">Order Date</field>
 
116
        <field name="cube_id" ref="cube_sales_order_line"/>
 
117
     </record>
 
118
     
 
119
     <record model="olap.cube.table" id="table_sales_order">
 
120
        <field name="name">sale_order</field>
 
121
     </record>
 
122
     
 
123
     <record model="olap.hierarchy" id="hierarchy_sales_order">
 
124
        <field name="name">Order Date</field>
 
125
        <field name="dimension_id" ref="dimension_sales_order"/>
 
126
        <field name="primary_key_table">sale_order</field>
 
127
        <field name="table_id" ref="table_sales_order"/>
 
128
     </record>
 
129
     
 
130
     <record model="olap.database.columns" id="columns_sale_order_date_order">
 
131
        <field name="name">date_order</field>
 
132
        <field name="column_db_name">date_order</field>
 
133
        <field name="type">date</field>
 
134
        <field name="table_id" ref="table_sale_order"/>
 
135
        <field name="active">True</field>
 
136
      </record>
 
137
 
 
138
Making levels in Order Date so to get details as per year,quarters and months. 
 
139
 
 
140
.. code-block:: xml
 
141
 
 
142
     <record model="olap.level" id="level_sales_order">
 
143
        <field name="name">date_order</field>
 
144
        <field name="column_name" ref="columns_sale_order_date_order"></field>
 
145
        <field name="column_id_name">date_order</field>
 
146
        <field name="type">date_year</field>
 
147
        <field name="sequence">1</field>
 
148
        <field name="table_name">sale_order</field>
 
149
        <field name="hierarchy_id" ref="hierarchy_sales_order"/>
 
150
     </record>
 
151
 
 
152
     <record model="olap.level" id="level_sales_order_q">
 
153
        <field name="name">date_order</field>
 
154
        <field name="column_name" ref="columns_sale_order_date_order"></field>
 
155
        <field name="column_id_name">date_order</field>
 
156
        <field name="type">date_quarter</field>
 
157
        <field name="sequence">2</field>
 
158
        <field name="table_name">sale_order</field>
 
159
        <field name="hierarchy_id" ref="hierarchy_sales_order"/>
 
160
     </record>
 
161
     <record model="olap.level" id="level_sales_order_m">
 
162
        <field name="name">date_order</field>
 
163
        <field name="column_name" ref="columns_sale_order_date_order"></field>
 
164
        <field name="column_id_name">date_order</field>
 
165
        <field name="type">date_month</field>
 
166
        <field name="sequence">3</field>
 
167
        <field name="table_name">sale_order</field>
 
168
        <field name="hierarchy_id" ref="hierarchy_sales_order"/>
 
169
     </record>
 
170
 
 
171
**Step: 7  Creating Dimension res_country**
 
172
 
 
173
.. code-block:: xml
 
174
 
 
175
      <record model="olap.cube.table" id="table_sale_order">
 
176
        <field name="name">sale_order</field>
 
177
                <field name='schema_id' ref='schema_main_sales'/>
 
178
     </record>
 
179
     
 
180
     <record model="olap.cube.table" id="table_partner_address_0">
 
181
        <field name="name">res_partner_address</field>
 
182
        <field name='schema_id' ref='schema_main_sales'/>
 
183
     </record>
 
184
     <record model="olap.cube.table" id="table_partner_address_1">
 
185
        <field name="name">res_country</field>
 
186
        <field name='schema_id' ref='schema_main_sales'/>
 
187
     </record>
 
188
 
 
189
     <record model="olap.cube.table" id="table_partner_address">
 
190
        <field name="name">res_partner_address</field>
 
191
        <field name='schema_id' ref='schema_main_sales'/>
 
192
     </record>
 
193
 
 
194
     <record model="olap.cube.table" id="table_partner_country">
 
195
        <field name="name">sale_order_country</field>
 
196
        <field name='schema_id' ref='schema_main_sales'/>
 
197
     </record>
 
198
 
 
199
     <record model="olap.dimension" id="dimension_partner_country">
 
200
        <field name="name">Sales From Partners</field>
 
201
        <field name="cube_id" ref="cube_sales_order_line"/>
 
202
     </record>
 
203
 
 
204
     <record model="olap.hierarchy" id="hierarchy_partner_country">
 
205
        <field name="name">partner_country</field>
 
206
        <field name="dimension_id" ref="dimension_partner_country"/>
 
207
        <field name="primary_key_table">sale_order</field>
 
208
        <field name="table_id" ref="table_partner_country"/>
 
209
     </record>
 
210
 
 
211
     <record model="olap.level" id="level_partner_country">
 
212
        <field name="name">country_id</field>
 
213
        <field name="column_name" ref="columns_sale_order_date_order"></field>
 
214
        <field name="column_id_name">name</field>
 
215
        <field name="table_name">res_country</field>
 
216
        <field name="hierarchy_id" ref="hierarchy_partner_country"/>
 
217
     </record>
 
218
 
 
219
**Step: 8  Creating Dimension res_parnter_address**
 
220
 
 
221
.. code-block:: xml
 
222
 
 
223
      <record model="olap.database.columns" id="columns_res_partner_address">
 
224
                <field name="name">name</field>
 
225
                <field name="column_db_name">name</field>
 
226
                <field name="type">varchar</field>
 
227
                <field name="table_id" ref="table_sales_order"/>
 
228
                <field name="active">True</field>
 
229
    </record>
 
230
     <record model="olap.cube.table" id="table_address">
 
231
        <field name="name">res_partner_address</field>
 
232
        <field name='schema_id' ref='schema_main_sales'/>
 
233
     </record>
 
234
 
 
235
     <record model="olap.cube.table" id="table_address_country">
 
236
        <field name="name">sale_order_country</field>
 
237
        <field name='schema_id' ref='schema_main_sales'/>
 
238
     </record>
 
239
 
 
240
     <record model="olap.dimension" id="dimension_partner_address_country">
 
241
        <field name="name">Sales by Order Address</field>
 
242
        <field name="cube_id" ref="cube_sales_order_line"/>
 
243
     </record>
 
244
 
 
245
     <record model="olap.hierarchy" id="hierarchy_partner_address_country">
 
246
        <field name="name">address_country</field>
 
247
        <field name="dimension_id" ref="dimension_partner_address_country"/>
 
248
        <field name="primary_key_table">sale_order</field>
 
249
        <field name="table_id" ref="table_address_country"/>
 
250
     </record>
 
251
 
 
252
     <record model="olap.level" id="level_address_country">
 
253
        <field name="name">country_id</field>
 
254
        <field name="sequence">1</field>
 
255
        <field name="column_name" ref="columns_res_partner_address"></field>
 
256
        <field name="column_id_name">country_id</field>
 
257
        <field name="table_name">res_partner_address</field>
 
258
        <field name="hierarchy_id" ref="hierarchy_partner_address_country"/>
 
259
     </record>
 
260
 
 
261
     <record model="olap.level" id="level_address_partner">
 
262
        <field name="name">partner_id</field>
 
263
        <field name="sequence">2</field>
 
264
        <field name="column_name" ref="columns_res_partner_address"></field>
 
265
        <field name="column_id_name">partner_id</field>
 
266
        <field name="table_name">res_partner_address</field>
 
267
        <field name="hierarchy_id" ref="hierarchy_partner_address_country"/>
 
268
      </record>
 
269
 
 
270
 
 
271
**Step: 9  Creating Dimension res_user**
 
272
 
 
273
.. code-block:: xml
 
274
 
 
275
      <record model="olap.database.columns" id="columns_res_user_name">
 
276
        <field name="name">name</field>
 
277
        <field name="column_db_name">name</field>
 
278
        <field name="type">varchar</field>
 
279
        <field name="table_id" ref="table_sales_order"/>
 
280
        <field name="active">True</field>
 
281
     </record>
 
282
     
 
283
     <record model="olap.dimension" id="dimension_sales_user">
 
284
        <field name="name">user</field>
 
285
        <field name="cube_id" ref="cube_sales_order_line"/>
 
286
     </record>
 
287
     
 
288
     <record model="olap.cube.table" id="table_sales_res_users">
 
289
        <field name="name">res_users</field>
 
290
     </record>
 
291
     
 
292
     <record model="olap.hierarchy" id="hierarchy_sales_user">
 
293
        <field name="name">user</field>
 
294
        <field name="dimension_id" ref="dimension_sales_user"/>
 
295
        <field name="primary_key_table">res_users</field>
 
296
        <field name="table_id" ref="table_sales_res_users"/>
 
297
     </record>
 
298
     
 
299
     <record model="olap.level" id="hierarchy_sales_user_level">
 
300
        <field name="name">name</field>
 
301
        <field name="column_name" ref="columns_res_user_name""></field>
 
302
        <field name="hierarchy_id" ref="hierarchy_sales_user"/>
 
303
     </record>
 
304
 
 
305
**Step: 10  Creating Measures Item Sold and Total Sold**
 
306
 
 
307
.. code-block:: xml
 
308
 
 
309
    <record model="olap.database.columns" id="columns_sale_order_line_product_uom_qty">
 
310
                <field name="name">product_uom_qty</field>
 
311
                <field name="column_db_name">product_uom_qty</field>
 
312
                <field name="type">numeric</field>
 
313
                <field name="table_id" ref="table_sale_order_line"/>
 
314
                <field name="active">True</field>
 
315
    </record>
 
316
        
 
317
   <record model="olap.measure" id="measure_item_sold">
 
318
        <field name="name">Items Sold</field>
 
319
        <field name="cube_id" ref="cube_sales_order_line"/>
 
320
        <field name="value_column" ref="columns_sale_order_line_product_uom_qty"></field>
 
321
        <field name="value_column_id_name">product_uom_qty</field>
 
322
        <field name="table_name">sale_order_line</field>
 
323
        <field name="agregator">sum</field>
 
324
   </record>
 
325
   <record model="olap.measure" id="measure_total_sales">
 
326
        <field name="name">Total Sold</field>
 
327
        <field name="cube_id" ref="cube_sales_order_line"/>
 
328
        <field name="value_column" ref="columns_sale_order_line_price_unit"></field>
 
329
        <field name="value_column_id_name">price_unit</field>
 
330
        <field name="table_name">sale_order_line</field>
 
331
        <field name="agregator">sum</field>
 
332
  </record>
 
333