Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Step by Step Creating Workflow in Informatica - PDF

VIEWS: 111 PAGES: 53

Step by Step Creating Workflow in Informatica document sample

More Info
									Extending and Customizing the BI Apps DW
Mark Rittman, Director, Rittman Mead Consulting
Oracle Open World 2008, San Francisco. Session #S301063

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
http://OracleBIWA.org




                        2
Reminder Oracle BIWA Summit 2008
Business Intelligence, Warehousing, Analytics
Oracle BIWA Summit 2008
Oracle HQ, Redwood Shores on Dec 2-3, 2008
Four keynotes, 50+ sessions, vendor displays
Hands-on labs
Early Bird $250 ($200 for IOUG members), Sep 30
Detail are at:
http://BIWASummit08.org
Email:
Conference@OracleBIWA.org


                                                   3
BIWA Speaker Survey – Open World
Please complete the BIWA SIG speaker evaluations
 for Sep 21 at:
http://OracleBIWA.org,
After logging in, “Resources for Members” tab




                                                4
Who Am I?

• Oracle BI&W Architecture and Development Specialist
• Co-Founder of Rittman Mead Consulting
   ‣ Oracle BI&W Project Delivery Specialists
• 10+ years with Discoverer, OWB etc
• Oracle ACE Director, ACE of the Year 2005
• Writer for OTN and Oracle Magazine
• Longest-running Oracle blog
   ‣ http://www.rittmanmead.com/blog
• Chair of UKOUG BIRT SIG
• Co-Chair of ODTUG BI&DW SIG
• Speaker at IOUG and BIWA events




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Rittman Mead Consulting

• Oracle BI&DW Project Specialists
• Consulting, Training, Support
• Works with you to ensure OBIEE and
  Oracle BI Applications project success
• Small, focused team
• OWB, Oracle BI, DW technical
  specialists
• Clients in the UK, Europe, USA




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
What is this Presentation About?

• The Oracle BI Applications are Oracle’s packaged DW & Analytics offering
• Based around OBIEE, comes with pre-built data warehouse, ETL and RPD
• The pre-built offerings covers a wide aspect of EBS, PSFT, Siebel, SAP data
• But what if you want to extend or customize it - how does this work?




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Oracle Business Intelligence Applications

• Packaged set of ETL mappings, dimensional data warehouse and pre-built reports and
  dashboards
• Financial Analytics
• HR Analytics
• Marketing Analytics
• Order Management Analytics
• Sales Analytics
• Service Analytics
• Contact Center Analytics
• Supply Chain Analytics




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Oracle BI Applications Architecture
                                                                                   !!   Role Based Dashboards
                                                          Oracle BI
                                                                                   !!   Analytic Workflow
                          Dashboards by Role             Presentation              !!   Guided Navigation
                                                           Services
                      Reports, Analysis / Analytic
                                                                                   !!   Security / Visibility
                              Workflows                                            !!   Alerts & Proactive Delivery
Administration




                               Metrics / KPIs                                      !!   Logical to Physical Abstraction Layer
                     Logical Model / Subject Areas
                                                          Oracle BI
                                                           Server
                                                                        Metadata   !!   Calculations and Metrics Definition
                                                                                   !!   Visibility & Personalization
                               Physical Map                                        !!   Dynamic SQL Generation

                                     Data Warehouse /                              !!   Abstracted Data Model
                                        Data Model
                    Direct                                                         !!   Conformed Dimensions
                   Access to          Load Process
                                                                                   !!   Heterogeneous Database support
                                                                  DAC




                    Source
                     Data              Staging Area         ETL                    !!   Database specific indexing
                                    Extraction Process
                                                                                   !! Highly Parallel
                                                                                   !! Multistage and Customizable
                                                                                   !! Deployment Modularity

                 Oracle   SAP R/3    Siebel     PSFT     EDW
                                                                   Other
                          Federated Data Sources
                                                                                                                                45


    T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Oracle Data Warehouse Administration Console

• Control panel for running the OBAW load process
• ETL packaged into Execution Plans
• Tight integration with Informatica
• Run jobs, monitor progress
• The added value on top of
  the OBAW and INFA mappings




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Informatica PowerCenter 8.1.1

• The Informatica ETL tool is used to load data into the OBAW
   ‣ Support for multiple platforms, initial work done by INFA to load warehouse
• Similar development
  style to OWB, ODI
• Licensed for loading
  the OBAW only
