Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

It’s All About The “Data” within an organization

Posted on September 19th, 2012 by Sanjit Anand ||Email This Post Email This Post

Different types and structures of data exist within an organization. Here to go:

dgreybarrow Master Data -Enterprise-level data entities that are of strategic value to an organization. These are typically non-volatile and non-transactional in nature. Typically example of such data are Customer, product, supplier, assets, System Name

dgreybarrow Transaction Data-These are Business transactions that are captured during business operations and processes, such examples are PO, Invoices, PR , Payments etc .

Therefore, Data is intrinsically simple and can be divided into data that identifies and describes things, master data, and data that describes events, transaction data.

dgreybarrow Reference Data- There are Internally managed or externally sourced facts to support an organization’s ability to effectively process transactions, manage master data, and provide decision support capabilities.

Typical example are as such :
Cities within States/Provinces/Territories
Street names within cities
Street types (Dr, Lane, Boulevard…)
Street direction (N, NE, E, S…)
Blocks & Block Groups
Postal codes within states
Months of the year

dgreybarrowUnstructured Data—This is data found in e-mail, white papers like this, magazine articles, corporate intranet portals, product specifications, marketing collateral, and PDF files.

dgreybarrowMetadata -This is defined as “data about the data.” These data are Typically Used as an abstraction layer for standardized descriptions and operations.

dgreybarrow Analytical Data -These data are derivations of the business operation and transaction data used to satisfy reporting and analytical needs. In the organziation such data typically reside in data warehouses, data marts, and other decision support applications.

dgreybarrow Big Data -Data sets whose size is beyond the ability of commonly used software tools to capture, manage, and process the data within a tolerable elapsed time.

Big data typically refers to the following types of data:

  • Traditional enterprise data includes customer information from CRM systems, transactional ERP data, web store transactions, general ledger data.
  • Machine-generated /sensor data – includes Call Detail Records (“CDR”¨), weblogs, smart meters, manufacturing sensors, equipment logs (often referred to as digital exhaust), trading systems data.
  • Social data includes customer feedback streams, micro-blogging sites like Twitter, social media platforms like Facebook

Posted in Conversion, MDM | No Comments »

Golden rules for a Successful Data Migration

Posted on June 21st, 2009 by Sanjit Anand ||Email This Post Email This Post

Top 10 Key Tips for Successful Data Migration are:

  1. Scope – Clearly define the scope of the project.
  2. Profiling -Actively refine the scope of the project through targeted profiling and auditing.
  3. Volumn – Minimize the amount of data to be migrated.
  4. Documentation – Profile and audit all source data in the scope before writing mapping specifications.
  5. Time, cost – Define a realistic project budget and timeline, based on knowledge of data issues.
  6. Sign-offs -Secure sign-off on each stage from a senior business representative.
  7. Prioritize with a top-down, target-driven approach.
  8. Testing – Aim to volume-test all data in the scope as early as possible at the unit level.
  9. Allow time for volume testing and issue resolution.
  10. Segment the project into manageable, incremental chunks.

Posted in Conversion | No Comments »

Data Migration – Data Profiling

Posted on January 22nd, 2008 by Sanjit Anand ||Email This Post Email This Post

In Typical Data Migration activity, the most effective way of delivering a data migration program is to fully understand the datasources before starting to migration code. This is best achieved with a complete profiling and audit of all source data within the scope at an early stage, doing this it gives these benefits:

  • With complete visibility of all source data, you can identify and address potential problems that might have remained hidden until a later stage.
  • The rules for planning, mapping, building, and testing migration code can be based on a thorough analysis of all source data rather than a small sample set.
  • Decisions can be based on proven facts rather than assumptions.
  • Early data validation can assist with the choice of the migration method.

dgreybarrow Data Profiling

This is defined as “assessment of the existing source data that determine the relative levels of data quality based upon predefined parameters”

Data profiling is an initial step in the data quality analysis that focuses on understanding the attributes of the data (e.g., completeness, uniqueness, range of values). In addition to overall data statistics, profiling also provides information related to:


Dimension Description
Reasonable Values Some attributes have a certain range of acceptable values based on another field. These types of business rules will analyze the field to identify outlier values.
Validity Test for valid fields based on the type of data. For example, all Supplier must have a valid address.
Completeness Business rules that analyze data to determine if required fields are populated. These rules are necessary to ensure attributes that are required for a successful data load are populated
Format Format business rules will analyze the data for proper formatting based on target product (Oracle if you are doing in Oracle) requirements. Different code fields as well as other required fields within the legacy data are analyzed to determine if the format is appropriate for Oracle.

