Oracle Business Intelligence Suite(OBIEE) User Manual
Contents
1. Physical Catalog Check Cyt Connection Pool Cut Chris Copy Ctrl C Paste Clrl Delete Del Mark Set Icon Duplicate Expand All Check Consistency Business Model Diagram d Physical Diagram d Rename Properties FCIS FD07 03 01 OBIEE repository Development Guide 8 Name it as Synonyms and click on ok Physical Schema Synonyms General Mame Phong Description Cancel Help 9 Synonyms Schema will get added Physical Pe FlexCube ai Connection Pool SPMOMYMS 10 Open the FCIS_Full_ Schema rpd from FLEXCUBE IS Software area using Administrator username and password administrator Expand FlexCube gt Synonyms select the required Tables for a given module using control click CSTMS_PRODUCT PCTBS_CONTRACT_MASTER STTMS_BRANCH SMTBS_PRODUCT_TYPES Right click and Copy FCIS FD07 03 01 OBIEE repository Development Guide 13 E STTMS ACCOUNT PROW PERCEN New Object DUNT REPORT GEN_ Update Rom Count S147 REPLINES DETE view Data NOTICE PREF 15S_AMTBLK_ORDER Display Related FASS INTERIM CR AMT SS_INTERIM DR_AMT 55 PRODUCTS Cut Ctr 455 REPORT GEN_TIb Chri 455 TSNCODE Paste Clrl EENTITIES PRE_IMA Delete Del DATES Mark POS DETAIL Set Icon Duplicate _OPERATION CODE Expand All _TABLE_UDF_DETAIL _TABLE_UDF_MASTEI Check Consistency H RESTRICT DETAIL H_ RESTRICT MASTEI Physical Diagram CODE Rename N2. TCP IP SSL Oracle BI Web SOAP Web Services XML and URL Interface Oracle Interactive Oracle Answers DENOI FCUBS Application Server Oracle Delivers Server Oracle BI Server Oracle BI Administration FCUBS Database ODBC over TCP IP SSL gt Oracle BI Server is the place where the metadata repository is put that is connected FLEXCUBE OLTP database and serves for the requests coming from different sources for generating reports gt BI Administrators are the users of BI Administration Tool who creates and edits the repository gt The Administration Tool is a Windows application that allows the Oracle BI Administrator to create and edit repositories gt Oracle BI Web is the place where all the presentation services related items like Answers Dashboards Filters and other related files will be kept gt Web servers will be responding to the requests coming in to generate reports using Oracle BI Web Users of it can be the any FCIS business users or any external applications or portals if integrated FCIS FD07 03 01 OBIEE repository Development Guide 5 gt Business Users of FCIS Application can also be the users for Oracle BI presentation services gt FCIS Application Server is where the FCIS application resides gt FCIS database is the underlying OLTP system used for both FCIS application and BI reports gt Both FCIS Application amp Oracle BI Web are accessible by business
3. FCIS FD07 03 01 OBIEE repository Development Guide 57 8 Notice that the expression is added in the Logical Table Source dialog box Logical Table Source Pc Pctbs Contract Master General Column Mapping Content W Show mapped columns W Show unmapped columns Logical column to physical columri New column Logical Column E Expression Collected Amount _ FCOLLECTED_AMOUNT Exch Fate _ EXCH_RATE Fep Amount ___ FCY_AMOUNT Interest Amount __ INTERES T_AMOUNT Ley Equiv Amt _ LCY_EQUIW_AMT Max Int Amt __ FMAX INT_AMT Max Tan Amt a MAs TXN_AMT Net Balance Amount Physical ME FlesCube Synonyms PC_PCTES_CONT No of Products _ _ PRODUCT_CODE Old Exch Aate _ OLD_EXCH_RATE Org Stlrant Amt __ ORG_STLMNT_AMT Tan Amount _ THN_AMOUNT KH MBG KK KEE 9 Click OK to close the Logical Table Source dialog box The icon for Net Balance Amount Physical changes to indicate an aggregation rule is applied gA Pc T2 BranchDim TA Contract DimDim TA ProductDim Branch Contract Din zs Contract Facts Fa Sources Ea Pe Petbs Contract Master E Collected Amount Fey Amount Esch Rate Interest Amount Ley Egui Amt Max Int Amt blas Tsn Amt Old Exch Rate Tsn Amount Org Stlmnt Amt No of Products Net Balance Amount AE Net Balance Amount Physical Product l AR le R TEH T T in i ia H EH i H FCIS FD07 03 01 OBIEE repository Development Guide 58 10 Drag Net Balance
4. 41 2 Contract Dim i i Le Contra Mew Object Logical Foreign Key a Logical Key gwd Display Related L gica Table Sauce B H z Check Out Logical Column e Ej Cut Ctrl x Eg a Cony Ctrl C L Faste Clrl saisi E bi Delete Del ae EI a M Mark EI A E g an i 4 Eg Dpat 4 E F T Expand All H H E Produ Check Consistency D E Business Model Diagrarn 2 I Physical Diagram E Rename 3 ad I Properties Pg 2 In the Logical Column dialog box name the logical column Net Balance Amount and check Use existing logical columns as the source Click the Expression Builder button three dots to open the Expression builder l 5 ia m Logical Column Net Balance Amount fol General Data Type Aggregation Levels Name Net Balance Amount Met Balance Amount Belongs to T able Pen Contract Fact Sort order column Mone se existing logical columna as the source Descriptors Cancel Hep FCIS FD07 03 01 OBIEE repository Development Guide 50 3 In the left pane click Logical Tables Select Contract Facts in the middle pane Txn Amt in the right pane and click Insert The txn Amt column is added to the formula Click the minus sign operator to add it to the formula Double click Collected Amount in the right pane to add it to the formula Click on OK Expression Builder Derived logical column Pe Contra
5. FCIS FD07 03 01 OBIEE repository Development Guide 2 1 Preface This OBIEE repository development document describes steps to develop the FLEXCUBE IS OBIEE RPD Meta data file 1 1 Audience The OBIEE repository development guide is intended for the FLEXCUBE Application Developers users who are authorized to perform the following tasks Develop the new RPD file for a given module Enhance the existing RPD file To Use this manual you need conceptual and working knowledge of the below Proficiency Resources FLEXCUBE IS Development overview FCIS FD01 01 01 Development Overview Guide Report development introduction FCIS FD07 01 01 Report Getting started 1 2 Related documents For more information on Reports development see these resources FCIS FD01 01 01 Development Overview Guide FCIS FD07 01 01 Report Getting started 1 3 Conventions The following text conventions are used in this document Convention Meaning Boldface Boldface type indicates graphical user interface elements for example menus and menu items buttons tabs dialog controls including options that you select Italic italic type indicates book titles emphasis or placeholder variables for which you supply particular values Monospace Monospace type indicates language and syntax elements directory and file names URLs text that appears on the screen or text that you enter FCIS FD07 03 01 OBIEE repository Development Guide 3 2 In
6. Features Connection Pools Display Folders Marne FlexCube Data source definition Database ODBC Basic CRM metadata tables Virtual Private Database 5 Go to Connection Pools tab Click on add FCIS FD07 03 01 OBIEE repository Development Guide 10 Name it as Connection Pool Call Interface as ODBC3 5 Select Data Source name from dropdown Type in User name and Password Schema Details click ok type in the password once again and click on Ok onnection Pool Connection Pool OX General Connection Scripts ML Winite Back Et Hame Connection Pool Permissions Call interlace opec 3 5 Maximun connections 10 44 Require fully qualified table names Data Source name Kermen 1G M Shared logon User name FCCOE Password a M Enable connection pooling Tirneout M Use multithr Confirm Password Execute gue E Password fies W Parameters Isolation level Description UF Cancel Help 6 The Connection Pool will get added click on ok FCIS FD07 03 01 OBIEE repository Development Guide 11 Database Flext ube General Features Connection Pools Display Folders ae Connection Pool Up Down Add Edit Remove Cancel Help 7 Right Click on FlexCube Database gt New Object gt Physical Schema Physical Table New Object Physical Display Folder Display Related Physical Schema
7. Session g Initialization Blo Ef1 Variables Ef1 System 15 Click Action gt Close to close the Variable Manager 16 Save the repository FCIS FD07 03 01 OBIEE repository Development Guide 71 11 Resources For more information on building a repository you can go through Oracle Documentation Oracle by Example at http www oracle com technolo obe obe bi bi ee 1013 bi_ admin biadmin html FCIS FD07 03 01 OBIEE repository Development Guide 72 ORACLE FCIS FD07 03 01 OBIEE repository Development Guide ee 2012 Oracle Corporation World Headquarters 500 Oracle oO nee a Shores CA 94065 Worldwide Inquiries Phone 1 650 506 7000 Fax 1 650 506 7200 www oracle com financial_services Copyright 2012 Oracle Financial Services Software Limited All rights reserved No part of this work may be reproduced stored in a retrieval system adopted or transmitted in any form or by any means electronic mechanical photographic graphic optic recording or otherwise translated in any language or computer language without the prior written permission of Oracle Financial Services Software Limited Due care has been taken to make this document FCIS FD07 03 01 OBIEE repository Development Guide and accompanying software package as accurate as possible However Oracle Financial Services Software Limited makes no representation or warranties with respect to the contents hereof and shall not be responsible for any loss
8. CONTRACT MASTER SMTBS PRODUCT TYPES STTMS_ BRANCH LC ad od od i ele ae HE EE Le 15 Create a physical display folder by right clicking on FlexCube gt New Object gt Physical Display Folder name it as PC Physical Display Folder PC Tables Name Po 16 Copy entire alias from Synonyms Physical Schema to Physical Display Folder Physical E FlexCube eg Connection Pool od PC PL_CSTMS PRODUCT PL_PCTBS CONTAACT MASTER PL_SMTBS PRODUCT TYPES PL_USTTMS BRANCH OMI CSOTMS PRODUCT PLLCSTMS PRODUCT PL_PCTBS CONTRACT MASTER PL_SMTBS PRODUCT TPES PC STTMS BRANCH PLTBS CONTRACT MASTER SMTBS PRODUCT TPES STTMS BRANCH sa a E A l oo a C2 i fe H FCIS FD07 03 01 OBIEE repository Development Guide 16 17 Right click on physical display folder gt Physical Diagram gt Selected Objects only fANnTATION Hneinage Mandal anni Mannino gt Physical Diagram F C_PCTES_COHTRACT_MASTER PC_STTH 4 hE RANCH GD PC_CSTHMS PRODUCT PC_SMTES_PRODUCT_TYPES 18 Click on New Foreign key k New Foreign key 19 First Click on PC_STTMS_BRANCH and Click on PC_PCTBS_CONTRACT_MASTER FCIS FD07 03 01 OBIEE repository Development Guide 17 Physical Foreign Key PC_PCTBS_CONTRACT_MASTER_FKey a Name PC_PCTBS CONTRACT MASTER Fkey Table T able PC_STTMS_BRANCH Px PC_PCTBS_CONTRACT_MASTER El Column Column E Al Operator T r BR
9. Cree locical Tablesand Local COMMIS are EEn E AE E E EEE 2d ILS Cre AD ICAL I OLS AEEA E A E EE E N EE T AE 24 32A Rename BUSINESS Model ODE CIS iroisenraonioroe e eie a e A 27 Ja Bond Dimension terine MNCS saderen A EE E E E EEA 35 5 4 Building the Presentation Layer of a REPOSItOTY ccccccccceeccecceceeaeeeeesseeeeeeceeeeeeeeeeaaasseesesseeeeeeeeeeeeeeeaaaas 41 IAL Buld Presenta On May Or cae ct cater E EA ENTA AE O AE AE 4 IAA EEE O Ic 1 1 Y RR RIPE ABE OPP EOE POPE OES oT REE E92 ERT ORME A DEES PUR TOR ee Se 42 6 Fpable Cer Te 11 ata ati aceite at Sac za tc eae botany E Hee dunes Botan etic ted aa cde 43 E INNO ty NOSCONn 2 Alsea E an wae ences deck chaatstasebaute N 45 S Creatine Calculation Measures rieseni tiers e e aE a N O N a 46 al Create a New MEIU inian T AER 48 8 2 Create a Calculation Measure Using Logical Columns seessesssssssssseeeeererssssssssssssseeeeresssssssssssssseeeeeeee 49 8 3 Create a Calculation Measure Using Physical Columns ccccccccccccccssssssseseeseeecceeeeeeeeseeeassseeaesseeeeeeeeees 53 9 Oreamzine the Presentation Lid y CU esrin n EE E R T A eate enor e 59 10 Usine Mitalizanon Dlocksand Variabli eeii A oe alesse adds 64 10 1 Create an Initialization Block for Session Variables csessesseecceeceeeeeeecaaaeseseseeseeeeeeeeeeeeeeeeeaaaaeeeness 65 TaD NCS OPCS nimien a a anh addi toa a a ead uteienaa ena ound duenelabeanetedsiniesstuuan sed 72
10. The ratio from one level to the next is more important than the absolute number These numbers only affect which aggregate source is used optimization not correctness of queries FCIS FD07 03 01 OBIEE repository Development Guide 37 Logical Level Product Type General Keps Prefered Drill Path Product Tyo Number of elements at this level 3 1 for dimension total Grand total level MW Supports rollup to higher level of aggregation Child levels EA Product Detail Add Remove Descriptors Cancel Help 5 Now Drag Product Type and Product Type Description from Product Details Level to Product Type Level FCIS FD07 03 01 OBIEE repository Development Guide 38 tama Pe TA ProductDim E Product Total E Product Type ed Product Type E Product Type Desc 29 Product Detail Product Code Product Description Product Start Date Product End Date Product Group Module Pool Code Currencies List Categories List fal ga ka lg a lg 6 Right Click on Product Type gt New Logical Level Key and Click on ok ogical Level Key Product Type ey Mame roduck Type Qualified names Columns Add Mw Use For drilldown Description caret Heh FCIS FD07 03 01 OBIEE repository Development Guide 39 Business Model and Mapping 249 Fe A ProductDim H Product Total E Product Type EF Product Type EL Product Type Desc 29 Product Detail Product Code Produc
11. Ugel FLLMSERY Oracle in oracle 1 gel kermelliG Oracle in oracle Ugel KERNMELDEW Oracle in oracle gel KEERIFI NER Creele in arseled Mac iil gt An ODBC System data source stores information about how to connect to the indicated data provider A System data source is visible to all users on this machine melding NT services 0K Cancel Help FCIS FD07 03 01 OBIEE repository Development Guide 9 5 Building OBIEE RPD This section explains the steps to create RPD file in OBIEE server 5 1 Building the Physical Layer of a Repository The Physical layer defines the data sources to which Oracle BI Server submits queries and the relationships between physical databases and other data sources that are used to process multiple data source queries The data sources can be of the same or different varieties 5 1 1 Create a new Repository 1 Click Start gt Programs gt Oracle Business Intelligence gt Administration to open BI Administration Tool 2 Click File gt New to open New Repository dialog box OracleBI Administration Tool File Tools Help New Ctrl h Open p Multiuser 1 Metadatal rod 2 SH_J5 rpd C OracleBl_ O92706 5H rpd 4 sh rpd Exit 3 In the New Repository dialog box Repository folder is selected by default Name the file lt module gt rpd Example pc rpd 4 Right Click on physical Layer gt New Database name it as FLEXCUBE Database FlexCube General
12. lap System PD Security Yariable Manager fl l xj Action Edit Help Repository ve Gl Initialization Blocks Variables a P Dynamic Initialization string Mew Initialization Black oo LP Static FI Session Check Out a Initialization Blocks E71 Variables Delete Del System Duplicate APD Security Mark T EF Non System Set Icon Properties 4 In the Session Variable Initialization Block dialog box type PC_Current_Date in the Name field Session Variable Initialization Block PC_Current_Date Mame aes Source 5 Click the Edit Data Source button to open the Session Variable Initialization Block Data Source dialog box FCIS FD07 03 01 OBIEE repository Development Guide 66 Session Yariable Initialization Block Data Source setUser i _ Oo x Data Source Type Use database specific SOL Default Initialization String r H Connection Pool nok assigned Browse Test Cancel Help 6 Click the Browse button and select FlexCube gt Connection Pool in the Select Connection Pool dialog box Select Connection Pool igen Name F SOSCS S S Type Connection Fool Iw Query Only Selectable Objects Show Qualified Names Aan View Select Cancel Help i Connection Pool 7 Double click Connection Pool or click the Select button to add the connection pool to the Session Variable Initialization Block Data Source dial
13. space and select New Business Model FCIS FD07 03 01 OBIEE repository Development Guide 20 l Business Model and Mapping New Business Model k Gheck Gut ut Etrit Copy Cr Paste Clrl Delete Del Mark Seb Icon Duplicate Expand sll heck Gonsishency Business Model Diagram F Physical Diagram k Properties 2 In the Business Model dialog box name the business model PC 5 2 2 Create Logical Tables and Logical Columns To create a new logical table perform the following steps 1 In the Business Model and Mapping layer right click the PC business model and select New Object gt Logical Table 2 Inthe Logical Table dialog box name the table Contract Facts Business Model and Mapping tract Facts S Ounces 3 To create a logical column In the Physical layer expand PC_PCTBS_CONTRACT_MASTER physical table and drag the entire measures column to Contract Facts table in business layer FCIS FD07 03 01 OBIEE repository Development Guide 21 Business Model and Mapping PC E3 Contract Facts Sources wl PC_PCTBS_CONTRACT_MASTER COLLECTED AMOUNT E FCY_AMOUNT EXCH RATE E INTEREST AMOUNT LCr_EQUIY_AMT E MAx_INT AMT E MAX THN AMT OLO_EXCH RATE TXN_AMOUNT Z ORG _STLMNT_AMT 4 In the Business Model and Mapping layer double click COLLECTED_AMOUNT logical column to open the Logical Column dialog box Go to Aggregation Tab select Sum as shown Logical Column COLLECTE
14. the Required columns from PC_SMTBS_PRODCT_TYPES from Physical layer to Product Table in Business layer Thereby creating one more LTS in Product table as shown below TE e ee AS ES Product Sources Pa PC_CSTMS PRODUCT Pa PC_SMTEBS PRODUCT TYPES PRODUCT CODE PRODUCT DESCRIPTION PRODUCT SLOGAN PRODUCT REMARKS PRODUCT START DATE PRODUCT END DATE PRODUCT GROUP WAAEHOUSE CODE PART OF PRODUCT MODULE RECORD STAT AUTH STAT ONCE_AUTH MARER_ID MARKER DT STAMP ova fa nef fu fon fn ven fn fora re foal F 5 2 3 Create Logical Joins 1 Right click the PC business model and select Business Model Diagram gt Whole Diagram FCIS FD07 03 01 OBIEE repository Development Guide 24 Business Model and Mapping Physical T Mew Object e Display Related d Check Out Check Ouk Business Model ut Ctrl Copy Chrl Paste Cleri Delete Del Mark Set Icon Duplicate Duplicate with Presentation Catalog Expand All Check Consistency Business Model Diagrarn Whole Diagram Selected Tables Physical Diagram ae Rename Selected Tables Properties Selected Fact Tz Estimate Levels Logical Table Diagram PC ontract Dim Contract Facts Se Ho Pec 2 Make use of New Complex Join button in the toolbar File Edit View Manage Tools Window Help Ci cab car i ed amp Se ek tee T Presentation FCIS FD07 03 01 OBIEE repository Deve
15. 1 Create an Initialization Block for Session Variables Session variables are like dynamic repository variables in that they obtain their values from initialization blocks Unlike dynamic repository variables however the initialization of session variables is not scheduled When a user begins a session Oracle BI Server creates new instances of session variables and initializes them Unlike a repository variable there are as many instances of a session variable as there are active sessions on Oracle BI Server Each instance of a session variable could be initialized to a different value A session is an instance of a user running the client application The session starts when the application is started and ends when the application is exited To create an initialization block for session variables perform the following steps 1 Click Manage gt Variables to open the Variable Manager nlx Action Edit Help 2 Repository fil Initialization Blocks Name Description E P Variables API Dynamic EPI Static Session wi Initialization Blocks l Yarnables l System a ffl Security fo GP Nor System 2 Click Session gt Initialization Blocks FCIS FD07 03 01 OBIEE repository Development Guide 65 Yariable Manager E 0 x Action Edit Help j Repository ve fq Initialization Blocks Variables a P Dynamic IPI Static agal Session TE Initialization string laf Variables
16. ABCESTEND SQL Server Analticsw eb OracleBl Server Remove Localberver SOL Server OLAF SOL Server Configure ORCL Oracle in Ora bi 0g_homel ORCL SH Oracle in Ora bi Og_ hore 5 NO SCHED SQL Server SECURITY SOL Server SIEBE C OLAF SQL Server Suppler _kshekhar pcl Oracle in Oral Og hornet Suppliers local Oracle in Uratb1 Oo hore kd An ODBC System data source stores information about how to connect to the indicated data provider 4 System data source is visible to all users on this machine including HT services Cancel Apply Help The Create New Data Source dialog box opens 3 In Create New Data Source dialog box select Oracle driver Create New Data Source Select a driver for which you want to setup a data source gt licrosoft Parados Oriwer db Microsoft Parados Treiber db Microgott Test Driver tst cev Microsoft Text Treiber tat caw Microgott Visual FoxPro Driver Microsott Visual FoxPro Treiber Oracle Bl Server WW Jolie OO Se SQL Server lt h l l oe e e e A l w Ea z Back Cancel FCIS FD07 03 01 OBIEE repository Development Guide 4 Click Finish to open the Oracle ODBC Driver Configuration dialog box 5 In Oracle ODBC Driver Configuration dialog box enter a Data Source Name select appropriate TNS Service Name from the drop down list and enter schema name as the User ID Oracle ODBC Driver Configuration e Data Source Hame kemelliG L l
17. ANCH CODE E CONTRACT_REF_NO F BRANCH NAME z E BRANCH OF_INPLIT BRANCH ADDR BRANCH CODE E DOAKICU AMMO s 2 coioce come si 4 i gt 4 mT gt I Driving Type Cardinality on Co E a oi oi Hint Expression PL_STTMS BRANCH BRANCH CODE PCLPCTBS CONTRACT MASTER BRANCH CODE BI Cancel Help 20 Click BRANCH_CODE from PC_STTMS_BRANCH and BRANCH_CODE from PC_PCTBS CONTRACT MASTER and click on ok Click yes when asked for create a key in PC_STTMS_BRANCH table FCIS FD07 03 01 OBIEE repository Development Guide 18 Physical Foreign Key PC_PCTBS_CONTRACT_MASTER_FKey Fir Name PC_PLCTBS_CONTRACT_MASTER_FRey Table T able FC_ST THS_BRANCH SI PC_PCTBS_CONTRACT_MASTE Fi Bl Column Column l Operator Mame Al E BRANCH_CODE mao c CONTRACT_REF_NO BRANCH_H a BRANCH AL Oracle BI Administration Tool E DDAMCU A Ss 3 A matching table key doesn t exist in table PC_STTMS_BRANCH Do you want to create one Driving Mo aF a Hint Expressions PC_STTMS_ BRANCH BRANCH CODE PC_PCTBS CONTRACT MASTER BRANCH_ CODE OF Cancel Help 21 Similarly Join other tables as shown in diagram using New foreign key PC_SMTBS_PRODUCT_TYPES PRODUCT_TYPE PC_CSTMS_PRODUCT PRODUCT_TYPE PC_CSTMS_PRODUCT PRODUCT_CODE PC_PCTBS_CONTRACT_MASTER PRODUCT_CODE FCIS FD07 03 01 OBIEE repository Development Guide 19 Sey PC_PCTES_COHTRACT_HMASTER Se PC CSTMS PRODUCT P
18. AS Interest Amour _ INTEREST_AMOUNT se PC_PCTES_CONTRACT_MAS Ley Equiv Art _ LCY_EQUIY_AMT se PC_PCTBS CONTRACT _MAS Max Int Amt _ M amp x_INT_AMT PC_PCTBS_CONTRACT_MAS Max Txn Amt a Mas TsN_AMT se PC_PCTES_CONTRACT_MAS Net Balance Amount Physical el Mo of Products _ PRODUCT_CODE se PC_CSTMS_PRODUCT Old Exch Fate _ OLD_EXCH_RATE PC_PCTBS_CONTRACT_MAS Org Stlririt Amt _ ORG_STLMNT_ amp MT se PC_PCTBS_CONTRACT_MAS Tan Amount _ TAN_AMOUNT se PC_PCTBS_CONTRACT_MAS 7 In the Expression Builder select Physical Tables gt PC_PCTBS_CONTRACT_ MASTER gt TXN_AMT and then click the Insert button to add the column to the formula Click the minus sign operator to add it to the formula COLLECTED AMOUNT and then click the Insert button to add the column to the formula Click Ok Expression Builder Logical Column CE FlesCube Synonyms PC_PCTES_CONTRACT_MASTER TSN_AMOUNT OK FlesCube Synonyme PC PCTBS CONTRACT MASTER COLLECTED_AMOUNT Cancel Unda Help eee oma J Physical Tables L COLLEC TEE AMOUNT Operators Expressions CHARGE AC STATISTICS i CHARGE_CUST_STATISTICS Functions PREY_MTH_AC_TN_COUNT Constants PREY MTH_ AC TXN_AMOUNT Types PREY _MTH CUST_T N_ COUNT PREY MTH_ CUST TN AMOUNT 45CiLHANDOFF_REGD PEIECT MIF TO Flinn k Sort Panes Search Search FlexCube Synonyms PI_PCTBS CONTRACT MASTER COLLECTED_AMOUNT Session Vanables
19. Amount Physical to Contract Facts in the Presentation layer Save the Repository 9 Organizing the Presentation Layer The Presentation layer is built after the Physical layer and Business Model and Mapping layer and adds a level of abstraction over the Business Model and Mapping layer It is the view of the data seen by end users in client tools and applications such as Oracle BI Answers The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users For example you can organize columns into catalogs and folders Simplifying the view of the data for users makes it easier to craft queries based on users business needs because you can expose only the data that is meaningful to the users organize the data in a way that aligns with the way users think about the data and rename data as necessary for the set of users You typically create Presentation layer objects by dragging objects from the Business Model and Mapping layer Corresponding objects are automatically created in the Presentation layer You also can manually create catalogs and folders based on the needs of your users Presentation catalogs allow you to show different views of a business model to different sets of users Presentation catalogs have to be populated with content from a single business model They cannot span business models However multiple presentation catalogs can refer to the same business model Present
20. C STTHS BRANCH Md l PC_SMTES_PRODUCT_TYPES 5 2 Building the Business Model and Mapping Layer of a Repository The Business Model and Mapping layer of the Administration Tool defines the business or logical model of the data and specifies the mappings between the business model and the Physical layer schemas This is where the physical schemas are simplified to form the basis for the user s view of the data The Business Model and Mapping layer of the Administration Tool can contain one or more business model objects A business model object contains the business model definitions and the mappings from logical to physical tables for the business model The main purpose of the business model is to capture how users think about their business using their own vocabulary The business model simplifies the physical schema and maps the user s business vocabulary to physical sources Most of the vocabulary translates into logical columns in the business model Collections of logical columns form logical tables Each logical column and hence each logical table can have one or more physical objects as sources There are two main categories of logical tables fact and dimension Logical fact tables contain the measures by which an organization gauges its business operations and performance Logical dimension tables contain the data used to qualify the facts 5 2 1 Create new business model 1 In Business Model and Mapping layer right click the white
21. CIS FD07 03 01 OBIEE repository Development Guide 69 i Session Variable i Hame Enable any user to set the value LI Security Sensitive Initialization Block setLiser E MEN Default Initializer a i Description col tee 11 In the Name field type PC_CurrentDate Session Variable Initialization Block Variable Target PC_Cur q mfx mie Variables Default Initializer A Po _Currentbate mE Row wise initialization Use cachim cancel Help FCIS FD07 03 01 OBIEE repository Development Guide 70 12 Click OK to close the Session Variable dialog box 13 The PC_CurrentDate variable is added to the Session Variable Initialization Block Variable Target dialog box Session Variable Initialization Block PC_Current_Date Mame PC_Current_Date Disabled Data Source Connection Pool FlexCube Connection Pool Edit Data Source Data base ODBC 2 0 Initialization string inherited From Default SELECT TO_CHAR SYSDATE MMIDD YY HH MI 55 FROM dual Variable Target Mame Default Initializer gl PC_CurrentDate Edit Data Target Execution Precedence 14 Click OK to close the Session Variable Initialization Block dialog box The PC_Current_Date initialization block is visible in the Security Manager Variable Manager Action Edit Help Repository AP Initialization Blo gere SELECT TO_C 7 Variables 97 Dynamic 7 Static
22. Check Manager Messages Options Display W Errors lw Warnings lw Best Practices Messages Message Type Object Type Object sd Error Description Warning User Administrator 29032 The user Administrator belongs to the Administrators Group arn Best Practice Physical Table PCTBS CONTRACT MASTER 89001 The Physical Table FlexCube Synonyinis PCTBS CONTRACT Best Practice Physical Table STTMS_BRANICH 89001 The Physical Table FlexcCube Synonyme STTMS_BRANCH t Best Practice Physical Table Pr_PCTBS_CONTRACT_MASTER 69001 The Physical Table FlexCube Synonyms PC_PCTBS_CONTR 4 If the Consistency Check Manager displays any Error messages edit the repository to correct the inconsistencies and run the consistency check again If you see only Warning and Best Practices messages you can ignore the messages for now and click Close 6 Enable Query Logging To enable query logging for the Administrator user perform the following steps 1 Select Manage gt Security 2 In the Security Manager select Users in the left pane The Administrator user appears in the right pane iol x Action Edit LDAP Help curity Full name it Administrator FCIS FD07 03 01 OBIEE repository Development Guide 43 3 In the right pane double click Administrator The User dialog box opens Verify that the User tab is selected In the Password field type Administrator and then confirm in the Confirm Password f
23. D_AMOUNT General Data Type Aggregation im hones Default aggregation rue Cancel Help FCIS FD07 03 01 OBIEE repository Development Guide 22 5 Repeat the step no 4 for all the amount columns Business Model and Mapping PC ES Contract Facts Sources PC_PCTBS_CONTRACT_MASTER E COLLECTED AMOUNT E FEY AMOUNT EXCH RATE E INTEREST AMOUNT E LCY EQUIV AMT E MAX INT_AMT E MAX TXN_AMT OLD EXCH RATE E TSN AMOUNT E ORG STLMNT_AMT 6 Drag PC_PCTMS_CONTRACT_MASTER table from Physical layer Right Click and rename it as Contract Dim delete all the measure columns from it siness Model and Mapping _ i Sources Fa PC_PCTBS CONTRACT MASTER LON TRACT REF NO BRANCH OF_IMPUT BRANCH CODE SOURCE CODE SOURCE REF INTERFACE REF AC_ENTAY_REF_NO PROUD _AREF_NO THEIR_FREF_NU LUSTOM REFNO STATION ID ME TORE BATCH NO LUARA_NO PRUDULT CATEGURY PRODUCT CODE PRODUCT_TYFE PRIORITY CUST_NO LUST _BANECODE cer oA boar 7 Drag the PC_CSTMS_PRODUCT and PC_STTMS_BRANCH table from Physical layer to business layer FCIS FD07 03 01 OBIEE repository Development Guide 23 Business Model and Mapping Contract Dim Contract Facts PC_CSTMS PRODUCT PC_STTMS BRANCH E 8 Rename PC_CSTMS PRODUCT and PC_STTMS_BRANCH as Product and Branch respectively Business Model and Mappi E3 Contract Dim ze Contract Facts ES Product 9 Drag
24. Description Cancel Hel ie Santen e KERNEL11G He ECITF Test Connection UserID n Application Oracle Workarounds SOLS ernver Migration Enable Result Sets Mw Enable Query Timeout W Read Only Connection Enable Closing Cursors Enable Thread Safety Batch Autocommit Mode Commit only if all statements succeed Numeric Settings Use Oracle WLS settings 6 Click Test Connection to open Oracle ODBC Driver Connect dialog box 7 In Oracle ODBC Driver Connect dialog box enter the schema password and click OK Oracle ODBC Driver Connect Service Mame Meenen User Name FETA EE Password Noni FCIS FD07 03 01 OBIEE repository Development Guide 8 8 You should see a Testing Connection message indicating the connection is successful Testing Connectio n Connection successful 9 Click OK to close the Testing Connection message 10 Click OK to close the Oracle ODBC Driver Configuration dialog box 11 Verify that the DSN created is added in the ODBC Data Source Administrator and click OK to close the ODBC Data Source Administrator ODBC Data Source Administrator User OSM System DSN File DSN Drivers Tracing Connection Fooling About Sistem Data Sources Oracle Bl Server Analytics eb Oracle Bl Server Remove BL Oracle in oracle 1 gel CSW Microgott Access Test Driver tet cev Configure ESTERMAL Microgott Test Driver tst cev feide Oracle in oracle
25. G PREFERENCE Properties TRAN_CODE mm a i CH A E STTMS RRANCH ASSYNT set i iiti wii i 11 Go to PC rpd and right click on Synonyms and click on Paste a FlexCube e Connection Pool Mew Object d Display Related d Cut Chris Copy Cerl C Delete Del Mark Set Icon Duplicate Expand All Check Consistency Fename Properties FCIS FD07 03 01 OBIEE repository Development Guide Physical j FlexCube eg Connection Pool Seo Synonym H E CSTMS_PRODUCT E PCTBS CONTRACT MASTER E SMTBS PRODUCT TYPES E STTMS_ BRANCH 12 Right click on the table gt new object gt alias as shown Physical fa FlexCube ei Connection Pool H E PC Spony BSE CCTC DOOL T MEENE Mew Object d Physical Kew Update Row Count Physical Foreign Key View Data Physical Column T nmaa Display Related d w E SMT Check Out H E STT Cut Ctrl Cony Crrl e 13 Name it as lt Module Code_Synonym name gt ex PC_CSTMS_PRODUCT and click on ok Alias Physical Table PC_CSTMS_PRODUCT General Columns Keys Foreign Keys Hame eL CSTHMS PRODUCT Sune Tahle eTe OoOnmlict Tli 14 Create alias for all the synonyms FCIS FD07 03 01 OBIEE repository Development Guide 15 ll ih Y FlesCube 2 Connection Pool 239 Synonyms COTMS PRODUCT PC_CSTMS PRODUCT PC_PCTBS CONTRACT MASTER PC_SMTBS PRODUCT TYRES PC_STTMS BRANCH PCTBS
26. Oracle FLEXCUBE Investor Servicing OBIEE Repository Development Guide Release 12 0 April 2012 Oracle Part Number E51528 01 ORACLE FINANCIAL SERVICES Contents 1 PY Fel ps ec re rc E sabe gs a eee re Soa me adaces tea toceee oe sate see heate as oaaee eo O 3 Wed Auden a2 ciacstancadzaceaduoncnistadaateanisnnlpis E tod paleadensapistedeateccunadpastodeatnadeneapistedantnucewatpagtedaneadewetes 3 K2 Related COCUMEINS snccwetnnstacuacnicennenantednacnadennenantedasinacewndpen tad naceadennenan ted E E S 3 ker KCOUVENMONS sinrin oa tad aacnacewoenanted uatnncewodpen tel aacnace woceas tod patnnca ocean a O S 3 2 BRON C O Ramee Rep earn eine E PONT et uarg EN A SOE ear ae eID Een A Sew Ae 4 AE TOW AO MS MG COIs cesctess ccatetinceteen sche sect lhmenors calc otatteceteas uate nace heaters caucotattaueteas uate T 4 2 OBEE Getto Stance cgsije sacs sac esac eters E a eaten cess eee ent A 4 Sik Component inte eration Mode ai csca tosh aie ete S inane 5 4 OBIEE environments UD aiaa a E O E I 6 AL C ete am ODBC Data SOUCO iraa O cntaenteaseeeie cats ateuseeaaaaets 6 5 Budine OBIE ERP Deneio r E O aa E EOS 10 JL Building the Physical Layer of a Repository ne A a E a ee ee 10 SLL Creul MEU RPO OY e aa I E EE E OTE AEE AEO 10 5 2 Building the Business Model and Mapping Layer of a ReEPOSItOry cccccccccecccccceeeceseseeeeeseeeeeeeeeeeeeeenaaas 20 I2 Cren eneo DuS neS INOREN Seres eia aie lot EA OEE E NE EE E E iene ue 20 S22
27. Physical layer to the Pc Pctbs Contract Master logical table source for the Contract Facts logical table PRODUCT_CODE appears as a logical column in the Contract Facts logical table Rename it as No of Products and Set the aggregation rule for the No of Products logical column to COUNT FCIS FD07 03 01 OBIEE repository Development Guide 48 Pe TA BranchDim TA Contract DimDim TA ProductDim Branch Contract Dim E Contract Facts ZA Sources Ea Pe Petbs Contract Master Collected Amount Fep Amount Esch Rate Interest Amount Ea Ea E z Ley Equiv Amt E E E Max Int Amt Max Tsn Amt Old Exch Rate Tsn Amount EJ Org Stirnmt Amt z Ho of Products Product 3 Drag the No of Products logical column to the Contract Facts presentation folder 4 Click File gt Check In Changes or click the Check In Changes button on the toolbar to check in changes File Edit View Manage Tools Window le a D oe pea Check In Changes 5 Save the repository Check for consistency 8 2 Create a Calculation Measure Using Logical Columns Define a new calculation measure named Net Balance Amount in the Contract Facts logical table using existing logical columns to define the calculation formula Here Net Balance Amount is Txn Amount Collected Amount 1 Right click the Contract Facts logical table and select New Object gt Logical Column FCIS FD07 03 01 OBIEE repository Development Guide 49
28. Presentation 3 4 Pe Contract Facts Collected Amount z Fep Amount E Esch Rate E Interest Amount E Ley Equiv Amt Max nt Amt Max Tsn Amt Old Exch Rate Tsn Amount Fa Org Stlmnt Amt Fa No of Products Net Balance Amount Contract Dim Product Branch 7 Save the Repository 8 3 Create a Calculation Measure Using Physical Columns Define a new calculation measure named Net balance Amount Physical in the Contract Facts logical table using physical columns to define the calculation formula To create a calculation measure using physical columns perform the following steps 1 Right click the Contract Facts logical table and select New Object gt Logical Column 2 In the Logical Column dialog box name the logical column Net Balance Amount Physical FCIS FD07 03 01 OBIEE repository Development Guide 53 Logical Column Net Balance Amount Physical Bf General Data Type Aggregation Levels Name Net Balance Amount Physical Belongs to Table Pe Contract Facts Sort onder column None Set T Use existing logical columns as the source Description Cancel Help 3 Click the Aggregation tab Set the default aggregation rule to SUM 4 Click OK to close the Logical Column dialog box Net Balance Amount Physical is added to the business model FCIS FD07 03 01 OBIEE repository Development Guide 54 1 Se l ee a ee Branch Contract Dim Contract Facts gA Sources Ea P
29. a text editor such as Notepad Locate the REPOSITORY section and change the entry to Star PC rpd DEFAULT In the CACHE section immediately below the REPOSITORY section change the ENABLE parameter from YES to NO REPOSITORY 5tar PC rpd DEFAULT 5Tar LC rpd Star PC rpd DEFAULT Fotar FCUBS Full Schema rpd DEFAULT OTAaL CLl10Z rpd DEFAULT g5tar paint rpd DEFAULT EEEE EEE EEEE EEEE EEEE EEEE EEEE EEEE E E EE E E E E E EEEE EE Query Result Cache Section Pe EE ee ee Ee EE EEE E E E E EEEE EE CACHE ENABLE NO A comma separated list of lt directory maxSize gt 2 Save the changes and close NOSConfig INI Start Oracle BI Services 1 Select Start gt Control Panel gt Administrative Tools gt Services 2 Start Restart the Oracle BI Server service Verify that the Oracle BI Presentation Server and Oracle BI Java Host services are started If they are not started start them It is not necessary to start the Oracle BI Scheduler or Oracle BI Cluster Controller services The services can be started in any order FCIS FD07 03 01 OBIEE repository Development Guide 45 ey Office Source Engine Saves inst Manual Local System Sa Oracle BI Cluster C Manual Local System Ait Oracle BI Java Host Started Automatic Local System Ss Oracle BI Presentati Started Automatic Local System Sa Oracle BI Scheduler Manual Local System oracle BI Ser
30. ange specified text Add Remove ii Ey E mjn i fi All text lowercase ORACLE Cancel FCIS FD07 03 01 OBIEE repository Development Guide 30 6 Click on Change specified Text in Find type _ Underscore and in Replace with give Space click on Add Rename Wizard What do you want to rename All text uppercase All text lowercase First letter capital First letter of each word capital Insert space before each first uppercase letter unless on the first position or there is a ga soecilved text All text lowercase ORACLE lt Back Next gt Cancai FCIS FD07 03 01 OBIEE repository Development Guide 31 7 Click on First letter of each word capital and Add Click on Next Rename Wizard What do you want to rename All text uppercase All text lowercase Insert space before each first uppercase letter unless on the first position or there is 4 Change specified text on we All test lowercase Change each occurence of into rot case sensitive First letter of each word capital ORACLE Back Cancel FCIS FD07 03 01 OBIEE repository Development Guide 32 8 Click Finish and verify logical tables and logical columns in the Business Model and Mapping layer are changed as expected tename Wizard Finish Following objects will be renamed Le gical Column PC Contract ct Facts COLLECTED A OUNT will be renamed to COLLECTED Logical Co
31. ation catalogs contain presentation tables You can use the Presentation Tables tab in the Presentation Catalog properties dialog box to reorder sort or delete Presentation layer tables You can also use this tab to access the Presentation Table dialog box where you can create and edit tables Changes to the Presentation layer do not impact corresponding objects in the Business Model and Mapping layer Use presentation tables to organize columns into categories that make sense to the user community Presentation tables appear as folders in the Oracle BI user interface Presentation tables contain presentation columns A presentation table can contain columns from one or more logical tables The names and object properties of the presentation tables are independent of the logical table properties Presentation tables contain presentation columns Use the Columns tab in the Presentation Table properties dialog box to reorder sort or delete Presentation layer columns You can also use this tab to access the Presentation Column dialog box where you can create and edit columns Presentation columns define the columns used to build queries in the Oracle BI user interface such as Oracle BI Answers The presentation column names are by default identical to the logical column names in the Business Model layer To provide a convenient organization for FCIS FD07 03 01 OBIEE repository Development Guide 59 your end users you can drag a column from a
32. ck Dim ontract Facts roduct Sources Fa Pe Cstme Product Fa Pe Smitbs Product Types Product Code Product Description Product Start Date Product End Date Product Group Module Pool Code Currencies List Categones List Product Type Product Type Desc b E H E E E E mg oo a oo oc fo pa E 4 5 3 Build Dimension Hierarchies Dimension hierarchies introduce formal hierarchies into a business model allowing Oracle BI Server to calculate useful measures and allowing users to drill down to more detail In a business model a dimension hierarchy represents a hierarchical organization of logical columns belonging to a single logical dimension table Common dimension hierarchies used in a business model are time periods products customers suppliers and so forth Dimension hierarchies are created in the Business Model and Mapping layer and end users do not see them in end user tools such as Oracle BI Answers or Interactive Dashboards In each dimension hierarchy you organize dimension attributes into hierarchical levels These levels represent the organizational rules and reporting needs required by your business They provide the structure that Oracle BI Server uses to drill into and across dimensions to get more detailed views of the data Dimension hierarchy levels are used to perform aggregate navigation FCIS FD07 03 01 OBIEE repository Development Guide 35 configure level based measure calculations and determin
33. ct Facts Tsn Amount Po Contract Facts Collected Amount F HJ Logical Tables j Operators Expressions Interest Amount Functions Ley Equiv Art Constants Mas Int Amt Types Mas Tsn Amt LLA ml Session Yarables Sort Panes Eec Contract Facts Collected Amount FCIS FD07 03 01 OBIEE repository Development Guide 51 4 Click OK to close the Expression Builder Notice that the formula appears in the Logical Column dialog box Logical Column Met Balance Amount Sleds General Data Type Jgrec Levels M ame Net Balance Amount Belongs to Table Pe Contract Facts 000 Sort order column ome Iw Use existing logical columns as the source Peo Contract Facts UT sn Amount Pe Contract Facts Collected Amount Description 5 Click OK to close the Logical Column dialog box The Net Balance Amount logical column appears in the business model Fc A BranchDim T4 Contract DimEim TE ProductDim Branch Contract Dim fs Contract Facts Sources el Pe Petbs Contract Master Ex Collected Amount EJ Fep Amount E Exch Rate E Interest Amount EJ Ley Equiv Amit E Was Int Amt E blas Tsn Amt Old Exch Rate Ex Tsn Amount Ex Org Stint Arnt Ex No of Products Net Balance Amount Product FCIS FD07 03 01 OBIEE repository Development Guide 52 6 Drag the Net Balance Amount logical column to the Sales Facts table in the Presentation layer
34. e Petbs Contract Master Collected Amount Fep Amount Esch Rate Interest Amount Ley Equiv Amt Max Int Amt blas Tsn Amt Old Exch Rate Ten Amount Org Stlmnt Art Ho of Products Het Balance Amount AE Net Balance Amount Physical Product a EREN R TER T R n ot ne ia n TEH Fe H TEH 5 Expand Contract Facts gt Sources and double click the Pc Pctbs Contract Master logical table source The Logical Table Source dialog box opens FCIS FD07 03 01 OBIEE repository Development Guide 55 Logical Table Source Pc Pctbs Contract Master General Column Mapping Content Hame Pe Petbs Contract Master I Active Map to these tables FlesCube synonyms PO_CSTMS_ PRODUCT FK FlexCube Synoryms PC_PCTBS_CONTRACT_MASTER Add Remove Jone Table Table Type I PC_CSTMS_ PC_PCTBS_ Inner LARP RODIICT 0 CONTRACT Description 6 Click the Column Mapping tab Click the Expression Builder button for the Net Balance Amount Physical logical column FCIS FD07 03 01 OBIEE repository Development Guide 56 Logical Jable Source Pc Pctbs Contract Master General Column Mapping Content W Show mapped columns M Show unmapped columns Logical column to physical column New column Logical Column Expression P Collected Amount COLLECTED_AMOUNT PC_PCTBS_CONTRACT_MAS Exch Rate EXCH_RATE z PC_PCTBS_CONTRACT_MAS Fey Amount a FEY AMOUNT se PC_PCTES_CONTRACT_M
35. e what attributes appear when Oracle BI users drill down in their data requests To build the Product dimension hierarchy in the Pc business model perform the following steps 1 Right click the Product logical table and select Create Dimension a Pe Branch Contract Dim Contract Facts jeer New Object d Lt Display Related d Cut Chril e Copy Chrl 0 Paste Clrl Delete Del Mark Set Icon Duplicate Expand All Check Consistency Business Model Diagram d Physical Diagram d Rename Properties Create Dimension 2 Right click the ProductDim object which was created by the action in the previous step and select Expand All FCIS FD07 03 01 OBIEE repository Development Guide 36 Pe TA ProductDim 29 Product Total E Product Detail Product Code Product Description Product Start Date Product End Date Product Group Module Pool Code Currencies List Categories List Product Type Product Type Desc al a a a a lg lg SY 3 Right click the Product Detail level and select New Object gt Parent Level Pe FlexCube TA ProductDim ai Connection Po HE Product Total E3 PC PC CSTM Mew Object Logical Key Parent Level Child Level P Display Related Pr isplay Relate 4 In the Logical Level dialog box name the logical level Product Type and set the Number of elements at this level to 3 This number does not have to be exact
36. ield In the Logging level field set the value to 2 User Administrator Ioj x User Logon Full name nal Description Password _ Confirm Password a EE Logging level ge Password never expires Password expires ever es days Group membership 4 Administrators Cancel Help To test the repository you need to generate some queries retrieve the results and examine the query log You log query activity at the individual user level Logging is intended for testing debugging and technical support In production mode logging is normally disabled because query logging can impact performance by producing very large log files 4 Click OK to close the User dialog box 5 Click Action gt Close to close the Security Manager 6 Save the repository FCIS FD07 03 01 OBIEE repository Development Guide 44 7 Modify NOQSConfig ini In this step you update the Repository and Cache sections of the initialization file NQSConfig ini An entry in the Repository section of the initialization file instructs Oracle BI Server to load a specific repository into memory upon startup If Oracle BI Server detects a syntax error while loading the repository it logs the error to the server log file OracleBI server Log NOServer log You also disable caching Caching is typically not used during development except to test the cache 1 Navigate to OracleBI server Config and open NQSConfig INI with
37. k File gt Open gt Online 3 In the Open Online Analytics Web dialog box type Administrator in the Password field and click Open FCIS FD07 03 01 OBIEE repository Development Guide 46 Open Online Analytics Web 7 x User Administrator Open Password a Cancel Help ddi Load all objects on startup Analytics web 4 The PC repository opens in online mode Online Oracle BI Administration Tool AnalyticsWeb File Edit View Manage Tools Window Help Presentation Business Model and Mapping Physical za jin i i FlexCube Up to this point all the changes you have made to the repository have been in offline mode Now that you have a consistent repository and Oracle BI Server is running and using the repository you can make changes to the metadata in online mode In offline mode the relationship between the Administration Tool and the repository is like the relationship between any Windows application and a file An application the Administration Tool in this case opens a file for editing makes changes to its in memory copy and tells the OS to save the changed file In online mode the relationship is different Oracle BI Server has already opened the repository file and the operating system has put a write lock on the file In this mode Oracle BI Server can act as an agent of the Administration Tool When the Administration Tool tells it to Oracle BI Server sends the Administration Tool a copy of its i
38. lopment Guide 3 First click on Contract Dim then Click on Contract Facts Click on ok Logical Join Relationship_ 004 123911743331594 eters Mame Business Fe Table T able Contract Dir Contract Facts Column Column o Al Operator T 5 Contract Ref Ho E m H Collected Amount E Z Branch Code nt FA Fep Amount nP lt im gt o 9 em gt z Driving Hone Type Inner ai Cardinality C e OT f 1 i Coi EN E pression l Cancel Help 4 Similarly join the Branch and Product logical table with Contract facts as shown FCIS FD07 03 01 OBIEE repository Development Guide 26 Logical Table Diagram PC Wo Contract Dim tt Contract Facts yi y 5 Notice that the color of the table icons for the dimension tables has changed to white in the business model In a business model a yellow icon indicates a fact table and a white icon indicates a dimension table Defining the join relationships determined which tables are the logical dimension tables and which is the logical fact table A fact table is always on the many side of a logical join You now have a logical star schema consisting of one logical fact table Contract Facts and 3 logical dimension tables Contract Dim Branch and Product Business Model and Mapping PC ees Branch E Contract Dim E3 Contract Facts Product 5 2 4Rename Business Model Objects 1 To use the Rena
39. lumn PC Contract Facts FOY AMOUNT will be renamed to FEY AMOUNT Logical Column PC Contract Facts EXC H _RATE will be renamed to EXCH RATERS Logical Column PC Contract Facts INTERES AMOUNT will be renamed to INTEREST AMO Logical Column PO Contract Facta LC H IY AMT will be renamed to LOY EQUI AMT Logical Column PE Contract Facts Mas _INT_AMT will be renamed to MA INT AMT Logical Column PC Contract Facts Mas Ts N AMT will be renamed to Mas TaM AMTER Lo gic al Column PC Contract Facts OLO Ex UE H RATE will be renamed to OLD EXCH RATE Logical Column PC Contract Facts Tsh_ AMO JHT will be renamed to TSN AMOI iT Logical Column PC Contract Facts ORG STLMNT AMT will be renamed to ORG STLMNT A Logical Column PO Contract Dim AFD TYPE will be renamed to RFO T r PE Logical Column PC Contract Dim COLLEC TION STATUS will be renamed to COLLECTION Logical Column PC Contract Dim DEBTOR_C ATEGO IA will be renamed to DEBTOR CATEG Logical Column PC Contract Dim INYOICE SPLIT REGO will be renamed to INVOICE SPLIT Logical Column PC Contract Dim Max T A AMT Will be renamed to Mas TX AMTI Logical Column PC Contract Dim S PLIT_ INDIC ATOR will be renamed to SPLIT INDIC ATC IF Logical Column PC Contract Dim SPLIT NUMBER will be renamed to SPLIT NUMBER Logical Column PC Contract Dim S PLIT Ps RENT REF NO will be renamed to SPLIT PARES Lo gical Colum
40. me Wizard to rename the objects in the business model Click tools gt Utilities File Edit View Manage Tools Window Help oro Fs k Update All Row Counts Consistency Check Ctrl E Options FCIS FD07 03 01 OBIEE repository Development Guide 21 2 In Utilities dialog box click Rename Wizard and then Execute Replace Column or Table in Logical T able Sources OracleBl Event Tables Extermnalize Strings Update Physical Layer Hel Repository Documentation P Generate Metadata Dictionary Remove Unused Physical Objects Aggregate Persistence Wizard Execute 3 In Rename Wizard click on Business Model and Mapping tab and select the PC business model Click on Add Hierarchy Rename Wizard What do you want to rename fy PC Add Add Hierarchy Remove ORACLE on Business Model and Mapping z Back Cancel FCIS FD07 03 01 OBIEE repository Development Guide 28 4 Click on Next Select all Next Logical Source Folder Select Mone Logical Table Source Logical Column ORACLE lt Back Cancel FCIS FD07 03 01 OBIEE repository Development Guide 29 5 Click on All text lowercase Add Rename Wizard What do you want to rename Al text uppercase All test lowercase First letter capital First letter of each word capital Insert space before each first uppercase letter unless on the frst position or there is a Ch
41. n PC Contract Dim AUTO_RESPONSE will be renamed to AUTO RESPONSE Logical Column PC Contract Dim RES PONS E_DATE will be renamed to RESPONSE DATE F Logical Column PC Contract Dim RESPONSE _ABYVIC E_REQD will be renamed ta RESPO INSI ORACLE Logical Column PC Contract Dim RESPONSE ADYIL E BAS AS vill be renamed ta RESPONS Logical Column PC Contract Dim RESPONSE ADVICE DATE will be renamed to RESPONSE Logical Column PC Contract Dim RESPONSE ADVICE SENT will be renamed to RESPONSE Logical Column PC Contract Dim REDISPATCH REGO will be renamed to REDISPATCH RE Logical Column PC Contract Dim AUTO REDISI PA TCH will be renamed to AUTO REDISFATI EI ii FCIS FD07 03 01 OBIEE repository Development Guide 33 9 Expand Branch table select the columns which are not required for reporting right click on them and delete Branch Sources Branch Code Branch Name Farent Branch Regional Office Bank Code Fund Branch Gen MOS hia Display Related d b H H E H E H B E E Check Gut uk Cret Copy Chrl C TOD EELE E Mark Set Icon Duplicate Business Model Diagram d FCIS FD07 03 01 OBIEE repository Development Guide 34 10 Similarly delete unnecessary columns from all the logical tables Business Model and Mapping Branch _ Sources Branch Code Branch Name Parent Branch Regional Office Bank Code Fund Branch Gen Mia ontra
42. n memory repository Then it listens for messages from the Administration Tool about changes makes those changes to its in memory copy and when told by the Administration Tool tells the OS to save the changed file When you start the Administration Tool in online mode you pick an Oracle BI ODBC DSN that points to the repository you want to edit i e the repository that is the default repository in the DSN The Administration Tool then communicates changes to Oracle BI Server and Oracle BI Server makes the corresponding changes to its in memory copy FCIS FD07 03 01 OBIEE repository Development Guide 47 Typically you develop a repository in offline mode and use online mode for minor updates and changes 8 1 Create a New Measure To add a new measure to the Contract Facts table perform the following steps 1 In the Physical layer navigate to PC_CSTMS_PRODUCT gt PRODUCT_CODE 3 2 PC Hil a C_CSTMS_PRODUCT AS5ET_ CATEGORIES LIST SUTH_STAT BRANCHES LIST CATEGORIES LIST CHECKER_OT_STAMP CHECKER_ID CURRENCIES LIST GEN_MT103F INCLUDE _FOR_TOS_CALC INSTRUMENT PRODUCT ALLOW LOCATION LIST MARER_OT_ STAMP MARER_ID MAaIMUM RATE VARISNCE MOD _NO MODULE NO_OF LEGS NORMAL RATE VARISNCE ONCE _AUTH PART _OF PRODUCT POOL_CODE PORTFOLIO PRODUCT _ALLOMW PAOUDUCT CODE PRODUCT DESCRIPTION Aire e ire re re re re re re re re re re re re re re a re re re re re re 2 Drag PRODUCT_CODE from the
43. ngle value at any point in time Variables can be used instead of literals or constants in the Expression Builder in the Administration Tool Oracle BI Server substitutes the value of a variable for the variable itself in the metadata You use the Variable Manager to define variables and initialization blocks There are two classes of variables repository variables and session variables A repository variable has a single value at any point in time There are two types of repository variables static and dynamic Static repository variables have values that are constant and does not change while Oracle BI Server is running Dynamic repository variables have values that are refreshed by data returned from queries in initialization blocks Repository variables are represented by a question mark icon in the Variable Manager Session variables are created and assigned a value when each user logs on There are two types of session variables system and nonsystem System variables have reserved names and are used for specific purposes by Oracle BI Server such as authenticating users Non system variables are application specific variables created by an Administrator System and nonsystem variables are represented by a question mark icon in the Variable Manager FCIS FD07 03 01 OBIEE repository Development Guide 64 Initialization blocks are used to initialize dynamic repository variables system session variables and nonsystem session variables 10
44. og box FCIS FD07 03 01 OBIEE repository Development Guide 67 Session Variable Initialization Block Data Source setUser Data Source Type Database Use database specific SOL Default Initialization String Connection Pool FlexCube Connection Pool Browse OK Cancel Help 8 Type SELECT TO_CHAR SYSDATE MM DD YYYY HH MI SS FROM dual in the Default Initilization String session Variable Initialization Block Data Source PC_Current_Date el s Data Source Type Database Use database specific SOL Default Initialization String SELECT TO CHARCSYSDATE MMDD YYY HH MM SS FROM dual Connection Pool FlexCube Connection Pool Browse cet te FCIS FD07 03 01 OBIEE repository Development Guide 68 Session Variable Initialization Block PC_Current_Date Mame PC Current Date Disabled Data Source Connection Pool FlexCube Connection Pool Edit Data Source Data base ODBC 2 0 Initialization string inherited From Default SELECT TO_CHAR SYSDATE MMOD HH MI 55 FROM dual ieviskl T aent 9 Click Edit Data Target to open the Session Variable Initialization Block Variable Target dialog box Session ariable Initialization Block ariable Target setUser Ioj x Default Initializer Up Down Edit Mew Links Remove C Row wise initialization Use caching cel __ 10 Click New to open the Session Variable dialog box F
45. or damage caused to the user by the direct or indirect use of this FCIS FD07 03 01 OBIEE repository Development Guide and the accompanying Software System Furthermore Oracle Financial Services Software Limited reserves the right to alter modify or otherwise change in any manner the content hereof without obligation of Oracle Financial Services Software Limited to notify any person of such revision or changes All company and product names are trademarks of the respective companies with which they are associated FCIS FD07 03 01 OBIEE repository Development Guide 73
46. s indicate errors that need to be fixed to make the repository consistent Warning messages indicate conditions that may or may not be errors depending upon the intent of the Oracle BI Server administrator For example if the Administrator user has an empty password this should be addressed but is not a requirement for a consistent repository Best Practices messages provide information about conditions but do not indicate an inconsistency For example if there are physical tables with no keys defined a best practice message is displayed Defining keys for physical tables is best practice but is not a requirement for a consistent repository 5 4 2 Check consistency 1 Select File gt Check Global Consistency Edit View Manage Tools Window Mew Ctrl M Open Multiuser j Close Copy As Import Compare Check Global Consistency CErl k Check Gut All 1 CeOracleBly Repository Pic 2 0 OracleBl FlexCube rpd 3 0 Oraclebl FlexCube 4 BRANCH Shut Down Server Exit FCIS FD07 03 01 OBIEE repository Development Guide 42 2 You should receive a message indicating that the repository is consistent and asking if you want to make it available for queries Oracle BI Administration Tool 1 Me Business model Pc is consistent Do you want to mark it as available For queries Mo 3 Click Yes to make the Pc business model available for queries The Consistency Check Manager displays consistency
47. single logical table in the Business Model and Mapping layer onto multiple presentation tables To organize the Presentation layer perform the following steps 1 In the Presentation layer double click the Pc presentation catalog to open the Presentation Catalog dialog box Presentation Catalog Pc General Presentation Tables Aliases Name Pe Persson Custom display name VALUEOFING_SESSION CN_Pc Business model Export logical keys Implicit Fact Column nat assigned Set Clear Description FCIS FD07 03 01 OBIEE repository Development Guide 60 2 Click the Presentation Tables tab Presentation Catalog Pc General Presentation Tables Aliases Contract Facts Contract Dim Product Branch Up Down Add Edit Remove Cancel Help 3 To create a new presentation Table click the Add button to open the Presentation Table dialog box FCIS FD07 03 01 OBIEE repository Development Guide 61 4 Click the General tab and name the presentation table as Product Type In the Description field type gt Product Type attributes Adding gt in the description nests the table under the table above it The description displays as a tool tip in Answers Click on OK Presentation Table Product Type General Columns Aliases E Name Product Type Permissions Custom display name VALUEOF NG_SESSION CM_Po_Product_Type Description gt Product Type A
48. t Description Product Start Date Product End Date Product Group Module Pool Code Currencies List Categories List Fal lg a ka lg a lg Branch Contract Dim ES Contract Facts Product 7 Similarly Create Dimensions for Branch Contract Dim as shown below 4 3 Pe TA BranchDim E28 Branch Total HE Bank ER Bank Code HEA Branch Detail ap Branch Code ra Branch Name TA Contract DimDim E Contract Dim Total E Contract Dim Detall a Contract Ref No TA ProductDim E Product Total 2 6 Product Type ab Product Type ra Product Type Desc E Product Detail ER Froduct Code EL Product Description rd Product Group Branch Contract Dim E3 Contract Facts Product FCIS FD07 03 01 OBIEE repository Development Guide 40 5 4 Building the Presentation Layer of a Repository The Presentation layer is built after the Physical layer and Business Model and Mapping layer and adds a level of abstraction over the Business Model and Mapping layer It is the view of the data seen by end users in client tools and applications such as Oracle BI Answers The Presentation layer provides a means to further simplify or customize the Business Model and Mapping layer for end users For example you can organize columns into catalogs and folders Simplifying the view of the data for users makes it easier to craft queries based on users business needs because you can expose only the data that is meaningful
49. to the users organize the data in a way that aligns with the way users think about the data and rename data as necessary for the set of users You typically create Presentation layer objects by dragging objects from the Business Model and Mapping layer Corresponding objects are automatically created in the Presentation layer Presentation layer objects can then be renamed and reorganized 5 4 1 Build Presentation layer 1 Drag the Pc business model from Business Model and Mapping layer to the Presentation layer to create Pc catalog in the Presentation layer D co e Presentation 2 Expand the Pc catalog in the Presentation layer Notice that the tables and columns in the Presentation layer exactly match the tables and columns in the Business Model and Mapping layer Pe Contract Facts Contract Dim Product Branch 3 Save the Repository Click No when asked for Consistence Check e Run a Consistency Check Consistency check is a utility in the Administration Tool that checks if a repository has met certain requirements Repositories and the business models within them must pass the consistency check before you can make business models available for queries When a FCIS FD07 03 01 OBIEE repository Development Guide 4 repository or business model is inconsistent a detailed message alerts you to the nature of the inconsistency The Consistency Check Manager displays three types of messages Error message
50. troduction 2 1 How to use this Guide The information in this guide includes Chapter 2 Introduction Chapter 3 OBIEE Getting started Chapter 4 OBIEE environment setup Chapter 5 OBIEE RPD development Chapter 6 Enable Query logging Chapter 7 Modify NOSConfig ini Chapter 8 Creating Calculation Measures Chapter 9 Organizing the Presentation Layer Chapter 10 Using Initialization Blocks and Variables 3 OBIEE Getting started OBIEE Plus is a comprehensive BI platform that provides the best foundation for building enterprise BI solutions It includes a full range of easy to use and intuitive end user tools such as Interactive Dashboards advanced reporting and publishing full ad hoc analysis over the Web proactive detection and alerts mobile analytics Microsoft Office integration Web Services and business process integration and more FCIS FD07 03 01 OBIEE repository Development Guide 4 3 1 Component Integration Model The below diagram depicts FLEXCUBE IS report architecture BI Administrators External Applications and Portals Business Users HTML SOAP over HTTP HTTPS Web Browser SAW Bridge J2EE IS API i e eeeeeneneneeeeeeeeeeeee eee e Web Browser l JavaScript i XML HTML For Usability amp Interactivity XLS PDF TXT over HTTP HTTPS e e e e e e JavaScript e For Usability amp Interactivity e e e e e
51. ttributes Cancel Help FCIS FD07 03 01 OBIEE repository Development Guide 62 5 Similarly add another table as Product Details with description as Product Attribute Presentation Table Product Details General Columns Aliases gmH MH ame Product Details Permissions Custom displar name JUEOFIN GW SESSION CA Pe_Product Details Description gt Product Attributes 6 Rearrange the Tables using Up and Down Buttons as shown below Click on ok Presentation Catalog Pc General Presentation T ables Alaszesz Product EEF Product Type tid Product Details Contract Facta Contract Dim Branch Lip Dower Agd E dit Remove Cancel Hep FCIS FD07 03 01 OBIEE repository Development Guide 63 7 Now Cut Product Type and Product Type Description Columns from Product Table and paste it in Product Type Table Cut the remaining column from Product table and paste it in Product Details Table Pe Product Product Type Product Type Product Type Desc Product Details Product Code Product Description Product Start Date Product End Date Product Group Module Pool Code Currencies List Categories List ie Contract Facts Contract Din Branch 10 Using Initialization Blocks and Variables You can use variables in a repository to streamline administrative tasks and modify metadata content dynamically to adjust to a changing data environment A variable has a si
52. users through web browsers 4 OBIEE environment setup This section explains the OBIEE server environment setup required for RPD development 4 1 Create an ODBC Data Source An ODBC data source is needed to import schema information about a data source into an Oracle BI Server repository To create an ODBC data source perform the following steps 1 Click Start gt Control Panel gt Administrative Tools gt Data Sources ODBC to open ODBC Data Source Administrator ODBC Data Source Administrator Tl x User Doh System DSN File DSN Drivers Tracing Connection Pooling About User Data Sources Add BASE Files Microsoft dBase Driver dbf disconnected Adaptive Server Anywhere 6 0 Remove Excel Files Microgott Excel Driver xl MS Access Database Microsoft Access Driver mdb Configure Visual FoxPro Database Microsoft Visual FoxPro Driver Visual FoxPro Tables Microsoft Visual FoxPro Driver An ODBC User data source stores information about how to connect to the indicated data provider A User data source is only visible to you and can only be used on the current machine Cancel Apply Help FCIS FD07 03 01 OBIEE repository Development Guide 6 2 Click on System DSN tab and click Add ODBC Data Source Administrator 2 x User DSN System DSN File DSN Drivers Tracing Connection Fooling About System Data Sources Name Driver A Add
53. ver Started Automatic Local System 4 Oracle Connector 4 Checks for Started Automatic Local System Sy OracleClientCachead Manual Local System Sy Oracle oracleProces 7 Started Automatic Local System Ghadna iaar Felbdene EE E 8 Creating Calculation Measures Oracle BI Server has a calculation engine to perform a multitude of calculations There are different methods for creating calculation measures in the Administration Tool You can use existing logical columns as objects in a formula use physical columns as objects in a formula or use the Calculation Wizard to automate the process You use physical columns for calculations that require and aggregation rule to be applied after the calculation You use logical columns for calculation formulas that require an aggregation rule that is applied before the calculation You can also build calculation measures in Answers The advantage from building calculation measures in the repository is that the measures are built once and can be made available to all users The advantage of defining a logical column formula based on existing logical columns is that you only have to define it once When you create formulas based on physical columns you have to map for each physical source it could be derived from To create calculation measures you perform the following steps 1 Click Start gt Programs gt Oracle Business Intelligence gt Administration to open the Administration Tool 2 Clic
Download Pdf Manuals
Related Search