| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
 Previous |
 Next |
| Oracle® Business Intelligence Applications Installation and Configuration Guide Version 7.9.4 E10742-01 |
|
Previous |
Next |
This chapter describes concepts and techniques for customizing the Oracle Business Analytics Warehouse.
|
Note: Before implementing customizations, you should already be familiar with Informatica PowerCenter. |
This chapter includes the following main topics:
Section 6.1, "Oracle Business Analytics Warehouse Customization Scenarios"
Section 6.2, "Category 1 Customizations: Adding Columns to Existing Fact or Dimension Tables"
Section 6.3, "Other Types of Customizations Requiring Special Handling"
Section 6.4, "Category 2 Customizations: Adding Additional Tables"
Section 6.13, "Configuring the Domain Value Set with CSV Worksheet Files"
Section 6.14, "Configuring the Domain Value Set Using PowerCenter Designer"
In customizing the Oracle Business Analytics Warehouse, various scenarios are available based on the type of your data source:
Packaged applications (for example, Siebel CRM or Oracle EBS) use prepackaged adapters.
Non-packaged data sources use the Universal adapter.
The figure below shows the categories of supported customization scenarios, based on the data source.
For detailed information about tables and naming conventions, see Oracle Business Analytics Warehouse Data Model Reference.
Figure 6-1, "Supported customizations based on data source" shows the following categories of customization:
Category 1. In a Category 1 customization, you add additional columns from source systems that have pre-packaged adapters and load the data into existing data warehouse tables.
Category 2. In a Category 2 customization, you use prepackaged adapters to add new fact or dimension tables to the data warehouse. Category 2 customizations normally require that you build new SDE and SIL mappings.
Category 3. In a Category 3 customization, you use the Universal adapter to load data from sources that do not have pre-packaged adapters.
One of the most difficult aspects about working with customizations is handling the customizations at the time of an upgrade. Informatica does not provide a 'diff-merge' capability that would automatically detect changes introduced by customers and add them into upgraded mappings. Therefore, customizations must be reapplied manually to upgraded mappings. Oracle BI Applications attempts to minimize the amount of effort required to reapply customizations after an upgrade. As long as the customization methodology is followed, the effort at upgrade time should be minimal and in many cases there may be no manual effort required at all.
This section includes the following topics:
Section 6.2.3, "Typical Steps to Extend Mappings in the Oracle Business Analytics Warehouse"
Section 6.2.4, "Scenario for Extending the Oracle Business Analytics Warehouse"
Category 1 customizations involve extracting additional columns from source systems for which pre-packaged adapters are included (for example, Siebel or Oracle) and loading the data into existing data warehouse tables. For Category 1 customizations, data can also come from non-packaged sources, but this section assumes that the sources have already been mapped with a Universal adapter and only need to be extended to capture additional columns. (The initial mapping of a Universal adapter is considered a Category 3 customization. For information, see Section 6.5, "Category 3 Customizations: Adding New Data as a Whole Row into a Standard Dimension Table".)
In order to see additional columns in the data warehouse, the columns must first be passed through the ETL process. The existing mappings and tables are extensible. Sample placeholders demonstrate how to pass and store additional data. Oracle BI Applications provides a methodology to extend preconfigured mappings to include these additional columns and load the data into existing tables.
Oracle BI Applications recognizes two types of customization: extension and modification. The supported extension logic allows you to add to existing objects. For example, you can extract additional columns from a source, pass them through existing mappings, and populate new columns added to an existing table. Generally, Oracle BI Applications does not allow you to modify existing logic or columns. You should not change existing calculations to use different columns, and you should not remap existing columns to be loaded from different sources.
For example, if you want to calculate revenue differently from the existing logic, you should create a new transformation and connect that calculation to a new column, for example, X_REVENUE. You can then remap the Oracle Business Intelligence repository to point to the new X_REVENUE column.
Most mappings have a single placeholder column, named X_CUSTOM, that marks a safe path through the mapping. All extension logic should follow the same route through the mapping as X_CUSTOM. You can add additional transformations to the mapping, but they should follow the same route through the mapping as X_CUSTOM.
In the following figure, the preconfigured logic is shaded in gray. You should not modify anything contained within these objects. You should add customizations to the existing mapping, which allows them to run parallel to the existing logic.
Because some objects need to be modified in order to allow for extensions, Oracle BI Applications breaks down extensions into the following categories:
Exposed objects. These objects allow changes, but the changes must be in the form of extensions (additive), and must never modify existing preconfigured logic. These objects are included in the mapping when shipped and are usually sources, targets, and nonreusable transformations.
Encapsulated objects. These objects cannot be extended. They attempt to hide as much of the shipped transformation logic as possible to prevent breaking the preconfigured logic. These objects are included in the mapping when shipped and are usually mapplets and reusable transformations.
Custom objects. You add custom objects to a mapping. (They are not shipped by Oracle.) Custom objects can be sources, transformations (reusable and nonreusable), or mapplets. Reusable transformations and mapplets that are shipped are considered encapsulated objects, but when you add such objects to an existing mapping, they are considered custom objects to that particular mapping. For example, if you want to add another amount to a fact table and that amount needs to be converted from its original currency to the data warehouse currency, you would normally add the existing Currency Exchange mapplet to the mapping to convert this new amount. In this case, the mapplet is considered a custom object to this particular mapping; however, it is also encapsulated, so the internal logic must not be changed.
|
Note: Targets should not be added to a mapping. |
When upgrading, you will deploy customized mappings on an individual basis. Only the actual mappings that have changed will be applied in your existing environment. This means any mappings that have not changed will not be affected, so any customizations made to these mappings remain. Only the mappings that have actually changed will require some work to reapply customizations. If you follow the recommended approach, the amount of work required to reapply customizations should be minimal.
By encapsulating the logic as much as possible, any changes made to the preconfigured logic can be switched as either part of a patch release or upgrade without impacting any extension logic, as shown in the following figure.
If there is a change to an exposed object, the new logic will always take precedence over the extension logic. However, rather than losing all of the extensions, much of the extension logic is retained and only has to be reapplied to the exposed objects. For example, if you add an additional column from the source and load it into the target, during an upgrade, the upgraded mapping brings additional columns from the source and loads them into the target.
The source and target are completely replaced so any extensions to these are lost in Informatica (note that the columns will still exist in the database). However, the extension logic itself still exists after the upgrade. The source and target must be re-extended and then reconnected to the extension logic.
If you extend a mapping and the mapping…
Does not change during the upgrade, all extensions are retained.
Experiences changes to the encapsulated logic, all extensions are retained.
Experiences changes to the exposed objects, extensions to these objects are lost but the underlying extension logic is retained. Extensions to exposed objects must be manually reapplied.
Encapsulated objects must never be customized unless directed by Oracle. Encapsulated objects are usually mapplets and reusable transformations.
Exposed objects can be extended but must never be otherwise modified. Exposed objects may be completely replaced at upgrade.
Custom objects are never changed during an upgrade.
To minimize the work required for upgrading, try to minimize the number of changes to exposed objects by using custom objects. For example, rather than adding a table to the Source Qualifier to bring in a column from a related table, add a lookup to that table in the mapping.
In customizing objects, you must evaluate the options and determine the best approach for your environment. If you find the custom object approach allows the ETL to run in an acceptable amount of time, then this is the preferred approach. If the custom object causes the ETL process to take too long, you may want to consider incorporating the extension into an exposed object.
When you add custom columns to the Oracle Business Analytics Data Warehouse, you do not need to manually add change capture views. The DAC automatically creates change capture views for all columns (including new columns) at runtime.
|
Note: Most SDE adapter folders use the concept of Business Component mapplets. These are extract mapplets that may contain relational, application, or flat file sources. The Siebel adapter folders do not use Business Component mapplets; the sources are exposed directly in the mapping. Usually, the Business Component mapplet can be treated as an exposed object and is the only mapplet object that should be modified. |
The most common scenario for extending the data warehouse is to extract existing columns from a source and pass them through to an existing data warehouse table (either fact or dimension). This type of change generally requires extending an SIL mapping. If the data is coming from a packaged source, then you will also need to extend an appropriate SDE adapter mapping. If the data is coming from a non-packaged source, then you must use a Universal adapter mapping. (You will need to create a Universal adapter mapping if an appropriate one does not already exist).
To extend a mapping in the Oracle Business Analytics Warehouse
Copy the mapping to a custom folder.
Extend the source and target tables by making changes to the tables in the database. You then import the source and target definitions into the custom folder (which replaces the existing definitions) or manually edit the existing definition.
As a best practice, Oracle recommends that you rename custom columns with a "X_" prefix to make it easier to distinguish custom columns that have been added to an existing table and to ensure there are no name conflicts with any columns Oracle may add later to that table.
Extend the SDE mapping by bringing in the additional columns.
Either modify the Source Qualifier (which is an exposed object) to include the column in the SQL Override or add a lookup (which is a custom object).
Connect objects to the placeholder transformation 'Exp_Custom'.
Connect placeholder transformations to the target table.
Extend the SIL mapping by bringing in the additional columns.
Modify the Source Qualifier (which is an exposed object) to include the column in the SQL Override.
Add the column to the Source Qualifier and pass it through the filter to the 'Exp_Custom' transformation, to the Update strategy, and to the target.
Copy the workflow to the custom folder.
Update the DAC with the necessary changes.
In this scenario, data is passed from an existing source to an existing data warehouse table. The company in this example has identified additional fields in a base table that need to be added to the data warehouse table W_ORG_D. The company used an extension field to capture information related to organizations referred to as 'ACCOUNT_LOG.' In addition, the company wants to include the name of the person who last updated the record as an attribute of the organization.
This scenario is applied to examples with the different source types that are supported: Prepackaged adapters for Siebel, non-Siebel prepackaged application sources, and non-packaged data.
This section includes the following topics:
Section 6.2.4.1, "Tips for Modifying the SQL Override in a Source Qualifier"
Section 6.2.4.2, "Example of Extracting Data from a Siebel Source"
Section 6.2.4.3, "Including a Source Table for the Change Capture Process"
Section 6.2.4.4, "Example of Extracting Data from a Non-Siebel Packaged Source"
Section 6.2.4.5, "Example of Extracting Data from a Universal Source"
Section 6.2.4.6, "Example of Loading Data into an Existing Target Table"
It is very important that the connected columns in the Source Qualifier appear in the same order in the SQL Override. A common mistake is to have the ports appear in the Source Qualifier in a different order than in the SQL Override.
The column in the SELECT clause must reference the aliased name of the table if an alias is used. In the example Section 6.2.4.2, "Example of Extracting Data from a Siebel Source", the actual source for the new column LOGIN is S_CONTACT but the SELECT clause references the alias LAST_UPDATE_BY.
Make sure you include a comma before new columns in a SELECT clause or before a new table in a FROM clause.
A new table should always be defined using LEFT OUTER join syntax. Do not use INNER join or RIGHT OUTER join syntax, because you could lose records as a result.
Make sure you define joins to match on a unique set of values. If you do not define a join that ensures a unique relationship, you may get a cartesian product, which changes the granularity and will result in duplicate errors downstream. If you cannot define a unique join, then you should bring the data in with a Lookup Transformation, which guarantees that at most one record will be returned.
As a best practice, you should comment custom code you have introduced. Comments should include at least the developer's name and the date the code was added.
In this example, the company is using Siebel Business Analytics version 7.8 and has used the S_ORG_EXT_X.ATTRIB_04 field in the S_ORG_EXT extension table to capture data related to 'ACCOUNT_LOG'. The name of the last person to update the record is retrieved by joining to the S_CONTACT table on S_ORG_EXT.LAST_UP_BY.
|
Note: The Siebel adapter folders do not use Business Component mapplets; the sources are exposed directly in the mapping. |
To extract data from a Siebel source
Create a new folder named CUSTOM_SDE_SBL_78_adapter.
Copy the SDE_OrganizationDimension mapping and workflow to this folder.
Edit the target definition W_ORG_DS to include the following columns:
| Column Name | Data Type |
|---|---|
| X_ACCOUNT_LOG | VARCHAR2(10) |
| X_LAST_LOGIN | VARCHAR2(10) |
|
Note: If the source table has been customized, it would be necessary to re-import the source table into the custom folder, replacing the existing version. For this example, the source table has not changed. |
The extension table S_ORG_EXT_X is already joined in this mapping. Drag the ATTRIB_04 column from the Source Definition to the Source Qualifier. The column should appear after the X_CUSTOM column.
Since S_CONTACT is not already joined for the last update person, drag a copy of S_CONTACT into the mapping. (Delete the new Source Qualifier associated with this source if one exists.)
As a best practice, you should rename the source to indicate what it represents. In this case, rename the source to S_CONTACT_LAST_UPDATE_BY.
Drag the LOGIN column from the Source Definition to the Source Qualifier.
Drag ATTRIB_04 and LOGIN to the EXP_Custom Expression.
As a best practice, rename these ports to indicate both the table and column they come from.
In case the mapping is changed and the related exposed objects are replaced, this will make it easier to reconnect because the custom expression will not be replaced.
Connect the appropriate ports to the target definition
Edit the SQL override in the Source Qualifier.
Add the ATTRIB_04 and LOGIN columns immediately after X_CUSTOM in the SELECT clause.
Add the table to the FROM clause.
Add the join criteria.
The Siebel application can run on various database platforms. To maintain database independence, you should write the SQL using Informatica's join syntax, which will automatically be translated to the appropriate database syntax when run. When adding tables, be sure to follow the Informatica syntax when defining the join.
Below is an example of the SQL with the modified code in bold font.
S_ADDR_ORG.LAST_UPD, S_ORG_EXT_T.LAST_UPD, 0 AS X_CUSTOM - Added by J.Smith on 1/10/2007 ,S_ORG_EXT_X.ATTRIB_04 ,LAST_UPDATE_BY.LOGIN FROM V_ORG_EXT S_ORG_EXT, S_ORG_EXT BU_NAME, ... S_ORG_EXT_T, S_ORG_EXT_X, S_ADDR_ORG, … S_MKT_SEG PRTNR_MKTSEG, S_MKT_SEG TRGT_MKTSEG -Added by J.Smith on 1/10/2007 ,S_CONTACT LAST_UPDATE_BY WHERE { V_ORG_EXT S_ORG_EXT LEFT OUTER JOIN S_ORG_EXT_X ON S_ORG_EXT.ROW_ID = S_ORG_EXT_X.PAR_ROW_ID … LEFT OUTER JOIN S_MKT_SEG TRGT_MKTSEG ON ORG.PR_ORG_TRGT_MKT_ID = TRGT_MKTSEG.ROW_ID - Added by J.Smith on 1/10/2007 LEFT OUTER JOIN S_CONTACT LAST_UPDATE_BY ON S_ORG_EXT.LAST_UPD_BY = LAST_UPDATE_BY.ROW_ID }
Save your changes.
Refresh and validate the session in the Workflow Manager.
This is necessary because it is possible that changes made to the mapping may invalidate the session.
If you are bringing in data from a new table that was not previously included in an existing SDE mapping, it may be necessary to create an auxiliary change capture mapping so that when a row changes in the new table, the corresponding row in the main table is marked as changed. If you do not create an auxiliary process, it is possible that the new column from the new table is changed but if the base table does not experience any changes, this event will not be picked up. Keep in mind that auxiliary processing can have a negative impact on ETL performance. So, if it is not necessary to flag the main record as changed when there is a change in the related table, you may want to avoid building this mapping.
In this example, the company is using Oracle Applications version 11.5.8 and has used the HZ_CUST_ACCOUNTS.ATTRIBUTE1 field to capture data related to 'ACCOUNT_LOG.' The name of the last person to update the record is already stored in the HZ_CUST_ACCOUNTS.LAST_UPDATE_LOGIN field. There is no need to join to any additional tables.
Because Oracle Applications always run on an Oracle database, you do not need to use Informatica's SQL syntax when defining joins in a SQL Override. If you need to add additional tables, you can define the join using standard Oracle syntax.
If you add another table as a source, in addition to defining the join, you must also include the table's 'LAST_UPDATE_DATE' in the WHERE clause using the following syntax:
OR TABLE_NAME.LAST_UPDATE_DATE > TO_DATE('$$LAST_EXTRACT_DATE', 'MM/DD/YYYY HH24:MI:SS')
)
AND
…
This ensures that changes to a record in that table will trigger an extract. If this were the only table to have an update and the other tables were not updated, then this change would not be detected.
|
Note: Most SDE adapter folders use the concept of Business Component mapplets. These are extract mapplets that contain relational, application, or flat file sources. Generally, the Business Component mapplet can be treated as an exposed object and is the only mapplet object that should be modified. Keep in mind that you can modify exposed objects, but there is a risk that these changes may be lost at upgrade time. |
To extract data from a non-Siebel packaged source
Create a new folder named CUSTOM_SDE_ORA1158_adapter.
Copy the SDE_ORA_OrganizationDimension_Customer mapping and workflow to this folder.
Edit the target definition W_ORG_DS to include the following columns:
| Column Name | Data Type |
|---|---|
| X_ACCOUNT_LOG | VARCHAR2(10) |
| X_LAST_LOGIN | VARCHAR2(10) |
|
Note: If the source table has been customized, it would be necessary to re-import the source table into the custom folder, replacing the existing version. For this example, the source table has not changed. |
Open the mapping.
Edit the Business Component 'mplt_BC_ORA_OrganizationDimension_Customer' by right-clicking the mapplet and selecting 'Open Mapplet.'
Remember, the Business Component mapplets are the only mapplets you can normally edit. You should not edit any other mapplets unless directed by Oracle.
Drag the columns LAST_UPDATE_LOGIN and ATTRIBUTE1 to the Source Qualifier, and then drag these columns to the Mapplet Output.
Edit the Source Qualifier to include the new columns, as shown below:
SELECT … HZ_PARTIES.SIC_CODE - Added by J.Smith on 1/10/2007 , HZ_CUST_ACCOUNTS.LAST_UPDATE_LOGIN , HZ_CUST_ACCOUNTS.ATTRIBUTE1 FROM HZ_CUST_ACCOUNTS, HZ_PARTIES WHERE …
Return to the mapping.
Add a new expression and rename it to 'X_CUSTOM.'
Connect the new columns from the Business Component mapplet to this expression.
As a best practice, you should rename these ports to indicate both the table and column they came from. If the mapping is changed and the related exposed objects are replaced, this will make it easier to reconnect, because the custom expression will not be replaced
Connect these columns to the appropriate columns in the target definition.
Save your changes.
Refresh and validate the session in the Workflow Manager.
This is necessary because it is possible that changes made to the mapping may invalidate the session.
In this example, the company has a legacy mainframe with data they would like to incorporate into the data warehouse. This requires that data be preformatted to match the source definition for FILE_ORG. Since the preexisting source definition does not include columns for the additional data the company desires, the source definition must be modified to include these columns.
|
Note: The Universal adapter folders do not use Business Component mapplets; the sources are exposed directly in the mapping. |
To extract data from a Universal source
Create a new folder named CUSTOM_SDE_Universal_adapter.
Copy the SDE_Universal_OrganizationDimension mapping and workflow to this folder.
Edit the source definition to include the following columns:
| Column Name | Data Type |
|---|---|
| ACCOUNT_LOG | String(10) |
| LAST_LOGIN | String(10) |
Edit the target definition W_ORG_DS to include the following columns:
| Column Name | Data Type |
|---|---|
| X_ACCOUNT_LOG | VARCHAR2(10) |
| X_LAST_LOGIN | VARCHAR2(10) |
Open the mapping.
Drag the columns LAST_UPDATE_LOGIN and ATTRIBUTE1 to the Source Qualifier.
Add a new expression and rename it to 'EXP_CUSTOM.'
Connect the new columns from the Source Qualifier to this expression.
Connect these columns to the appropriate columns in the target definition.
Save your changes.
Refresh and validate the session in the Workflow Manager.
This is necessary because it is possible that changes made to the mapping may invalidate the session.
Once the required data has been extracted and staged, it must be loaded into an existing target table in the data warehouse.
To load data into an existing target table in the data warehouse
Create a new folder named CUSTOM_SILOS.
Copy the SIL_OrganizationDimension mapping and workflow to this folder.
Edit the source definition W_ORG_DS to include the following columns:
| Column Name | Data Type |
|---|---|
| X_ACCOUNT_LOG | VARCHAR2(10) |
| X_LAST_LOGIN | VARCHAR2(10) |
Edit the target definition W_ORG_D to include the following columns:
| Column Name | Data Type |
|---|---|
| X_ACCOUNT_LOG | VARCHAR2(10) |
| X_LAST_LOGIN | VARCHAR2(10) |
Open the mapping.
Drag the columns X_ACCOUNT_LOG and X_LAST_LOGIN to the Source Qualifier.
Drag the columns X_ACCOUNT_LOG and X_LAST_LOGIN from the Source Qualifier to the Filter.
Normally, existing transformations should not be modified. Filters are active transformations, and it is not possible to route data around an active transformation and bring it back to the same data flow. In this case, the filter is considered an exposed object and may be modified, but any changes are at risk of being lost at upgrade time.
Drag the columns X_ACCOUNT_LOG and X_LAST_LOGIN from the Filter to the Expression EXP_Custom. If you need to apply any transformations, you should do so in this expression.
Drag the columns X_ACCOUNT_LOG and X_LAST_LOGIN from the Expression to the Update Strategy.
The Update Strategy is another active transformation and is, therefore, considered an to be an exposed object, just like the Filter.
Connect these columns to the appropriate columns in the target definition.
Save your changes.
Refresh and validate the session in the Workflow Manager.
This is necessary because it is possible that changes made to the mapping may invalidate the session.
After making these changes to the mappings, you need to register the changes in the DAC. You need to include the table definition, with any additional columns or indexes, and the required changes so the tasks execute the modified sessions in the new custom folders. For information about registering data warehouse objects in the DAC, see the Oracle Business Intelligence Data Warehouse Administration Console Guide.
This section includes the following topics:
All dimensions are configured as Category 1 dimensions when first installed. If you want to capture historical changes, the dimension must be enabled to capture Category 2 changes. However, a common form of customization is to change the criteria that triggers a Category 2 change in a dimension. Most changes in a dimension are treated as Category 1 changes in that the existing column is simply overwritten with the new value. Once enabled, there are only a small number of columns that will trigger a Category 2 change. You can extend the logic that triggers a Category 2 change by adding additional columns to the logic that tracks Category 2 changes. In addition, you can remove columns from this logic in case you do not want these types of changes to trigger a Category 2 change. Modifying the Category 2 tracking logic is one of the only exceptions to the rule that you should not make changes to shipped logic. The logic that tracks Category 2 changes is contained in exposed objects in each SIL dimension mapping that supports Category 2 changes.
There is a lookup between the Source Qualifier and the Filter. This lookup is used to determine if the record already exists in the target and, therefore, needs to be updated in addition to other system columns. Columns that track Category 2 changes are returned in this lookup and passed to the next expression. The columns returned by the lookup are compared with the columns passed from the staging table. If any of these columns are different, the record is flagged for a Category 2 change.
This expression contains a variable port named 'TYPE2_COLS_DIFF'. If this port is flagged as 'Y' then a Category 2 change will be triggered. If it is flagged as 'N' then a Category 1 change will be triggered.
To change the columns used to determine a Category 2 change, modify the lookup to pass any additional columns you want to be evaluated for Category 2 changes. Then, modify the variable port 'TYPE2_COLS_DIFF' to include this column when being evaluated.
For example, the SIL_BOMHeaderDimension mapping compares the following columns:
BOM_HEADER
BOM_VERSION
BASE_QTY
ACTIVE_FLG
If you wanted to include BOM_VERSION as part of Category 2 logic, you would change the logic for 'TYPE2_COLS_DIFF' from the following:
IIF(BOM_NUMBER != LKP_BOM_NUMBER, 'Y', IIF(BOM_VERSION != LKP_BOM_VERSION, 'Y', IIF(BASE_QTY != LKP_BASE_QTY, 'Y', IIF(ACTIVE_FLG != LKP_ACTIVE_FLG, 'Y', 'N'))))
To this:
IIF(BOM_NUMBER != LKP_BOM_NUMBER, 'Y', IIF(BOM_VERSION != LKP_BOM_VERSION, 'Y', IIF(BASE_QTY != LKP_BASE_QTY, 'Y', IIF(ACTIVE_FLG != LKP_ACTIVE_FLG, 'Y', IIF(BOM_VERSION!= LKP_ BOM_VERSION, 'Y', 'N')))))
This section covers adding a dimension (preexisting or custom) to an existing fact. It assumes you have already built the required process to populate this dimension.
This process involves extending both the fact staging table and the fact data warehouse table to include the new column. In Informatica, remember to define the tables using the Oracle database type. The staging table should be defined as a varchar2(80) field and named with in _ID suffix. The data warehouse table column should be defined as an integer and named with a _WID suffix.
The SDE fact mapping must be modified to pass through the unique identifier of the dimension key. This assumes that there is some relationship between the base table and this unique identifier. It may already be stored in the base table or stored by joining to a related table. Depending on the source system, this identifier may be based on a single column or derived from multiple columns. Table 6-1 depicts various formats used to derive the INTEGRATION_ID, which is used to identify a dimension key. The INTEGRATION_ID value should be passed to the fact staging table.
Table 6-1 Formats to Derive INTEGRATION_ID
| Dimension | Foreign Key | When Source is Oracle Application | When Source is Siebel Application |
|---|---|---|---|
|
W_AP_TERMS_D |
TO_CHAR(TERM_ID) |
Not applicable |
|
|
W_BUSN_LOCATION_D |
ASSET_LOC_WID |
ASSET_LOC~' || LOCATION_ID |
Not applicable |
|
W_BUSN_LOCATION_D |
EMP_LOC_WID |
EMP_LOC~' || LOCATION_ID |
Not applicable |
|
W_BUSN_LOCATION_D |
INVENTORY_LOC_WID |
STORAGE_LOC' || '~' || ORGANIZATION_ID || '~' || SUBINVENTORY_CODE || '~' || INVENTORY_LOCATION_ID |
Not applicable |
|
W_BUSN_LOCATION_D |
PLANT_LOC_WID |
'PLANT' || '~' || TO_CHAR(ORGANIZATION_ID) |
Not applicable |
|
W_BUSN_LOCATION_D |
RECEIVING_LOC_WID |
'RECIPIENT_LOC' || '~' || TO_CHAR(LOCATION_ID) |
Not applicable |
|
W_BUSN_LOCATION_D |
STORAGE_LOC_WID |
'STORAGE_LOC' || '~' || ORGANIZATION_ID || '~' || SECONDARY_INVENTORY_NAME || '~' |
Not applicable |
|
W_CUSTOMER_FIN_PROFL_D |
CUSTOMER_FIN_PROFL_WID |
P'||'~'||TO_CHAR(CUSTOMER_ID) ||'~' || TO_CHAR(SITE_USE_ID) ||'~' || CURRENCY_CODE - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS and CURRENCY_CODE is from HZ_CUST_PROF_CLASS_AMTS |
Not applicable |
|
W_CUSTOMER_LOC_D |
To get the customer location key, look up W_CUSTOMER_LOC_USE_D as below |
Not applicable |
|
|
W_CUSTOMER_LOC_USE_D |
TO_CHAR(SITE_USE_ID) - Get Site Use Id from HZ_CUST_ACCOUNT_ROLES |
Not applicable |
|
|
W_FREIGHT_TERMS_D |
LOOKUP_CODE |
Not applicable |
|
|
W_GL_ACCOUNT_D |
to_char(ccid) |
Not applicable |
|
|
W_INT_ORG_D |
COMPANY_ORG_KEY |
COMPANY'||'~'||TO_CHAR(SET_OF_BOOKS_ID) |
S_ORG_EXT.ROW_ID |
|
W_INT_ORG_D |
*_ORG_KEY |
Remove any prefixes and use TO_CHAR() |
S_ORG_EXT.ROW_ID |
|
W_ORG_D |
CUSTOMER_WID |
TO_CHAR(CUSTOMER_ID) - CUSTOMER_ID is CUST_ACCOUNT_ID from HZ_CUST_ACCOUNTS |
UNION OF S_ORG_EXT AND S_CONTACT. When source is S_ORG_EXT, ROW_ID is passed. When source is S_CONTACT, use ''C-'||ROW_ID - the ROW_ID is the ROW_ID from the S_PERSON (not from S_ORG_EXT). This is the new value passed to lookup the Contact Customers in W_ORG_D for resolving the ACCOUNT_WID (read as CUSTOMER_WID) |
|
W_PAYMENT_METHOD_D |
LOOKUP_CODE |
Not applicable |
|
|
W_PAYMENT_METHOD_D |
TO_CHAR(TERM_ID) |
Not applicable |
|
|
W_PERSON_D |
CUST_CONTCT_WID |
TO_CHAR(PARTY_ID) - PARTY_ID from HZ_PARTY_RELATIONS |
S_CONTACT.ROW_ID |
|
W_PRODUCT_D |
PRODUCT_WID |
TO_CHAR(INVENTORY_ITEM_ID) |
S_PROD_INT.ROW_ID |
|
W_SALES_PRODUCT_D |
TO_CHAR(INVENTORY_ITEM_ID) ||'~'||TO_CHAR(ORGANIZATION_ID) |
Not applicable |
If you are adding an existing dimension, the SIL mapping should be extended to include the preexisting reusable Lookup transformation to that dimension. Pass the dimension's INTEGRATION_ID through the mapping along the path identified by the X_CUSTOM column and connect it to the Lookup after the Filter transformation. Also, connect the DATASOURCE_NUM_ID to the Lookup. If the dimension is a slowly changing dimension, the fact table's standard or 'canonical' date should be passed to the lookup as well, even if the dimension has not been enabled to capture Category 2 changes.
Remember to connect the ROW_WID of the Lookup to the X_CUSTOM transformation and include logic to default this value to 0 if no record is returned from the Lookup. Pass this column on to the Update strategy, and then on to the target.
Update the DAC to include the foreign key to this dimension in the fact table's definition. You should reassemble the subject Area and rebuild the execution plan to ensure that the DAC populates this dimension table before this fact table starts to load.
If adding a date dimension to a fact table, you merely have to pass the date itself through the SDE mapping to the stage table. In the SIL mapping, pass the date along the same path as X_CUSTOM. Add the reusable expression EXP_DAY_DIMENSION_FK_RESOLUTION after the Filter. Connect the date to any input and connect the appropriate output to the EXP_Custom transformation, then on to the Update Strategy and finally to the target.
Amounts must be converted from the original currency to the data warehouse currency. Along with the amount, you must pass the currency code if it is not already connected in the mapping. Depending on the source system, there may be more than one currency code
If the source is Siebel, there is generally only one currency type
Other sources may have several currency types. Be sure to read the section on configuring currency codes to get a better understanding of how these work.
If the SIL mapping does not already include it, add the mapplet MPLT_CURCY_CONVERSION_RATES after the Filter and connect all required input ports.
Connect the appropriate exchange rate(s) to the EXP_Custom expression. Use the appropriate exchange rate to convert the amount to the data warehouse currency. Pass the converted currency to the Update strategy then onto the target.
This section includes the following topics:
Section 6.4.1, "About Creating New Dimension or Fact Tables"
Section 6.4.2, "Using Custom Folders in the Oracle Business Analytics Warehouse"
Section 6.4.4, "Important Notes About Customizing the Oracle Business Analytics Warehouse"
Section 6.4.5, "Including a Source Table for Change Capture Process"
Section 6.4.6, "Adding a New Dimension in the Oracle Business Analytics Warehouse"
Section 6.4.7, "Adding a New Fact Table in the Oracle Business Analytics Warehouse"
This section relates to building entirely new tables that will be loaded with data from a source table that is not already extracted from. For example, you may want to create a new "Project" dimension table. In this case, you create new dimension and staging tables as well as new extract and load ETL mappings.
When creating a new custom table, use the prefix "WC_" to help distinguish custom tables from tables provided by Oracle as well as to avoid naming conflicts in case Oracle later releases a table with a similar name. For example, for your "Project" dimension you may create a "WC_PROJECT_DS" and a "WC_PROJECT_D" table.
When you create a new dimension or fact table, use the required system columns that are part of each of the data warehouse tables to maintain consistency and the ability to reference existing table structures. When you create a new table, you need to register the tables and indices in the DAC. You will also have to register in the DAC the new tasks for new Informatica workflows and then reassemble the appropriate subject area and rebuild the appropriate execution plan. For information about assembling subject areas and building execution plans, see the Oracle Business Intelligence Data Warehouse Administration Console Guide.
|
Note: If you are creating tables on a DB2-UDB database, make sure when you register the tables in the DAC the Not Logged Initially option is enabled. |
For custom staging tables, the following columns are required:
INTEGRATION_ID. Stores the primary key or the unique identifier of a record as in the source table.
DATASRC_NUM_ID. Stores the data source from which the data is extracted.
For dimension and fact tables, the required columns are the INTEGRATION_ID and DATASRC_NUM_ID columns as well as the following:
ROW_WID. A sequence number generated during the ETL process, which is used as a unique identifier for the data warehouse.
ETL_PROC_WID. Stores the ID of the ETL process information. The details of the ETL process are stored in the W_ETL_RUN_S table on the data warehouse side. This is also the Process ID on Current Run/Run History screen in the DAC.
All the tables in the Oracle Business Analytics Warehouse schema have DATASRC_NUM_ID as part of their unique user key. While the transactional application normally ensures that a primary key is unique, it is possible that a primary key is duplicated between transactional systems. To avoid problems when loading this data into the data warehouse, uniqueness is ensured by including the DATASOURCE_NUM_ID as part of the user key. This means that the rows can be loaded in the same data warehouse tables from different sources if this column is given a different value for each data source.
|
Note: The DATASRC_NUM_ID is maintained by the DAC. Make sure that each source system has a unique value assigned. It is possible to have multiple instances of the same source system (for example, a U.S.-based and a European-based Siebel transactional database both loading into the same data warehouse). The two different transactional database systems should be assigned different DATASOURCE_NUM_ID values in the DAC. The DAC is predefined with one entry for Siebel and the DATASOURCE_NUM_ID is assigned the value of 1. If you are going to extract from additional Siebel transactional database systems and load the data into the same data warehouse, a different DATASOURCE_NUM_ID must be assigned to each Siebel transactional database system. |
If you want to make changes to the Oracle Business Analytics Warehouse, you must create a custom folder and make the changes in it. Do not change objects in any of the shipped folders unless explicitly directed by Oracle. The shipped folders and the objects within them may be overwritten in future upgrades.
The deployed repository does not include any custom folders; you must create your own. You should create a custom folder for each SDE folder you have deployed that will have customizations. These folders hold the extract mappings to various sources. You should also create a separate custom folder for customizations you want to make to the SILOS folder. Do not store customized extract and load mappings in the same folder.
The easiest way to modify an object is to copy an existing object from the shipped folder into the corresponding custom folder and to reuse existing business components, source and target definitions, transformations, mapplets, and mappings.
|
Note: If source tables are extended, then the source tables require manual editing in Informatica Designer. Do not import the tables from the database into the repository, because it changes the source table definitions for the entire Oracle Business Analytics Warehouse. |
When importing new tables from the database into the custom folder, use the Oracle Business Analytics Warehouse and transactional database ODBC database connections (using the ODBC drivers supplied by your database vendor) to connect to the source and target databases.
After importing new table definitions, change the database type to Oracle in the Informatica repository, regardless of the database platform you are using. This has no effect on the choice of a relational database. This step is very important because in Informatica if the database type of the source tables are not identical, all mappings and workflows that refer to the source tables will be invalid.
You must create custom workflows for all customized mappings. The general requirements for creating custom workflows are as follows:
Create the workflow such that each loads only one table. This helps to integrate the workflow with the DAC.
The workflow name should match a session name that is used inside the workflow. This helps the DAC to collect certain statistics.
The flag "Fail parent if this task fails" should be checked for all the sessions within a workflow.
The flag "Fail parent if this task does not run" should be checked for all the sessions within a workflow.
The Stop on Errors parameter should be set to 1 for all sessions within a workflow. This parameter is located in the Error Handling area of the Config Object tab in Informatica Designer.
Set the appropriate source and target connection values in Informatica Designer.
If the workflow is going to be used for full load command, you can choose to load in bulk mode (applicable only to Oracle and DB2-UDB databases). If the workflow is going to be used for the full load command in the DAC, then in the Properties tab of Informatica Designer, set the Target Load type to Bulk. This requires that the target table does not have any indices during the load. The DAC will drop the indices automatically; no action is required on your part.
For all entities, such as dimension and fact tables, create two workflows, one to be used for a full load and the other to be used for an incremental load. Both workflows are based on the same mapping. The same mapping gets executed during both full and incremental loads. This provides an opportunity to tune each of these load scenarios.
If the workflow is designed for loading a dimension in a full mode, make sure the workflow also includes a session for the unspecified row creation.
When defining a task in the DAC, you must choose the appropriate truncate option. This helps the DAC to decide whether to drop and create indices on the table.
You should not truncate target tables using the Informatica 'truncate target' option. It is especially important that the DAC handle truncating tables when extracting from more than a single source system and loading into the same data warehouse. The DAC will dynamically determine when tables need to be truncated. By setting the truncate option in the Informatica workflow, the table will always be truncated, limiting your ability to extract and load data from multiple sources. For example, you cannot extract data from two systems and load the data into the same staging table in parallel as the Informatica session will truncate the staging table when another session is running.
If some sessions have to be run in serial order and if all the sessions need to be rerun upon failure of the workflow, then you should consider designing a single workflow that has sessions run in serial. If upon failure, not all of the sessions need to be run again, then consider designing separate workflows and define dependencies in the DAC.
The custom workflows can be plugged into an ETL process by registering them in the DAC. All new tasks need to be registered in the DAC, with the appropriate properties set. Also, you must register in the DAC, source and target tables, task definitions, and dependencies.
All custom work, unless stated otherwise, must be done in the Custom folder so that the custom work can be preserved during an Informatica repository upgrade. Doing work on the standard folder should be avoided whenever possible. An upgrade of the Informatica repository overrides any changes to the standard folder.
Table definitions in Informatica. Make sure that the SQL style is set to Oracle while importing the table definitions from external data sources. Even if the actual data source is of another database type, such as DB2 or MSSQL, it does not affect the logic of how data gets loaded.
Update strategy. For loading new fact and dimension tables, design a custom process on the source side to detect the new and modified records. The SDE process should be designed to load only the changed data (new and modified). If the data is loaded without the incremental process, the data that was previously loaded will be updated again, which is a costly process. For example, the logic in the preconfigured SIL mappings looks up the destination tables based on the INTEGRATION_ID and DATASRC_NUM_ID and returns the ROW_WID if the combination exists, in which case it updates the record. If the lookup returns null, it inserts the record instead. In some cases, last update date(s) stored in target tables are also compared in addition to the columns mentioned above to determine insert or update. Look at the similar mappings in the preconfigured folder for more details.
ETL process. When using multiple sources for the data warehouse, you can decide to load from all of them at the same time or at different time frequencies using different execution plans.
Truncating target tables. Truncating should be done through the DAC. A single task has place holders for a full load, and one for an incremental load.
For the SDE workflows, the commands for full load and incremental load are the same. They should have the Truncate Always flag checked in the DAC. For these kinds of tasks, the command for full load and incremental load are based on the same mapping.
For SIL workflows, the command can be different for full and incremental loads. They should have the Truncate For Full Load option checked in the DAC. When a table gets truncated, the indices are automatically dropped and created after the data is loaded. The workflow associated with the full load command can have the Bulk Load option turned on for an optimized version of the mapping that quickly inserts data. Note that if there are indexes on the table, the bulk load may fail, so it is very important that the indices are registered in the DAC and that you drop all of the indexes on this table during a full load if you use the bulk load option.
If a source requires an auxiliary task, it needs to be run only during incremental mode. So, for these tasks, the full load command is empty. No truncate options should be set.
ETL_PROC_WID. Use the same ETL_PROC_WID in W_PARAM_G table in custom mappings. ETL_PROC_WID is a reference key to Run History in the DAC. To use the same ETL_PROC_WID, copy the reusable lookup (called LKP_ETL_PROC_WID) defined in the SILOS folder. The input to the lookup is a constant (hard coded to 1).
DATASRC_NUM_ID. Use a parameter to define this value in the mapping. The DAC will automatically create a parameter file with the correct DATASOURCE_NUM_ID, which will be picked up by the parameter in the mapping. This allows you to make multiple copies of the same mapping when you have multiple instances of the same transactional database type. You do not have to do any additional hard-coding other than to register the sources in the DAC.
Creating indices and naming conventions. Staging tables typically do not require any indices. Use care to determine if indices are required on staging tables. Create indices on all the columns that the ETL will use for dimensions and facts (for example, ROW_WIDs of Dimensions and Facts, INTEGRATION_ID and DATASRC_NUM_ID, and flags). Carefully consider which columns or combination of columns filter conditions should exist, and define indices to improve query performance. Inspect the OTB objects for guidance. Name all the newly created tables as WC_. This helps visually isolate the new tables from the OTB tables. Keep good documentation of the customizations done; this helps when upgrading your data warehouse. Once the indices are decided upon, they should be registered in the DAC, either manually or by right-clicking on the certain table and invoking the Import Indices command.
Currency. For currency-related data, populate the table with the base currency and exchange date field (in order to convert the data appropriately). The data for currency conversion should be maintained in the main data source. (The currency data is maintained by converting all the currency information into a single Base Currency code specified in the DAC.)
Day Dimension. For the data that is related to W_DAY_D, use the reusable transformation EXP_DAY_DIMENSION_FK_RESOLUTION. Note that this transformation will take a date as input and return the foreign key to the Day dimension as output in the appropriate format (YYYYMMDD) avoiding the need of costly join or lookup to the W_DAY_D dimension table each time for resolution. Copy the reusable transformation and use it.
List Of Values. This applies in particular to Category 1 and II. The preconfigured columns that depend on a list of values have a language-dependent column and a language-independent column. Use the mapplet MPLT_LOV_TRANSLATION to populate the language-dependent and independent columns in the dimension tables. For fact tables, use MPLT_LOV_D_ROW_WID to create a new foreign key to the LOV dimension. You could also deal with translations directly in SQL overrides to improve performance.
This procedure applies to Siebel source tables only.
To include a source table for the change capture process
Verify whether the source table is registered in the DAC.
If an entry is not in the DAC, create a new record for the table and assign an image suffix to it.
If the table is registered, make sure there is an image suffix assigned for this table.
If an image suffix does not exist for the source table, assign one.
The image suffix should be three characters long. The recommended naming convention is to start with C. For example, use C1, C2, CA1, CA2, and so on.
Make sure the image suffix is not used by other tables by querying the tables list in the DAC for that image suffix.
The DAC client does not validate this information when entering data.
Create Image tables in the Siebel transactional database.
Right-click on the table record in the DAC client, and choose Generate Change Capture Scripts.
This will assist you in creating the image tables, necessary indices, and triggers if you plan on tracking deletes on the transactional database.
Execute these scripts on the transactional database if you have the appropriate permission, otherwise ask the OLTP DBA to create it.
For the task that is created for the extract process, set the Build Image flag to True and select the new table as an auxiliary table or primary table.
|
Note: At the end of the change capture process, the DAC creates a view on the actual source table. Use this view as the main source table in all the extract procedures. For example, if your new source table is S_COMPENSATION, then the default view name is V_COMPENSATION. |
Follow this procedure to add a new dimension in the Oracle Business Analytics Warehouse.
To add a new dimension and use it with an existing fact table
Create a DDL for the new dimension based on the standard structure (with appropriate system columns). Create a staging table for this dimension.
Register the new source table and its staging table (if it does not already exist) in the DAC repository and associate it with the appropriate database connection.
Create a new custom map SDE_XYZ to populate the dimension stage. Instead of the actual source table (for example S_ABC), use the view that will be generated by the change capture process (for example V_ABC) in the SQL so that it extracts only the incremental data. Use existing reference maps as examples of how to populate the system columns. Make sure you truncate the stage table in corresponding tasks.
Create a new custom map SIL_XYZ to populate the new dimension from the stage table. Use the above referenced map as example for how to populate the system columns.
Register the new dimension table in the DAC and associate it with the appropriate database connection.
If you are planning to build a new dimension incrementally, assign an image suffix to the source table.
Register the workflows as tasks in the DAC.
For SDE mapping of the dimension make sure you set the Build Image flag to True, and the Truncate Always option to True. And in the list of source tables, mark the primary/auxiliary source(s) of this dimension.
For SIL workflows of the dimension make sure you set only Truncate for Full Load option to True.
Make sure the target table of the SDE_XYZ is defined as source table for SIL_XYZ.
Follow this procedure to add a new fact table in the Oracle Business Analytics Warehouse.
To add a new fact table
Create a DDL for the new fact based on the standard structure (with appropriate system columns). Create a staging table for this fact.
Register the new source table (if it does not already exist) in the DAC repository and associate it with a database connection.
Create the change capture tables by right-clicking and selecting Generate Change Capture Scripts. For instructions, see Section 6.4.5, "Including a Source Table for Change Capture Process".
Create SDE mappings to populate the custom stage table. Use the view created by change capture as the main table in the SQL so that it extracts only the incremental data. Use the reference maps (above) as examples of how to populate the system columns. Be sure to truncate the stage table in corresponding workflows.
Create SIL mapping to populate the custom fact table. Use reference maps as examples of how to populate the system columns.
Use lookups or SQL override joins to dimension tables for populating dimension foreign keys (ROW_WIDs) pointing to the existing dimension.
In the DAC, register the target tables.
Create new tasks for the workflows.
For the SDE task, make sure you have the Build Image flag set to True, and list all the source tables that it queries from. Choose one or more tables as primary or auxiliary. For the target tables choose the staging table. Set the Truncate Always flag to True.
For the SIL task, list all the dimensions that will be required under source tables.
The steps for creating a new dimension table are similar to the steps for incremental change capture.
To add a new dimension table for a new fact table
In the new custom fact loading mapping (SIL), use lookups for getting foreign keys to the new dimension.
Use existing maps as examples.
Follow this procedure to add new data as a whole row into a standard dimension table in the Oracle Business Analytics Warehouse.
To add new data as a whole row into the standard dimension table
Identify and understand the existing structure of staging tables. Refer to Oracle Business Analytics Warehouse Data Model Reference for the table structure. Non-system columns can include the null value.
Create a custom SDE mapping to load the data into the staging table in the custom folder for this purpose. The staging table needs to be populated with incremental data (rows that have been added or changed since the last Refresh ETL process), for performance reasons.
Populate the INTEGRATION_ID column with the unique identifier for the record.
The combination of INTEGRATION_ID and DATASRC_NUM_ID is unique. When importing the data, make sure that a unique identifier for the external data source is inserted in the DATASRC_NUM_ID column. The DATASRC_NUM_ID is set to 1 for mappings that source data from the Siebel transactional database. This is a reserved value and is used in all standard mappings. For example, a value of 2 can be defined for DATASRC_NUM_ID in the custom SDE mapping. The standard SDE mappings populate the INTEGRATION_ID column of the dimension staging table (used for resolving the dimension's Siebel transactional database ROW_ID value). The custom process must be used to populate the same column with a unique identifier from the external data source.
After the data is populated in the staging table, use the standard SIL mappings to populate the dimension target tables.
Modify the SDE and SIL mappings of all the related fact tables (fact tables that need to be linked to this dimension).
The custom fact SDE mappings must populate the foreign key column of the changed dimension (using a custom map table process to convert from Siebel's row IDs to the external data source row IDs). The custom SIL mapping should be modified to use the appropriate DATASRC_NUM_ID, because the standard SIL mappings assume DATASRC_NUM_ID for the dimensions are the same as the fact table's DATASRC_NUM_ID.
It is very important to decide when the data is going to be loaded. If it is going to be loaded along with the Siebel source data, you must be careful with how you handle failure recovery. The preconfigured workflows truncate the target staging table prior to loading. Upon failure, when the DAC server restarts the task, all the data is truncated and all the data is loaded again.
If the data from the external source gets loaded into the same staging table, be careful with how you handle this situation, since you cannot use the truncate table functionality. The data migrating into the staging table is not incrementally loaded, and, therefore, should be cleaned up prior to attempting to load this table again.
In such a case, it is recommended that you encapsulate the extract part from both the sources inside an Informatica workflow. Upon failure of either of the extracts, the whole workflow gets rerun. Note that the data from both the sources should be run at the same time, all the time.
If it is decided that the data is going to be loaded at different time frequencies, then the new SDE workflows need not depend on the preconfigured SDE workflows and can use the Truncate Table option for failure recovery. In this case, in the DAC Design view, define a new execution plan in the Execution Plans tab, and define the new data source in the Database Connections child tab. Make sure the shared SIL process depends on the SDE processes from both sources.
Each application has prepackaged logic to extract particular data from a particular source. This section discusses how to capture all data relevant to your reports and ad hoc queries by addressing what type of records you want and do not want to load into the data warehouse, and includes the following topics:
You can configure extract mappings and mapplets in the Oracle Business Analytics Warehouse to accommodate additional source data. For example, if your business divides customer information into separate tables based on region, then you would have to set up the extract mapping to include data from these tables.
Extract mappings generally consist of a source table or Business Component, an Expression transformation, and a staging table. If you want to extract new data using the existing mapping, you have to modify the extract mapping to include the new data by performing the following tasks:
To modify an existing mapping to include new data
Modify the existing Business Component to extract information from the source, and add it to an appropriate extension column.
|
Tip: You can perform calculation transformations in the Business Component mapplet or the Source Adapter mapplet in the extract mapping. However, do not use performance-expensive calculations in the extract that could tie up your source transaction system. For these types of calculations, it is recommended that you perform them in the Source Adapter mapplet. |
Modify the Expression transformation to perform any necessary transformations.
Connect all input and output ports within the extract mapping so that the data moves from the source or Business Component to the Expression transformation and through the Source Adapter mapplet, and finally to the staging table's appropriate extension column.
You have to determine which type of extension column to map the data to in the staging table. After you modified the extract mapping, you would also have to modify the corresponding load mapping to make sure the extension columns that you added are connected all the way from the staging table to the warehouse table.
Business Components are packaged as mapplets, which reside in source-specific folders within the repository. Business Components are used to extract data from the source system. You can configure these mapplets to perform the following:
Extract data from a new source table
Set incremental extraction logic
The following procedure contains instructions for adding a new table to the Business Component. The procedure includes adding a new source definition, connecting the ports to the Source Qualifier, editing the Source Qualifier, connecting the ports to the Output transformation, and editing the Output transformation.
To add a new source table to an existing Business Component mapplet
In PowerCenter Designer, open the applicable source system configuration folder.
Open Mapplet Designer tool.
Drag the Business Component mapplet into Mapplet Designer to view the transformations that comprise the Business Component.
Expand the Sources folder, and copy a source table into the mapplet by dragging and dropping the table into Mapplet Designer.
Connect the applicable ports from the new source definition to the Source Qualifier by clicking on the port in the new source table and dragging it to the connecting port in the Source Qualifier.
Double-click the Source Qualifier to open the Edit Transformations box.
In the Ports tab, make any changes to the new ports for data type, precision, scale, or all these values, as necessary.
Connect the applicable ports from the Source Qualifier to the Mapplet Output transformation (MAPO).
|
Note: In some cases, the Business Component contains an Expression transformation between the Source Qualifier and the MAPO. |
In the Properties tab, make changes to the SQL statement as necessary.
Validate and save your changes to the repository.
You must make sure that the delimiter used in the CSV files is not present in the source data. Oracle Business Analytics Warehouse is preconfigured with a comma as the source file delimiter. If your data contains commas, then you need to enclose the data fields using a character that is not present in the data set. For example, common enclosing characters include single quotes and double quotes.
To set up the delimiter for source files
Open the CSV file.
Enclose the data fields with the enclosing character that you have identified.
You can identify an enclosing character by identifying a character that is not present in the source data. Common enclosing characters include single quotes and double quotes.
Save and close the CSV file.
Identify all the source definitions associated with the modified files.
Change the properties for each of these source definitions to use the enclosing character.
Validate and save your changes to the repository.
The majority of all source-specific transformations occur in the Source Adapter mapplet; source-independent transformations generally take place in the Analytic Data Interface (load mapping). The Source Adapter mapplet converts source-specific data elements into standard formats and then stores them in a staging table. The source independent loading mapping then picks up these records, which are already transformed into standard format.
The figure below illustrates the three components of the Source Adapter mapplet that allow transformations of data to occur. The three components are Mapplet Input (MAPI), Expression transformation (EXP), and Mapplet Output (MAPO).
In the figure above, if the input data is transformed, the data is passed to the Expression transformation (EXP) as input only. After the data is transformed, it is output through a new port, which is prefixed with EXT_. If the data is not transformed, it comes in as input-only and leaves through an output-only port.
If you want to add a new transformation, you must add a new port to contain the expression that is used to transform the data.
To add a new port to the Source Adapter mapplet
In PowerCenter Designer, open the applicable source system configuration folder.
Open the applicable Source Adapter mapplet.
Double-click the MAPI component of the mapplet, and add a new input port following the INP_* naming convention.
Copy the new input port from the MAPI to the Expression transformation.
Connect the new port from the MAPI to the Expression transformation.
In the Expression transformation, uncheck the Output indicator for the new input port; you use the value from this port in an transformation expression.
Perform any necessary transformations within the Expression transformation.
The transformed data is passed out of an EXT_* output-only port.
Connect the port from the Expression transformation to the MAPO.
Validate and save your repository.
The Oracle Business Analytics Warehouse prepackages load mappings for every data warehouse table.
|
Note: This section does not apply to Oracle's Siebel sources. |
In a typical implementation, records that are deleted from your source system are not removed from the Oracle Business Analytics Warehouse. If you want to mark these records as deleted in the data warehouse, which were removed from the source system's database and archived in a separate database, you must enable the primary extract and delete mappings.
Primary extract mappings flag records that are deleted from the data warehouse. Delete mappings sets the DELETE_FLG column to 'Y' for these records in the warehouse tables. When enabled, primary extract and delete mappings by default look for any records removed from the source system's database. If these mappings find that the records no longer exist in that database, the mappings mark them as deleted in the data ware
|
Caution: It is important to note that delete and primary extract mappings must always be disabled together; you may not disable only one type. |
|
Note: This section does not apply to Oracle's Siebel adapter, which does not have Primary Extract mappings. |
Before you decide to enable primary extract and delete sessions, it is important to understand their function within the Oracle Business Analytics Warehouse. Primary extract and delete mappings allow your analytics system to determine which records are removed from the source system by comparing primary extract staging tables with the most current Oracle Business Analytics Warehouse table.
The primary extract mappings perform a full extract of the primary keys from the source system. Although many rows are generated from this extract, the data only extracts the Key ID and Source ID information from the source table. The primary extract mappings load these two columns into staging tables that are marked with a *_PE suffix.
The figure below provides an example of the beginning of the extract process. It shows the sequence of events over a two day period during which the information in the source table has changed. On day one, the data is extracted from a source table and loaded into the Oracle Business Analytics Warehouse table. On day two, Sales Order number three is deleted and a new sales order is received, creating a disparity between the Sales Order information in the two tables.
Figure 6-8 shows the primary extract and delete process that occurs when day two's information is extracted and loaded into the Oracle Business Analytics Warehouse from the source. The initial extract brings record four into the Oracle Business Analytics Warehouse. Then, using a primary extract mapping, the system extracts the Key IDs and the Source IDs from the source table and loads them into a primary extract staging table.
The extract mapping compares the keys in the primary extract staging table with the keys in the most current the Oracle Business Analytics Warehouse table. It looks for records that exist in the Oracle Business Analytics Warehouse but do not exist in the staging table (in the preceding example, record three), and sets the delete flag to Y in the Source Adapter mapplet, causing the corresponding record to be marked as deleted.
The extract mapping also looks for any new records that have been added to the source, and which do not already exist in the Oracle Business Analytics Warehouse; in this case, record four. Based on the information in the staging table, Sales Order number three is physically deleted from Oracle Business Analytics Warehouse, as shown in Figure 6-8. When the extract and load mappings run, the new sales order is added to the warehouse.
The primary extract (*_Primary) and delete mappings (*_IdentifyDelete and *_Softdelete) serve a critical role in identifying which records have been physically deleted from the source system. However, there are some instances when you can disable or remove the primary extract and delete mappings, such as when you want to retain records in the data warehouse that were removed from the source systems' database and archived in a separate database.
Because delete mappings use Source IDs and Key IDs to identify purged data, if you are using multiple source systems, you must modify the SQL Query statement to verify that the proper Source ID is used in the delete mapping. In addition to the primary extract and delete mappings, the configuration of the delete flag in the load mapping also determines how record deletion is handled.
You can manage the extraction and deletion of data in the following ways:
Deleting the configuration for source-archived records
Deleting records from a particular source
Enabling delete and primary-extract sessions
Configuring the Record Deletion flag
Configuring the Record Reject flag
This topic provides procedures for these management tasks.
Some sources archive records in separate databases and retain only the current information in the main database. If you have enabled the delete mappings, you must reconfigure the delete mappings in the Oracle Business Analytics Warehouse to retain the archived data.
To retain source-archived records in the Oracle Business Analytics Warehouse, make sure the $$LAST_ARCHIVE_DATE parameter value is set properly to reflect your archive date. The delete mappings will not mark the archived records as 'deleted'. For more information about extract and delete mappings, see Section 6.7.3, "About Working with Primary Extract and Delete Mappings".
If you want to mark your source-deleted records as deleted in the Oracle Business Analytics Warehouse, you need to enable the delete and primary extract tasks for your application.
To enable primary extract and delete sessions
Log into DAC
Go to your container
Select the 'Tasks' tab
Query for all tasks containing the string 'Delete' or 'Primary'
Uncheck the 'Inactive' check box for those tasks.
The Oracle Business Analytics Warehouse provides Type II slowly changing dimension (SCD) functionality, which allows you to track the history of updates to dimension records. When a record in the Oracle Business Analytics Warehouse has an update, the updated information is posted into a new row and the old information is kept for historical reporting purposes.
The Oracle Business Analytics Warehouse identifies and applies the slowly changing dimension logic chosen by the user after data has been extracted and transformed to be source-independent. Users may configure Oracle BI Applications to support both Type I SCDs, in which data is overwritten with updates, and Type II SCDs, in which the original records are maintained while a new record stores the updated data. Choosing Type I or Type II SCDs depends on identifying your historically significant attributes.
By default, all dimensions are using Type I updates. If you need change a dimension to Type II SCD update, follow the procedure below.
To turn a dimension to Type II SCD update enabled
In the parameter file parameterfileDW.txt located under Informatica PowerCenter 7.1.4\Server\SrcFiles
Locate the parameter section for the SIL_* mapping which load this dimension.
Set the $$TYPE2_FLG to 'Y'.
You may want to retain a history of all the updates to a particular dimension so that you can use them in reports. These dimensions are known as historically significant attributes. For example, if a customer moves to a different region and you assign that customer a new regional salesperson and territory ID, you may want to keep records of that customer's account history with the original salesperson and territory ID. In this case, the salesperson and territory IDs are historically significant attributes. In contrast, you may have a load that populates the telephone number field. If your business does not perform data analysis on phone number history, then this information may be considered a historically insignificant attribute.
Identifying attributes as significant or insignificant allows you to determine the type of SCD you require. However, before you can select the appropriate type of SCD, you must understand their differences.
The extract view of any given table in the Staging Area consists of four types of records:
New records
Changed records with data that is historically insignificant
Changed records having historical significance
Changed records whose changes have no significance of any kind and are ignored altogether
Of the four kinds of records, only the first three are of interest for the data mart. Of those three, brand new records and records whose changes are tracked as SCDs are both treated as new and become inserts into the data warehouse. Records with changes that are important but not historically tracked are overwritten in the data warehouse, based on the primary key.
After you have correctly identified your significant and insignificant attributes, you can configure the Oracle Business Analytics Warehouse based on the type of slowly changing dimension (SCD) that best fits your needs—Type I or Type II.
A Type I SCD overwrites the column's value and is the default SCD for the Oracle Business Analytics Warehouse. Although a Type I does not maintain history, it is the simplest and fastest way to load dimension data. Type I is used when the old value of the changed dimension is not deemed important for tracking or is an historically insignificant attribute. For example, you may want to use Type I when changing incorrect values in a column.
In the figure below, the State Name column for the supplier KMT is changed in the source table Suppliers, because it was incorrectly entered as California. When the data is loaded into the data warehouse table, no historical data is retained and the value is overwritten. If you look up supplier values for California, records for KMT do not appear; they only appear for Michigan, as they have from the beginning.
A Type II SCD creates another record and leaves the old record intact. Type II is the most common SCD because it allows you to track historically significant attributes. The old records point to all history prior to the latest change, and the new record maintains the most current information.
Slowly changing dimensions work in different parts of a star schema (the fact table and the dimension table). The figure below shows how an extract table (SOURCE_CUSTOMERS) becomes a data warehouse dimension table (W_ORG_D). Although there are other attributes that are tracked, such as Customer Contact, in this example there is only one historically tracked attribute, Sales Territory. This attribute is of historical importance because businesses frequently compare territory statistics to determine performance and compensation. Then, if a customer changes region, the sales activity is recorded with the region that earned it.
This example deals specifically with a single day's extract, which brings in a new record for each customer. The extracted data from SOURCE_CUSTOMERS is loaded into the target table W_ORG_D, and each record is assigned a unique primary key (ROW_WID).
However, this data is not static; the next time a data extract shows a change for your customers in W_ORG_D, the records must change. This situation occurs when slowly changing dimensions are invoked. The figure below shows that records for the two customers, ABC Co., and XYZ inc. have changed when compared with the figure below. Notice that ABC's Customer Contact has changed from Mary to Jane, and XYZ's Sales Territory has changed from West to North.
As discussed earlier in this example, the Customer Contact column is historically insignificant; therefore a Type I SCD is applied and Mary is overwritten with Jane. Because the change in ABC's record was a Type I SCD, there was no reason to create a new customer record. In contrast, the change in XYZ's record shows a change of sales territory, an attribute that is historically significant. In this example, the Type II slowly changing dimension is required.
As shown in the figure below, instead of overwriting the Sales Territory column in the XYZ's record, a new record is added, assigning a new ROW_WID, 172, to XYZ in W_ORG_D. XYZ's original record, 102, remains and is linked to all the sales that occurred when XYZ was located in the West sales territory. However, new sales records coming in are now attributed to ROW_WID 172 in the North sales territory.
Effective dates specify when a record was effective. For example, if you load a new customer's address on January 10, 2003 and that customer moves locations on January 20, 2003, the address is only effective between these dates. Effective Dates are handled in the following manner:
If the source supplies both effective dates, these dates are used in the warehouse table.
If the source does not supply both the effective to and effective from dates, then the Type II logic creates effective dates.
If the source supplies one of the two effective dates, then you can set up the Oracle Business Analytics Warehouse to populate the missing effective dates using a wrapper mapping. This situation is discussed in this section. By default, these wrapper sessions are disabled and need to be enabled in order to be executed.
For example, in the W_ORG_D table previously discussed, XYZ moved to a new sales territory.
If your source system supplied historical data on the location changes, your table may contain a record for XYZ in the West sales territory with an effective from date of January 1, 2001 and an effective to date of January 1, 3714. If the next year your source indicates XYZ has moved to the North sales territory, then a second record is inserted with an effective from date of January 1, 2002, and an effective to date of January 1, 3714, as shown in Table 6-2.
Table 6-2 Records Before a Wrapper Session in W_CUSTOMER
| Customer Name | Sales Territory | Customer Contact | Effective From | Effective To | Current |
|---|---|---|---|---|---|
|
ABC |
East |
Jane |
1/1/2001 |
1/1/3714 |
Y |
|
XYZ |
West |
John |
1/1/2001 |
1/1/3714 |
Y |
|
XYZ |
North |
John |
1/1/2002 |
1/1/3714 |
Y |
Note your first record for XYZ still shows as effective from January 1, 2001 to January 1, 3714, while a second record has been added for XYZ in the North territory with the new effective from date of January 1, 2002. In this second record the effective to date remains the same, January 1, 3714.
When you schedule a wrapper session to execute, the effective dates for the first XYZ are corrected (January 1, 2001-January 1, 2002), and the Current Flag is adjusted in the Analytic Data Interface (load mapping) so that only the second record (January 1, 2002-January 1, 3714) is set to Y. After the wrapper session completes its work, you have Type II information for XYZ in your data warehouse rather than two disparate records, as shown in Table 6-3.
Table 6-3 Records After a Wrapper Session in W_CUSTOMER
| Customer Name | Sales Territory | Customer Contact | Effective From | Effective To | Current |
|---|---|---|---|---|---|
|
ABC |
East |
Jane |
1/1/2001 |
1/1/3714 |
Y |
|
XYZ |
West |
John |
1/1/2001 |
1/1/2002 |
N |
|
XYZ |
North |
John |
1/1/2002 |
1/1/3714 |
Y |
In the previous paragraph, the wrapper session corrected the effective to dates and current flag. However, if the record's dates had been correct, the wrapper mapping would simply have set the current flag as needed, because its logic is set to check dates and flags and only adjust columns that contain discrepancies. Finally, if your source system does not supply any Type II information, you may disable the wrapper session completely; in this case all Type II work is handled by the Analytics Data Interface mapplet.
A lookup transformation allows you to specify a reference table, and then retrieve information such as code descriptions, exchange rates, and currency codes. The main types of preconfigured lookups within the Oracle Business Analytics Warehouse are:
Dimension Key Lookups. For more information, see Section 6.11, "About Resolving Dimension Keys".
Some source systems use intelligent codes that are intuitively descriptive, such as HD for hard disks, while other systems use non-intuitive codes (like numbers, or other vague descriptors), such as 16 for hard disks. While codes are an important tool with which to analyze information, the variety of codes and code descriptions used poses a problem when performing an analysis across source systems. The lack of uniformity in source system codes must be resolved to integrate data for the Oracle Business Analytics Warehouse.
The code lookup in the load mapping integrates both intelligent and non-intuitive codes by performing a separate extract for codes, and inserting the codes and their description into a codes table. The codes table provides the load mapping with a resource from which it can automatically perform a lookup for code descriptions.
The Analytic Data Interface's architecture uses components, as well as both fact and dimension tables, to facilitate lookup functionality. The following components and process are used in a lookup:
The load control table W_CODES consolidates all codes for future reference and assigns them a category and a single language for efficient lookup capability.
The Oracle Business Analytics Warehouse uses mappings designed to extract codes from source systems and populate the W_CODES table in preparation for use by the load mapping.
To understand how codes mappings function, it is helpful to first understand the columns within W_CODES. Table 6-4 describes these columns.
Table 6-4 Columns in Code Mapplet
| Column | Description |
|---|---|
|
DATASOURCE_NUM_ID |
Unique identifier of the source system from which data was extracted |
|
SOURCE_CODE1 |
The first code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_CODE2 |
The second code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_CODE3 |
The third code in the hierarchy of the various source system codes used to identify a particular code and description combinations |
|
SOURCE_DESC_1 |
Short description of the source system code |
|
SOURCE_DESC_2 |
Long description for code |
The naming convention for mappings designed for codes lookup is SDE_[SOURCE]_CodeDimension_[CATEGORY]. The figure below shows an example of a code mapping in PowerCenter Mapping Designer.
There are several mapplets that support the codes mappings in preparation for the source-independent load mapping. They are as follows:
Source Adapter mapplets. The Source Adapter mapplet connects the source-specific input attributes of CODES and the attributes from control or warehouse tables to the expression transform for mapping them. The naming convention for the Source Adapter codes mapplet is MPLT_SA_CODES.
Business Component mapplets. The Business Component mapplet makes the source system attributes of CODES_CUST_CLASS available to the extract mapping. The naming convention for the Business Component codes mapplet is MPLT_BC_CODES_[CATEGORY].
ADI Mapplet. The Analytic Data Interface (load mapping) mapplet is source system independent and resolves the codes for the target table. The naming convention for the load mapping codes mapplet is MPLT_ADI_CODES.
The load mapping integrates multiple source system codes by designating one source system instance as a master in a mapping. All other source system codes are then mapped to the master. When the load mapping encounters a code that requires definition, it references the load control lookup table to match the source system code to an Oracle Business Analytics Warehouse source-independent code, which retains all the source system codes' original functionality.
The following columns are used to designate a source system instance as the master source system:
MASTER_ID. Code for the source system designated as master.
DATASOURCE_NUM_ID. Unique identifier for the source system.
You can configure dimension and fact load sessions to perform specific lookups by editing the category of the data to be extracted from the W_CODES table and loading the code information into a target table. If the code and code name do not exist in the W_CODES table, then you must add them to the table. To configure the lookup, create a session override; do not modify the load mapping in the load mapping.
To configure sessions for lookups
In PowerCenter Workflow Manager, open the applicable source system configuration folder.
Open the Edit Tasks box.
In the Transformations tab, edit the SQL statement for the lookup.
For example, you may wish to edit the following lookup:
MPLT_ADI_SUPPLIERS.LKP_SPLR_ATTR1
Edit the SQL statement to use the desired code category.
Edit the SQL statement from 'GENERIC' to the category you wish to use for the lookup.
By default, dimension key resolution is performed by the Oracle Business Analytics Warehouse in the load mapping. The load mapping uses prepackaged, reusable lookup transformations to provide prepackaged dimension key resolution. This section describes how dimension keys are looked up and resolved.
There are two commonly used methods for resolving dimension keys. The first method, which is the primary method used, is to perform a lookup for the dimension key. The second method is to supply the dimension key directly into the fact load mapping.
If the dimension key is not provided to the load mapping through database joins, the load mapping performs the lookup in the dimension table. The load mapping does this using prepackaged lookup transformations.
The load mapping uses the integration ID, the DATASOURCE_NUM_ID and Lookup date in looking up the dimension key. All these columns are necessary for the load mapping to return the dimension key. The ports are described in Table 6-5.
Table 6-5 Columns Used in the load mapping Dimension Key Lookup
| Port | Description |
|---|---|
|
INTEGRATION ID |
Uniquely identifies the dimension entity within its source system. Formed from the transaction in the Source Adapter of the fact table. |
|
DATASOURCE_NUM_ID |
Unique identifier of the source system instance. |
|
Lookup Date |
The primary date of the transaction; for example, receipt date, sales date, and so on. |
In the figure below, the Supplier Products Key Lookup transformation illustrates the three input columns needed for the load mapping lookup—the INTEGRATION ID, DATASOURCE_NUM_ID, and Date (lookup date). The transformation then outputs the Supplier Product key (the dimension key) to the data warehouse table W_SUPPLIER_PRODUCT_D.
If Type II slowly changing dimensions are enabled, the load mapping uses the unique effective dates for each update of the dimension records. When a dimension key is looked up, it uses the fact's primary date to resolve the appropriate dimension key.
The effective date range gives the effective period for the dimension record. The same entity can have multiple records in the dimension table with different effective periods due to Type II slowly changing dimensions. This effective date range is used to exactly identify a record in its dimension, representing the information in a historically accurate manner. In the lookup for Employee Contract Data shown in the figure below, you can see the effective dates used to provide the effective period of employee contracts.
The Oracle Business Analytics Warehouse foundation comprises a data model that accommodates data from disparate source systems. Data is sourced from operational systems and systematically molded into a source-independent format. After the data is source independent, it can then be used to create key metrics for analytic reporting, so that metric calculations are not source dependent. This clear separation allows you to swap source systems or integrate additional source systems without having to reconfigure the metric calculations to accommodate each source system's requirements.
One method for transforming source data into a source-independent format is to convert the source-supplied values to domain values. Domain values are a set of distinct values used to calculate prepackaged metrics. These values are provided by the Oracle Business Analytics Warehouse to allow you to create metric calculations independent of source system values.
To best understand the domain value conversion process, consider an example of two source systems—Source System A and Source System B. Each source system stores two types of employee events—hire and rehire. Source system A uses H to denote a hire event and R to denote a rehire event, whereas source system B uses 1 to denote a hire event and 2 to denote a rehire event. When the Oracle Business Analytics Warehouse extracts data from both systems, it ports those source values through the extract mapping until the data reaches the W_EVENT_GRP_CODE column in the TF_EVENT_TYPES Staging table.
The load mapping then ports the extracted source values (H and R from source system A, and 1 and 2 from source system B) into the Source Adapter mapplet. Within the Source Adapter, source values are translated into domain values (HIR and REH) based on a set of rules that are particular to your business practices.
You must define the rules so that the Source Adapter knows how to map your specific source values to the given set of domain values. Before you set up the rules you must:
Analyze all of your source values and how they map to the prepackaged domain values. You may find that you need to create additional domain values for particular columns. The result of this preparation work is a list of each source value and how it is mapped to a domain value.
Implement this logic in the applicable Source Adapter mapplet. To set up the logic, modify the Expression transformation in the Source Adapter mapplet for each affected column. For information on setting up the rules for domain values, see Section 6.14, "Configuring the Domain Value Set Using PowerCenter Designer".
Figure 6-14 illustrates how the source values are converted to the domain values—HIR and REH.
The figure below illustrates a different situation where the records may not contain a source value that flags the record as Hire or Rehire. In this case, the source system stores hires in one table and rehires in another table. To make this work, one possible solution is to modify the extract mappings to populate the W_EVENT_GRP_CODE column with HIR or REH. If the field is populated in the extract mapping, you can then carry those same values through the Source Adapter mapplet.
After the Source Adapter mapplet converts the source-specific values to domain values, the domain values are inserted into an Oracle Business Analytics Warehouse table. In this example, the HIR and REH values populate the W_EVENT_TYPES table, as illustrated in the figure below.
Values in the W_EVENT_TYPES table are used to create metrics in the front end. Some metrics are defined using domain values. For example, seven metrics use the HIR and REH event group code in their calculation. The following are the seven metrics, along with their descriptions and calculations:
This metric counts all hires for a specified period. The calculation is:
SUM(CASE WHEN (CMMNEVTP.W_EVENT_GRP_CODE IN ('HIR','REH')) THEN EVNT.EVENT_CNT ELSE 0 END)
This metric determines the ratio of rehires to all employees hired during a specified period. The calculation is:
CASE WHEN SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH','HIR') THEN EVNT.EVENT_CNT ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH') THEN EVNT.EVENT_CNT ELSE 0 END)/SUM(CASE WHEN CMMNEVTP.W_EVENT_GRP_CODE IN ('REH','HIR') THEN EVNT.EVENT_CNT ELSE 0 END) END
This metric counts the head count hired for regular full-time positions. The calculation is:
SUM(CASE WHEN CMMNEMPT.FULL_TIME_FLAG = 'Y' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts the regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '4' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '3' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '1' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
This metric counts regular full-time and part-time employees who belong to this category of veterans and were hired during the previous 12 months. The calculation is:
SUM(CASE WHEN CMMNEMPD.VETERAN_STAT_CODE = '2' AND CMMNEMPT.EMP_CAT_CODE = 'R' AND (CMMNEVTP.W_EVENT_GRP_CODE = 'HIR' OR CMMNEVTP.W_EVENT_GRP_CODE = 'REH') AND EVNT.EVENT_DK >= (CMMNDATE.DATE_KEY - 365) AND EVNT.EVENT_DK <= CMMNDATE.DATE_KEY THEN EVNT.EVENT_CNT ELSE 0 END)
Each of these metric calculations is based on the domain values HIR and REH. All records whose source values are converted to one of these domain values are included in the metric calculations, as shown in the figure below.
The Oracle Business Analytics Warehouse is also extensible in that you can create additional domain values for those columns that do not fit into the existing domain value definitions. However, before you modify the domain value set for a particular column, you first perform impact analysis on existing metrics. For example, the Oracle Business Analytics Warehouse prepackages the following two events:
New Hire. This event occurs when a new person is hired.
New Position. This event occurs when a position is created, but an existing employee may be hired internally.
If you have an event that represents both a New Hire and a New Position, you may have to create a third event that depicts both. If you create this new event type domain value, you need to include it in the applicable metric definitions so as to account for all hires and positions.
Domain values are a set of distinct values used to calculate prepackaged metrics. These values are provided by Oracle Business Analytics Warehouse to allow you to create metric calculations independent of source system values. Oracle Business Analytics Warehouse provides CSV worksheet files to map source system values to domain values.
You can add to these worksheet files if you need extra source system values and map them to domain values. You can also modify the worksheet files if you need to customize the domain values. You can use an existing domain value if you want to change the preconfigured metrics. Otherwise you can create a new domain value and create new metrics based on this domain value.
The source system values that are not mapped to a domain values in the CSV worksheet files have a question mark (?) as the domain value in the Oracle Business Analytics Warehouse. These values do not affect the domain values metrics.
If there are no worksheet files to map the source system values to the domain values, you need to modify the domain values using PowerCenter Designer. For more information on configuring domain values using PowerCenter Designer, see Section 6.14, "Configuring the Domain Value Set Using PowerCenter Designer".
For a list of CSV worksheet files and their domain values for your application, see your application configuration chapter.
To map source values to domain values using CSV worksheet files
Identify all the Oracle Business Analytics Warehouse table columns that use domain values.
For a list of columns that use domain values, see the Oracle Business Analytics Warehouse Naming Conventions and Domain Values.
List all of your source values that qualify for conversion to one of the domain values.
Map each source value to a domain value.
If any of your source system values do not map to a prepackaged domain value, and you may modify the list of domain values, then create a list of new domain values and map your orphaned source system values to your newly created domain values.
You cannot modify all domain value sets. Also, you must check which metrics are affected by the modified domain value set. For more information, see the Oracle Business Analytics Warehouse Naming Conventions and Domain Values.
Open the CSV worksheet file in the ...\Informatica\SrcFiles folder.
Edit the file to map your source values to the existing domain values.
Alternately, if you want to add additional domain values, add them in this worksheet file.
Save and close the worksheet file.
If there are no worksheet files to map the source system values to the domain values, you need to modify the values using PowerCenter Designer. For more information on configuring the domain value set with CSV worksheet files, see Section 6.13, "Configuring the Domain Value Set with CSV Worksheet Files".
Configuring the domain value set for a particular column, using PowerCenter Designer, entails one or both of the following activities:
Mapping source-specific values to domain values
Adding more domain values to the prepackaged set of values
Regardless of which activity you choose, the configuration occurs in the Expression transformation of the applicable Source Adapter mapplet. The following procedure shows how to configure the Expression transformation to change the domain values.
To map source values to domain values using PowerCenter Designer
Identify all the Oracle Business Analytics Warehouse table columns that use domain values.
For a list of columns that use domain values, see the Oracle Business Analytics Warehouse Naming Conventions and Domain Values.
List all of your source values that qualify for conversion to one of the domain values.
Map each source value to a domain value.
If any of your source system values do not map to a prepackaged domain value, and you may modify the list of domain values, then create a list of new domain values and map your orphaned source system values to your newly created domain values.
You cannot modify all domain value sets. Also, you must check which metrics are affected by the modified domain value set. For more information, see the Oracle Business Analytics Warehouse Naming Conventions and Domain Values.
In PowerCenter Designer, open the applicable Source Adapter mapplet.
Open the Expression transformation.
Locate the applicable port's expression so that you can modify it.
Edit the port's expression to map your source values to the existing domain values.
Alternately, if you want to add additional domain values, add them in this same expression.
Save and validate your changes to the repository.
This section provides procedures on configuring objects that apply to more than one application and contains the following topics:
This section provides configuration procedures for modifying dimensions that are loaded using a universal business adapter.
The Oracle Business Analytics Warehouse stores product Effective To (SRC_EFF_TO_DT) and From (SRC_EFF_FROM_DT) dates in the Products dimension table, W_PRODUCTS. In addition, the Products dimension stores a Support Withdrawn Date column, SPRT_WITHDRAWN_DT.
By default, the Support Withdrawn Date takes precedence over the product Effective To Date. This prioritization means that if you supply a value for the Support Withdrawn Date column in your flat file upload, the Oracle Business Analytics Warehouse uses that value as the product Effective To value as well, overwriting anything in the SRC_EFF_TO_DT column. You can change this default behavior by modifying the Products Expression in the Universal Source Products Extract mapping.
To modify the product Effective To Date logic for a flat file extract
