1 / 40

BUSINESS INTELLIGENCE/DATA INTEGRATION/ETL/INTEGRATION

BUSINESS INTELLIGENCE/DATA INTEGRATION/ETL/INTEGRATION. AN INTRODUCTION Presented by: Gautam Sinha. What is Business Intelligence.

kylynn-duran
Download Presentation

BUSINESS INTELLIGENCE/DATA INTEGRATION/ETL/INTEGRATION

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. BUSINESS INTELLIGENCE/DATA INTEGRATION/ETL/INTEGRATION AN INTRODUCTION Presented by: Gautam Sinha

  2. What is Business Intelligence Business Intelligence (BI) encompasses the processes, tools, and technologies required to transform enterprise data into information, and information into knowledge that can be used to enhance decision-making and to create actionable plans that drive effective business activity. • BI can be used to acquire • Tactical insight to optimize business processes by identifying trends, anomalies, and behaviors that require management action.  • Strategic insight to align multiple business processes with key business objectives through integrated performance management and analysis.

  3. What is Business Intelligence • Business Intelligence (BI) is about getting the right information, to the right decision makers, at the right time. • BI is an enterprise-wide platform that supports reporting, analysis and decision making. • BI leads to: • fact-based decision making • “single version of the truth” • BI includes reporting and analytics.

  4. Ad hoc Reporting Dashboards Business Strategy Metrics Push Technology Analytical Skills Predictive Analysis Data Warehouse BI is not a single computer system, but framework for leveraging data for tactical and strategic use Used for:

  5. Disparate Data Sources Extract Transform Load Single Reporting Repository Real-time Dashboards Data Input OLTP AIMSPC Static and Ad-hoc Reporting OLTP TIMS DW RECBASS OLTP Graphical Data Analysis ATRRS Other Possible Data Sources RATSS RFMSS How BI Works Together

  6. Components of BI • Data Integration ( Informatica, DataStage) • Data Reporting ( Cognos, Business Objects)

  7. Data Integration • Data integration involves combining data residing in different sources and providing users with a unified view of these data.This process becomes significant in a variety of situations both commercial (when two similar companies need to merge their database) and scientific (combining research results from different bioinformatics repositories, for example). • Data integration appears with increasing frequency as the volume and the need to share existing data explodes It has become the focus of extensive theoretical work, and numerous open problems remain unsolved. In management circles, people frequently refer to data integration as "Enterprise Information Integration" (EII).

  8. How to enable Data Integration USING ETL PROCESS

  9. ETL ( Extract Transform Load) • ETL stands for extract, transform and load, the processes that enable companies to move data from multiple sources, reformat and cleanse it, and load it into another database, a data mart or a data warehouse for analysis, or on another operational system to support a business process

  10. ETL ( Extract Transform Load) “A Properly designed ETL system extracts data from the source systems, enforces data quality and consistency standards, conforms data so that separate sources can be used together, and finally delivers data in a presentation-ready format so that application developers can build applications and end users can make decisions… ETL makes or breaks the data warehouse…” Ralph Kimball

  11. ETL ( Extract Transform Load)

  12. ETL ( Extract Transform Load)

  13. ETL – Process Flow

  14. ETL – Process Flow

  15. ETL Glossary • Source SystemA database, application, file, or other storage facility from which the data in a data warehouse is derived. • MappingThe definition of the relationship and data flow between source and target objects. • MetadataData that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata. • Staging AreaA place where data is processed before entering the warehouse

  16. ETL Glossary • CleansingThe process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process. • TransformationThe process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources. • TransportationThe process of moving copied or transformed data from a source to a data warehouse. • Target SystemA database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse.

  17. ETL Tools

  18. Informatica 8.6 – What & How to work? • What is Informatica 8.6? • Informatica is an ETL tool that delivers an open, scalable data integration solution addressing the complete life cycle for data warehouse and analytic application development. • Informatica provides an environment that can extract data from multiple sources, transform the data according to the business logic that is built in the Informatica Client application and load the transformed data into files or relational targets.

  19. Informatica 8.6– PowerCenter PowerCenter provides an environment that allows you to load data into a centralized location, such as a data warehouse or operational data store (ODS). You can extract data from multiple sources, transform the data according to business logic you build in the client application, and load the transformed data into file and relational targets.

  20. Informatica Architecture 8.6

  21. Informatica Architecture 8.6- Data Flow

  22. Informatica Architecture 8.6- Components

  23. PowerCenter - Components

  24. PowerCenter - Components

  25. Informatica – PowerCenter Domain

  26. PowerCenter - Domain

  27. PowerCenter – Admin Console

  28. PowerCenter – Application Services

  29. Informatica-Power Center Repository Service

  30. Informatica-Power Center Integration Service

  31. PowerCenter – Client Components The Informatica Client is used to manage users, define sources and targets, building mappings and mapplets with the transformation logic, and create sessions to run the mapping logic. The Informatica Client has the following main applications: Repository Manager Designer Workflow Manager Workflow Monitor

  32. PowerCenter – Repository

  33. PowerCenter – Client Components

  34. PowerCenter – Client Components Repository Manager: This is used to create and administer the metadata repository. • The repository users and groups are created through the Repository Manager. • Assigning privileges and permissions, managing folders in the repository and managing locks on the mappings are also done through the Repository Manager

  35. Informatica/Power Center Client Components Designer: The Designer has five tools that are used to analyze sources, design target schemas and build the Source to Target mappings. These are • Source Analyzer: This is used to either import or create the source definitions. • Target Designer: This is used to import or create target definitions. • Mapping Designer: This is used to create mappings that will be run by the Informatica Server to extract, transform and load data. • Transformation Developer: This is used to develop reusable transformations that can be used in mappings. • Mapplet Designer: This is used to create sets of transformations referred to as Mapplets which can be used across mappings.

  36. Informatica/Power Center Client Components • What is WORKFLOW MANAGER? • It’s a tool where you define a set of instructions called a workflow to execute mappings you build in the Designer. • What are workflow manager tools? • It consists of three tools to help you develop a workflow. • Task Developer. Use the Task Developer to create tasks you want to execute in the workflow. • Workflow Designer. Use the Workflow Designer to create a workflow by connecting tasks with links. You can also create tasks in the Workflow Designer as you develop the workflow. • Worklet Designer. Use the Worklet Designer to create a worklet.

  37. Load Design Process • Create Source definition(s) • Create Target definition(s) • Create a Mapping • Create a Session Task • Create a Workflow from Task components • Run the Workflow and verify the results

  38. Informatica Transformations • Informatica – Transformations • In Informatica,Transformations help to transform the source data according to the requirements of target system and it ensures the quality of the data being loaded into target. • Following are the list of Transformations available in Informatica: • Aggregator Transformation • Expression Transformation • Filter Transformation • Joiner Transformation • Lookup Transformation • Normalizer Transformation • Rank Transformation • Router Transformation • Sequence Generator Transformation • Sorter Transformation • Update Strategy Transformation

  39. Informatica Transformations • Aggregator Transformation • Aggregator transformation is an Active and Connected transformation. This transformation is useful to perform calculations such as averages and Sums • Expression Transformation • Expression transformation is a Passive and Connected transformation. This can be used to calculate values in a single row before writing to the Target • Filter Transformation • Filter transformation is an Active and Connected transformation. This can be used to filter rows in a mapping that do not meet the condition. • Joiner Transformation • Joiner Transformation is an Active and Connected transformation. This can be used to join two sources coming from two different locations or from same location • Rank Transformation • Rank transformation is an Active and Connected transformation. It is used • to select the top or bottom rank of data

  40. Any Suggestions Questions?

More Related