Informatica Tutorial

 Informatica is a widely used ETL tool for extracting the source data and loading it into the target after applying the required transformation. In the following section, we will try to explain the usage of Informatica in the Data Warehouse environment with an example. Here we are not going into the details of data warehouse design and this tutorial simply provides the overview about how INFORMATICA can be used as an ETL tool.

Note: The exchanges/companies that are explained here is for illustrative purpose only.

Bombay Stock Exchange (BSE) and National Stock Exchange (NSE) are two major stock exchanges in India in which the shares of ABC Corporation and XYZ Private Limited are traded between Mondays through Friday except Holidays.  Assume that a software company “KLXY Limited” has taken the project to integrate the data between two exchanges BSE and NSE.

In order to complete this task of integrating the Raw data received  from NSE & BSE, KLXY Limited allots responsibilities to Data  Modelers, DBAs and ETL Developers. During this entire ETL process,  many IT professionals may involve, but we are highlighting the  roles of these three personals only for easy understanding and  better clarity.

• Data Modelers analyze the data from these two sources(Record Layout 1 & Record Layout 2), design Data Models, and then generate scripts to create necessary tables and the corresponding records.
• DBAs create the databases and tables based on the scripts generated by the data modelers.
• ETL developers map the extracted data from source systems and load it to target systems after applying the required transformations.

Data Cleansing for Data Warehousing

How important is Extract, Transform, Load (ETL) to data Warehousing?

Politicians raising money can be used as an analogy to compare data cleansing to data warehousing. There is almost no likelihood of one existing without the other. Data cleansing is often the most time intensive, and contentious, process for data warehousing projects.

What is Data Cleansing?
The elevator pitch: "Data cleansing ensures that undecipherable data does not enter the data warehouse. Undecipherable data will affect reports generated from the data warehouse via OLAP, Data Mining and KPI's."
A very simple example of where data cleansing would be utilized is how dates are stored in separate applications. Example: 11th March 2007 can be stored as '03/11/07' or '11/03/07' among other formats. A data warehousing project would require the different date formats to be transformed to a uniform standard before being entered in the data warehouse.

Why Extract, Transform and Load (ETL)?
Extract, Transform and Load (ETL) refers to a category of tools that can assist in ensuring that data is cleansed, i.e. conforms to a standard, before being entered into the data warehouse. Vendor supplied ETL tools are considerably more easy to utilized for managing data cleansing on an ongoing basis. ETL sits in front of the data warehouse, listening for incoming data. If it comes across data that it has been programmed to transform, it will make the change before loading the data into the data warehouse.
ETL tools can also be utilized to extract data from remote databases either through automatically scheduled events or via manual intervention. There are alternatives to purchasing ETL tools and that will depend on the complexity and budget for your project. Database Administrators (DBAs) can write scripts to perform ETL functionality which can usually suffice for smaller projects. Microsoft's SQL Server comes with a free ETL tool called Data Transforming Service (DTS). DTS is pretty good for a free tool but it does has limitations especially in the ongoing administration of data cleansing.
Example of ETL vendors are Data Mirror, Oracle, IBM, Cognos and SAS. As with all product selections, list what you think you would require from an ETL tool before approaching a vendor. It may be worthwhile to obtain the services of consultants that can assist with the requirements analysis for product selection.

Figure 1. ETL sits in front of Data Warehouses

How important is Data Cleansing and ETL to the success of Data Warehousing Projects?
ETL is often out-of-sight and out-of-mind if the data warehouse is producing the results that match stakeholders expectations. As a results ETL has been dubbed the silent killer of data warehousing projects. Most data warehousing projects experience delays and budget overruns due to unforeseen circumstances relating to data cleansing.
How to Plan for Data Cleansing?
It is important is start mapping out the data that will be entered into the data warehouse as early as possible. This may change as the project matures but the documentation trail will come in extremely valuable as you will need to obtain commitments from data owners that they will not change data formats without prior notice.
Create a list of data that will require Extracting, Transforming and Loading. Create a separate list for data that has a higher likelihood of changing formats. Decide on whether you need to purchase ETL tools and set aside an overall budget. Obtain advice from experts in the field and evaluate if the product fits into the overall technical hierarchy of your organization.

Data Warehouse – Data Model

Dimensional Modeling Techniques
What is Dimensional Modeling?

• Dimensional Modeling is a logical design technique that seeks to present the data in a standard framework that is intuitive and allows for high performance access.

Strengths of Dimensional Modeling

• Predictable, standard framework   (facts, dimensions)
• Gracefully extendable
• Standard Approaches to Standard Problems
• Easy management of aggregates

Most Important Terminology – Data Warehouse
STAR Schema

- A database design that stores a central fact table surrounded by multiple dimension tables
- Star schema represents a compromise between the fully normalized model and the denormalize
- OLAP Characteristics
- d model

Fact table
-   Contains Keys to dimensions, and measures
-   Measures are typically described as the performance measures of the business
-   Usually numerical, additive and represent counts, currency amounts, percentages or ratios
-    Examples of measures are transaction amounts, balance, count of approved and declined transactions

Dimension table
-    An entity by which the business views the measures (facts)
-   Dimensions are groupings of similar data into a larger category
-   Dimensions may be hierarchical in nature, like Time – days into months, months into quarters
What is OLAP?

• OLAP is an On-line Analytical processing technology which creates new business information from existing data , through a rich set of business transformations and numerical calculations.

- Involves drilling down to lower levels
- Involves roll-ups to higher levels of summarization

ETL Tools

• Informatica
• Data Integrator
• Data Stage
• MS SQL-Server DTS
• Ab Initio
• Data Junction

