This table lists the types of tables used in the Oracle Business Analytics Warehouse.
| Table Type | Description |
|---|---|
|
Aggregate tables (_A) |
Contain summed (aggregated) data. |
|
Dimension tables (_D) |
Star analysis dimensions. |
|
Delete tables (_DEL) |
Tables that store IDs of the entities that were physically deleted from the source system and should be flagged as deleted from the data warehouse. Note that there are two types of delete tables: _DEL and _PE. For more information about the _PE table type, see the row for Primary extract tables (_PE) in this table. |
|
Dimension Hierarchy tables (_DH) |
Tables that store the dimension's hierarchical structure. |
|
Dimension Helper tables (_DHL) |
Tables that store many-to-many relationships between two joining dimension tables. |
|
Staging tables for Dimension Helper (_DHLS) |
Staging tables for storing many-to-many relationships between two joining dimension tables. |
|
Dimension Hierarchy Staging table (_DHS) |
Staging tables for storing the hierarchy structures of dimensions that have not been through the final extract-transform-load (ETL) transformations. |
|
Dimension Staging tables (_DS) |
Tables used to hold information about dimensions that have not been through the final ETL transformations. |
|
Fact tables (_F) |
Contain the metrics being analyzed by dimensions. |
|
Fact Staging tables (_FS) |
Staging tables used to hold the metrics being analyzed by dimensions that have not been through the final ETL transformations. |
|
Internal tables (_G, _GS) |
General tables used to support ETL processing. |
|
Helper tables (_H) |
Inserted between the fact and dimension tables to support a many-to-many relationship between fact and dimension records. |
|
Helper Staging tables (_HS) |
Tables used to hold information about helper tables that have not been through the final ETL transformations. |
|
Mini dimension tables (_MD) |
Include combinations of the most queried attributes of their parent dimensions. The database joins these small tables to the fact tables. |
|
Primary extract tables (_PE) |
Tables used to support the soft delete feature. The table includes all the primary key columns (integration ID column) from the source system. When a delete event happens, the full extract from the source compares the data previously extracted in the primary extract table to determine if a physical deletion was done in the Siebel application. The soft delete feature is disabled by default. Therefore, the primary extract tables are not populated until you enable the soft delete feature. Note that there are two types of delete tables: _DEL and _PE. For more information about the _DEL table type, see the row for Delete table (_DEL) in this table. |
|
Persisted Staging table (_PS) |
Tables that source multiple data extracts from the same source table. These tables perform some common transformations required by multiple target objects. They also simplify the source object to a form that is consumable by the warehouse needed for multiple target objects. These tables are never truncated during the life of the data warehouse. These are truncated only during full load, and therefore, persist the data throughout. |
|
Row Flattened Hierarchy Table (_RH) |
Tables that record a node in the hierarchy by a set of ancestor-child relationships (parent-child for all parent levels). |
|
Translation Staging tables (_TL) |
Tables store names and descriptions in the languages supported by Oracle BI Applications. |
|
Pre-staging or post-staging Temporary table (_TMP) |
Source-specific tables used as part of the ETL processes to conform the data to fit the universal staging tables (table types_DS and _FS). These tables contain intermediate results that are created as part of the conforming process. |
|
Unbounded dimension (_UD) |
Tables containing information that is not bounded in transactional database data but should be treated as bounded data in the Oracle Business Analytics Warehouse. |
|
Staging tables for Usage Accelerator (_WS) |
Tables containing the necessary columns for the ETL transformations. |
One of the main uses of a data warehouse is to sum up fact data with respect to a given dimension, for example, by date or by sales region. Performing this summation on-demand is resource-intensive, and slows down response time.
Oracle Business Analytics Warehouse precalculates some of these sums and stores the information in aggregate tables. In the Oracle Business Analytics Warehouse, the aggregate tables have been suffixed with _A.
A class table is a single physical table that can store multiple logical entities that have similar business attributes. Various logical dimensions are separated by a separator column, such as, type or category. W_XACT_TYPE_D is an example of a dimension class table. Different transaction types, such as, sales order types, sales invoice types, purchase order types, and so on, can be housed in the same physical table.
You can add additional transaction types to an existing physical table and so reduce the effort of designing and maintaining new physical tables. However, while doing so, you should consider that attributes specific to a particular logical dimension cannot be defined in this physical table. Also, if a particular logical dimension has a large number of records, it might be a good design practice to define a separate physical table for that particular logical entity.
The unique numeric key (ROW_WID) for each dimension table is generated during the load process. This key is used to join each dimension table with its corresponding fact table or tables. It is also used to join the dimension with any associated hierarchy table or extension table. The ROW_WID columns in the Oracle Business Analytics Warehouse tables are numeric.
In every dimension table, the ROW_WID value of zero is reserved for Unspecified. If one or more dimensions for a given record in a fact table is unspecified, the corresponding key fields in that record are set to zero.
This design approach is used when the entity is logically the same but participates as different roles in the business process.
As an example, an employee could participate in a Human Resources business process as an employee, in the sales process as a sales representative, in the receivables process as a collector, and in the purchase process as a buyer. However, the employee is still the same. For such logical entities, flags have been provided in the corresponding physical table (for example, W_EMPLOYEE_D) to describe the record's participation in business as different roles.
While configuring the presentation layer, the same physical table can be used as a specific logical entity by flag-based filters. For example, if a particular star schema requires Buyer as a dimension, the Employee table can be used with a filter where the Buyer flag is set to Y.
Each fact table contains one or more numeric foreign key columns to link it to various dimension tables.
Helper tables are used to solve complex problems that cannot be resolved by simple dimensional schemas.
In a typical dimensional schema, fact records join to dimension records with a many-to-one relationship. To support a many-to-many relationship between fact and dimension records, a helper table is inserted between the fact and dimension tables.
The helper table can have multiple records for each fact and dimension key combination. This allows queries to retrieve facts for any given dimension value. It should be noted that any aggregation of fact records over a set of dimension values might contain overlaps (due to a many-to-many relationship) and can result in double counting.
At times there is a requirement to query facts related to the children of a given parent in the dimension by only specifying the parent value (example: manager's sales fact that includes sales facts of the manager's subordinates). In this situation, one helper table containing multiple records for each parent-child dimension key combination is inserted between the fact and the dimension. This allows queries to be run for all subordinates by specifying only the parent in the dimension.
Some dimension tables have hierarchies into which each record rolls. This hierarchy information is stored in a separate table, with one record for each record in the corresponding dimension table. This information allows users to drill up and down through the hierarchy in reports.
There are two types of hierarchies: a structured hierarchy in which there are fixed levels, and a hierarchy with parent-child relationships. Structured hierarchies are simple to model, since each child has a fixed number of parents and a child cannot be a parent. The second hierarchy, with unstructured parent-child relationships is difficult to model because each child record can potentially be a parent and the number of levels of parent-child relationships is not fixed. Hierarchy tables have a suffix of _DH.
Mini-dimension tables include combinations of the most queried attributes of their parent dimensions. They improve query performance because the database does not need to join the fact tables to the big parent dimensions but can join these small tables to the fact tables instead.
The table lists the mini-dimension tables in the Oracle Business Analytics Warehouse.
| Table Name | Parent Dimension |
|---|---|
|
W_RESPONSE_MD |
Parent W_RESPONSE_D |
|
W_AGREE_MD |
Parent W_AGREE_D |
|
W_ASSET_MD |
Parent W_ASSET_D |
|
W_OPTY_MD |
Parent W_OPTY_D |
|
W_ORDER_MD |
Parent W_ORDER_D |
|
W_QUOTE_MD |
Parent W_QUOTE_D |
|
W_SRVREQ_MD |
Parent W_SRVREQ_D |
Staging tables are used primarily to stage incremental data from the transactional database. When the ETL process runs, staging tables are truncated before they are populated with change capture data. During the initial full ETL load, these staging tables hold the entire source data set for a defined period of history, but they hold only a much smaller volume during subsequent refresh ETL runs.
This staging data (list of values translations, computations, currency conversions) is transformed and loaded to the dimension and fact staging tables. These tables are typically tagged as <TableName>_DS or <TableName>_FS. The staging tables for the Usage Accelerator are tagged as WS_<TableName>.
The staging table structure is independent of source data structures and resembles the structure of data warehouse tables. This resemblance allows staging tables to also be used as interface tables between the transactional database sources and data warehouse target tables.
Translation tables provide multi-language support by storing names and descriptions in each language that Oracle Business Analytics Warehouse supports.
There are two types of translation tables:
Domain tables that provide multi-language support associated with the values stored in the %_CODE columns.
Tables that provide multi-language support for dimensions.
Domains and their associated translated values are stored in a single table named W_DOMAIN_MEMBER_LKP_TL. Each dimension requiring multi-language support that cannot be achieved with domains has an associated _TL table. These tables have a one-to-many relationship with the dimension table. For each record in the dimension table, you will see multiple records in the associated translation table (one record for each supported language).