IBM IFW BDW Hands On: Improving Staging Area Design

Data Warehouse Staging Area is a temporary location intended to receive data from source systems.  A Staging area is mainly required in a Data Warehousing Architecture for timing reasons.  In short, all required data must be available before data can be integrated into the Data Warehouse.  Due to varying business cycles, data processing cycles, hardware and network resource limitations and geographical factors, it is not feasible to extract all the data from all Operational databases at exactly the same time.

Moreover, in terms of IBM BDW model, design of staging area could make significant impact to efficiency and effectiveness of ETL process. Let’s find out how?

Mapping of Operational data to the data warehouse model usually means mapping of hundreds of entities form both sides. Considering this fact, mapping process requires significant effort to be completed. So, the question is: can we make some improvement, in terms of usage of IBM BDW as data warehouse model?

The answer is YES, we can!

In order to do that, we need to apply certain principles for data staging design.

Presumption for staging area design (IBM BDW based):

1. Staging area should consist of relatively small number of entities, up to 20, enable to receive a whole informational potential form the operational sources. Those entities are named “messages”
2. Each “message” entity represents generalization of number of entities from BDW. Logical foundation for generalization and grouping is BDW data concepts.
3. Physical representation of staging database is relevant: could be relation database, files (txt, xml), in memory, etc.
4. Data loaded into Staging Area entities (“messages”) must be in their native format or slightly transformed, in order to prepare data for the next ETL stage ( form Staging database to data warehouse)
5. Classification resolving is not considered at this stage - should be a part of the next ETL stage

Benefits of using this approach for Staging area design:

1. Now, we have mapping of hundreds of entities form operational sources to up to 20 entities in the staging database, unlike of mapping hundreds of entities from both sides, That is much faster and less complex
2. Usage of BDW data concepts as a foundation for building staging area entities making mapping process much more effective and efficient

Note: This approach of BDW staging area design is applied in most of BDW implementation of Pexim Solutions (now Asseco SEE) which is major IBM channel partner for BDW implementation in SEE and ME. The benefits of such design are practically confirmed.

IBM IFW BDW Hands On: Resolving Product Data Concept Design

Today’s Banking Core systems are much more transaction oriented then product oriented. However, the leading Core Banking vendors are in the process of Core solutions renewal. Let’s compose certain feature abstraction of a new generation of core systems - one of most important improvement is product focus (most of them have feature like “Product Factory”). As soon as a new generation of Core Systems become main stream, it would be easy to implement Product Data Concept in IBM BDW. However, before that happen, we should find some other approach to do that.

Since that majority of the current core systems are transaction oriented there could be a quite problem to do product mappings from core system to BDW. Problem could increase in case of heterogonous applications (e.g. Loans, Deposits) that must work together. Accordingly, different organization units within the bank could have dissimilar view of product concept.

Conclusion: there is no way to establish single enterprise view of the product across the bank.

How IBM BDW model could help to get single enterprise view of product related data?

The following design principles should be considered:

• Logical level
  • Defining the REFERENT PRODUCT CLASIFICATION which would represent single enterprise view of the products on the bank level
  • Defining the LOCAL PRODUCT CLASSIFICATION (particular views of the product) based on product representation in particular organization unit or application. LOCAL PRODUCT CLASSIFICATION must be defined in a way that allows fully mapping to REFERENT PRODUCT CLASSIFICATION. Please note, when we speak about different product views across the organization, which could not be a “pure marketing/sale product definition” story – it is very common situation that for some organization units their view of “product” represents in fact group of transactions grouped by common characteristics.
• Physical level
  • REFERENT PRODUCT CLASSIFICATION should be implemented with PD entities
  • LOCAL PRODUCT CLASSIFICATION should be implemented with CL, CL_SCM entities as classifications
  • Mappings between REFERENT PRODUCT CLASSIFICATION and LOCAL PRODUCT CLASSIFICATION should be implemented with PD_X_CL entity

Benefits of this approach is efficient and effective mapping of product data concept of different views across organization and applications (via LOCAL PRODUCT CLASSIFICATION) and, at the same way ability to establish single enterprise view of product (via REFERENT PRODUCT CLASSIFICATION) on the bank level

Please note that LOCAL PRODUCT CLASIFFICATION realization with CL concept has a significant limitation – you cannot describe such defined products with attributes. However, these kind of products are much more group of transactions, then a “products in traditional way”– that’s make possible to use this approach. Otherwise you should describe all products classifications with PD concept.