1 / 37

Oracle Business Intelligence Applications Technical Hands-on Workshop – Day 2

Oracle Business Intelligence Applications Technical Hands-on Workshop – Day 2. Agenda – Day 2. <Insert Picture Here>. Discussion: DAC Execution Review. DAC Execution. Observations on the process What sort of “order” is used? SDE, SIL, PLP? How about indexes, when do they get created?

arawn
Download Presentation

Oracle Business Intelligence Applications Technical Hands-on Workshop – Day 2

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. Oracle Business Intelligence ApplicationsTechnical Hands-on Workshop – Day 2

  2. Agenda – Day 2

  3. <Insert Picture Here> Discussion:DAC Execution Review

  4. DAC Execution • Observations on the process • What sort of “order” is used?SDE, SIL, PLP? • How about indexes, when do they get created? • Observations on Run Information • Anything else that stands out?

  5. <Insert Picture Here> Lab Exercise:Data Warehouse Load Confirmation Lab

  6. Data Warehouse Load Confirmation • Lab 2.3 Warehouse Load Confirmation – Main Steps • Start Oracle Business Intelligence Enterprise Edition (OBIEE) • ./oc4j –start • ./run-sa.sh start • ./run-saw.sh start • Review data using Interactive Dashboards and Answers • Duration: 1.0 hour For more information on Oracle BI Enterprise Edition, refer to the documentation located on the DVD in the BIEE_10_1_3_3_Docs folder

  7. <Insert Picture Here> Lab Exercise:Informatica Overview Lab (Viewlet)

  8. Informatica Tools Overview (Viewlet) • Lab 2.4 Informatica Tools Overview – Main Steps • Review features of the Informatica tool • Informatica Repository Manager • Informatica Designer • Informatica Workflow Manager • Duration: 1.0 hour For more information on Informatica, refer to the Informatica product documentation

  9. <Insert Picture Here> Presentation:Oracle Business Analytics Warehouse Customization

  10. OBAW Customization Scenarios • Categorized by data sources and modification types

  11. Type I CustomizationAdding Additional Columns • Type I customizations involve extracting additional columns from source systems that are already mapped and loading the data to existing data warehouse tables. • Extend mappings • Existing mappings and tables are extensible • Sample placeholders demonstrate how to pass and store additional data • DO NOT modify existing logic or columns

  12. Type I Customization – Safe Path

  13. Type I CustomizationExtension Categories • Exposed objects: sources, targets, nonreusable transformations • Can be changed in the form of extensions (additive) • Must never modify existing preconfigured logic • Encapsulated objects: mapplets, reusable transformations • Cannot be changed • Custom objects: objects not shipped by Oracle • Can be added to a mapping • Never changed during an upgrade • Minimize the number of changes to exposed objects by using custom objects • Preferred approach if ETL time is acceptable

  14. Type I CustomizationTypical Extension Steps • Copy the appropriate mappings to a custom Informatica folder • Extend the source and target tables by making changes to the tables in the database • Extend the SDE and SIL mappings by bringing in the additional columns. • Copy the appropriate workflows to the custom Informatica folder. • Modify the workflows. • Update the DAC with the necessary changes.

  15. X_CUSTOM One extension column is provided in the table It serves as a template to be followed for extensions / customizations Every ETL map should have a X_CUSTOM flow which begins from the source table or source qualifier and goes all the way to the target. X_CUSTOM Extension

  16. Type II CustomizationAdding Additional Tables • Use pre-packaged adaptors to add new fact or dimension tables to the data warehouse, regardless of whether they are already mapped. • Build new SDE and SIL mappings. • Use required system columns • Register tables and indices in DAC • Register new tasks for Informatica workflows, assemble subject areas and build execution plans in DAC • Use naming convention WC_TABLENAME_<table type>

  17. Type II CustomizationRequired Columns • Staging tables • INTEGRATION_ID, DATASOURCE_NUM_ID • Fact, dimension and extension tables • INTEGRATION_ID, DATASOURCE_NUM_ID, ROW_WID, ETL_PROC_WID

  18. Type II CustomizationConsiderations • Create custom SDE and SIL folders in Informatica and make changes to them. • Do not change objects in shipped folders • Create custom workflows for all customized mappings • Each workflow should load only one table. • Workflow name should match a session name that is used inside the workflow. • Set the appropriate source and target connection values in Informatica Designer. • Register workflows in the DAC.

  19. Customization Labs Goals of this set of labs: • Allow you to gain high-level working skills with Informatica PowerCenter tools • Familiarize you with the steps needed to customize and extend the OBAW in line with Type I customizations

  20. Considerations to keep in mind • The Informatica portions will be conducted using a viewlet so you will perform actions inside the viewlet that do not reflect real behavior exactly. These deviations will be noted in the lab steps where appropriate. • The end state mapping has already been completed. • You cannot make a mistake. Seriously, if you make a mistake the viewlet will not advance (of course you can always advance using the forward button at the bottom of the viewlet).

  21. Use Case • Executives want to see additional data elements related to a customer outreach initiative in the reports that are related to customers. • The outreach initiative information is maintained in an Excel spreadsheet by the Director of Customer Satisfaction.

  22. Use Case: Technical Enablement • In the Oracle E-Business Suite 11.5.10 OLTP Database • The table LAB_CUST_OUTREACH_ZONES was created to hold the data on customer satisfaction outreach zones and the assignment of current customers to these zones. This data was imported into the database using Oracle Data Integrator. • In the Data Warehouse: • The table LAB_W_CUSTOMER_LOC_D was created as a copy of W_CUSTOMER_LOC_D with a new column called X_OUTREACH_ZONE added after the X_CUSTOM column. • The table LAB_W_CUSTOMER_LOC_DS was created as a copy of W_CUSTOMER_LOC_DS • The source table only has five records since the real point of the exercise is to work with the tools and flow versus loading hundreds of rows of data.

  23. Lab 1 • Create the mapping and workflow (viewlet)

  24. Lab 2 • Create the DAC artifacts

  25. Lab 3 • Run the execution plan and verify load

  26. <Insert Picture Here> Lab Exercise:Custom ETL (Viewlet)

  27. Informatica Custom ETL(Viewlet) • Lab 3.1 Informatica Custom ETL – Main Steps • Create a new Repository folder • Define Source and Target tables • Create new Mapping • Create new Workflow • Duration: 1.0 hour For more information on Informatica, refer to the Informatica product documentation

  28. <Insert Picture Here> Lab Exercise:DAC Configuration for Customization

  29. DAC Configuration for Customization • Lab 3.2 DAC Configuration for Custom ETL – Main Steps • Create in DAC: • Folder • Tasks • Subject Area • Execution Plan • Duration: 1.5 hours For more information on DAC, refer to the Oracle BI Applications Data Warehouse Administration Console Guide located on the DVD in the BI_Apps_794_Docs folder

  30. <Insert Picture Here> Lab Exercise:DAC Execution and Warehouse Load Confirmation of Customization

  31. DAC Configuration for Customization • Lab 3.3 DAC Execution / Custom ETL Confirmation – Main Steps • Run custom Execution Plan • Verify data using SQL*Plus • Duration: 1.5 hours For more information on DAC, refer to the Oracle BI Applications Data Warehouse Administration Console Guide located on the DVD in the BI_Apps_794_Docs folder

More Related