Data Warehousing (DW) is a method of gathering and managing data from multiple sources to help organizations with valuable insights. A typical data warehouse is majorly used to integrate and analyze data from multiple sources. Data warehousing is the central source for the BI tools and for visualizing data.

Data Warehousing is associated with various components and technologies that enable the organizations to use the data in a systematic way.  It stores all the business information in an electronic format for further analysis instead of transaction processing. The Data warehouse transforms the data into understandable information and makes it available for business users.

There are four fundamental stages of Data Warehousing they are:

• Offline Operational Databases: Perhaps this is the first stage in which a data warehouse system is developed from copying the operational process into an offline server. This process doesn’t make any impact or disturbance to the actual performance of the system.
• Offline Data Warehouse: In this stage, the operational data gets updated into the warehouse on a timely basis like daily, weekly or monthly. And also the data gets stored in an integrated report oriented way.
• Real-Time Data Warehouse: In this stage, data warehouses are updated whenever an event or transaction happens. A transaction or event includes an order or a booking or a delivery etc.
• Integrated Data Warehouse: In this stage, transactions and activity generated by warehouses go through the operating system and are helpful in the daily functioning of a business.

Informatica PowerCenter is an organization extract, transform, and load (ETL) tool employed in building the data warehouses for an organization. It is a well-developed organization by an Informatica organization that loads data into a centralized point like a data warehouse. Informatica Powercenter extracts data from multiple data sources, transforms and load that data into files. It provides the foundation for major data integrations with external parties.

Data mapping is a process of mapping a field data sources to the targeted file or location. There are multiple data mapping tools available which help the developers in mapping the data from a source file to target file.

Following is the list of components available in Informatica PowerCenter.

• PowerCenter Repository
• PowerCenter Client
• Integration Service
• Data Analyser
• PowerCenter Repository Reports
• PowerCenter Domain
• Administration Console
• Repository Service
• Web Services Hub
• Metadata Manager

It is a reusable object that has a group of transformations and allows us to reuse the transformation logic in different mappings.

Data mining is a process of analyzing huge sets of data to find the hidden valuable insights out of it.  It allows the users to find the previously unknown patterns and relationships between various elements in data. The insights extracted for data mining would help in fraud detection, marketing, and scientific discovery, etc. The other names for data mining are Knowledge extraction, Knowledge discovery, information harvesting, data/pattern analysis, etc.

In data warehousing, a fact table contains metrics, measures or facts about a business process. The fact table is located at the snowflake schema or star schema surrounded by multiple dimension tables. A fact table typically contains two columns in which one contains facts and the other one is a foreign key.

It is a table in the star schema of a data warehouse. While building Data Warehouses dimensional data models use dimension tables and facts. The dimension table is a compilation of hierarchies, categories, and logic.

Following are the two different paths to load data in dimension tables:

• Conventional (slow): Before loading data into a dimension table all the keys and constraints are validated against the data. This process maintains data integrity and it’s a time taking process.
• Direct (Fast): In this process before loading the data into dimensional tables all the constraints and keys are disabled. The constraint and key validation process can be done once you are done with the data loading process. If any set of data found as invalid or irrelated then this data is skipped from the index and from all future processes as well.

Following are the objects you can’t use in Mapplet:

• COBOL source definition
• Normalizer transformations
• Joiner transformations
• Pre or post-session stored procedures
• sequence generator transformations that are non-reusable
• XML source definitions
• Target definitions
• IBM MQ source definitions
• Power mart 3.5 styles of Lookup functions

Following are the different ways to migrate to different environments in Informatica:

• By exporting repository and deploy into a new environment
• By copying objects/folders
• By exporting every mapping to XML and deploying them in a new environment.

By using deployment groups in Informatica

A mapping variable is dynamic in nature and changes through the sessions. The integration service saves the value of Mapping variable in the repository on successful completion of every session. And the same value will be used when we run the session.

A Mapping Parameter is different from a Mapping variable, it is a static value. You are required to define a variable before executing a session and the value you have given remains the same even after successful completion of the session. While executing the session Powercenter validates the value from the Parameter and keeps the same value till the end of the session. Whenever you run the session it values are extracted from the file.

