Abinitio Interview Questions and Answers

Abinitio is a Latin word meaning from the beginning Abinitio is a tool used to extract, transform and load data (ETL). It is a BI platform comprised of six data processing products such as co>operating system, the component library, data profiler, conduct, graphical development environment, and enterprise Meta> environment. It is also used for data analysis, data manipulation, batch processing, and graphical user interface based parallel processing.

• GDE (Graphical Development Environment)
• Component library
• Co-operating System
• Data profiler
• Enterprise meta-environment (EME)
• Conduct-IT

The Abinitio co-operating system provide features like

• Manage and run Abinitio graph and control the ETL processes
• Provide Abinitio extensions to the operating system
• ETL processes monitoring and debugging
• Meta-data management and interaction with the EME

GDE (Graphical Development Enviroment) is a graphical application for developers which are used for designing and running AbInitio graphs. It also provides:

The ETL process in AbInitio is represented by AbInitio graphs. Graphs are formed by components (from the standard components library or custom), flows (data streams) and parameters.

A user-friendly frontend for designing Ab Initio ETL graphs

Ability to run, debug Ab Initio jobs and trace execution logs

GDE AbInitio graph compilation process results in generation of a UNIX shell script which may be executed on a machine without the GDE installed

The Ab Initio Component Library is a reusable software module for sorting, data transformation, and high-speed database loading and unloading. This is a flexible and extensible tool which adapts at runtime to the formats of records entered and allows creation and incorporation of new components obtained from any program that permits integration and reuse of external legacy codes and storage engines.

Supports serial and parallel layouts

A graph layout supports both serial and parallel layouts at a time.

A multi-file system is a 4-way parallel system

A component in a graph system can run 4-way parallel system.

In Abinitio, dependency analysis is a process through which the EME examines a project entirely and traces how data is transferred and transformed- from component-to-component, field-by-field, within and between graphs.

Enterprise Meta-Environment (EME): It’s an environment for storage and also metadata management (Both business and technical metadata). The metadata is accessed from the graphical development environment and also the web browser or the cooperating command line. It is ab initio repository for any placeholders.

The Data Profiler is an analytical application that can specify data range, scope, distribution, variance, and quality. It runs in a graphic environment on top of the Co Operating system.

Abinition is logically divided into two segments

• Data Integration Portion
• User Interface (Access to the meta-data information)

To connect with Abinitio Server, there are several ways like

• Set AB_AIR_ROOT
• Login to EME web interface- https://serverhost:[serverport]/abinitio
• Through GDE, you can connect to EME data-store
• Through air-command

The file extensions used in Abinitio are

• .mp: It stores Abinitio graph or graph component
• .mpc: Custom component or program
• .mdc: Dataset or custom data-set component
• .dml: Data manipulation language file or record type definition
• .xfr: Transform function file
• .dat: Data file (multifile or serial file)

The .dbc extension provides the GDE with the information to connect with the database are

• Name and version number of the data-base to which you want to connect
• Name of the computer on which the data-base instance or server to which you want to connect runs, or on which the database remote access software is installed
• Name of the server, database instance or provider to which you want to link

To execute graph infinitely, the graph end script should call the .ksh file of the graph. Therefore, if the graph name is abc.mp then in the end script of the graph it should call to abc.ksh. This will run the graph for infinitely.

A SANDBOX is referred for the collection of graphs and related files that are saved in a single directory tree and behaves as a group for the purposes of navigation, version control, and migration.

Multifile is a set of directories on different nodes in a cluster. They possess identical directory structure. The multifile system leads to a better performance as it is parallel processing where the data resides on multiple disks.

The main components in ab initio are here below,

Dedup: To remove duplicates

Join: To join multiple input dataset based on a common key value.

Sort: This component reorders the data. It takes the collation order and dumps data to memory

Filter: Any conditional related removal of data.

Replicate: This is component is mainly for the parallelism as an additional copy of data is useful while any other nodes go unavailable.

mergeT:his component is to combine multiple input data.

To run a graph infinitely, the end script in the graph should call the .ksh file of the graph. Thus if the name of the graph is abc.mp then in the end script of the graph there should be a call to abc.ksh. Like this the graph will run infinite.

