--
-- Host: localhost    Database: vanilla_kpi
-- ------------------------------------------------------




--
-- Table structure for table `fm_alert`
--

CREATE TABLE `fm_alert` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `axis_id` int(11) DEFAULT NULL,
  `alert_description` varchar(255) DEFAULT NULL,
  `level_index` int(11) DEFAULT NULL,
  `level_value` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `alert_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_alert`
--

INSERT INTO `fm_alert` VALUES (1,0,NULL,0,NULL,1,'Alert_Price');

--
-- Table structure for table `fm_alert_comment`
--

CREATE TABLE `fm_alert_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comment_text` varchar(255) DEFAULT NULL,
  `comment_date` datetime DEFAULT NULL,
  `raised_id` int(11) DEFAULT NULL,
  `resolution_comment` tinyint(1) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_alert_comment`
--


--
-- Table structure for table `fm_alert_condition`
--

CREATE TABLE `fm_alert_condition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `left_field` varchar(255) DEFAULT NULL,
  `missing_type` varchar(255) DEFAULT NULL,
  `operator` varchar(255) DEFAULT NULL,
  `right_field` varchar(255) DEFAULT NULL,
  `state_type` varchar(255) DEFAULT NULL,
  `alert_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_alert_condition`
--

INSERT INTO `fm_alert_condition` VALUES (1,1,'Metric value',NULL,'<','Objective',NULL,'VALUE');

--
-- Table structure for table `fm_alert_event`
--

CREATE TABLE `fm_alert_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `alert_message` varchar(255) DEFAULT NULL,
  `recipients` varchar(255) DEFAULT NULL,
  `event_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_alert_event`
--

INSERT INTO `fm_alert_event` VALUES (1,1,0,'Alert for price of the orders.\nOrders price is below objective.','system-no-reply@bpm-conseil.com','Mail');

--
-- Table structure for table `fm_alert_raised`
--

CREATE TABLE `fm_alert_raised` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `alert_id` int(11) DEFAULT NULL,
  `alert_date` datetime DEFAULT NULL,
  `handled` tinyint(1) DEFAULT NULL,
  `resolution_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_alert_raised`
--

INSERT INTO `fm_alert_raised` VALUES (1,1,'2014-09-10 00:00:00',1,'2014-09-12 00:00:00'),(2,1,'2014-11-02 00:00:00',0,'2014-11-02 00:00:00'),(3,1,'2015-02-01 00:00:00',1,'2015-02-10 00:00:00'),(4,1,'2015-03-15 00:00:00',1,'2015-03-16 00:00:00'),(5,1,'2015-05-01 00:00:00',0,'2015-05-01 00:00:00'),(6,1,'2015-06-10 00:00:00',1,'2015-06-20 00:00:00');

--
-- Table structure for table `fm_axis`
--

CREATE TABLE `fm_axis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation_date` datetime DEFAULT NULL,
  `axis_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_axis`
--

INSERT INTO `fm_axis` VALUES (1,'2014-09-30 14:36:22','Customers'),(2,'2014-09-30 14:37:48','Products');

--
-- Table structure for table `fm_comment`
--

CREATE TABLE `fm_comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value_comment` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `value_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_comment`
--


--
-- Table structure for table `fm_complex_axis`
--

CREATE TABLE `fm_complex_axis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idAxis` int(11) DEFAULT NULL,
  `idMap` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=36 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_complex_axis`
--

INSERT INTO `fm_complex_axis` VALUES (35,1,1);

--
-- Table structure for table `fm_complex_level`
--

CREATE TABLE `fm_complex_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `color` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `idAxis` int(11) DEFAULT NULL,
  `idLevel` int(11) DEFAULT NULL,
  `idMap` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_complex_level`
--

INSERT INTO `fm_complex_level` VALUES (38,'ee9f00','1430751676_map_marker.png',1,2,1),(37,'111111','1430751524_world_map.png',1,1,1);

--
-- Table structure for table `fm_complex_map`
--