• Mappings sequenced
  and controlled from
  DAC
   ‣ Positioned as an
     embedded tool




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Prebuilt OBIEE Repository




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Prebuilt Dashboards and Reports




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
The Oracle BI Applications Business Model & Mapping Layer

• The Business Model and Mapping layer
  contains the complete, integrated logical
  data model for the OBAW
• Divided in to core and application-specific
  business models
• Coverage of the business model is
  determined by the products selected
  at installation time




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Demonstration
Oracle Business Analytics Warehouse, DAC and Prebuilt Repository


T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Customizing the Oracle Business Analytics Warehouse

• Although the OBAW comes with many star schemas and many ETL routines, you will
  probably wish to extend and customize it to meet your neds
   ‣ Include flex-field data
   ‣ Adding data from a custom source
   ‣ Adding new facts, dimensions, hierarchies

• Data can come from many sources
  ‣ Sources with adapters and existing load routines you can customize
  ‣ Sources with adapters but no load routines (source unsupported for subject area)
  ‣ Unsupported sources (using the Universal Adapter)




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Customization Types

• OBAW customizations can be divided in to three categories
  ‣ Category 1 are where we add an additional column to an existing star schema
  ‣ Category 2 are where we add a new fact or dimension table
  ‣ Category 3 are where we add
    additional rows to an existing
    fact or dimension, from an
    unsupported source




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Customizing and Creating New OBAW ETL Routines

• Whichever type of customization you perform,
  you will be working with two mappings per
  target fact or dimension
   ‣ An SDE (Source-Dependent Extract) map
     to extract data from the source system
   ‣ An SIL (Source-Independent Layer) map
     to load data into the fact or dimension
• Category 1 mappings involve customizing
  existing SDE and SIL mappings
• Category 2 customizations involve creating
  new SDE and SIL mappings from scratch
   ‣ In this first example, we will perform a
     Category 1 customization and edit
     existing SDE and SIL mappings



 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Source Dependent Extracts


          Source Dependent Extract (SDE)

          !! Source-specific and Universal
             Business Adapters                          Source Independent Layer
          !! Expose simplified business
             entities from complex source
             systems
          !! Converts source-specific data to
             universal staging table format
          !! Lightweight and designed for
                                                          Source Dependent Extract
             performance, parallelism
                                                                      Power         Power
          !! Extensible                                 SQL          Connect       Connect



                                                               SQL




                                                                                                 SQL
                                                                                    ABAP
                                                                      SQL
                                                                       App Layer




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com        App Layer
Source Independent Layer


            Source Independent Layer (SIL)
            !!Encapsulates warehouse load
              logic                                    Source Independent Layer
            !!Handles:
               !!Slowly changing dimensions
               !!Key lookup resolution /
                 surrogate key generation
               !!Insert/update strategic
                                                          Source Dependent Extract
               !!Currency conversion
                                                                     Power         Power
               !!Data consolidation                                 Connect       Connect
                                                        SQL



                                                              SQL




                                                                                                SQL
                                                                                   ABAP
                                                                     SQL
            !!Uses Bulk Loaders on all db
              platforms

                                                                      App Layer




                                                                                    App Layer
T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Customization Scenario #1 : Category 1 Customization

• The W_ORG_D table contains dimensional information on customers
• We wish to add two new columns to the table from our EBS source
• We also wish to follow the Oracle-supported method of customisation
• Two ETL routines populate this dimension table
   ‣ SDE_OrganizationDimension_Customer
   ‣ SIL_OrganizationDimension_Customer




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Introduction to Informatica PowerCenter

• Informatica PowerCenter 8.1.1 is a complete ETL toolkit
   ‣ Very much like Oracle Warehouse Builder,
     but applies to all sources and targets
   ‣ Uses an ETL “hub” to process and
     distribute data
• Server supported on Windows, Unix, Linux
• Client is Windows-only
• Five Key Informatica Client Tools
   ‣ Informatica Designer
   ‣ Informatica Workflow Manager
   ‣ Informatica Repository Manager
   ‣ Informatica Workflow Monitor




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Informatica Designer

• Used for creating mappings
• Registering source and target tables
• The equivalent in OWB
  to the “Design Center”
• This is where you design all
  your mappings, transformations,
  expressions, data loading




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Informatica Repository Manager

• Manages the Informatica Repository
• Versioning of folders, mappings
• Impact analysis and item lineage
• Query repository
• Manage users and privileges




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Informatica Workflow Manager

• Create and execute workflows
  ‣ The equivalent of OWB process flows