A particular field might need to be unique depending on another field.

Required vs. Non-Required

When creating business rules, some are categorized as required vs. others as non-required. Required business rules are necessary to be remediated before the data can be loaded into Oracle.

Non-required rules are categorized as those that are needed in order for data to be business ready.

In data Profilic, the base source tables/files having been identified, profiling and auditing tools are now used to look at the data content of all potential sources to understand the data and identify what needs to be migrated.This stage helps in detecting possible conflicts and drilling down to a detailed level to resolve any issues and inconsistencies.

Key benefits of profiling and auditing are that they enable you to:

  • Create a single repository for all analysis, regardless of source system
  • Gain clear visibility into and access to all data problems, with the ability to investigate anomalies to any required depth
  • Identify unknown data issues by making it easy for nontechnical staff to find the answers to questions they didn’t know they needed to ask
  • View any inconsistency across the full scope of the data to be migrated and assess its impact on the whole migration project
  • Establish a single way of conducting analysis across the project
  • Remove dependence on technical source system owners and their time
  • Use a simple, business-friendly interface to review issue
  • Ask questions about technical and business inconsistencies through the same user interface

Posted in Conversion | No Comments »

Data Migration – Why Data Quality important ?

Posted on December 22nd, 2007 by Sanjit Anand ||Email This Post Email This Post

Poor data quality in source systems is always addressed before or during the migration process. Regardless of structure, type, or format, source data intended for migration should be validated in terms of the all key attributes:

When ever you are doing a data migration, make sure you have understanding for different dimensions of data quality are as:


Dimension Description
Relevance Is it relevant to its intended purpose?
Accuracy Does the data accurately represent another verifiable source in the system?
Completeness Does it provide all the information required?Is all necessary data present?
Integrity Do the data values fall within the acceptable ranges defined by the business? or Is the structure of data and relationships among entities and attributes maintained consistently?
Validity Is it within acceptable parameters for the business?
Timeliness Is it up to date and available whenever required?

Is it consistent and easily to understand?Is all necessary data present?

Accessibility Can it be easily accessed and exported to the target application?
Compliance Does it comply with regulatory standards?

Posted in Conversion | No Comments »

Conversion & AIM’s Deliverables

Posted on August 2nd, 2007 by Sanjit Anand ||Email This Post Email This Post

Do we have any AIM’s deliverables during conversion?

Yes..because data need to moved from old system to newer system thus, it is required to understand how important is during implementation time line. Application implementation methodology (AIM’s) does has categorize conversion and migration as a separate sub process process and it consist of various deliverables

Here are the list with there document number:conversionaims

  • CV 010 – Conversion Scope, Objectives, and Approach
  • CV020 – Conversion Strategy
  • CV030 – Conversion Standards
  • CV040 – Conversion Environment
  • CV050 – Conversion Data Mapping
  • CV055 – Conversion Detailed Data Mapping
  • CV060 – Manual Conversion Strategy
  • CV065 – Design Conversions and Interfaces
  • CV070 – Conversion Program design
  • CV080 – Conversion Test Plans
  • CV090 – Conversion Programs
  • CV095 – Custom Software Programs
  • CV100 – Conversion Unit Test Results
  • CV110 – Conversion Business Objects Test Results
  • CV120 – Conversion Validation Test Results
  • CV130 – Installed Conversion Software
  • CV140 – Converted and Verified Data

A close look on AIMs Tasks and Deliverables in Conversion Process:

The major tasks and corresponding deliverables during conversions are summerzied below. Please take a note , this is for your information purpose based out of AIM’s v3.1.


Posted in Conversion | 1 Comment »

Conversion Methodology

Posted on August 1st, 2007 by Sanjit Anand ||Email This Post Email This Post

In one of previous post , i have discussed the basics of interface ,conversion and migration.Lets take a more detailed life cycle for data conversion/migration activity. This activity can not be ignored in any Oracle ERP transformation project.

A conversion does follow there own methodology , being a typically methodology it consist of certain task and subtask identified at sub activity level . Here are some of them as discussed below.

1. Movement of Data or Transport of data

This is where you have to plan the movement of data from an external system/old system to Oracle Applications which normally consider within a conversion project plan. The more important is developing a detailed conversion plan for each entity, listing all design, development, testing, and conversion tasks. You have also include resource,software, and hardware requirements to successfully convert each entity.

2.Design Processconversion