CREATE TABLE `fm_complex_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_complex_map`
--

INSERT INTO `fm_complex_map` VALUES (1,'SalesMap');

--
-- Table structure for table `fm_complex_metric`
--

CREATE TABLE `fm_complex_metric` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `color` varchar(255) DEFAULT NULL,
  `icon` varchar(255) DEFAULT NULL,
  `idMap` int(11) DEFAULT NULL,
  `idMetric` int(11) DEFAULT NULL,
  `representation` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=39 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_complex_metric`
--

INSERT INTO `fm_complex_metric` VALUES (38,'ffffff','ic_style_black_18dp.png',1,2,'Bulles'),(37,'ffffff','ic_style_black_18dp.png',1,1,'Cartes de chaleurs');

--
-- Table structure for table `fm_fact_table`
--

CREATE TABLE `fm_fact_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datasource_id` int(11) DEFAULT NULL,
  `date_column` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `periodicity` varchar(255) DEFAULT NULL,
  `table_name` varchar(255) DEFAULT NULL,
  `value_column` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_fact_table`
--

INSERT INTO `fm_fact_table` VALUES (1,1,'ks_date',1,'Monthly','kpi_sales','ks_totalprice'),(2,1,'ks_date',2,'Monthly','kpi_sales','ks_quantity'),(4,1,'manager_date',5,'Yearly','manager','manager_id'),(5,1,'ks_date',3,'Monthly','kpi_sales','ks_loyaltypoints'),(7,1,'ks_date',5,'Monthly','kpi_sales','ks_totalpricewithtax'),(8,1,'ks_date',6,'Monthly','kpi_sales','ks_taxfees'),(9,1,'ks_date',7,'Monthly','kpi_sales','ks_discount'),(10,1,'ks_date',8,'Monthly','kpi_sales','ks_pricecut');

--
-- Table structure for table `fm_fact_table_axis`
--

CREATE TABLE `fm_fact_table_axis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `axis_id` int(11) DEFAULT NULL,
  `column_id` varchar(255) DEFAULT NULL,
  `fact_table_id` int(11) DEFAULT NULL,
  `objective_column_Id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_fact_table_axis`
--

INSERT INTO `fm_fact_table_axis` VALUES (1,1,'ks_customer',1,'ks_customer'),(2,1,'ks_customer',2,'ks_customer'),(3,2,'ks_product',1,'ks_product'),(5,2,'ks_product',2,'ks_product'),(6,1,'ks_customer',5,'ks_customer'),(7,1,'ks_customer',7,'ks_customer'),(8,1,'ks_customer',8,'ks_customer'),(9,1,'ks_customer',9,'ks_customer'),(10,1,'ks_customer',10,'ks_customer'),(11,2,'ks_product',5,'ks_product'),(12,2,'ks_product',7,'ks_product'),(13,2,'ks_product',8,'ks_product'),(14,2,'ks_product',9,'ks_product'),(15,2,'ks_product',10,'ks_product');

--
-- Table structure for table `fm_fact_table_calc`
--

CREATE TABLE `fm_fact_table_calc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `calculation` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_fact_table_calc`
--


--
-- Table structure for table `fm_fact_table_calc_metric`
--

CREATE TABLE `fm_fact_table_calc_metric` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fact_table_id` int(11) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_fact_table_calc_metric`
--


--
-- Table structure for table `fm_fact_table_objectives`
--

CREATE TABLE `fm_fact_table_objectives` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_column` varchar(255) DEFAULT NULL,
  `fact_table_id` int(11) DEFAULT NULL,
  `max_column` varchar(255) DEFAULT NULL,
  `min_column` varchar(255) DEFAULT NULL,
  `objective_column` varchar(255) DEFAULT NULL,
  `table_name` varchar(255) DEFAULT NULL,
  `tolerance` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_fact_table_objectives`
--

INSERT INTO `fm_fact_table_objectives` VALUES (1,'ks_date',1,'ks_totalprice_max','ks_totalprice_min','ks_totalprice_objective','kpi_sales',''),(2,'ks_date',2,'ks_quantity_max','ks_quantity_min','ks_quantity_objective','kpi_sales',''),(4,'',4,'','','','',''),(6,'ks_date',5,'ks_loyaltypoints_max','ks_loyaltypoints_min','ks_loyaltypoints_objective','kpi_sales','ks_loyaltypoints_tolerance'),(7,'ks_date',7,'ks_totalpricewithtax_max','ks_totalpricewithtax_min','ks_totalpricewithtax_objective','kpi_sales','ks_totalpricewithtax_tolerance'),(8,'ks_date',8,'ks_taxfees_max','ks_taxfees_min','ks_taxfees_objective','kpi_sales','ks_taxfees_tolerance'),(9,'ks_date',9,'ks_discount_max','ks_discount_min','ks_discount_objective','kpi_sales','ks_discount_tolerance'),(10,'ks_date',10,'ks_pricecut_max','ks_pricecut_min','ks_pricecut_objective','kpi_sales','ks_pricecut_tolerance');

--
-- Table structure for table `fm_group_observatory`
--

CREATE TABLE `fm_group_observatory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL,
  `observatory_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_group_observatory`
