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 | |||
| Manager_ID | varchar(50) | Yes | NULL | dim_employee -> User_ID | ||
| Head_Unit_ID | varchar(50) | Yes | NULL | |||
| Head_ID | varchar(50) | Yes | NULL | |||
| ID_DT | varchar(100) | 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 | 60 | A | No | |
| Manager_ID | BTREE | No | No | Manager_ID | 20 | 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 | 694 | A | No | |
| User_ID | BTREE | No | No | User_ID | 69 | 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 | 48 | A | No | |
| User_ID | 169 | A | No | |||||
| KPI_ID | 338 | A | No | |||||
| User_ID | BTREE | No | No | User_ID | 169 | 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 | 14 | A | No | |
| uq_notif | BTREE | Yes | No | periode | 2 | A | No | |
| dingtalk_user_id | 14 | A | No | |||||
| recipient_type | 14 | A | No |
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 | |||
| 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 | |||
| 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 | |||
| 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 | |||
| 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 |