Below mentioned are the ways to eliminate the duplicate records:

• By selecting the distinct option in the source qualifier
• By Overriding a SQL Query in Source qualifier
• By using Aggregator and group by all fields

Following are the various types of repositories that we can create in Informatica:

• Standalone Repository: This is an individual repository that functions individually and is not related to any other repository.
• Global Repository: This is a centralized repository in a domain. This can hold shared objects across different repositories of a domain. All the objects are shred using global shortcuts.
• Local Repository: It is a repository that resides within a domain. This repository can be connected to a global repository using objects of shared folders and using global shortcuts.

We have two data movement modes available in Informatica.  Powercenter decides the handling process based on the instructions provided by the data movement code.  You can select the data movement mode in the configuration. Following are the data movement types.

• Unicode mode
• ASCII mode

OLAP (Online Analytical Processing) is a powerful technology that works behind the scenes to support many business intelligence applications. This application gathers, manages, transforms and presents multidimensional data for analysis purposes.

OLTP (Online Transaction Processing) which consists of online data and normalized tables. It is designed to store the operational data on a continuous basis.  It performs day-to-day operations and is used for data analysis. OLTP store data one transaction at a time.

Following are the two different LOCK’s used in Informatica MDM 10.1:

• Exclusive Lock: This Lock can only allow access to a single user to make changes to underlying ORS and also blocks other users from modifying metadata in the ORS till the Exclusive lock exits.
• Write Lock: This lock allows multiple users at a time to make changes to the underlying metadata.

In the current connection, the hub console is refreshed every 60 seconds. Here users can release the lock manually. The lock will automatically be released when a user switches to another database while having a hold of a lock. When a hub console is terminated by a user the lock gets expired within a minute.

Following are the tools that do not need lock:

• Data Manager
• Merge Manager
• Hierarchy manager
• Audit Manager

To make configuration changes to the database of MDM hub master there are multiple tools that need LOCK. They are:

• Message Queues
• Users
• Databases
• Tool Access
• Security Providers
• Repository Manager

We have multiple tables that can be integrated with the staging data in MDM. They are:

• Raw Table
• Staging Table
• Landing Table
• Rejects Table

The Schema is defined as a data model that is being used in the implementation of a Siperian Hub. In general, a Siperian Hub does not require any specific schema. The Siperian Hub contains a schema and it is independent.

The Siperian Hub consists of various components, each of them has been designed to address specific problems. The following are the various components of Siperian Hub.

Master Reference Manage: It works restlessly to create the most accurate records by performing various tasks such as data cleansing, matching, consolidation, and merging.

Hierarchy Manager: It builds, manages data, and also describes the relationship between various records.

Activity Manager: It performs functions like master data synchronization,  data events evaluation, etc.

Following are the components of Informatica Hub Console:

Design Console: This component is helpful in solution configuration during deployment,  and allows ongoing configuration according to the changing needs.

Data Steward Console: This component is being used to review consolidated data and also matched data queued for exception handling.

Administration Console: Thi component has been used to assign role-based security and various database administrative activities.

A Base object in MDM is used to define core business entities such as products,  employees, customers, accounts, etc.  The base object acts as an endpoint for consolidating data from various systems. The Schema manager is the only way you have to define base objects, it is not allowed to configure in the database.

Informatica Activity Manager (AM)  synchronizes master data,  examines data events, delivers unique views of activity and reference data from the varied sources.

Activity manager provides the following features:

• The activity manager facilitates combining master data that is resided in Informatica hub with analytical and transactional data of other systems.
• The activity manager looks after data modifications, in the Informatica MDM hub and also other transactional applications. And also if any changes made to the data the same will be synchronized across all other systems.

The Hierarchy Manager helps you to manage hierarchy data that is associated with the records you manage in MRM.  Whatever the applications provide data to MRM also store relationship data across master data. This system creates high complexity to manage data relationships because each application is different and has a unique hierarchy. In the same way, every data mart and data warehouse is developed to reflect relationships that are needed for specific purposes.

The following are the various stages in which data is stored into hub stores in a sequential process.

• Land
• Stage
• Load
• Match
• Consolidate