saiku+kettle整合（三）初始匯入

qixiaobo發表於2018-01-05
AI

title: saiku+kettle整合（三）初始匯入 tags: categories: saiku date: 2016-08-25 18:18:54

裝載

一般預裝載包含裝載日期維度以及其他維度表和事實表

100013_tiqH_871390.png

建立資料庫及對應維度表事實表如下


    /*
     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初始化匯入

100024_9r3i_871390.png

  1. 清空過渡區
    100029_uc9G_871390.png
  2. 裝載過渡區
     
        
    100034_tLZU_871390.png

    100041_BGFM_871390.png

    100051_bh9X_871390.png
  3. 裝載維度表
    100058_FgWZ_871390.png

    100110_dTx1_871390.png

    100116_9QDE_871390.png
  4. 裝載事實表
    100124_ch21_871390.png

    100130_zZvy_871390.png

    100137_L9iw_871390.png

    100144_rzCq_871390.png

    100148_C8LK_871390.png

    100156_clR9_871390.png

    100202_9wTi_871390.png

    100209_HCk4_871390.png

相關文章