<?xml version="1.0"?>
<openerp>
<data>

<!--
CUSTOM CUBE FOR CRM
-->

  <record model="olap.fact.database" id="fact_databases_BI">
    <field name="name">OpenERP CRM Database</field>
    <field name="db_name">crm</field>
    <field name="db_login">postgres</field>
    <field name="db_password">postgres</field>
  </record>
<!--
Schema
-->
  <record model="olap.schema" id="schema_crm">
    <field name="name">OpenERPCRM</field>
    <field name="state">none</field>
    <field name="database_id" ref="fact_databases_BI"/>
  </record>

<!--
Adding the database tables and database columns to be used and making up the of the
cube table and cube table line
-->


<!-- Olap Database Tables -->
    <record model="olap.database.tables" id="table_crm_case">
        <field name="name">crm_case</field>
        <field name="table_db_name">crm_case</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>

    <record model="olap.database.tables" id="table_crm_case_categ">
        <field name="name">crm_case_categ</field>
        <field name="table_db_name">crm_case_categ</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>

    <record model="olap.database.tables" id="table_crm_case_stage">
        <field name="name">crm_case_stage</field>
        <field name="table_db_name">crm_case_stage</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>

    <record model="olap.database.tables" id="table_res_partner">
        <field name="name">res_partner</field>
        <field name="table_db_name">res_partner</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>

     <record model="olap.database.tables" id="table_crm_case_section">
        <field name="name">crm_case_section</field>
        <field name="table_db_name">crm_case_section</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>
    <record model="olap.database.tables" id="table_res_partner_canal">
        <field name="name">res_partner_canal</field>
        <field name="table_db_name">res_partner_canal</field>
        <field name="fact_database_id" ref="fact_databases_BI"/>
        <field name="active">True</field>
    </record>

<!-- Olap Database Columns -->


    <record model="olap.database.columns" id="crm_case_id">
        <field name="name">id</field>
        <field name="column_db_name">id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="primary_key">True</field>
        <field name="active">True</field>
    </record>
    <record model="olap.database.columns" id="crm_case_planned_revenue">
        <field name="name">planned_revenue</field>
        <field name="column_db_name">planned_revenue</field>
        <field name="type">float8</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="active">True</field>
    </record>
    <record model="olap.database.columns" id="crm_case_partner_id">
        <field name="name">parnter_id</field>
        <field name="column_db_name">partner_id</field>
        <field name="type">float8</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="active">True</field>
        <field name="related_to" ref="table_res_partner"/>
    </record>

    <record model="olap.database.columns" id="crm_case_planned_cost">
        <field name="name">id</field>
        <field name="column_db_name">id</field>
        <field name="type">float8</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="active">True</field>
    </record>
    <record model="olap.database.columns" id="crm_case_probability">
        <field name="name">probability</field>
        <field name="column_db_name">probability</field>
        <field name="type">float8</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="active">True</field>
    </record>
    <record model="olap.database.columns" id="crm_case_state">
        <field name="name">state</field>
        <field name="column_db_name">state</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="active">True</field>
    </record>

    <record model="olap.database.columns" id="res_partner_canal_name">
        <field name="name">name</field>
        <field name="column_db_name">name</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_res_partner_canal"/>
        <field name="active">True</field>
    </record>





    <record model="olap.database.columns" id="crm_case_categ_id">
        <field name="name">categ_id</field>
        <field name="column_db_name">categ_id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="related_to" ref="table_crm_case_categ"/>
    </record>

    <record model="olap.database.columns" id="crm_case_partner_id">
        <field name="name">partner_id</field>
        <field name="column_db_name">partner_id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="related_to" ref="table_res_partner"/>
    </record>

    <record model="olap.database.columns" id="crm_case_canal_id">
        <field name="name">canal_id</field>
        <field name="column_db_name">canal_id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="related_to" ref="table_res_partner_canal"/>
    </record>


    <record model="olap.database.columns" id="res_partner_name">
        <field name="name">name</field>
        <field name="column_db_name">name</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_res_partner"/>
    </record>

    <record model="olap.database.columns" id="crm_case_section_name">
        <field name="name">name</field>
        <field name="column_db_name">name</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_crm_case_section"/>
    </record>
    <record model="olap.database.columns" id="crm_case_stage_name">
        <field name="name">name</field>
        <field name="column_db_name">name</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_crm_case_stage"/>
    </record>

    <record model="olap.database.columns" id="crm_case_stage_id">
        <field name="name">stage_id</field>
        <field name="column_db_name">stage_id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case"/>
        <field name="related_to" ref="table_crm_case_stage"/>
    </record>

    <record model="olap.database.columns" id="crm_case_stage_section_id">
        <field name="name">section_id</field>
        <field name="column_db_name">section_id</field>
        <field name="type">int4</field>
        <field name="table_id" ref="table_crm_case_stage"/>
        <field name="related_to" ref="table_crm_case_section"/>
    </record>

    <record model="olap.database.columns" id="crm_case_categ_name">
        <field name="name">name</field>
        <field name="column_db_name">name</field>
        <field name="type">varchar</field>
        <field name="table_id" ref="table_crm_case_categ"/>
    </record>