Component parallelism: A graph with multiple processes executing simultaneously on separate data uses parallelism

Data parallelism: A graph that works with data divided into segments and operates on each segment respectively, uses data parallelism.

Pipeline parallelism: A graph that deals with multiple components executing simultaneously on the same data uses pipeline parallelism. Each component in the pipeline read continuously from the upstream components, processes data and writes to downstream components.  Both components can operate in parallel.

The Sort Component in Abinitio re-orders the data. It comprises of two parameters “Key” and “Max-core”.

Key: It is one of the parameters for sort component which determines the collation order

Max-core: This parameter controls how often the sort component dumps data from memory to disk

Dedup component: It is used to remove duplicate records

Replicate component: It combines the data records from the inputs into one flow and writes a copy of that flow to each of its output ports

In Abinitio, partition is the process of dividing data sets into multiple sets for further processing. Different types of partition component includes

Partition by Round-Robin: Distributing data evenly, in block size chunks, across the output partitions

Partition by Range: You can divide data evenly among nodes, based on a set of partitioning ranges and key

Partition by Percentage: Distribution data, so the output is proportional to fractions of 100

Partition by Load balance: Dynamic load balancing

Partition by Expression: Data dividing according to a DML expression

Partition by Key: Data grouping by a key

De-partition is done in order to read data from multiple flow or operations and are used to re-join data records from different flows. There are several de-partition components available which includes Gather, Merge, Interleave, and Concatenation.

Air command used in Abinitio includes

air object Is:  It is used to see the listings of objects in a directory inside the project

air object rm: It is used to remove an object from the repository

air object versions-verbose: It gives the version history of the object.

Other air command for Abinitio include air object cat, air object modify, air lock show user, etc.

Roll-up component enables the users to group the records on certain field values.  It is a multiple stage function and consists initialize 2 and Rollup 3.

By using rollup we can’t generate cumulative summary records for that we will be

using scan.

The syntax for m_dump in Abinitio is used to view the data in multifile from unix prompt. The command for m_dump includes

m_dump a.dml a.dat: This command will print the data as it manifested from GDE when we view data in formatted text

m_dump a.dml a.dat>b.dat: The output is re-directed in b.dat and will act as a serial file.b.dat that can be referred when it is required.

EME is said as enterprise metadataenv, GDE as graphical development env and co-operating system can be said as abinitio server relation b/w this co-op, eme and gde is as fallowsco operating system is the abinitio server. This co-op is installed on particular o.s platform that is called native o.s .coming to the eme, its just as repository in Informatica, its hold the metadata, transformations, dbconfig files source and targets information’s. Coming to gde its is end user environment where we can develop the graphs (mapping just like in Informatica) designer uses the gde and designs the graphs and save to the eme or sand box it is at user side. Where eme is at server side.

To run a graph infinitely, the end script in the graph should call the .ksh file of the graph. Thus if the name of the graph is abc.mp then in the end script of the graph there should be a call to abc.ksh. Like this the graph will run infinitely.

Double click on the transform parameter of parameter tab page of component properties, it will open transform editor. In the transform editor click on the Edit menu and then select Add Default Rules from the dropdown.

It will show two options –

• Match Names
• Wildcard

If your lookup file is a multifile and partioned/sorted on a particular key then local lookup function can be used ahead of lookup function call. This is local to a particular partition depending on the key.

Lookup File consists of data records which can be held in main memory. This makes the transform function to retrieve the records much faster than retrieving from disk. It allows the transform component to process the data records of multiple files fast.

Generally Lookup file represents one or more serial files (Flat files). The amount of data is small enough to be held in the memory. This allows transform functions to retrieve records much more quickly than it could retrieve from Disk.

A lookup is a component of abinitio graph where we can store data and retrieve it by using a key parameter.A lookup file is the physical file where the data for the lookup is stored.

