saiku+kettle整合(三)初始导入


title: saiku+kettle整合(三)初始导入 tags: categories: saiku date: 2016-08-25 18:18:54

装载

一般预装载包含装载日期维度以及其他维度表和事实表

创建数据库及对应维度表事实表如下


    /*
     Navicat Premium Data Transfer
     Source Server         : local
     Source Server Type    : MySQL
     Source Server Version : 50712
     Source Host           : localhost
     Source Database       : dw
     Target Server Type    : MySQL
     Target Server Version : 50712
     File Encoding         : utf-8
     Date: 09/01/2016 11:01:42 AM
    */
    SET NAMES utf8;
    SET FOREIGN_KEY_CHECKS = 0;
    -- ----------------------------
    --  Table structure for `car_dim`
    -- ----------------------------
    DROP TABLE IF EXISTS `car_dim`;
    CREATE TABLE `car_dim` (
      `car_sk` int(11) NOT NULL AUTO_INCREMENT,
      `car_id` bigint(20) unsigned DEFAULT NULL,
      `car_prefix` char(2) DEFAULT NULL,
      `car_no` varchar(20) DEFAULT NULL,
      `brand_id` varchar(20) DEFAULT NULL,
      `factory_id` varchar(20) DEFAULT NULL,
      `series_id` varchar(20) DEFAULT NULL,
      `year_id` varchar(20) DEFAULT NULL,
      `main_id` varchar(20) DEFAULT NULL,
      `id_own_org` bigint(20) unsigned DEFAULT NULL,
      `expiry_date` date DEFAULT NULL,
      `efficative_date` date DEFAULT NULL,
      `version` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`car_sk`)
    ) ENGINE=InnoDB AUTO_INCREMENT=23125 DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `car_stg`
    -- ----------------------------
    DROP TABLE IF EXISTS `car_stg`;
    CREATE TABLE `car_stg` (
      `car_id` bigint(20) unsigned NOT NULL,
      `car_prefix` char(2) DEFAULT NULL,
      `car_no` varchar(20) DEFAULT NULL,
      `brand_id` varchar(20) DEFAULT NULL,
      `factory_id` varchar(20) DEFAULT NULL,
      `series_id` varchar(20) DEFAULT NULL,
      `year_id` varchar(20) DEFAULT NULL,
      `main_id` varchar(20) DEFAULT NULL,
      `id_own_org` bigint(20) unsigned DEFAULT NULL,
      PRIMARY KEY (`car_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `cdc_time`
    -- ----------------------------
    DROP TABLE IF EXISTS `cdc_time`;
    CREATE TABLE `cdc_time` (
      `last_load` date DEFAULT NULL,
      `current_load` date DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    -- ----------------------------
    --  Table structure for `customer_dim`
    -- ----------------------------
    DROP TABLE IF EXISTS `customer_dim`;
    CREATE TABLE `customer_dim` (
      `customer_sk` int(11) NOT NULL AUTO_INCREMENT,
      `customer_id` bigint(20) unsigned DEFAULT NULL,
      `customer_name` varchar(100) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `birthday` varchar(20) DEFAULT NULL,
      `customer_source` varchar(10) DEFAULT NULL,
      `customer_type` varchar(20) DEFAULT NULL,
      `company_name` varchar(100) DEFAULT NULL,
      `id_own_org` bigint(20) unsigned DEFAULT NULL,
      `points` decimal(18,2) DEFAULT NULL,
      `level` bigint(20) unsigned DEFAULT NULL,
      `efficative_date` date DEFAULT NULL,
      `expiry_date` date DEFAULT NULL,
      `version` int(11) DEFAULT NULL,
      PRIMARY KEY (`customer_sk`)
    ) ENGINE=InnoDB AUTO_INCREMENT=21296 DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `customer_stg`
    -- ----------------------------
    DROP TABLE IF EXISTS `customer_stg`;
    CREATE TABLE `customer_stg` (
      `customer_id` bigint(20) unsigned NOT NULL,
      `customer_name` varchar(100) DEFAULT NULL,
      `sex` varchar(5) DEFAULT NULL,
      `birthday` varchar(20) DEFAULT NULL,
      `customer_source` varchar(10) DEFAULT NULL,
      `customer_type` varchar(20) DEFAULT NULL,
      `company_name` varchar(100) DEFAULT NULL,
      `id_own_org` bigint(20) unsigned DEFAULT NULL,
      `points` decimal(18,2) DEFAULT NULL,
      `level` bigint(20) unsigned DEFAULT NULL,
      PRIMARY KEY (`customer_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `group_dim`
    -- ----------------------------
    DROP TABLE IF EXISTS `group_dim`;
    CREATE TABLE `group_dim` (
      `group_sk` int(11) NOT NULL AUTO_INCREMENT,
      `group_id` bigint(20) unsigned NOT NULL,
      `group_name` varchar(100) NOT NULL,
      `group_sponsor` bigint(20) unsigned NOT NULL,
      `expiry_date` date DEFAULT NULL,
      `effictive_date` date DEFAULT NULL,
      `version` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`group_sk`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `group_stg`
    -- ----------------------------
    DROP TABLE IF EXISTS `group_stg`;
    CREATE TABLE `group_stg` (
      `group_id` bigint(20) unsigned NOT NULL,
      `group_name` varchar(100) NOT NULL,
      `group_sponsor` bigint(20) unsigned NOT NULL,
      PRIMARY KEY (`group_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `maintain_dim`
    -- ----------------------------
    DROP TABLE IF EXISTS `maintain_dim`;
    CREATE TABLE `maintain_dim` (
      `maintain_sk` int(11) NOT NULL AUTO_INCREMENT,
      `maintain_id` bigint(20) unsigned NOT NULL,
      `expiry_date` date DEFAULT NULL,
      `efficative_date` date DEFAULT NULL,
      `version` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`maintain_sk`)
    ) ENGINE=InnoDB AUTO_INCREMENT=12888 DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `maintain_fact`
    -- ----------------------------
    DROP TABLE IF EXISTS `maintain_fact`;
    CREATE TABLE `maintain_fact` (
      `maintain_sk` int(11) NOT NULL,
      `car_sk` int(11) DEFAULT NULL,
      `customer_sk` int(11) DEFAULT NULL,
      `bill_date_sk` int(11) DEFAULT NULL,
      `bill_type` char(4) COLLATE utf8mb4_bin DEFAULT NULL,
      `receive_amount` decimal(18,2) DEFAULT NULL,
      `amount` decimal(18,2) DEFAULT NULL,
      `vip_expense` decimal(18,2) DEFAULT NULL,
      `complate_date_sk` int(11) DEFAULT NULL,
      `org_sk` int(11) DEFAULT NULL,
      KEY `maintain_sk` (`maintain_sk`),
      KEY `car_sk` (`car_sk`),
      KEY `customer_sk` (`customer_sk`),
      KEY `bill_date_sk` (`bill_date_sk`),
      KEY `complate_date_sk` (`complate_date_sk`),
      KEY `org_sk` (`org_sk`),
      CONSTRAINT `maintain_fact_ibfk_1` FOREIGN KEY (`maintain_sk`) REFERENCES `maintain_dim` (`maintain_sk`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `maintain_fact_ibfk_2` FOREIGN KEY (`car_sk`) REFERENCES `car_dim` (`car_sk`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `maintain_fact_ibfk_3` FOREIGN KEY (`customer_sk`) REFERENCES `customer_dim` (`customer_sk`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `maintain_fact_ibfk_4` FOREIGN KEY (`bill_date_sk`) REFERENCES `date_dim` (`date_sk`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `maintain_fact_ibfk_5` FOREIGN KEY (`complate_date_sk`) REFERENCES `date_dim` (`date_sk`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `maintain_fact_ibfk_6` FOREIGN KEY (`org_sk`) REFERENCES `org_dim` (`org_sk`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    -- ----------------------------
    --  Table structure for `org_dim`
    -- ----------------------------
    DROP TABLE IF EXISTS `org_dim`;
    CREATE TABLE `org_dim` (
      `org_sk` int(11) NOT NULL AUTO_INCREMENT,
      `org_id` bigint(20) unsigned NOT NULL,
      `org_name` varchar(100) DEFAULT NULL,
      `province` varchar(20) DEFAULT NULL,
      `city` varchar(20) DEFAULT NULL,
      `area` varchar(20) DEFAULT NULL,
      `is_del` tinyint(4) DEFAULT NULL,
      `effective_date` date DEFAULT NULL,
      `expiry_date` date DEFAULT NULL,
      `version` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`org_sk`)
    ) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4;
    -- ----------------------------
    --  Table structure for `org_stg`
    -- ----------------------------
    DROP TABLE IF EXISTS `org_stg`;
    CREATE TABLE `org_stg` (
      `org_id` bigint(20) unsigned NOT NULL,
      `org_name` varchar(100) DEFAULT NULL,
      `province` varchar(20) DEFAULT NULL,
      `city` varchar(20) DEFAULT NULL,
      `area` varchar(20) DEFAULT NULL,
      `is_del` tinyint(4) DEFAULT NULL,
      PRIMARY KEY (`org_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    SET FOREIGN_KEY_CHECKS = 1;
复制代码

kettle初始化导入

  1. 清空过渡区
  2. 装载过渡区
     
        


  3. 装载维度表


  4. 装载事实表







  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值