<!-- Cube-->

    <!-- Cube Table -->
    <record model="olap.cube.table" id="crm_cube_table">
        <field name="name">crm_cube</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_id'/>
    </record>


    <record model="olap.cube.table" id="crm_cube_section_table">
        <field name="name">crm_cube_section</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_stage_id'/>
    </record>

    <record model="olap.cube.table" id="crm_cube_stage_table">
        <field name="name">crm_cube_stage_parnter</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_stage_id'/>
    </record>
    <record model="olap.cube.table" id="crm_cube_parnter_table">
        <field name="name">crm_cube_parnter</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_partner_id'/>
    </record>


    <record model="olap.cube.table" id="crm_cube_categ_table">
        <field name="name">crm_cube_categ</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_categ_id'/>
    </record>

    <record model="olap.cube.table" id="crm_cube_channel_table">
        <field name="name">crm_cube_channel</field>
        <field name='schema_id' ref='schema_crm'/>
        <field name='column_link_id' ref='crm_case_canal_id'/>
    </record>


    <!-- Cube  Table Line -->
    <record model="olap.cube.table.line" id="crm_cube_table_line">
        <field name="field_id" ref="crm_case_categ_id"/>
        <field name="table_id" ref="table_crm_case"/>
        <field name="cube_table_id" ref="crm_cube_table"/>
    </record>

    <record model="olap.cube.table.line" id="crm_section_table_line">
        <field name="field_id" ref="crm_case_stage_section_id"/>
        <field name="table_id" ref="table_crm_case_stage"/>
        <field name="cube_table_id" ref="crm_cube_section_table"/>
    </record>
    <!-- Cube -->

    <record model="olap.cube" id="cube_crm">
        <field name="name">CRM</field>
        <field name="table_id" ref="crm_cube_table"/>
        <field name="schema_id" ref="schema_crm"/>
    </record>

<!--
DIMENSION Sections

-->

    <record model="olap.dimension" id="dimension_crm_section">
        <field name="name">Sections</field>
        <field name="cube_id" ref="cube_crm"/>
    </record>


    <record model="olap.hierarchy" id="hierarchy_crm_section">
        <field name="name">Sections</field>
        <field name="dimension_id" ref="dimension_crm_section"/>
        <field name="table_id" ref="crm_cube_section_table"/>
    </record>

    <record model="olap.level" id="level_crm_section">
        <field name="name">Sections</field>
        <field name="column_name" ref="crm_case_section_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">crm_case_section</field>
        <field name="hierarchy_id" ref="hierarchy_crm_section"/>
        <field name="sequence">1</field>
    </record>

    <record model="olap.level" id="level_crm_section_seq_2">
        <field name="name">States</field>
        <field name="column_name" ref="crm_case_stage_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">crm_case_stage</field>
        <field name="hierarchy_id" ref="hierarchy_crm_section"/>
        <field name="sequence">2</field>
    </record>

<!-- Dimension State-->

    <record model="olap.dimension" id="dimension_crm_state">
        <field name="name">States</field>
        <field name="cube_id" ref="cube_crm"/>
    </record>


    <record model="olap.hierarchy" id="hierarchy_crm_state">
        <field name="name">States</field>
        <field name="dimension_id" ref="dimension_crm_state"/>
        <field name="table_id" ref="crm_cube_table"/>
    </record>

    <record model="olap.level" id="level_crm_section">
        <field name="name">States</field>
        <field name="column_name" ref="crm_case_state"></field>
        <field name="column_id_name">state</field>
        <field name="table_name">crm_case</field>
        <field name="hierarchy_id" ref="hierarchy_crm_state"/>
        <field name="sequence">1</field>
    </record>

    <record model="olap.level" id="level_crm_section_seq_2">
        <field name="name">States</field>
        <field name="column_name" ref="crm_case_stage_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">crm_case_stage</field>
        <field name="hierarchy_id" ref="hierarchy_crm_section"/>
        <field name="sequence">2</field>
    </record>



