dim_employee
| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| User_ID (Primary) | varchar(50) | No | ||||
| Nama | varchar(150) | No | ||||
| Jabatan | varchar(150) | Yes | NULL | |||
| Atasan_ID | varchar(50) | Yes | NULL | |||
| ID_DT | varchar(100) | Yes | NULL | |||
| Departemen | varchar(150) | Yes | NULL | |||
| created_at | timestamp | No | CURRENT_TIMESTAMP | |||
| updated_at | timestamp | No | CURRENT_TIMESTAMP |
Indexes
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
|---|---|---|---|---|---|---|---|---|
| PRIMARY | BTREE | Yes | No | User_ID | 63 | A | No | |
| fk_atasan_employee | BTREE | No | No | Atasan_ID | 31 | A | Yes |
fact_kpi_actual
| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Actual_ID (Primary) | varchar(100) | No | ||||
| Periode | date | No | ||||
| User_ID | varchar(50) | No | dim_employee -> User_ID | |||
| KPI_ID | varchar(50) | No | ||||
| Nilai_Actual | decimal(18,2) | Yes | NULL |
Indexes
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
|---|---|---|---|---|---|---|---|---|
| PRIMARY | BTREE | Yes | No | Actual_ID | 533 | A | No | |
| User_ID | BTREE | No | No | User_ID | 35 | A | No |
fact_kpi_target
| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Periode (Primary) | date | No | ||||
| User_ID (Primary) | varchar(50) | No | dim_employee -> User_ID | |||
| KPI_ID (Primary) | varchar(50) | No | ||||
| Nama_Indikator | varchar(255) | No | ||||
| Sifat | enum('More', 'Less') | Yes | More | |||
| Target | decimal(18,2) | Yes | NULL | |||
| Satuan | varchar(50) | Yes | NULL | |||
| Weight | decimal(5,4) | Yes | 0.0000 | |||
| Source_Sheet_ID | varchar(255) | Yes | NULL | |||
| Source_Sheet_Name | varchar(255) | Yes | NULL |
Indexes
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
|---|---|---|---|---|---|---|---|---|
| PRIMARY | BTREE | Yes | No | Periode | 47 | A | No | |
| User_ID | 284 | A | No | |||||
| KPI_ID | 284 | A | No | |||||
| kunci_unik_target | BTREE | Yes | No | User_ID | 142 | A | No | |
| KPI_ID | 284 | A | No |
trx_notification_log
| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| id (Primary) | int(11) | No | ||||
| periode | date | No | ||||
| user_id | varchar(50) | Yes | NULL | |||
| nama_penerima | varchar(150) | Yes | NULL | |||
| dingtalk_user_id | varchar(100) | No | ||||
| recipient_type | enum('PEGAWAI', 'MANAJEMEN') | No | ||||
| nilai_kpi_dikirim | decimal(5,2) | Yes | NULL | |||
| status_send | enum('PENDING', 'SUCCESS', 'FAILED') | Yes | PENDING | |||
| error_message | text | Yes | NULL | |||
| sent_at | timestamp | Yes | NULL | |||
| created_at | timestamp | No | CURRENT_TIMESTAMP |
Indexes
| Keyname | Type | Unique | Packed | Column | Cardinality | Collation | Null | Comment |
|---|---|---|---|---|---|---|---|---|
| PRIMARY | BTREE | Yes | No | id | 15 | A | No | |
| uq_notif | BTREE | Yes | No | periode | 2 | A | No | |
| dingtalk_user_id | 15 | A | No | |||||
| recipient_type | 15 | A | No |
v_employee_hierarchy
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Pegawai_User_ID | varchar(50) | No | ||||
| BOD_User_ID | varchar(50) | Yes | NULL | |||
| Head_User_ID | varchar(50) | Yes | NULL | |||
| Head_Unit_User_ID | varchar(50) | Yes | NULL | |||
| Manager_User_ID | varchar(50) | Yes | NULL | |||
| Supervisor_User_ID | varchar(50) | Yes | NULL |
v_master_view
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| BOD User ID | varchar(50) | Yes | NULL | |||
| BOD Nama | varchar(150) | Yes | NULL | |||
| BOD Gestun | varchar(150) | Yes | NULL | |||
| BOD DT ID | varchar(100) | Yes | NULL | |||
| Head User ID | varchar(50) | Yes | NULL | |||
| Head Nama | varchar(150) | Yes | NULL | |||
| Head Jabatan | varchar(150) | Yes | NULL | |||
| Head DT ID | varchar(100) | Yes | NULL | |||
| Head Unit User ID | varchar(50) | Yes | NULL | |||
| Head Unit Nama | varchar(150) | Yes | NULL | |||
| Head Unit Jabatan | varchar(150) | Yes | NULL | |||
| Head Unit DT ID | varchar(100) | Yes | NULL | |||
| Manager User ID | varchar(50) | Yes | NULL | |||
| Manager Nama | varchar(150) | Yes | NULL | |||
| Manager Jabatan | varchar(150) | Yes | NULL | |||
| Manager DT ID | varchar(100) | Yes | NULL | |||
| Supervisor User ID | varchar(50) | Yes | NULL | |||
| Supervisor Nama | varchar(150) | Yes | NULL | |||
| Supervisor Jabatan | varchar(150) | Yes | NULL | |||
| Supervisor DT ID | varchar(100) | Yes | NULL | |||
| Pegawai User ID | varchar(50) | Yes | NULL | |||
| Pegawai Nama | varchar(150) | Yes | NULL | |||
| Pegawai Jabatan | varchar(150) | Yes | NULL | |||
| Pegawai DT ID | varchar(100) | Yes | NULL | |||
| Pegawai Departemen | varchar(150) | Yes | NULL | |||
| Periode | date | Yes | NULL | |||
| Indikator KPI | varchar(255) | No | ||||
| Target | decimal(18,2) | Yes | NULL | |||
| Satuan | varchar(50) | Yes | NULL | |||
| Actual | decimal(18,2) | Yes | NULL | |||
| Achieved | decimal(24,6) | Yes | NULL | |||
| Score | int(1) | Yes | NULL | |||
| Weight | decimal(5,4) | Yes | 0.0000 | |||
| KPI (Score x Weight) | decimal(5,2) | Yes | NULL |
v_master_view_base
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| BOD User ID | varchar(50) | Yes | NULL | |||
| BOD Nama | varchar(150) | Yes | NULL | |||
| BOD Gestun | varchar(150) | Yes | NULL | |||
| BOD DT ID | varchar(100) | Yes | NULL | |||
| Head User ID | varchar(50) | Yes | NULL | |||
| Head Nama | varchar(150) | Yes | NULL | |||
| Head Jabatan | varchar(150) | Yes | NULL | |||
| Head DT ID | varchar(100) | Yes | NULL | |||
| Head Unit User ID | varchar(50) | Yes | NULL | |||
| Head Unit Nama | varchar(150) | Yes | NULL | |||
| Head Unit Jabatan | varchar(150) | Yes | NULL | |||
| Head Unit DT ID | varchar(100) | Yes | NULL | |||
| Manager User ID | varchar(50) | Yes | NULL | |||
| Manager Nama | varchar(150) | Yes | NULL | |||
| Manager Jabatan | varchar(150) | Yes | NULL | |||
| Manager DT ID | varchar(100) | Yes | NULL | |||
| Supervisor User ID | varchar(50) | Yes | NULL | |||
| Supervisor Nama | varchar(150) | Yes | NULL | |||
| Supervisor Jabatan | varchar(150) | Yes | NULL | |||
| Supervisor DT ID | varchar(100) | Yes | NULL | |||
| Pegawai User ID | varchar(50) | Yes | NULL | |||
| Pegawai Nama | varchar(150) | Yes | NULL | |||
| Pegawai Jabatan | varchar(150) | Yes | NULL | |||
| Pegawai DT ID | varchar(100) | Yes | NULL | |||
| Pegawai Departemen | varchar(150) | Yes | NULL | |||
| Periode | date | Yes | NULL | |||
| Indikator KPI | varchar(255) | No | ||||
| Target | decimal(18,2) | Yes | NULL | |||
| Satuan | varchar(50) | Yes | NULL | |||
| Actual | decimal(18,2) | Yes | NULL | |||
| Weight | decimal(5,4) | Yes | 0.0000 | |||
| Achieved | decimal(24,6) | Yes | NULL |
v_notif_payload_manajemen
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Periode | date | Yes | NULL | |||
| User_ID | varchar(50) | Yes | NULL | |||
| Nama | varchar(150) | Yes | NULL | |||
| DingTalk_ID | varchar(100) | Yes | NULL | |||
| Recipient_Type | varchar(9) | No | ||||
| Nilai_KPI | decimal(28,2) | Yes | NULL |
v_notif_payload_pegawai
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Periode | date | Yes | NULL | |||
| User_ID | varchar(50) | Yes | NULL | |||
| Nama | varchar(150) | Yes | NULL | |||
| DingTalk_ID | varchar(100) | Yes | NULL | |||
| Recipient_Type | varchar(7) | No | ||||
| Nilai_KPI | decimal(27,2) | Yes | NULL |
v_periode_berjalan
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Periode | date | Yes | NULL |
v_target_cross_periode
Table comments: VIEW| Column | Type | Null | Default | Links to | Comments | MIME |
|---|---|---|---|---|---|---|
| Periode_Berjalan | date | Yes | NULL | |||
| User_ID | varchar(50) | No | ||||
| KPI_ID | varchar(50) | No | ||||
| Nama_Indikator | varchar(255) | No | ||||
| Sifat | enum('More', 'Less') | Yes | More | |||
| Target | decimal(18,2) | Yes | NULL | |||
| Satuan | varchar(50) | Yes | NULL | |||
| Weight | decimal(5,4) | Yes | 0.0000 |