--

INSERT INTO `fm_group_observatory` VALUES (1,1,1),(2,2,1),(3,3,1);

--
-- Table structure for table `fm_level`
--

CREATE TABLE `fm_level` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column_id` varchar(255) DEFAULT NULL,
  `column_name` varchar(255) DEFAULT NULL,
  `creation_date` datetime DEFAULT NULL,
  `datasource_id` int(11) DEFAULT NULL,
  `geo_column_id` varchar(255) DEFAULT NULL,
  `level_index` int(11) DEFAULT NULL,
  `map_dataset_id` int(11) DEFAULT NULL,
  `level_name` varchar(255) DEFAULT NULL,
  `parent_column_id` varchar(255) DEFAULT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `table_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_level`
--

INSERT INTO `fm_level` VALUES (2,'kc_city','kc_city',NULL,1,'kc_city',1,2,'City','',1,'kpi_customers'),(3,'kp_line','kp_line',NULL,1,NULL,0,NULL,'ProductLine','',2,'kpi_products'),(1,'kc_city','kc_country',NULL,1,'kc_country',0,1,'Country','',1,'kpi_customers');

--
-- Table structure for table `fm_metric`
--

CREATE TABLE `fm_metric` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation_date` datetime DEFAULT NULL,
  `metric_description` varchar(255) DEFAULT NULL,
  `metric_direction` varchar(255) DEFAULT NULL,
  `etl_name` varchar(255) DEFAULT NULL,
  `metric_type` varchar(255) DEFAULT NULL,
  `metric_name` varchar(255) DEFAULT NULL,
  `metric_operator` varchar(255) DEFAULT NULL,
  `owner` varchar(255) DEFAULT NULL,
  `report_id` varchar(255) DEFAULT NULL,
  `responsible` varchar(255) DEFAULT NULL,
  `cube_id` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_metric`
--

INSERT INTO `fm_metric` VALUES (1,'2014-09-30 00:00:00','Total price of the orders by month, city and productline','Top',NULL,'Classic','Total price of the orders','sum','BPM',NULL,'BPM','39;Sales Basics'),(2,'2014-09-30 00:00:00','Total quantity of the orders by month, city and productline','Top',NULL,'Classic','Total quantity ordered','sum','BPM',NULL,'BPM','65;Sales Analysis'),(3,'2015-07-06 00:00:00','','Top',NULL,'Classic','Total loyalty points','sum','',NULL,'',NULL),(5,'2015-07-06 00:00:00','','Top',NULL,'Classic','Total price with tax of the orders','sum','',NULL,'',NULL),(6,'2015-07-06 00:00:00','','Top',NULL,'Classic','Total tax fees','sum','',NULL,'',NULL),(7,'2015-07-06 00:00:00','','Top',NULL,'Classic','Total discount of the orders','sum','',NULL,'',NULL),(8,'2015-07-06 00:00:00','','Top',NULL,'Classic','Total price cut of the orders','sum','',NULL,'',NULL);

--
-- Table structure for table `fm_metric_action`
--

CREATE TABLE `fm_metric_action` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action_description` varchar(255) DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `action_formula` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `action_name` varchar(255) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_metric_action`
--

INSERT INTO `fm_metric_action` VALUES (1,'When the value is higher than the objective','2008-04-18 12:00:00','{Value}\n>\n{Objective}',2,'Value_Sup_Objective','2008-02-01 12:00:00'),(2,'When the value is lower than the objective','2008-07-03 12:00:00','{Value}\n<\n{Objective}',2,'Value_Inf_Objective','2008-04-01 12:00:00'),(3,'When the value is lower than the objective','2008-04-18 12:00:00','{Value}\n<\n{Objective}',1,'Value_Inf_Objective','2008-02-01 12:00:00'),(4,'When the value is higher than the objective','2008-07-09 12:00:00','{Value}\n>\n{Objective}',1,'Value_Sup_Objective','2008-04-01 12:00:00');

--
-- Table structure for table `fm_metric_linked_item`
--

CREATE TABLE `fm_metric_linked_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) DEFAULT NULL,
  `item_name` varchar(255) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `item_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=34 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_metric_linked_item`