<!--
Category
-->
    <record model="olap.dimension" id="dimension_crm_category">
        <field name="name">Category</field>
        <field name="cube_id" ref="cube_crm"/>
    </record>


    <record model="olap.hierarchy" id="hierarchy_crm_category">
        <field name="name">Category</field>
        <field name="dimension_id" ref="dimension_crm_category"/>
        <field name="primary_key_table">crm_case</field>
        <field name="table_id" ref="crm_cube_categ_table"/>
    </record>

    <record model="olap.level" id="level_crm_categorys">
        <field name="name">name</field>
        <field name="column_name" ref="crm_case_categ_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">crm_case_categ</field>
        <field name="hierarchy_id" ref="hierarchy_crm_category"/>
    </record>

<!--
    Customer
-->
    <record model="olap.dimension" id="dimension_crm_customer">
        <field name="name">Customer</field>
        <field name="cube_id" ref="cube_crm"/>
    </record>

    <record model="olap.hierarchy" id="hierarchy_crm_customer">
        <field name="name">Customer</field>
        <field name="dimension_id" ref="dimension_crm_customer"/>
        <field name="primary_key_table">crm_case</field>
        <field name="table_id" ref="crm_cube_parnter_table"/>
    </record>

    <record model="olap.level" id="level_crm_category">
        <field name="name">name</field>
        <field name="column_name" ref="res_partner_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">res_partner</field>
        <field name="hierarchy_id" ref="hierarchy_crm_customer"/>
    </record>



    <record model="olap.dimension" id="dimension_crm_channel">
        <field name="name">Channels</field>
        <field name="cube_id" ref="cube_crm"/>
    </record>


    <record model="olap.hierarchy" id="hierarchy_crm_channel">
        <field name="name">Channel</field>
        <field name="dimension_id" ref="dimension_crm_channel"/>
        <field name="table_id" ref="crm_cube_channel_table"/>
    </record>

    <record model="olap.level" id="level_crm_channel">
        <field name="name">Channel</field>
        <field name="column_name" ref="res_partner_canal_name"></field>
        <field name="column_id_name">name</field>
        <field name="table_name">res_partner_canal</field>
        <field name="hierarchy_id" ref="hierarchy_crm_channel"/>
        <field name="sequence">1</field>
    </record>



<!--
Measures
-->

    <record model="olap.measure" id="measure_crm_total_cases">
        <field name="name">Total Cases</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="measure_type">sql_expr</field>
        <field name="value_sql">count(crm_case.id)</field>
    </record>

    <record model="olap.measure" id="measure_crm_avg_planned_cost">
        <field name="name">Average Planned Revenue</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="measure_type">sql_expr</field>
        <field name="value_sql">avg(crm_case.planned_revenue)</field>
        <field name="formatstring">cr_postfix_comma</field>
    </record>

    <record model="olap.measure" id="measure_crm_avg_planned_revenue">
        <field name="name">Average Planned Cost</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="measure_type">sql_expr</field>
        <field name="value_sql">avg(crm_case.planned_cost)</field>
        <field name="formatstring">cr_postfix_comma</field>
    </record>

     <record model="olap.measure" id="measure_planned_revenue">
        <field name="name">Planned Revenue</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="value_column" ref="crm_case_planned_cost"></field>
        <field name="value_column_id_name">planned_cost</field>
        <field name="table_name">crm_case</field>
        <field name="agregator">sum</field>
        <field name="formatstring">cr_postfix_comma</field>
    </record>

    <record model="olap.measure" id="measure_planned_revenue">
        <field name="name">Planned Revenue</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="value_column" ref="crm_case_planned_revenue"></field>
        <field name="value_column_id_name">planned_revenue</field>
        <field name="table_name">crm_case</field>
        <field name="agregator">sum</field>
    </record>

    <record model="olap.measure" id="measure_probability">
        <field name="name">Probability</field>
        <field name="cube_id" ref="cube_crm"/>
        <field name="value_column" ref="crm_case_probability"></field>
        <field name="value_column_id_name">probability</field>
        <field name="table_name">crm_case</field>
        <field name="agregator">sum</field>
    </record>

</data>
</openerp>