• Define relational, file etc connections
• Create tasks (packaged mappings)
• Start and stop workflows
• Restart after errors
  ‣ Starting and stopping workflows
    in Oracle BI Apps is handled by DAC
  ‣ Workflow Manager is used for
    initial creation and packaging of
    workflows and tasks




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Informatica Workflow Monitor

• Used to monitor the progress of workflows
• Shows whether workflows have completed or errored
• Used in the initial creation and testing of workflows
  ‣ Subsequent workflow
    monitoring carried out
    using DAC Console




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Demonstration
Informatica PowerCenter 8.1.1


T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 1 : Create New Folder

• Create a new folder in the Informatica Repository
• Call the folder CUSTOM_SDE_ORA11510_ADAPTER
• This folder will need to be migrated after any software upgrade




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 2 : Copy Across SDE Mapping

• Open the SDE_ORA11510_ADAPTER folder
• Locate the SDE mapping you wish copy
• Select Edit > Copy
• Open the CUSTOM_SDE_ORA11510_ADAPTER
  folder, select Edit > Paste
• The mapping, any mapplets, sources and targets
  will all be copied to the new folder




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 3 : Check Mapping Still Runs

• Before progressing to the customizations, it is best to check that the mapping
  still executes correctly
• You may need to drop existing workflow and recreate it
• Checks that everything is correct before introducing changes




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 4 : Customizing Sources/Targets

• The W_ORG_DS (staging table) needs to be customized to add new columns
   ‣ W_ORG_D will be customized in the next section
• Table needs to be customized using Target Designer, and through SQL*Plus

 sqlplus olap/password@orcl

 alter table w_org_ds
 add x_account_log varchar2(10))
 /

 alter table w_org_ds
 add x_last_login varchar2(10))
 /




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 5 : Locating SDE Mapplet

• Mapplets are encapsulated business logic for extracting from sources
  ‣ Most encapsulated logic you can’t change (not supported), but these ones you can
• Open the SDE mapping, locate mapplet and open it




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 6 : Editing SDE Mapplet

• In this instance, the columns you need are already in one of the source tables
• Source Qualified transformation needs to be edited to bring in the new columns
   ‣ SQ transformations are like views
   ‣ Allow many tables to be resolved
     to a single source
   ‣ Two step editing process
      - Map in columns to ports
      - Edit the underlying SQL




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 7 : Editing SDE Mapping

• Once the new data is available from the mapplet, the SDE mapping can be edited
• New data goes through the X_CUSTOM expression (the “safe path”)
• Presented out through the Target Definition using X_ column naming standard




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 8 : Create new SILOS Folder

• Just as with the SDE Mapping, a new folder for custom SIL mappings has to be created
   ‣ Named SILOS (standard name within OBI Apps)
• Once folder is created, the SIL_OrganizationDimension_Customer is then copied
   ‣ Brings across all source, mapplets, targets
• W_ORG_DS source and
  W_ORG_D target are then
  updated to reflect new columns




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 9 : Edit SIL Mapping Source

• The source element of the SIL is then edited, to bring in the new X_columns
   ‣ The SQ Source Qualified needs to have new data introduced to new ports
   ‣ The underlying SQL for the SQ needs to reflect the new columns




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 10 : Add New Columns to Filter

• New columns in mapping have now to be passed through the existing filter
• Ensures new columns are subject to the same filter as the existing columns
• New columns added after the X_CUSTOM (safe path) column




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 11 : Add Safe Path Expression

• Existing columns go through an expression transformation
• Standard practice is to create a new X_CUSTOM expression (if it is not already present)
  to accommodate the additional columns




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 12 : Copy Columns to Target

• New X_ columns from the expression transformation are then copied to the
• Once X_ data is copied to the Target Definition transformation it is ready to be loaded
  into the data warehouse and available for analysis
• Further step (not in this lab) to integrate into DAC, same as previous exercise




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 13 : Add New INFA Folders to DAC

• The new Informatica Repository folders are then registered in the DAC
• DAC tasks can now run mappings from these folders as part of an execution plan




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 14 : Update Tables in DAC

• The DAC Repository holds details of all tables, columns, mappings etc associated
  with the Oracle Business Analytics Warehouse
• Now that we have added new columns to the W_ORG_DS and W_ORG_D tables,
  we need to reflect these changes in the DAC




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Category 1 Customization Step 15 : Amend DAC Task

• The DAC task to load the dimension can now be altered, to run our customized
  version of the mapping
• This is achieved by changing the
  folder that the DAC goes to for the
  mapping, to point to our
  customized mappings folders instead




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Demonstration
An Original, and Customized, SDE and SIL Mapping