--

INSERT INTO `fm_metric_linked_item` VALUES (1,39,'Sales Basics',1,'Cube'),(33,65,'Sales Analysis',2,'Cube');

--
-- Table structure for table `fm_metric_map`
--

CREATE TABLE `fm_metric_map` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `latitude_column` varchar(255) DEFAULT NULL,
  `longitude_column` varchar(255) DEFAULT NULL,
  `zone_column` varchar(255) DEFAULT NULL,
  `datasource_id` int(11) DEFAULT NULL,
  `map_desc` varchar(255) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `metric_id` int(11) DEFAULT NULL,
  `map_name` varchar(255) DEFAULT NULL,
  `table_name` varchar(255) DEFAULT NULL,
  `map_type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_metric_map`
--

INSERT INTO `fm_metric_map` VALUES (1,'latitude','longitude','name_country',1,'This map shows the quantity sold in the world',1,2,'Quantity_World','country',NULL),(2,'latitude','longitude','name_country',1,'This map shows the sales realized in the world',1,1,'Sales_World','country',NULL);

--
-- Table structure for table `fm_observatory`
--

CREATE TABLE `fm_observatory` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation_date` datetime DEFAULT NULL,
  `observatory_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_observatory`
--

INSERT INTO `fm_observatory` VALUES (1,'2014-09-30 14:28:46','Sales');

--
-- Table structure for table `fm_observatory_theme`
--

CREATE TABLE `fm_observatory_theme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `observatory_id` int(11) DEFAULT NULL,
  `theme_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_observatory_theme`
--

INSERT INTO `fm_observatory_theme` VALUES (1,1,1);

--
-- Table structure for table `fm_theme`
--

CREATE TABLE `fm_theme` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creation_date` datetime DEFAULT NULL,
  `theme_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_theme`
--

INSERT INTO `fm_theme` VALUES (1,'2014-09-30 14:32:19','Miniatures');

--
-- Table structure for table `fm_theme_axis`
--

CREATE TABLE `fm_theme_axis` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `axis_id` int(11) DEFAULT NULL,
  `theme_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_theme_axis`
--

INSERT INTO `fm_theme_axis` VALUES (1,1,1),(2,2,1);

--
-- Table structure for table `fm_theme_metric`
--

CREATE TABLE `fm_theme_metric` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `metric_id` int(11) DEFAULT NULL,
  `theme_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `fm_theme_metric`
--

INSERT INTO `fm_theme_metric` VALUES (1,1,1),(2,2,1),(4,5,1),(5,6,1),(6,7,1),(7,8,1);