TOOLS for Front End Analysis (BI tools)

• SAP BusinessObjects
• Cognos
• Web Focus
• Brio
• Hyperion
• Micro Strategy

What is Business Intelligence?

• Business Intelligence is a broad category of application and technologies for:

–         gathering,
–         storing,
–         providing access and
–         analyzing data

to help enterprise users make better, faster  business decisions.
Why Business Intelligence?
–   By definition, the moment any given business is operating, it begins generating data. Some obvious examples are banking, sales, production data, etc.
–  In addition there also exists large volumes of data which are important to the business but not directly generated by business operations. Examples are market data, competitive data, tenders and proposal etc.
–   As such, none of the above described information can be used in its raw form by corporate management to make decisions although the information is critical in helping make those business decisions.
–   Therein lies the necessity for Business Intelligence.
–   BI technologies help bring decision-makers the Data in a form they can quickly digest and apply to their decision making.
–    BI turns Data into Information for people making decisions in a company.
Business Intelligence applications include the activities of:
–    Decision support systems,
–    Query and reporting,
–   Online analytical processing (OLAP),
–    Statistical analysis, Forecasting, and
–    Data Visualization

BI Benefits
–        Make better decisions by turning enterprise data into real information
–        Gain a competitive advantage by getting timely, flexible, sophisticated analysis of corporate data

DATA WAREHOUSING ARCHITECTURE

An analysis and reporting system that covers all the areas an organization requires to support its business decisions at an enterprise level.
Reporting Pressures
Relieve reporting pressure on transactional databases.
Restructure Data
Restructure data to speed up data analysis and reporting capabilities.
Reduce Complexity
Reduce the user complexity associated with generating new reports
Clean Data
Create a repository of “clean data” that does not require wholesale changes to the transactional systems or business processes.
Multiple Source Analysis
Allow easier reporting across multiple transactional systems and external data sources.
Historic Analysis
To provide a data source supporting a longer span of time than can be reasonable supported on the transactional systems.

ETL Architectures – Concepts and Implementation

ETL Architectures is an in-depth, technical course that teaches the concepts for designing and implementing the appropriate architectures to use in managing the extraction, transformation and loading (ETL) of data for:

    High performance decision support environments (data warehouses, dimensional data marts, Operational Data Stores (ODS), etc)
    Master Data Management hubs (Customer Data Integration (CDI), Product Information Management (PIM), etc)
    General data integration (e.g. Service Oriented Architectures (SOA))

This course will review these architectures and concepts with the primary focus on the concepts and techniques that apply to various approaches to ETL. Participants will learn when to use certain techniques, based on their technical and business requirements. With hands-on workshops, attendees will study different ETL products and methodologies for implementation in today’s heterogeneous system environments.
Benefits To Your Company

By learning the best way to design ETL architectures, architects and ETL developers will be able to implement the appropriate tools and techniques to satisfy business requirements and relate them to the supporting data structures. They will:

    Understand the concepts of extraction, transformation and loading in decision support systems, master data management systems, SOA environments.
    Understand the various forms of data architectures and how to apply ETL techniques to these
    Understand sophisticated techniques for more complicated ETL solutions (real-time, high volume, etc)
    Construct ETL architectures that are flexible to support changing business and technical requirements
    Learn about the most common ETL products and their strengths and weaknesses.

Who Should Attend

    Data Warehouse Architects
    Enterprise Architects (Data, Technical)
    ETL Developers
    Data Architects
    Business Intelligence designers
    Database designers
    Database administrators (DBA)

What Makes This Certified Course Unique

This ICCP-certified course provides participants with practical, in-depth understanding of how to create appropriate ETL architectures for decision support and data integration solutions. Hands-on workshops throughout the course will reinforce the learning experience and provide the attendees with concrete results that can be utilized in their organizations.
Course Outline

    Review common system architectures
        Transaction Processing
        Decision Support
        Master Data Management
        Service Oriented Architecture
    ETL Concepts
        General principles
        Design and plan for reuse
        Design for error handling
        Design for performance
        Design for maintainability
        ETL Standards
        ETL and Meta Data
        ETL Tool Usage
    ETL for Decision Support
        ETL for the Data Warehouse
            Data Sourcing / Changed Data Capture
            Data Transport
            Data Staging
            Changed Data Determination
            Loading normalized warehouse structures
        ETL for the Data Mart
            Surrogate key lookup and assignment
            Slowly Changing Dimensions - Types 1,2, 3 & 6
            Denormalization and impact on ETL
            Populating “junk” dimensions using a Cartesian product
            Aggregation
        ETL for the ODS
            Real/near time approaches
            Data Modeling differences
        Row level security
        Closing the loop
    ETL for Master Data Management (MDM) and Service Oriented Architectures (SOA)
        Customer Data Integration (CDI)
        Product Information Management (PIM)
        Integrating ETL and SOA environments
        Integrating ETL with Data Quality tools
        Integration with OLTP systems
    ETL Tools
        Leading ETL tool vendors
        ETL tool strengths / weaknesses
        Choosing the correct ETL tool
    High performance ETL
        Indexing (b-tree, bitmap, join indexes, etc)
        Forms of Parallelism
        RDBMS tuning and ETL
        Massively Parallel Processing (MPP) platforms vs. Symmetrical Multiprocessing (SMP) platforms
        ETL query optimization
    Workshop conclusion
        Summary, additional exercises, sources for further reading, etc.

SOURCE:http://www.ewsolutions.com/education/data-warehouse-training/document.2008-04-23.8274387713