Thursday Feb 05, 2015

ETL Design for sharing the sequence ID

Author: Archana Dixit

Introduction

This blog describes an ETL design to share the same sequence ID among several tables. Party Dimension is an exceptional case where Records from different streams merge into one table. Organization, person and group party types are loaded into separate tables W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D and later get merged into a master table W_PARTY_D.

 

These dimensions are Slowly Changing dimension out-of-the-box in the BI Application. They also carry a slowly changing type1 WID named as SCD1_WID. It holds the same value as for the new record also in case of SCD type 2 change. For example, if Organization Name is changed from ‘ABC Software’ to ‘ABCD Software’, the current record would still have the same value for SCD1_WID.

 

This WID is populated with a sequence generated numeric value. Knowledge modules (KM) use a separate DB sequence for each target table while loading data into W_PARTY_ORG_D, W_PARTY_PER_D & W_PARTY_GROUP_D tables resulting in different sequence numbers for SCD1_WID column in master table W_PARTY_D. The following steps describe an approach to share the same sequence ID.

Step 1:

KM driven option ‘OBI_SCD1_WID’ should be disabled to refrain it from creating separate DB sequences. Set the default value of OBI_SCD1_WID IKM option to ‘false’ as shown in the screen shot below.

 

Step 2:

Create a mapping to populate the helper table W_PARTY_PER_T1_D table. The source for this mapping should be the corresponding staging table W_PARTY_PER_DS. The mapping expression for SCD1_WID column should read from the sequence created from the previous load stream (in this case W_PARTY_ORG_D load). Set the mapping expression as NEXTVAL (W_PARTY_ORG_S1W) and uncheck ‘Update’ checkbox.

 

Step 3:

In the flow tab, DETECTION_STRATEGY IKM option should be set to ‘NONE’.

 

Step 4:

Configure LP components to execute the scenarios loading W_PARTY_PER_T1_D, W_PARTY_PER_D & W_PARTY_D in serial mode in the order as follows.

 

Thursday May 08, 2014

BI apps 11.1.1.8.1 is available now

Oracle Business Intelligence (BI) Applications 11.1.1.8.1 is now available on the Oracle Software Delivery Cloud (eDelivery), and on the Oracle BI Applications OTN page. This is the second major release on the 11g code line leveraging the power of ODI, and certified with the latest version of Oracle BI Foundation 11.1.1.7

 Highlights from the announcement.

What’s New

This new release of Oracle Business Intelligence Applications 11g adds:

1. Prebuilt Connector for Oracle Procurement and Spend Analytics with Oracle JD Edwards

2. Expanded functional content coverage across existing Oracle BI Applications

3. New utilities and enhancements to increase productivity and further drive down Total Cost of Ownership (TCO)

New Prebuilt Connector for BI Applications

· Oracle Procurement and Spend Analytics introduces a new adapter that provides out-of-box integration between Oracle Procurement and Spend Analytics and Oracle’s JD Edwards EnterpriseOne, enabling purchasing and sourcing organizations quickly identify savings opportunities and improve operational performance through decision-ready, best practice analytics.

New Content Enhancements: Significant expansion of content improves existing Oracle BI Applications including:

· Oracle Human Resources Analytics expands analysis on HR programs and workforce performance with the introduction of Talent Management Analysis, to help HR and business managers assess talent strengths and build potential leaders by delivering greater insight into job profiles. Improvements to Absence Management, new support for Workforce Frozen Snapshots and Workforce Flex Fields are also available.

· Oracle Financial Analytics improves company Financial Performance with the new Subledger Accounting Module, providing complete account balance analysis, including supporting reference balances and drill down to accounting transactions. Aging Snapshot restoration for Receivables and Payables, drilldown from Payables to Purchase Orders, and Fixed Assets support for GL Posting Status are features also included in the release.

· Oracle Project Analytics expands insight into project-based data with new Earned Value Management Analysis, providing visibility for Project Stakeholders into Planned Value, Earned Value, and Actual Cost. Analysis of variances and indices, derived from Cost and Schedule Variance, CPI, TCPI, SPI and TSPI, is also available.

· Oracle Supply Chain & Order Management Analytics introduces Costing and Inventory Turn support for Oracle JDE EnterpriseOne. In addition, the ability to save and restore the Inventory Snapshot when executing a full load is now supported.

· Oracle Student Information Analytics introduces new Financial Aid Analysis, containing term-based detail information associated with a student award such as amount, status, disbursement, and aid type.

· Oracle Manufacturing Analytics and Enterprise Asset Management Analytics have been re-introduced into the BI Applications data model. Although new with ODI, both modules provide now the same broad functionality available with BI Applications 7.9.6.4

· Unstructured Data for Information Discovery – The BI Applications Data Model has been extended to include unstructured CRM and ERP text fields. Sample Information Discovery templates – including ODI interfaces and runtime security definitions –provide the ability to quickly create sample Information Discovery Applications from combined structured and unstructured data.

New Utilities and Enhancements to increase productivity and drive down TCO

· New Data Lineage Tool allows customers and implementers to track lineage from source to target – improving information transparency across their enterprise

· Configuration Management expands System Setup and Load Plan definition capabilities, including the registration of multiple instances for the same Product Line Version, UI enhancements to support DW connection configuration and Load Plan definition, and Load Plan definition migration.

Fact Partitioning IKMs and Parallel Index Builds are key performance Innovations introduced with this release. The former enables a feature that tags partitions so that only the indexes that need to be re-built during incremental loads are executed. The latter uses ODI parallel process so that Indexes can be built using multiple session (faster than and in addition to DB parallel).


 

About

Phil Wang-Oracle

Search

Categories
Archives
« March 2015
SunMonTueWedThuFriSat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
24
25
26
27
28
29
30
31
    
       
Today