Skip to main content

Posts

Showing posts from September, 2014

Types of Dimensions ,Types of Facts and Features of Dimension and Fact table

Types of Dimensions A dimension table consists of the  attributes  about the  facts . Dimensions store the textual descriptions of the business. Without the dimensions,  we cannot measure the facts.  The different types of dimension tables are explained in detail below.   Conformed Dimension:  Conformed dimensions mean the exact same thing with every possible fact table to which they are joined. Eg: The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.   Junk Dimension:  A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.  Eg: Assume that we have a gender dimension and marital status dimension. In the fact table we need to maintain two keys referring to these dimensions. Instead of that create a junk dimension w

Executing A Load Plan

Load Plan  A Load Plan is an executable object in  Oracle Data Integrator  that can contain a hierarchy of steps that can be executed conditionally, in parallel or in series. Procedures, interfaces, packages, variables, and can be added to Load Plans for executions in the form of scenarios in a Load Plan. An ODI Load Plan is made up of a sequence of several types of steps. Each step can contain several small steps. Depending on the step type, the steps can be executed conditionally, in parallel, or sequentially. By default, an ODI Load Plan contains an empty root serial step. This root step is mandatory and the step type cannot be changed. Creating a Load Plan  ODI Load Plans appear in both the Designer and Operator Navigator. ODI Load Plans are available for edition in a development and production repositories, and can be organized into scenario folders. "Right Click"  and select  "New"  (Load Plan). Then specify a  "Name"  for the Load Plan.

Performance Tuning Oracle Data Integrator (ODI)

The most common question faced by any ETL developer or a Data Warehouse project implementer is always related to performance. Whatever the tool may be, and however fast the job may be running, the end user will always desire something which is a little faster and quicker. The following tips will help the developer in addressing the performance of an  ETL  task, developed in  Oracle Data Integrator (ODI) . Select appropriate  K nowledge  M odules  (KMs)  to load data.  Each knowledge module available is designed for a specific technology.  Identify this and use the one that fits best your implementation. If you are performing a simple file to Oracle DB transfer, using native Database utilities like  SQLLDR or External Tables  will provide an improved performance. Execution of joins and filters in the appropriate area provides significant improvement in the performance. If there are more records in the source then setting the filter condition to be executed on source, rath

A Detailed Explanation of ODI Knowledge Modules LKM & IKM

Another article in Ashok's series where he examines Oracle Data Integrator (ODI) Knowledge Modules (KM).  For an basic introductory overview, check out  ' What is an ODI Knowledge Module (KM )?' Loading Knowledge Module (LKM) The LKM is used to load data from a source datastore to staging tables. This loading comes into picture when some transformations take place in the staging area and the source datastore is on a different data server than in the staging area. The LKM is not required when all source datastores reside on the same data server as the staging area. An interface consists of a set of declarative rules that define the loading of a datastore or a temporary target structure from one or more source datastores. The LKM executes the declarative rules on the source server and retrieves a single result set that it stores in a "C$" table in the staging area using defined loading method. An interface may require several LKMs when it uses datastores fro

Introduction to Knowledge Modules

1) What is a Knowledge Module? Knowledge Modules (KMs) are code templates. Each KM is dedicated to an individual task in the overall data integration process. The code in the KMs appears in nearly the form that it will be executed except that it includes Oracle Data Integrator (ODI) substitution methods enabling it to be used generically by many different integration jobs. The code that is generated and executed is derived from the declarative rules and metadata defined in the ODI Designer module. 2)     Types of Knowledge Modules: 2.1) Reverse-Engineering Knowledge Modules (RKM) The RKM role is to perform customized reverse engineering for a model. The RKM is in charge of connecting to the application or meta-data provider then transforming and writing the resulting meta-data into Oracle Data Integration's repository. The meta-data is written temporarily into the  SNP_REV_xx  tables. The RKM then calls the Oracle Data Integrator API to read from these tables an