| Oracle® Fusion Middleware Reference Guide for Oracle Business Intelligence Applications 11g Release 1 (11.1.1) Part Number E16816-04 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Reference Guide for Oracle Business Intelligence Applications 11g Release 1 (11.1.1) Part Number E16816-04 |
|
|
PDF · Mobi · ePub |
This chapter includes information on the types of tables and columns in the Oracle Business Analytics Warehouse, including the naming conventions used.
Note:
This chapter contains naming conventions used for database tables and columns in the Oracle Business Analytics Warehouse. This information does not apply to objects in the Oracle Business Intelligence repository.
This chapter contains the following topics:
Section 3.1, "Naming Conventions for Oracle Business Analytics Warehouse Tables"
Section 3.2, "Table Types for Oracle Business Analytics Warehouse"
Section 3.3, "Internal Tables in Oracle Business Analytics Warehouse"
Section 3.4, "Standard Column Prefixes in Oracle Business Analytics Warehouse"
Section 3.5, "Standard Column Suffixes in Oracle Business Analytics Warehouse"
Section 3.6, "System Columns in Oracle Business Analytics Warehouse Tables"
Section 3.8, "Oracle Business Analytics Warehouse Primary Data Values"
Section 3.9, "About Multi-Language Support in the Oracle Business Analytics Warehouse"
Section 3.10, "Oracle Business Analytics Warehouse Currency Preferences"
Oracle Business Analytics Warehouse tables use a three-part naming convention: PREFIX_NAME_SUFFIX, as shown in Table 3-1.
Table 3-1 Naming Conventions for Oracle Business Analytics Data Warehouse Tables
| Part | Meaning | Table Type |
|---|---|---|
|
PREFIX |
Shows Oracle Business Analytics-specific data warehouse application tables. |
W_ = Warehouse |
|
NAME |
Unique table name. |
All tables. |
|
SUFFIX |
Indicates the table type. |
_A = Aggregate _D = Dimension _DEL = Delete _DH = Dimension Hierarchy _DHL = Dimension Helper _DHLS = Staging for Dimension Helper _DHS = Staging for Dimension Hierarchy _DS = Staging for Dimension _F = Fact _FS = Staging for Fact _G, _GS = Internal _H = Helper _HS = Staging for Helper _MD = Mini Dimension _PE = Primary Extract _PS = Persisted Staging _RH = Row Flattened Hierarchy _TL = Translation Staging (supports multi-language support) _TMP = Pre-staging or post-staging temporary table _UD = Unbounded Dimension _WS = Staging for Usage Accelerator |
Table 3-2 lists the types of tables used in the Oracle Business Analytics Warehouse.
Table 3-2 Table Types 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 |
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. The 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 by the Oracle Business Analytics Warehouse 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 in the Oracle Business Analytics Warehouse: 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.
Table 3-3 lists the mini-dimension tables in the Oracle Business Analytics Warehouse.
Table 3-3 Mini-Dimension Tables in 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).
Internal tables are used primarily by ETL mappings for data transformation and for controlling ETL runs. These tables are not queried by end users and are not directly managed by the Oracle Data Warehouse Administration Console (DAC). These tables are described in Table 3-4.
Table 3-4 Oracle Business Analytics Warehouse Internal Tables
| Name | Purpose | Location |
|---|---|---|
|
W_DUAL_G |
Used to generate records for the Day dimension. |
Data warehouse |
|
W_COSTLST_G |
Stores cost lists. |
Data warehouse |
|
W_EXCH_RATE_G |
Stores exchange rates. |
Data warehouse |
|
W_LOV_EXCPT_G |
Stores the list of values for the list of values types in which the ETL process finds exceptions. |
Data warehouse |
|
W_UOM_CONVERSION_G |
Stores a list of From and To UOM codes and their conversion rates. |
Data warehouse |
|
W_DOMAIN_MEMBER_G |
Staging table for populating incremental changes into W_DOMAIN_MEMBER_G and W_DOMAIN_MEMBER_G_TL. |
Data warehouse |
|
W_DOMAIN_MEMBER_G_TL |
Stores translated values for each installed language corresponding to the domain member codes in W_DOMAIN_MEMBER_G_TL. |
Data warehouse |
|
W_DOMAIN_MEMBER_GS |
Stores all the domain members and value for each installed language. |
Data warehouse |
|
W_DOMAIN_MEMBER_MAP_G |
Used at ETL run time to resolve at target domain code base on the value of a source domain code. |
Data warehouse |
|
W_DOMAIN_MAP_NUM_G |
Used at ETL run time to resolve a target domain code based on the comparison of a numeric value within the source numeric range. |
Data warehouse |
The Oracle Business Analytics Warehouse uses a standard prefix to indicate fields that must contain specific values, as shown in Table 3-5.
The Oracle Business Analytics Warehouse uses suffixes to indicate fields that must contain specific values, as shown in Table 3-6.
Table 3-6 Standard Column Suffixes
| Suffix | Description | In Table Types |
|---|---|---|
|
_CODE |
Code field. |
_D, _DS, _FS, _G, _GS |
|
_DT |
Date field. |
_D, _DS, _FS, _G, _DHL, |
|
_ID |
Correspond to the _WID columns of the corresponding _F table. |
_FS, _DS |
|
_FLG |
Indicator or Flag. |
_D, _DHL, _DS, _FS, _F, _G, _DHLS |
|
_WID |
Identifier generated by Oracle Business Intelligence linking dimension and fact tables, except for ROW_WID. |
_F, _A, _DHL |
|
_NAME |
A multi-language support column that holds the name associated with an attribute in all languages supported by the data warehouse. |
_TL |
|
_DESCR |
A multi-language support column that holds the description associated with an attribute in all languages supported by the data warehouse |
_TL |
Oracle Business Analytics Warehouse tables contain system fields. These system fields are populated automatically and should not be modified by the user. Table 3-7 lists the system columns used in data warehouse dimension tables.
Table 3-7 System Columns Used in Data Warehouse Tables
| System Column | Description |
|---|---|
|
ROW_WID |
Surrogate key to identify a record uniquely. |
|
CREATED_BY_WID |
Foreign key to the W_USER_D dimension that specifies the user who created the record in the source system. |
|
CHANGED_BY_WID |
Foreign key to the W_USER_D dimension that specifies the user who last modified the record in the source system. |
|
CREATED_ON_DT |
The date and time when the record was initially created in the source system. |
|
CHANGED_ON_DT |
The date and time when the record was last modified in the source system. |
|
AUX1_CHANGED_ON_DT |
System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table. |
|
AUX2_CHANGED_ON_DT |
System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table. |
|
AUX3_CHANGED_ON_DT |
System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table. |
|
AUX4_CHANGED_ON_DT |
System field. This column identifies the last modified date and time of the auxiliary table's record that acts as a source for the current table. |
|
DELETE_FLG |
This flag indicates the deletion status of the record in the source system. A value of Y indicates the record is deleted from the source system and logically deleted from the data warehouse. A value of N indicates that the record is active. |
|
W_INSERT_DT |
Stores the date on which the record was inserted in the data warehouse table. |
|
W_UPDATE_DT |
Stores the date on which the record was last updated in the data warehouse table. |
|
DATASOURCE_NUM_ID |
Unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, it is recommended that you define separate unique source IDs for each of your different source instances. |
|
ETL_PROC_WID |
System field. This column is the unique identifier for the specific ETL process used to create or update this data. |
|
INTEGRATION_ID |
Unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts. |
|
TENANT_ID |
Unique identifier for a tenant in a multi-tenant environment. This column is typically be used in an Application Service Provider (ASP)/Software as a Service (SaaS) model. |
|
X_CUSTOM |
Column used as a generic field for customer extensions. |
|
CURRENT_FLG |
This is a flag for marking dimension records as "Y" in order to represent the current state of a dimension entity. This flag is typically critical for Type II slowly changing dimensions, as records in a Type II situation tend to be numerous. |
|
EFFECTIVE_FROM_DT |
This column stores the date from which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source. |
|
EFFECTIVE_TO_DT |
This column stores the date up to which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source. |
|
SRC_EFF_FROM_DT |
This column stores the date from which the source record (in the Source system) is effective. The value is extracted from the source (whenever available). |
|
STC_EFF_TO_DT |
This column stores the date up to which the source record (in the Source system) is effective. The value is extracted from the source (whenever available). |
Table 3-8 lists the currency codes and rates for related system columns.
Table 3-8 Currency Codes and Rates for Related System Columns
| System Column | Description |
|---|---|
|
DOC_CURR_CODE |
Code for the currency in which the document was created in the source system. |
|
LOC_CURR_CODE |
Usually the reporting currency code for the financial company in which the document was created. |
|
GRP_CURR_CODE |
The primary group reporting currency code for the group of companies or organizations in which the document was created. |
|
LOC_EXCHANGE_RATE |
Currency conversion rate from the document currency code to the local currency code. |
|
GLOBAL1_EXCHANGE_RATE |
Currency conversion rate from the document currency code to the Global1 currency code. |
|
GLOBAL2_EXCHANGE_RATE |
Currency conversion rate from the document currency code to the GLOBAL2 currency code. |
|
GLOBAL3_EXCHANGE_RATE |
Currency conversion rate from document currency code to the GLOBAL3 currency code. |
|
PROJ_CURR_CODE |
Code used in Project Analytics that corresponds to the project currency in the OLTP system. |
It is possible for various dimensions to have one-to-many and many-to-many relationships with each other. These kinds of relationships can introduce problems in analyses. For example, an Opportunity can be associated with many Sales Representatives and a Sales Representative can be associated with many Opportunities. If your analysis includes both Opportunities and Sales Representatives, a count of Opportunities would not be accurate because the same Opportunity would be counted for each Sales Representative with which it is associated.
To avoid these kinds of problems, the Oracle Business Analytics Warehouse reflects the primary member in the "many" part of the relationship. In the example where an Opportunity can be associated with many Sales Representatives, only the Primary Sales Representative is associated with that Opportunity. In an analysis that includes both Opportunity and Sales Representative, only a single Opportunity will display and a count of Opportunities returns the correct result.
There are a few important exceptions to this rule. The Person star schema supports a many-to-many relationship between Contacts and Accounts. Therefore, when querying the Person star schema on both Accounts and Contacts, every combination of Account and Contact is returned. The Opportunity-Competitor star schema supports a many-to-many relationship between Opportunities and Competitor Accounts, and the Campaign-Opportunity star schema supports a many-to-many relationship between Campaigns and Opportunities. In other star schemas, however, querying returns only the primary account for a given contact.
Oracle BI Applications provides multi-language support for metadata level objects exposed in Oracle BI Enterprise Edition dashboards and reports, as well as data, which enables users to see records translated in their preferred language. For more information about multi-language support, see Chapter 2, "About Multi-Language Support."
For information about setting up currencies, refer to the following task in Functional Setup Manager: Common Areas and Dimensions Configurations\ Configure Global Currencies.
The Oracle Business Analytics Warehouse supports the following currency preferences.
Contract currency. The currency used to define the contract amount. This currency is used only in Project Analytics.
CRM currency. The CRM corporate currency as defined in the Fusion CRM application. This currency is used only in CRM Analytics applications.
Document currency. The currency in which the transaction was done and the related document created.
Global currency. The Oracle Business Analytics Warehouse stores up to three group currencies. These need to be pre-configured so as to allow global reporting by the different currencies. The exchange rates are stored in the table W_EXCH_RATE_G.
Local currency. The accounting currency of the legal entity in which the transaction occurred.
Project currency. The currency in which the project is managed. This may be different from the functional currency. This applies only to Project Analytics.
|
 Copyright © 2010, 2012, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