This is where you have to decide what need to convert. This start with identifying all objects first there corresponding volume.

  • Examine the business objectives and requirements to determine the data to be converted.
  • Specify time constraints for the conversion, especially for transaction data.
  • Determine the appropriate conversion method, it is not recommended to go for manual entry.If data volume is low , try to find alternate product.
  • Then need is performing data mapping.
  • Then its required to install all hardware and software required for the conversion process.
  • Determine the testing requirements. Identify testing method if available in Oracle else design a custom query to compare the result.

3.Developing Programs

This process does consist of :

  • Writing extract and import programs.
  • Scripts to create any interface or translation tables in Oracle RDBMS.
  • Writing validation, translation, and migration programs.
  • Write verification scripts and reports.

4. Performing Conversion

This is process in which major activity is performed, this consist of:

  • Extract and format data.
  • Create temporary interface tables.
  • Upload data to interface tables.
  • Run translation programs & validation programs.
  • Migrate data into production tables.
  • Run verification scripts.
  • Run application reports to verify converted data.

5. Data Verification

In this phase each converted entity, design a conversion process from data extraction through data verification. Main consideration is business objectives and dependencies for each point in the process.

Posted in Conversion | No Comments »

Data Conversion,Migration and interface ..Why important

Posted on June 9th, 2007 by Sanjit Anand ||Email This Post Email This Post

Do you know how many ways; we can enter the data into oracle application. Most of us can guess three different ways as:

  • The Data can be entered using the application Screens.
  • The data can be entered using Oracle’s Open System Interface.
  • The data can be stored in the database table directly

But those who works in some complex business environment may figure out some of more like:

  • 3rd Party Software (for the third options)
  • Taviz (formerly SmartDB) which is EAI tool.
  • Crossroads
  • See Beyond (formerly STC)
  • Vitria
  • Data Loader: They have macro enabled spreadsheet kind of tool
  • More4apps

And there are many more, but most of these are used for master data, and few cases for transaction data via Open interface if available.

Importance of data conversion/migration and interfaces within any ERP implementation project can’t be ignored. Since ERP mostly deals with data which finally leads into Information , thus it is equally important to understand the aspect how “data” is important in any ERP system specailly in implementation phase, no matter how simple and unified operation is. Since I been involved in some big transformation oracle apps project thus It is a absolute a good cause to share some information about integration touch point, conversion/migration and interface development to someone who is very new to ERP world as well as Oracle application.

Let’s start with some common situation, we have three cases,

  1. The Customer is running there some home grown IT application which cater most of the company need. Now management has decided to go for any ERP solutions, then the question what will happen for data which is already in the existing application?
  2. Another situation is already using ERP
    a. They want to upgrade to higher version…presuming the structure of some table get changed? Lets say 10.7 to 11i
    b. The company is acquired or merged with some other company, and the whole data need to move into the parent or child company .
    c. They want to enable some additional modules within existing application.
  3. There are few data interacting with both the cases irrespective of database technology for where data is coming and going based out of need.

The answer of the 1 is data migration and 2 is more pronounced as data conversion where as thirds is popularly known as Interface. The ways these are working haven’t much difference but it is more important to understand definition and need. I never found any big difference between migration/conversion unless there is huge transformation of data, the only things figure out is conversion might required some less steps to perform, as assumption is that set up related stuff been already been taken care before execution of activity.

Let’s understand like this: Data Migration as a process of moving required (and most often very large) volumes of data from our clients’ existing systems to new systems. Existing systems can be anything from custom-built IT infrastructures to spreadsheets and standalone databases. Data conversion can be defined as a process of converting data from one structural form to another to suit the requirements of the system to which it is migrated.

Lets take a deep drive to understand better:

Why conversion/Migration is more important in ERP?

Before Go-Live in the production environment the required master data, the open transaction data and the historical transaction data need to be imported from the old legacy applications to Oracle Applications. Since data structure and data design in legacy systems are different from those of Oracle Applications, data need to be messaged/ converted satisfying the business rules to suite the Oracle requirement. Initial data can be migrated by any other means as discussed above depending upon cetain paramater like Volumn, usage, complexity , business rule etc..

How we Define Data Conversion

  • Process where existing data from the client’s old system is extracted, cleansed, formatted, and installed into a new system.
  • These can be manual or automated.
  • The big difference is that these are One-time only process that requires extensive testing and preparation.
  • They must be executed and performed before a system goes into production.

