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)