Transcription
SAP BusinessObjectsConnecting to the Oracle Autonomous DataWarehouse Cloud Service using an Oracle WalletAugust 2018Erick CarlsonSAP Solution ArchitectN.A. SAP on Oracle Teamerick.carlson@oracle.comCopyright 2018, Oracle and/or its affiliates. All rights reserved.
Table of Contents1Introduction2SAP BusinessObjects Server – Oracle Client Setup3Windows Desktop – Oracle Client Setup4Creating and Publishing a SAP BusinessObjects Universe5Report Creation with Crystal Reports6Report Creation with Web Intelligence7ReferencesAugust 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved.
IntroductionThis guide will demonstrate the steps necessary to connect SAP BusinessObjects(SAP BO) to an Oracle Autonomous Data Warehouse Cloud (ADWC) for reporting.The primary focus is to validate the ability to use Oracle Wallet, required by ADWC,to make a secure connection to an Oracle Database. This guide illustrations how tocreate a simple SAP BO Universe relying on the Oracle Call Interface (OCI), not tobe confused with Oracle Cloud Infrastructure (OCI), as the driver for theconnection. This document is by no means is the only way to achieve connectivityto ADWC from SAP BO, it is just one example.This walkthrough is not intended to be a detailed SAP BO reporting guide. Thereporting examples simply demonstrate how to create very basic reports utilizingCrystal Reports and Web Intelligence.August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 3
Basic SkillsThis guide relies on some basic skills necessary to configure the Oracle Client anduse of SAP BO. This guide should provide the details needed to successfullycomplete the entire process of connecting SAP BusinessObjects to OracleAutonomous Data Warehouse Cloud.Required skills and tool knowledge:– Connecting to Linux a host and the ability to transfer files. (i.e. ssh & scp)– Navigating in a Linux server environment and a Windows desktop.– Oracle Client concepts (ORACLE HOME & TNS ADMIN) and tools (sqlplus & tnsping).– Oracle Database query understanding. (i.e. tables, columns & SQL)– Use of text editors on both Linux and Windows. (i.e. vi & notepad)August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 4
Pre-RequirementsThere are certain requirements necessary for this guide to be successful and arelisted below:– SAP BusinessObjects Server and application login credentials. Access to the Central Management Console (http:// hostname :8080/BOE/CMC). Access to the BI Launch Pad (http:// hostname :8080/BOE/BI). A Windows desktop with client development tools for SAP BusinessObjects.– SAP BusinessObjects Sever administrator operating system access.– An Oracle 12.2 Client installed on SAP BusinessObjects server and Windows desktop.– ADWC Oracle Wallet and login F75D4B1August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 5
System InformationSAP BusinessObjects Server host details:August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 6
System InformationSAP BusinessObjects Server details:August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 7
SAP BusinessObjects Server – Oracle Client SetupThis section will walkthrough the essential configuration to enable the Oracle Client onthe SAP BO Server to communicate with a Oracle Autonomous Data Warehouse Clouddatabase. This section is very detailed.The following requirements are needed.– SAP BusinessObjects Server shell access via the admin user.– The Oracle 12.2 Client access for admin user.– Oracle ADWC credentials and Oracle Wallet.August 22, 2018Copyright 2014 Oracle and/or its affiliates. All rights reserved. 8
SAP BusinessObjects Server – Oracle Client SetupLocate the SAP BO local Oracle Client TNS ADMIN directory Log into the SAP BO server asthe SAP BO admin user, in thisexample the user name is:bodadm Navigate to active Oracle ClientTNS ADMIN directory. In thisexample, the SAP BO server isusing the Oracle DatabaseTNS ADMIN directory:/oracle/BOD/12201/network/adminAugust 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 9
SAP BusinessObjects Server – Oracle Client SetupValidate sqlnet.ora file Display the contents of theexisting sqlnet.ora file. In this example, the sqlnet.orafile is rather simple with onlyone parameter set.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 10
SAP BusinessObjects Server – Oracle Client SetupValidate tnsnames.ora file Display the contents of theexisting tnsnames.ora file. In this example, thetnsnames.ora file contains onedatabase alias and listener. The listener is defined in theexample tnsnames.ora filebecause this file is also used bythe Oracle Database and notrequired by an Oracle Clientonly setup.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 11
SAP BusinessObjects Server – Oracle Client SetupValidate the SAP BO local Oracle Client functionality Validate the Oracle Client isable to communicate with theSAP BO Oracle Database oranother Oracle Database byusing the tnsping command. In this example tnsping bodretuned OK (0 msec). This was a successfulconnection test thatcompleted in 0 milliseconds. This can be expected becausethe Oracle database is locatedon the same host.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 12
SAP BusinessObjects Server – Oracle Client SetupExtract the contents of ADWC Oracle Wallet Copy the required ADWCOracle Wallet to the SAP BOserver in a location accessibleto the SAP BO admin user. In this example, SCP was usedto copy wallet ADWCbobj.zip toSAP BO admin (bodadm) user’shome directory. Extract the contents of theOracle Wallet to a subdirectoryin the TNS ADMIN location. In this example, the commandunzip /wallet ADWCbobj.zip -dextracted thecontents of the zip file in tothe directory wallet ADWCbobj.wallet ADWCbobjAugust 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 13
SAP BusinessObjects Server – Oracle Client SetupUpdate sqlnet.ora file Add the contents of the OracleWallet’s sqlnet.ora file to theTNS ADMIN sqlnet.ora file. In this example, the catcommand was first used tomerge the two files together.cat wallet ADWCbobj/sqlnet.ora sqlnet.ora Next the cat command wasused to verify the contents ofthe updated file.cat sqlnet.ora Two parameters are added tothe existing sqlnet.ora file,WALLET LOCATION andSSL SERVER DN MATCH.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 14
SAP BusinessObjects Server – Oracle Client SetupModify updated sqlnet.ora filebefore modification The DIRECTORY value of theparameter WALLET LOCATION inthe updated sqlnet.ora fileneeds adjusted to reflect thepath of the extracted OracleWallet files. In this example, vi was used tomodify the sqlnet.ora file toadd /wallet ADWCbobj after/admin to be:after modificationDIRECTORY “?/network/admin/wallet ADWCbobj”August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 15
SAP BusinessObjects Server – Oracle Client SetupUpdate tnsnames.ora file Add the contents of the OracleWallet’s tnsnames.ora file to theTNS ADMIN tnsnames.ora file. In this example, the catcommand was first used tomerge the two files together.cat wallet ADWCbobj/tnsnames.ora tnsnames.ora Next the cat command wasused to verify the contents ofthe updated file.cat tnsnames.ora Three database aliases areadded to the tnsnames.ora filefor the ADWC database.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 16
SAP BusinessObjects Server – Oracle Client SetupVerify ADWC connectivity Validate the Oracle Client isable to communicate with anADWC database using thetnsping command. In this example tnspingADWCbobj low retuned OK (340msec). This was a successfulconnection test thatcompleted in 340 milliseconds.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 17
SAP BusinessObjects Server – Oracle Client SetupExample of failed ADWC connectivity Here is an example of what afailed Oracle Client connectionattempt to an ADWC databaseusing the tnsping commandwould look like. In this example tnspingADWCbobj low retuned TNS-12560:TNS:protocol adapter error. The cause for this failure was adeliberate removal of thecwallet.sso file form the OracleWallet directory in order toshowcase a failure.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 18
SAP BusinessObjects Server – Oracle Client SetupValidate ADWC login Finally validate the ability tologin to the ADWC databasevia the sqlplus command. In this example, the commandsqlplus user @ADWCbobj lowwas issued, followed by thepassword. A successfulconnection to the databasewas established.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 19
Windows Desktop – Oracle Client SetupThis section will documents configuration to enable a Windows desktop to communicatewith an Oracle Autonomous Data Warehouse Cloud database. This section is not asdetailed as the previous because the steps are very similar. Please refer back to section“SAP BusinessObjects Server – Oracle Client Setup” for details.The following requirements are needed.– A Windows desktop with Oracle 12.2 Client access.– Oracle ADWC credentials and Oracle Wallet.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 20
Windows Desktop – Oracle Client SetupLocate the Windows Desktop Oracle Client TNS ADMIN directory This may be a difficult stepbecause the TNS ADMINenvironment value is not setby default during the OracleClient installation. The default location is derivedfrom the ORACLE HOME as:%ORACLE HOME%\network\admin Extract the contents of theOracle Wallet to a subdirectoryin the TNS ADMIN location.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 21
Windows Desktop – Oracle Client SetupUpdate the sqlnet.ora file Update the TNS ADMIN sqlnet.orafile with the contents of theOracle Wallet’s sqlnet.ora file. This is similar to the SAP BOServer setup above. The DIRECTORY value of theWALLET LOCATION parameterneeds updated to reflect thelocation of the Oracle Wallet. The updated parameter forthis example:DIRECTORY “?/network/admin/wallet ADWCbobj”August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 22
Windows Desktop – Oracle Client SetupUpdate the tnsnames.ora file Update the TNS ADMINtnsnames.ora file with thecontents of the Oracle Wallet’stnsnames.ora file. This is similar to the SAP BOServer setup above and theupdated tnsnames.ora fileshould now have theadditional ADWC databasealiases.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 23
Windows Desktop – Oracle Client SetupVerify ADWC connectivity Validate the Oracle Client isable to communicate with anADWC database using thetnsping command. In this example tnspingADWCbobj low retuned OK (440msec). This was a successfulconnection test thatcompleted in 440 milliseconds.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 24
Windows Desktop – Oracle Client SetupValidate ADWC login Finally validate the ability tologin to the ADWC databasevia the sqlplus command. In this example, the commandsqlplus user @ADWCbobj lowwas issued,followed by the password. Asuccessful connection to thedatabase was established.nsping ADWCbobj lowAugust 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 25
Creating and Publishing a SAP BusinessObjects UniverseThis is the longest segment of document. It goes through the process of creating aconnection to the ADWC, building a Universe with queries and publishing to the SAP BOServer. By no means is this section designed to be a complete guide to SAP BO’s, only abasic “to get started” reference. There are numerous ways to achieve the same objectiveand many other detailed guides documenting SAP BO reporting.The following requirements are needed.– SAP BusinessObjects application login credentials.– Access to the Central Management Console (http:// hostname :8080/BOE/CMC).– Access to the BI Launch Pad (http:// hostname :8080/BOE/BI).– A Windows desktop with client development tools for SAP BusinessObjects.– Oracle ADWC credentials.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 26
Information Design ToolRepository Resources: Open Session Provide the appropriate User Name, Password,Authentication type, and Description (if desired). Once connected, you are able to browse the existingConnections and Universes. Open the Repository Resources tab (located under theWindows menu). Insert a new or Open a previous session to the SAP BOServer. In this .August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 27
Information Design ToolNew Universe: Project Name & Data Source Type The Data Source should be of type Relational and thenproceed by pressing Next. Create a New Universe (located under the File menu)by creating a new Project. Provide a Project Name (i.e. ADWCbobj via OCI), ProjectLocation and press Next to continue.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 28
Information Design ToolNew Universe: New Relational Connection Navigate the Driver Selection tree to Oracle - Oracle12c Release 2. Select the Oracle Client driver andcontinue by pressing Next. Provide a Resource Name (i.e. ADWCbobj via OCI), ameaningful Description (if desired) for t he NewRelational Connection and press Next.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 29
Information Design ToolNew Universe: Relational Connection Credentials Press the Test Connection button followed by ShowDetails to display the connection information. Pressing the Close button will continue to the nextstep. Select the appropriate Authentication Mode, UserName and Password for the target ADWC Database. Type in the appropriate database alias from the clienttnsnames.ora file (the drop down selection may beempty or not display the desired alias).August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 30
Information Design ToolNew Universe: Relational Connection Parameters Change or add parameters as needed or preferred andpress Next to continue on. No changes were made to these default values.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 31
Information Design ToolNew Universe: New Data Foundation Navigate the newly created Relational Connection toselect the tables to be included in the New Universe. Review the below screenshot 1 and screenshot 2 onthe following page before continuing. Provide a Resource Name (i.e. ADWCbobj via OCI), ameaningful Description (if desired) for the New DataFoundation and press Next to begin database tableselection.screenshot 1August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 32
Information Design ToolNew Universe: Data Foundation Table Selection & Business Layer The final step of the wizard is to provide a ResourceName (i.e. ADWCbobj via OCI) and a meaningfulDescription (if desired) for the New Relational BusinessLayer.screenshot 2 After selecting all the sought after tables for theUniverse, choose any or all of the Detect check boxesto allow the Information Design Tool to discoverrelationships about the tables and press Next.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 33
Information Design ToolUniverse Business Layer After completing the Wizard theBusiness Layer (identified by the .blxfile type in the tab name) is displayed. The Business Layer displays the DataFoundation (identified by the .dfx filetype) showing the previously includedtables and relationships. This completes the initial Universecreation.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 34
Information Design ToolUniverse Connection: SQL Test After successfully completing the NewUniverse wizard, the locally createdconnection should be published to theSAP BO Server. Before publishing the connection a SQLquery can be entered to validate theconnection, if desired. Click on the Connection tab (identifiedby the .cnx file type in the tab name)and then the Show Values sub tab.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 35
Information Design ToolUniverse Connection: SQL Test Type or paste the chosen SQL query into the Show Expression text box. Do not place a semicolon at the end ofthe query. Press the Refresh button to execute theQuery. In this test: the query used was copiedfrom ADWC Star Schema BenchmarkAnalytic Views documentation. For more information UID431A16E8-4C4D-4786-BE5C-30029AC1EFD8August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 36
Information Design ToolUniverse Connection: SQL Test The query’s results will be displayed inthe Raw Data tab.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 37
Information Design ToolUniverse Connection: Publish To publish the local connection to theSAP BO server, first navigate to theLocal Projects tab (located under theWindows menu). If not already, Expand the active LocalProject (ADWCbobj via OCI). Right click on the local connection(.cnx) and select Publish Connection toa Repository.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 38
Information Design ToolUniverse Connection: Publish Select a folder to store the connection on the SAP BOserver. Provide the required Password for the current Session.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 39
Information Design ToolUniverse Connection: Publish Verify the newly published connection is visible underthe Repository Resources tab. Select Yes to create a shortcut to the publishedconnection in the Local Project.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 40
Information Design ToolUniverse Connection: Data Foundation Connection Select the newly published connection (.cns) and pressFinish to switch the connection. Change the local connection in the Data Foundation touse the new published connection. On the Data Foundation tab, right click on theconnection and select Change.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 41
Information Design ToolUniverse Business Layer: Query The next step would be to add a queryto the Universe’s Business Layer. Select the Queries section on theBusiness Layer tab and press the InsertQuery button.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 42
Information Design ToolUniverse Business Layer: Query Drag columns or entire tables to ResultObjects section to start building aquery. Where clause can be added andRanking in the Query Filters section. Pressing the Refresh button updatesthe results of the query. In the example the enter Customertable was used with no filters. When the desired query is complete,press OK.August 22, 2018Copyright 2018, Oracle and/or its affiliates. All rights reserved. 43
Information Desi
Creating and Publishing a SAP BusinessObjects Universe . This is the longest segment of document. It goes through the process of creating a connection to the ADWC, building a Universe with queries and publishing to the SAP BO Server. By no means isthis section designed to be a com
