Subject Area : 305 - Data Mart Load
Subject Area(s) of Model "Renoir Domain Model Detail"
# Name Definition ER Diagram Entity/Table
Total
. 305 - Data Mart Load Tables that control the Full and Incremental load process of the Data Mart. Details.. Details..
ER Diagram(s) of Subject Area "305 - Data Mart Load"
# Name Physical Display Level
Total 2
. Logical Column
. Physical Column
Entity/Table(s) of Subject Area "305 - Data Mart Load"
# Physical Name Name Logical Only Do Not Generate Definition Attribute/Column
Total 9
. 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. Details..
. 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.
Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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. Details..
Attribute/Column(s) of Entity/Table "Audit Transaction"
# Physical Name Name Definition Physical Data Type
Total 5
. 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
Attribute/Column(s) of Entity/Table "Data Load Type"
# Physical Name Name Definition Physical Data Type
Total 3
. 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)
Attribute/Column(s) of Entity/Table "Data Mart Load History"
# Physical Name Name Definition Physical Data Type
Total 9
. 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)
Attribute/Column(s) of Entity/Table "Data Mart Load Lock"
# Physical Name Name Definition Physical Data Type
Total 5
. 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
Attribute/Column(s) of Entity/Table "Data Mart Request History"
# Physical Name Name Definition Physical Data Type
Total 8
. 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
Attribute/Column(s) of Entity/Table "Data Mart Request Queue"
# Physical Name Name Definition Physical Data Type
Total 6
. 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
Attribute/Column(s) of Entity/Table "Data Mart Request State"
# Physical Name Name Definition Physical Data Type
Total 3
. 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)
Attribute/Column(s) of Entity/Table "Data Mart Request Type"
# Physical Name Name Definition Physical Data Type
Total 3
. 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
Attribute/Column(s) of Entity/Table "Definition"
# Physical Name Name Definition Physical Data Type
Total 7
. 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)