Subject Area : 305 - Data Mart Load |
Subject Area Name | Definition | |||||
---|---|---|---|---|---|---|
305 - Data Mart Load | Tables that control the Full and Incremental load process of the Data Mart. | |||||
ER Diagram Name | Physical Display Level | |||||
Logical | Column | |||||
Physical | Column | |||||
Physical Name | Entity/Table Name | Logical Only | Do Not Generate | Definition | ||
audit_txn | Audit Transaction | false | A record of the Principal and create time of the start of the commit time of a logical transaction. In many cases multiple Audit Log entries will use the same transaction. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
audit_txn_id | Audit Transaction Id | The automatically generated primary key of the audit_txn table. | bigint | |||
txn_start_date | Transaction Start Date | The transaction date of the Audit Log entry. This date may not be in strict order with the Audit Log id in the event that features are used to continue a previous Audit Log transaction. | datetime | |||
app_user_id | Application User Id | A Foreign Key to the Application User table. The audit trail transaction may or may not be associated with a customer or agent user. If this column is null, then a system component should be identified. | bigint | |||
sys_component_id | System Component Id |
This is a foreign key to the system component table. The audit trail transaction may or may not be associated with a customer or agent user. The system component will be set if there is no application user identified. It identifies the source of the transaction. Values include SYSTEM, WEB_SERVICES, ASSET_MANAGEMENT, CALENDAR, IMPORTING, etc. All identifiers are negative, so that they can be distinguished from userids in case they are both stored in the same dynamic table CreatedBy, and UpdatedBy fields. We are going to try first normal form normalization here and see if it works. |
int | |||
audit_rule_name_id | Audit Rule Name Id | The primary key is the original rule definition id. It will not change as the rule is renamed. If the rule is deleted, the deleted date will be filled in, but the rule name will still be known in this table. | bigint | |||
data_load_type | Data Load Type | false |
A lookup table for the load type. Values include: FULL_LOAD Full deletion and re-adding of item data in the data mart INCR_LOAD Incremental deletion and re-adding of item data changed in the data mart. |
|||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_load_type_name | Data Load Type Name | The name of the Data Load Type. | varchar(20) | |||
data_load_type_descr | Data Load Type Description | A descriptive name of the Data Load Type. This descriptive name may be localized. | varchar(512) | |||
load_proc_suffix | Load Process Suffix | The name of the DBMS_Object_Name. It is a database object name, limited to 30 characters in length. | varchar(30) | |||
data_mart_load_history | Data Mart Load History | false | The history of data mart full and incremental loads. A full load is chunked into 10K row batches and is restartable and re-runnable. An incremental load processes all the activity since the previous run, which is assumed to be a short duration. This table holds an Audit Transaction Id reference to the Audit tables, which identify the last transaction processed. This table is designed to handle many types of data marts in multiple different containers. Currently, only two Ticket data marts are defined and run. If a new data mart fact table were defined, and stored procedures written for the full and incremental load, and all entries defined in the request tables, the data mart java process should manage the loading. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_load_history_id | Data Mart Load History Id | The automatically generated primary key of the data_mart_load_history table. | bigint | |||
data_mart_name | Data Mart Name | The name of the Data Mart Load History. | varchar(255) | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table. | bigint | |||
load_start_date | Load Start Date | Begin Date. | datetime | |||
load_end_date | Load End Date | Load end date in UTC | datetime | |||
last_audit_txn_id | Last Audit Transaction Id | The automatically generated primary key of the audit_txn table. | bigint | |||
is_waiting | Is Waiting | Indicates if Is Waiting. | bit | |||
load_proc_name | Load Process Name | The name of the DBMS_Object_Name. It is a database object name, limited to 30 characters in length. | varchar(30) | |||
data_load_type_name | Data Load Type Name | The name of the Data Load Type. | varchar(20) | |||
data_mart_load_lock | Data Mart Load Lock | false | A pessimistic lock table designed to prevent multiple loads from running against the same item. It is also used to count and throttle the number of simultaneous loads. Both the full and incremental loads drop and create tables so that faster non-logged database operations can be used (Select * INTO..., CREATE TABLE AS SELECT *...) The tables have item specific names, so they must single thread. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_name | Data Mart Name | The name of the Data Mart Load Lock. | varchar(255) | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table. | bigint | |||
is_locked | Is Locked | Indicates if Is Locked. | bit | |||
owning_proc_id | Owning Process Id | The name of the Data Mart Load Lock. | varchar(255) | |||
data_mart_load_lock_id | Data Mart Load Lock Id | A Foreign Key to the Data Mart Load Lock table. | bigint | |||
data_mart_rqst_history | Data Mart Request History | false | false | A history table detailing the results of Data Mart request execution. It is also used to report the last run of the data mart on data mart reports. It may need to be purged from time to time as each request is logged even if no updates occur, and the frequency defaults to 10 minutes. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_rqst_history_id | Data Mart Request History Id | The automatically generated primary key of the data_mart_rqst_history table. | bigint | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table. | bigint | |||
rqst_begin_date | Request Begin Date | Request Begin Date | datetime | |||
rqst_begin_date_utc_string | Request Begin Date UTC String | Begin Date as a UTC string to avoid JDBC/Hibernate errors. | varchar(23) | |||
rqst_end_date | Request End Date | End Date | datetime | |||
rqst_end_date_utc_string | Request End Date UTC String | End Date as a UTC string to avoid JDBC/Hibernate errors. | varchar(23) | |||
data_mart_rqst_type_id | Data Mart Request Type Id | A Foreign Key to the Data Mart Request Type table. | int | |||
data_mart_rqst_state_id | Data Mart Request State Id | A Foreign Key to the Data Mart Request State table. | int | |||
data_mart_rqst_queue | Data Mart Request Queue | false | false | The queue of active data mart load requests. An entry in this table will be picked up by the Java Data Mart manager and used to initiate and then maintain state of the request. The FootPrints UI controls data mart execution by creating and updating rows in this table. During upgrade, a request to update all data marts is added, and once the system comes up, Data Marts are updated in round robin fashion. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_rqst_queue_id | Data Mart Request Queue Id | The automatically generated primary key of the data_mart_rqst_queue table. | bigint | |||
item_defn_id | Item Definition Id | A Foreign Key to the Definition table. | bigint | |||
rqst_date | Request Date | Request date | datetime | |||
rqst_date_utc_string | Request Date UTC String | Request Date as a UTC string to avoid JDBC/Hibernate errors. | varchar(23) | |||
data_mart_rqst_type_id | Data Mart Request Type Id | A Foreign Key to the Data Mart Request Type table. | int | |||
data_mart_rqst_state_id | Data Mart Request State Id | A Foreign Key to the Data Mart Request State table. | int | |||
data_mart_rqst_state | Data Mart Request State | false | false | A lookup table for the state of data mart request processing. Values include: PENDING, CANCEL_REQUEST, IN_PROCESS, DONE, FAILED, CANCELED. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_rqst_state_id | Data Mart Request State Id | The automatically generated primary key of the data_mart_rqst_state table. | int | |||
data_mart_rqst_state_name | Data Mart Request State Name | The name of the Data Mart Request State. | varchar(255) | |||
data_mart_rqst_state_descr | Data Mart Request State Description | A descriptive name of the Data Mart Request State. This descriptive name may be localized. | varchar(512) | |||
data_mart_rqst_type | Data Mart Request Type | false | false | A lookup table for the data mart load request type. Values include: FULL_LOAD, SCHEDULED_INC_LOAD, REQUESTED_INC_LOAD, FULL_LOAD_WITH_SCHD_UPDATE, REBUILD_ALL_DATAMARTS. | ||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
data_mart_rqst_type_name | Data Mart Request Type Name | The name of the Data Mart Request Type. | varchar(255) | |||
data_mart_rqst_type_descr | Data Mart Request Type Description | A descriptive name of the Data Mart Request Type. This descriptive name may be localized. | varchar(512) | |||
data_mart_rqst_type_id | Data Mart Request Type Id | The automatically generated primary key of the data_mart_rqst_type table. | int | |||
defn | Definition | false | A definition holds the structural and behavioral information needed for an application element that is configuarable by the system designer and/or the end user at definition time. Current Footrpints uses a combination of directories, files, tables, and programming code to represent this information. The new FootPrints will provide all of the same information within the definition. The definition can be persisted in a structure that is isolated at the Instance Level. The data for the definition will be stored as Configuration Settings associated with the Definition Version. See the database views v_defn_ver_active, v_cntnr_item_field. | |||
Physical Name | Attribute/Column Name | Definition | Physical Data Type | |||
defn_id | Definition Id | The automatically generated primary key of the defn table. | bigint | |||
defn_name | Definition Name | The name of the Definition. | nvarchar(4000) | |||
dsble_date | Disabled Date | The date that this instance of Definition is logically deleted and disabled. | datetime | |||
defn_guid | Definition GUID | A Global Unique IDentifier search key for the Definition, composed of letters and numbers. It is computed by the client and typically used in unstructured data. | varchar(50) | |||
defn_type_name | Definition Type Name | The unique string identifier for an instance of Definition Type. This identifier must be kept in sync with programming code that uses an enumeration constant to identify particular values. A scan program is generally used to populate this table. | varchar(255) | |||
defn_subtype_id | Definition Subtype Id | A Foreign Key to the Definition Subtype table. | int | |||
tmpl_export_guid | Template Export GUID | The value of a Definition, stored as a string. | VARCHAR(50) |