Lookup is basically a specific dataset which is keyed. This can be used to mapping values as per the data present in a particular file (serial/multi file). The dataset can be static as well dynamic ( in case the lookup file is being generated in previous phase and used as lookup file in current phase). Sometimes, hash-joins can be replaced by using reformat and lookup if one of the inputto the join contains less number of records with slim record length.AbInitio has built-in functions to retrieve values using the key for the lookup.

An outer join is used when one wants to select all the records from a port – whether it has satisfied the join criteria or not.

There are many ways the performance of the graph can be improved.

• Use a limited number of components in a particular phase
• Use optimum value of max core values for sort and join components
• Minimize the number of sort components
• Minimize sorted join component and if possible replace them by in-memory join/hash join
• Use only required fields in the sort, reformat, join components
• Use phasing/flow buffers in case of merge, sorted joins
• If the two inputs are huge then use sorted join, otherwise use hash join with proper driving port
• For large dataset don’t use broadcast as partitioner
• Minimize the use of regular expression functions like re_index in the transfer functions
• Avoid repartitioning of data unnecessarily
• Try to run the graph as long as possible in MFS. For these input files should be partitioned and if possible output file should also be partitioned.

There are many ways to do it.

• Probably the easiest way is to use Truncate Table
• Run Sql or update table can be used to do the same thing
• Run Program

.dbc file has the information required for Ab Initio to connect to the database to extract or load tables or views. While .CFG file is the table configuration file created by db_config while using components like Load DB Table

Data mapping deals with the transformation of the extracted data at FIELD level

i.e. the transformation of the source field to target field is specified by the mapping

defined on the target field. The data mapping is specified during the cleansing of the

data to be loaded.

For Example:

source;

string(35) name = “interviewgig”;

target;

string(“01”) nm=NULL(“”);/*(maximum length is string(35))*/

Then we can have a mapping like:

Straight move.Trim the leading or trailing spaces.

The above mapping specifies the transformation of the field nm

There are 2 types of graph parameters in AbInitio

• local parameter
• Formal parameters.(those parameters working at runtime)

Basically there are serial and parallel layouts supported by AbInitio. A graph can have both at the same time. The parallel one depends on the degree of data parallelism. If the multi-file system is 4-way parallel then a component in a graph can run 4 way parallel if the layout is defined such as it’s same as the degree of parallelism

In RDBMS the relationship between the two tables is represented as Primary key and foreign key relationship.Wheras the primary key table is the parent table and foreignkey table is the child table.The criteria for both the tables is there should be a matching column.

It will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.

From Abinitio run sql component using the DDL “truncate table by using the truncate table component in Ab Initio

In RUN ==> Deploy >> As script, it create a .bat file at ur host directory ,and then run .bat file from Command prompt

Do you mean by merging Gui map files in WR.If so, by merging GUI map files in GUI map editor it wont create corresponding test script.without testscript you cant run a file.So it is impossible to run a file by merging 2 GUI map files.

abinitiorc is the config file for ab initio. It is found in user’s home directory. Generally it is used to contain abinitio home path, different log in information like id encrypted password login method for hosts where the graph connects in time of execution.

It may contain inf like EME host and others.

Two are graph level parameters but in local you need to initialize the value at the time of declaration where as global no need to initialize the data it will prompt at the time of running the graph for that parameter.

Partition by key: we have to specify the key based on which the partition will occur. It results in well-balanced data due to the key based partitions. It is useful for key dependent parallelism.

Partition by round robin: Distributing data evenly in block size chunks the records are partitioned in a sequential way across the output partition. It is not key based and results are well-balanced data especially with a block size of 1. It is useful for record independent parallelism.

Add Default Rules: Opens the Add Default Rules dialog. Select one of the following: Match Names — Match names: generates a set of rules that copies input fields to output fields with the same name. Use Wildcard (.*) Rule — Generates one rule that copies input fields to output fields with the same name.

1) If it is not already displayed, display the Transform Editor Grid.

2) Click the Business Rules tab if it is not already displayed.

3) Select Edit > Add Default Rules.

In case of reformat if the destination field names are same or subset of the source fields then no need to write anything in the reformat xfr unless you dont want to use any real transform other than reducing the set of fields or split the flow into a number of flows to achieve the functionality.