What Is An Interface then

  • These are programs for connection between Two Systems In Order To Synchronize the Data.
  • They can be Manual, Batch or Real-Time.
  • Used Repeatedly and Should Therefore Be Designed and Constructed In the Most Efficient Manner Possible.
  • These can Be Triggered by an Event (Such As Running A Concurrent Program) Or It Can Be Scheduled To Run At A Certain Time.
  • Can Be Very Costly To Construct And Maintain.

Does the conversion/migration/interface have Life Cycle

Yes, they have, because they have a significant efforts required in development and design and implementation

  • Functional Designer works with business owners to determine the data mapping and complete the functional design using the Design Templates.
  • If the interface/conversion is automated, the Technical Designer converts functional requirements into technical specs for the construction of the interface programs.
  • The developer uses the functional and technical designs to build and test the interface/conversion programs.
  • More rounds of testing are done until the interface/conversion is migrated to the production environment for deployment.

Conversion is assumed as one time activity but never looks like small activity which can be performed with couple of days.


How conversion and interface differ?

There are good numbers of parameter on which they can be categorize. Take few of them:

  • Frequency
    • conversions are a one time event
    • interfaces are ongoing
  • Occurrence in the project timeline
    • conversions executed before production
    • interfaces executed during production
  • Manner of execution
    • conversions are batch
    • interfaces may be batch or real time
  • Complexity
    • Conversion does have very complex, its totally depends upon the data mapping activity.
    • coordinating with other systems make interfaces more complex
  • Maintenance
    • Maintence of interface is bit cost intensive task.

Interface Type
You have learned how interface is differ from Conversion/Migration. Now lets take few types of interfaces:
Normally in any system , there are two kinds of interface as:

Inbound Interfaces

  • An inbound interface receives data from one system (legacy) and inserts into Oracle open interface tables.
  • A typical inbound interface would follow these steps:
    1. Extract data from legacy system into a flat file.
    2. Use SQL*Loader or equivalent tool to upload information into a temporary table.
    3. Write a PL/SQL program to take data from the temp table and insert into the Open Interface Tables.
    4. Through the concurrent manager in Oracle Applications, run the standard Oracle Interface program to transform interface tables into Oracle data.


Outbound Interfaces

o An outbound interface takes data from Oracle tables and inserts it into an external system (via tables or flat file).
o A typical outbound interface would follow these steps:
– Write a PL/SQL program to extract data from Oracle base tables into a flat file.
– Use a custom program to read that data and post it into the legacy system


Do we have some other standard way to do interface?

  • Open Interface is a table based interface registered as a concurrent program
    • process records in batches.
    • spawned(Pro-C) or PL/SQL based programs.
  • API (Application Program Interface) is a parameter based stored procedure
    • directly impacts base database tables.
    • may be called from Oracle open interfaces,Forms, Reports.
  • EDI (Electronic Data Interchange) uses industry standard data definitions(US/ANSI/X.12) for transmission of documents such as PO’s, Invoices, Sales Order, etc.Oracle provides some EDI transactions through EDI Gateway.(
  • Enterprise Application Integration (EAI) solutions are often used when complex integration requirements exist.

What Is An Open Interface Table (OIT)?

  • For inbound interfaces, the interface table is the intermediary table where data from the source application temporarily resides until it is validated and processed into an Oracle base table through a standard import concurrent program.
  • Open Interface Tables are standard Oracle tables.
    • Oracle uses OITs to provide a simple interface to Oracle base tables.
    • Oracle has list of all the open interface that oracle offered in there product.

Oracle Interface Program

  • Most Oracle modules have standard import programs (concurrent processes) to facilitate custom inbound interfaces. The specific processing performed varies by application.
  • These programs pull data from the open interface tables, validate the data, and then insert into one or more Oracle base tables.
  • Upon successful completion of processing, the program deletes the processed rows from the interface table or marks them as completed.
  • Depending on the import, errors can be viewed in various ways (exception reports, error tables, forms, etc…).

Examples of standard import programs:

  • GL: Journal Import
  • AP: Payables Open Interface
  • AR: Customer Interface
  • INV : Item Import
  • AR – Autoinvoice

Ok, thats is all about Conversion and Interfaces briefing. I will write some more for Tools used for Conversion/Interface and will discuss some granular details about a conversion/migration project and share some information about how and where AIM’s documents fits into conversion and Migration projects. So watch out this space for some more stuff for conversions.. Till than..your comment and requset you to share some information relaeted to these areas.icn thumbs 32x32


Posted in Conversion | 66 Comments »