T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Customization Scenario #2 : Category 2 Customization

• We have a new fact and dimension to add to the Sales - Order Lines logical model
   ‣ Return Reasons (W_C_RETURN_REASON_D) dimension
   ‣ Sales Returns (W_C_SALES_RETURN_F) fact
• Each has supporting source and staging tables
• We wish to load these using the standard Oracle methodology
• Four ETL routines are required to load these fact and dimension tables
   ‣ SDE_C_ORA_ReturnReasonDimension
   ‣ SDE_C_ORA_SalesReturnFact
   ‣ SIL_C_ReturnReasonDimension
   ‣ SIL_C_SalesReturnFact




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Creating New SDE and SIL Mappings

• When it is not possible to load new data through customizing existing SDE and SIL
  mappings, you will need to create your own ones
• There is a standard methodology for creating these mappings
• Standard columns in tables record the source of data, the ETL cycle that loaded it
• Standard routines exist to retrieve load IDs, manage multiple data sources, prevent re-
  load of existing data, allow failed load routines to restart
• DAC and Informatica work together to manage the load process
• The key is to understand the control data and control routines that the BI Apps provide




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Standard SIL and SDE Data Items

• For SDE Mappings, the Staging Table need to include the following columns
   ‣ DATASOURCE_NUM_ID : an ID that uniquely identifies each data source
   ‣ INTEGRATION_ID : the primary key from the business source
• For SIL Mappings, the Target Table needs to include the following columns
   ‣ DATASOURCE_NUM_ID : an ID that
     uniquely identifies each data source
   ‣ INTEGRATION_ID : the primary key from
     the business source
   ‣ ETL_PROC_WID : the sequence number of
     the ETL run that populated this row
   ‣ ROW_WID : the primary key of the
     warehouse table




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Passing Runtime Parameters from the DAC to Informatica

• Parameters can be added to mappings
  ‣ Populated from the DAC repository when mapping called by DAC
  ‣ Populated using default values when executed from within Workflow Manager
  ‣ Populated by the DAC when mapping is called as part of DAC Execution Plan




T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Standard Transformations and Mapplets

• Various standard transformations and mapplets are shipped with the BI Apps
   ‣ LKP_Get_Target_ETL_Proc_WID
   ‣ EXP_SCD1_WID etc
• Various standard filters and transformations are used in all mappings
   ‣ Only load data that hasn’t already been loaded
   ‣ Replace missing dimension keys with
     an “unknown” value




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Deploying Customizations and New Dimensions and Facts

• All new mappings, workflows are created in custom Informatica folders
• Execute alongside, or instead of, standard ETL mappings
• Can be added to new subject areas or
  incorporate in existing ones
• Important that all customizations are
  added as they need to be reapplied
  after upgrades
• Oracle will support you if you
  follow their methodology
• We will now go through an example
  of adding a new dimension and fact




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Demonstration
New SDE and SIL Mappings


T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Summary

• The Oracle Business Intelligence Applications are a packaged DW + Analytics solution
  for ERP and other data
• The base install of the Oracle BI Apps provides a pre-built set of data warehouse facts
  and dimensions, ETL routines, RPD metadata and control routines for a number of ERP
  modules
• You can extend and customize these mappings to bring in data not covered by the pre-
  built routines
• The best way to do this is to follow Oracle’s methodology
• Mappings are divided into SDE and SIL mappings
• Common table columns assist with the control and co-ordination of mappings
• This presentation described the most common ways of performing these customizations
• More details can be found on our website or in our Oracle BI Apps Developer Course




 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Further Reading

• Other Rittman Mead sessions
  ‣ #S300201 : Automating Business Decisions with Oracle Real-Time Decisions
  ‣ #S300202 : Data Modeling Techniques using Oracle BI Server
  ‣ #S301063 : Extending and Customizing the Oracle BI Applications Data Warehouse
  ‣ #S301081 : ODTUG Essbase Symposium Part 3 : Oracle Business Intelligence
    Enterprise Edition

• Visit our website for presentations, papers,
  the Rittman Mead Blog
   ‣ http://www.rittmanmead.com

• Contact me at mark.rittman@rittmanmead.com

• Thank you for attending, please don’t forget to
  complete the online evaluation

 T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com
Extending and Customizing the BI Apps DW
Mark Rittman, Director, Rittman Mead Consulting
Oracle Open World 2008, San Francisco. Session #S301063

T : +44 (0) 8446 697 995 E : enquiries@rittmanmead.com W: www.rittmanmead.com

								
To top