Category 3 Customizations: Adding New Data as a Whole Row into a Standard Dimension Table
Category 3 customizations use the Universal adapter to load data from sources that do not have pre-packaged adapters.
How to Add New Data as a Whole Row Into a Standard Dimension Table
Follow this procedure to add new data as a whole row into a standard dimension table in the Oracle Business Analytics Warehouse.
Configuring Extracts
Each application has prepackaged logic to extract particular data from a particular source. You need 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 Oracle Business Analytics Warehouse.
Extract interfaces generally consist of source tables, expressions used in the target columns, and a staging table. If you want to extract new data using the existing interface, you have to modify the extract interface to include the new data.
Extracting Data from a New Source Table
Extract interfaces (which have the SQ_* naming convention) reside in source-specific folders within the repository. Extract interfaces are used to extract data from the source system. You can configure these extract interfaces to perform the following:
-
Extract data from a new source table.
-
Set incremental extraction logic.
Extracting New Data Using an Existing Source Table
You can configure extract mappings and Interfaces 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 interface to include data from these tables.
To modify an existing interface to include new data:
- Modify the existing interface to extract information from the source, and add it to an appropriate extension column.
- Modify the Expressions in the target table to perform any necessary transformations.
- Save the changes.
- Regenerate the scenario.
You have to determine which type of extension column to map the data to in the staging table. After you modified the extract interface, you would also have to modify the corresponding load interfaces (SDE and SIL) to make sure that the extension columns that you added are connected all the way from the staging table to the target data warehouse table.
Setting Up the Delimiter for a Source File
When you load data from a Comma Separated Values (CSV) formatted source file, if the data contains a comma character (,), you must enclose the source data with a suitable enclosing character known as a delimiter that does not exist in the source data.
Note:
Alternatively, you could configure your data extraction program to enclose the data with a suitable enclosing character automatically.For example, you might have a CSV source data file with the following data:
Months, Status January, February, March, Active April, May, June, Active
If you loaded this data without modification, ODI would load 'January' as the Months value, and 'February' as the Status value. The remaining data for the first record (that is, March, Active) would not be loaded.
To enable ODI to load this data correctly, you might enclose the data in the Months field within the double-quotation mark enclosing character (" ") as follows:
Months, Status "January, February, March", Active "April, May, June", Active
After modification, ODI would load the data correctly. In this example, for the first record ODI would load 'January, February, March' as the Months value, and 'Active' as the Status value.
To set up the delimiter for a source file:
You can now load data from the modified CSV file.
Configuring Loads
You can customize the way that Oracle Business Intelligence Applications loads data into the Oracle Business Analytics Warehouse.
About Primary Extract and Delete Mappings Process
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 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.
The figure 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, 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. When the extract and load mappings run, the new sales order is added to the warehouse.
About Working with Primary Extract and Delete Mappings
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 Oracle Business Analytics 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
Deleting the Configuration for Source-Archived Records
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'.