5/14/2019
Posted by admin
Troubleshooting Oracle BI EE Connections to Oracle OLAP Oracle BI EE 11.1.1.5, Oracle OLAP and Oracle Database 11g
NOTE:
The following is intended to outline our general product direction. It is intended for information purposes only and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle.
TABLE OF CONTENTS
NOTE ....................................................... 0 TABLE OF CONTENTS ........................................... 2 INTRODUCTION ............................................... 3 OVERVIEW .................................................. 3 ARCHITECTURE ............................................... 4 KEY CONFIGURATION CHECKLIST ................................. 5 TROUBLESHOOTING CONNECTIONS ............................... 6 1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path .................................. 7 2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll .... 8 3. Error attempting to load oraccore11.dll and the oranls11.dll....... 8 4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions .. 8 5. [nQSError: 60001] Connection failed for: Remote ServiceJavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC .............................. 9 6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000) ..................................... 9
Draft – Oracle Confidential
Page 2
Troubleshooting Oracle BI EE Connections to Oracle OLAP
Introduction This paper summarizes how Oracle BI EE connects to Oracle OLAP for both metadata administration and query. It then provides troubleshooting tips for common issues with those connections.
Overview Oracle BI EE 11.1.1.5 offers new capabilities over an Oracle OLAP source. The Administration Tool is now able to import metadata from an analytic workspace using a simple wizard – automatically populating BI EE’s Physical Layer with dimensions, hierarchies, measures and calculations. The BI Server uses this metadata for optimal query execution – exploiting OLAP strengths such as analytic calculations (time-series, shares, financial functions, etc.), advanced hierarchies (skip, ragged, value-based, etc.) and complex aggregations (sum, last, hierarchical last, weighted averages, etc.). These new capabilities result in enhanced developer productivity and outstanding performance for complex analytic queries. Figure 1 highlights the two key steps for metadata import: 1) connecting to the OLAP source and 2) selecting the metadata to import from the analytic workspace:
Draft – Oracle Confidential
Page 3
Figure 1: Administration Tool Import Wizard
Once imported, the creation of the Business Model and Presentation layer are greatly simplified. The Business Model is generally a direct reflection of the Physical Layer – few modifications are typically necessary.
Architecture Figure 2 illustrates how Oracle BI EE connects to Oracle OLAP for both administration and query:
Draft – Oracle Confidential
Page 4
Figure 2: Components required for OLAP access by BI EE
In order to import OLAP metadata, BI EE utilizes the OLAP Java API (the OLAP API libraries are installed with the BI middle tier). This requires the Administration Tool to invoke the middle tier Java Host to access OLAP metadata. Once the metadata is defined, the BI Server queries OLAP cubes and dimensions using SQL. Query access requires a standard OCI connection to the Oracle Database – similar to any other Oracle Database source.
Key Configuration Checklist Consult the following checklist to ensure your system is configured to allow connections between BI EE and Oracle OLAP:
Install the Oracle Database Client (use the Administrator or Runtime client install option). The Oracle Database Client must be in sync with the BI Administration Tool (i.e. both tools must be 32-bit or 64-bit. You cannot mix versions).
The Java Host must be running on the middle tier.
The Data Source in the Administration Tool’s Metadata Import wizard must use the host:port:sid connection string format. Note, the Java Host on the middle tier must be able to make a connection to the Oracle Database using this connection string.
Draft – Oracle Confidential
Page 5
After importing the metadata for your OLAP cubes, update the Data Source Name in the Connection Pool to use an Oracle Database Network Alias (i.e. a valid TNS entry).
Specify the Java Host connection details in NQSCONFIG.INI when running a stand-alone Administration Tool that connects to a remote Java Host: o
On the Administration Tool client machine, edit the NQSCONFIG.INI file in folder [bi_home] oraclebiorainstconfigOracleBIServerComponentco reapplication
o
Update the [JAVAHOST] section with the appropriate connection details. For example, if your Java Host is running on server “mybihost” and is configured for port “9810”, the section should read as follows (ignoring the text wrapping): JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = 'mybihost:9810';
Troubleshooting Connections This section provides solutions to common connection issues. Below you will find a list of error messages with links to the problem’s resolution. Administration Tool Import Error Messages: 1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path 2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll 3. Error attempting to load oraccore11.dll and the oranls11.dll 4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions 5. [nQSError: 60001] Connection failed for: Remote ServiceJavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC Answers Error Message 6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host
Draft – Oracle Confidential
Page 6
or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)
1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path
Issue TNS alias names are not supported for metadata import. Resolution The Data Source Name must use a host:port:sid connection string. Note, the Java Host will make the connection to OLAP on behalf of the Administration Tool. This means that the middle tier must be able to make the database connection using the specified connection string.
Draft – Oracle Confidential
Page 7
2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll
Issue There is a mismatch between the BI Administration Tool and the Oracle Database Client installed on the client machine. Resolution The BI Administration Tool and Oracle Database Client must both be either 32-bit or 64-bit.
3. Error attempting to load oraccore11.dll and the oranls11.dll Issue The incorrect type (InstantClient) of Oracle Database Client was installed on the client machine. Resolution You must install one of the following types of Oracle Database Clients: 1) Administrator or 2) Runtime.
4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions Issue The import was able to successfully connect to OLAP – but no metadata is available. Resolution
Draft – Oracle Confidential
Page 8
The OLAP API jars on the middle tier need to be updated to version 11.2.x. The OLAP API libraries are found in your Oracle Database home: [oracledb home]product11.2.0dbhome_1olapapilib. BI EE provides an 11.1.x version of these files in [obiee home]Oracle_BI1bifoundationjavahostlibobisintegrationaw11g. Backup the BI EE version of the OLAP API jars and replace them with the version provided by the database.
5. [nQSError: 60001] Connection failed for: Remote Service-JavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC Issue The import was unable to successfully connect to OLAP using the Java Host. Resolution Ensure the Java Host is running and that the NQSCONFIG.INI file on the client machine is set up to make a connection to that Java Host.
6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000) Issue After importing metadata from OLAP, the Data Source Name in the Connection Pool is using host:port:sid. It must be changed to use OCI.
Draft – Oracle Confidential
Page 9
Resolution Edit the Connection Pool object and specify a valid TNS entry for the Data Source Name.
Draft – Oracle Confidential
Page 10
Troubleshooting Oracle BI EE Connections to Oracle OLAP July 2011 Oracle BI EE 11.1.1.5, Oracle OLAP and Oracle Database 11g Author: Marty Gubar Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. Worldwide Inquiries: Phone: +1.650.506.7000 Fax: +1.650.506.7200 oracle.com Copyright © Oracle BI EE 11.1.1.5, Oracle OLAP and Oracle Database 11g, Oracle Corporation and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
NOTE:
The following is intended to outline our general product direction. It is intended for information purposes only and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remain at the sole discretion of Oracle.
TABLE OF CONTENTS
NOTE ....................................................... 0 TABLE OF CONTENTS ........................................... 2 INTRODUCTION ............................................... 3 OVERVIEW .................................................. 3 ARCHITECTURE ............................................... 4 KEY CONFIGURATION CHECKLIST ................................. 5 TROUBLESHOOTING CONNECTIONS ............................... 6 1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path .................................. 7 2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll .... 8 3. Error attempting to load oraccore11.dll and the oranls11.dll....... 8 4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions .. 8 5. [nQSError: 60001] Connection failed for: Remote ServiceJavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC .............................. 9 6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000) ..................................... 9
Draft – Oracle Confidential
Page 2
Troubleshooting Oracle BI EE Connections to Oracle OLAP
Introduction This paper summarizes how Oracle BI EE connects to Oracle OLAP for both metadata administration and query. It then provides troubleshooting tips for common issues with those connections.
Overview Oracle BI EE 11.1.1.5 offers new capabilities over an Oracle OLAP source. The Administration Tool is now able to import metadata from an analytic workspace using a simple wizard – automatically populating BI EE’s Physical Layer with dimensions, hierarchies, measures and calculations. The BI Server uses this metadata for optimal query execution – exploiting OLAP strengths such as analytic calculations (time-series, shares, financial functions, etc.), advanced hierarchies (skip, ragged, value-based, etc.) and complex aggregations (sum, last, hierarchical last, weighted averages, etc.). These new capabilities result in enhanced developer productivity and outstanding performance for complex analytic queries. Figure 1 highlights the two key steps for metadata import: 1) connecting to the OLAP source and 2) selecting the metadata to import from the analytic workspace:
Draft – Oracle Confidential
Page 3
Figure 1: Administration Tool Import Wizard
Once imported, the creation of the Business Model and Presentation layer are greatly simplified. The Business Model is generally a direct reflection of the Physical Layer – few modifications are typically necessary.
Architecture Figure 2 illustrates how Oracle BI EE connects to Oracle OLAP for both administration and query:
Draft – Oracle Confidential
Page 4
Figure 2: Components required for OLAP access by BI EE
In order to import OLAP metadata, BI EE utilizes the OLAP Java API (the OLAP API libraries are installed with the BI middle tier). This requires the Administration Tool to invoke the middle tier Java Host to access OLAP metadata. Once the metadata is defined, the BI Server queries OLAP cubes and dimensions using SQL. Query access requires a standard OCI connection to the Oracle Database – similar to any other Oracle Database source.
Key Configuration Checklist Consult the following checklist to ensure your system is configured to allow connections between BI EE and Oracle OLAP:
Install the Oracle Database Client (use the Administrator or Runtime client install option). The Oracle Database Client must be in sync with the BI Administration Tool (i.e. both tools must be 32-bit or 64-bit. You cannot mix versions).
The Java Host must be running on the middle tier.
The Data Source in the Administration Tool’s Metadata Import wizard must use the host:port:sid connection string format. Note, the Java Host on the middle tier must be able to make a connection to the Oracle Database using this connection string.
Draft – Oracle Confidential
Page 5
After importing the metadata for your OLAP cubes, update the Data Source Name in the Connection Pool to use an Oracle Database Network Alias (i.e. a valid TNS entry).
Specify the Java Host connection details in NQSCONFIG.INI when running a stand-alone Administration Tool that connects to a remote Java Host: o
On the Administration Tool client machine, edit the NQSCONFIG.INI file in folder [bi_home] oraclebiorainstconfigOracleBIServerComponentco reapplication
o
Update the [JAVAHOST] section with the appropriate connection details. For example, if your Java Host is running on server “mybihost” and is configured for port “9810”, the section should read as follows (ignoring the text wrapping): JAVAHOST_HOSTNAME_OR_IP_ADDRESSES = 'mybihost:9810';
Troubleshooting Connections This section provides solutions to common connection issues. Below you will find a list of error messages with links to the problem’s resolution. Administration Tool Import Error Messages: 1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path 2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll 3. Error attempting to load oraccore11.dll and the oranls11.dll 4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions 5. [nQSError: 60001] Connection failed for: Remote ServiceJavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC Answers Error Message 6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host
Draft – Oracle Confidential
Page 6
or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000)
1. [nQSError: 77031] Error occurs calling remote service AWImportService11g. Details: Error sending result for service … no ocijdbc11 in java.library.path
Issue TNS alias names are not supported for metadata import. Resolution The Data Source Name must use a host:port:sid connection string. Note, the Java Host will make the connection to OLAP on behalf of the Administration Tool. This means that the middle tier must be able to make the database connection using the specified connection string.
Draft – Oracle Confidential
Page 7
2. [nQSError: 77021] Could not load SAW RPC library sawrpcapi.dll
Issue There is a mismatch between the BI Administration Tool and the Oracle Database Client installed on the client machine. Resolution The BI Administration Tool and Oracle Database Client must both be either 32-bit or 64-bit.
3. Error attempting to load oraccore11.dll and the oranls11.dll Issue The incorrect type (InstantClient) of Oracle Database Client was installed on the client machine. Resolution You must install one of the following types of Oracle Database Clients: 1) Administrator or 2) Runtime.
4. Step 4 of the Import Wizard displays no OLAP cubes or dimensions Issue The import was able to successfully connect to OLAP – but no metadata is available. Resolution
Draft – Oracle Confidential
Page 8
The OLAP API jars on the middle tier need to be updated to version 11.2.x. The OLAP API libraries are found in your Oracle Database home: [oracledb home]product11.2.0dbhome_1olapapilib. BI EE provides an 11.1.x version of these files in [obiee home]Oracle_BI1bifoundationjavahostlibobisintegrationaw11g. Backup the BI EE version of the OLAP API jars and replace them with the version provided by the database.
5. [nQSError: 60001] Connection failed for: Remote Service-JavaHostImportService… Java Host is not available inside the function AWImportService11g of SAW RPC Issue The import was unable to successfully connect to OLAP using the Java Host. Resolution Ensure the Java Host is running and that the NQSCONFIG.INI file on the client machine is set up to make a connection to that Java Host.
6. Query Failed: [nQSError: 17001] Oracle Error code: 12545, message: ORA-12545: Connect failed because target host or object does not exist at OCI call OCIServerAttach. [nQSError: 17014] Could not connect to Oracle database. (HY000) Issue After importing metadata from OLAP, the Data Source Name in the Connection Pool is using host:port:sid. It must be changed to use OCI.
Draft – Oracle Confidential
Page 9
Resolution Edit the Connection Pool object and specify a valid TNS entry for the Data Source Name.
Draft – Oracle Confidential
Page 10
Troubleshooting Oracle BI EE Connections to Oracle OLAP July 2011 Oracle BI EE 11.1.1.5, Oracle OLAP and Oracle Database 11g Author: Marty Gubar Oracle Corporation World Headquarters 500 Oracle Parkway Redwood Shores, CA 94065 U.S.A. Worldwide Inquiries: Phone: +1.650.506.7000 Fax: +1.650.506.7200 oracle.com Copyright © Oracle BI EE 11.1.1.5, Oracle OLAP and Oracle Database 11g, Oracle Corporation and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Program scan ljk gratis. Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1.5). Fusion Middleware Online Documentation Library 11g Release 1 (11.1.1.5.0).
Overview
Oracle OLAP is an integrated component of Oracle Database 11g that enables companies to easily gain insights into business performance. It provides the following benefits:
- Exceptional query, calculation and data preparation performance
- Rich analytic capabilities
- Simple user model that reflects business usage
- Open access to any SQL tool
Native multidimensional object types in Oracle database are provided by Oracle OLAP cubes. Cubes are made up of measures and organized by dimensions.
Measures represent factual data, such as sales, cost, profit, and margin. Measures may be stored or calculated at query time. Stored measures are loaded and stored in the database. The values for calculated measures are computed dynamically by the OLAP calculation engine at query time. Common calculations include measures such as ratios, differences, time-series, indicies, moving totals, and averages. Calculations do not require disk storage space, and they do not extend the processing time required for data maintenance.
Dimensions identify and categorize your measure data. Examples of dimensions include product, geography, time, and distribution channel. Dimension hierarchies are optional but are common in OLAP systems. A hierarchy is a logical structure that groups like members of a dimension together for the purpose of analysis. A dimension�s structure is organized hierarchically based on parent-child relationships. These relationships enable navigation between levels, and aggregation from child values to parent values.
Cubes provide a convenient way of collecting similar measures of the same dimensionality. It is not uncommon for many measures to have the same shape, and so by defining their shape (and other shared characteristics) for a cube, you can save time when building your OLAP data model.
Oracle OLAP adds power to your SQL applications by providing extensive analytic content and fast query response times. A SQL query interface enables any application to query cubes and dimensions without any knowledge of OLAP.
The OLAP option automatically generates a set of relational views on cubes, dimensions, and hierarchies. SQL applications query these views to display the information-rich contents of these objects to analysts and decision makers.
Oracle BI EE is an end-user tool that depends on its own metadata layer, which means that the metadata repository must describe how queries should be constructed against the relational data sources. With Oracle OLAP data, you must complete the same administrative tasks for metadata that are required for any relational source.
An Oracle BI EE metadata repository requires you to create three layers of information:
- First, you define a Physical layer. This metadata layer identifies the source data.
- Second, you define a Business Model and Mapping layer. This metadata layer organizes the physical layer into logical categories and records the appropriate metadata for access to the source data.
- Finally, you define the Presentation layer. This metadata layer exposes the business model entities for end-user access.
Prerequisites
| 1. | Have access to or have installed Oracle BI EE 11.1.1.5 Note: You must have administrator-level access to the WebLogic managed server instance for Oracle BI EE. |
|---|---|
| 2. | Have access to or have installed Oracle 11g Database with the OLAP option Note: You will need sysdba access to the Oracle database. |
| 3. | Have downloaded the manual installation files for the Oracle Sample Application for Oracle BI EE, V107 (for 11.1.1.5) |
| 4. | Have started Oracle database, Oracle TNS listener, and Oracle BI services |
Setting Up the Tutorial Environment
In this topic, you use Oracle data pump to create the BISAMPLE schema required for this tutorial. If you already have a schema called BISAMPLE in your Oracle database, you must drop the existing schema before completing this topic. In the event that you cannot drop the existing schema, you can import the new schema into a different schema name using the schema mapping options available in the impdp command. However, if you choose to rename the new schema, be sure to reference your alternate schema name instead of BISAMPLE for the remainder of the tutorial.
Note: If you have already installed the complete Sample Application for Oracle BI EE (V107), this section is not required.
| 1. | Unzip the Sample Application installation files. Note: If you plan to install the entire sample application, Oracle recommends that you unzip the installation archive to a root directory (for example, c:) to avoid having long path name conflicts. |
|---|---|
| 2. | From your sample application installation files, copy DataSourcesORCLSASchemas.dmp to the machine where Oracle database 11gR2 is running. Place the file in a folder (for example, c:datapump). |
| 3. | Open a command window and enter a connection string to connect to SQLPlus as a SYSDBA user. For example: Note down the Oracle database release number displayed in the connection confirmation message; you will need this information when you import the database dump. |
| 4. | At the SQL prompt, create a directory object to import the database dump. For example: create or replace directory datapumpdir as 'c:datapump'; Note: Refer to the file path where you placed SASchemas.dmp. |
| 5. | Type Exit to exit SQLPLUS. At the Windows command prompt, use the impdp utility to import the BISAMPLE schema from the database dump file. For example: impdp 'sys/oracle@orcl as sysdba' directory=datapumpdir dumpfile=SASchemas.dmp version=11.2.0.1.0 schemas=BISAMPLE LOGFILE=SASchemas_imp.log Note: Replace the user and password in the example with a valid SYSDBA user. Replace the version in the example with the Oracle database release number specific to your installation.During the import process you might receive the following warning: ORA-39082: Object type VIEW:'BISAMPLE'.'ODM_SAMP_CUSTOMERS_LTV' created with compilation warnings. This a known warning; you can safely ignore it. |
| 6. | Connect to SQLPlus again as a SYSDBA user and change the BISAMPLE schema password to BISAMPLE. For example: At the Windows prompt: sqlplus sys/oracle@orcl as sysdba At the SQL prompt: alter user BISAMPLE identified by BISAMPLE; |
| 7. | Close the command window. |
Creating Oracle BI EE Metadata for Oracle OLAP
In this topic, you learn how to create a repository, import OLAP metadata into a repository, create business models and subject areas, and upload your repository to the Oracle BI server.
How To Open Oracle Bi Administration Tool
Creating a Repository and Importing Metadata to the Physical Layer
| 1. | Select Start > Programs > Oracle Business Intelligence > BI Administration. The Oracle BI Administration Tool window is displayed. |
|---|---|
| 2. | Select File, and then New Repository. The Create New Repository - Repository Information dialog box is displayed. |
| 3. | Provide the following information:
|
| 4. | Click Next to continue. The Select Data Source step is displayed. |
| 5. | From the Connection Type drop-down list, select Oracle OLAP. Oracle OLAP connection options are displayed. |
| 6. | Provide the following information:
Click Next to continue to the Select Metadata Objects step. |
| 7. | In the 'Data source view' pane, expand localhost:1521:orcl, and then BISAMPLE. Select OLAPSAMPLE and click (Insert selected) to import metadata to the Repository View pane. Note: Your server connection string may differ from this example. OLAPSAMPLE is added to the Repository View as BISAMPLE_OLAP. |
| 8. | Click Finish to view the repository in BI Administration Tool. Imported metadata is displayed in the Physical layer. |
| 9. | In the Physical layer, expand BISAMPLE_OLAP, then BISAMPLE, and then OLAPSAMPLE to view the OLAP hierarchy views and cube views. |
| 10. | In this step, you replace the import connection string with a valid data source name to facilitate data queries from the BI server. Under BISAMPLE_OLAP, double-click Connection Pool. The Connection Pool dialog box is displayed. |
| 11. | In the 'Data source name' text box, enter a valid tnsnames.ora entry (for example, ORCL). Click OK to save your change. Note: The data source name must be identified in the tnsnames.ora file located in the client directory (in this example,C:oracleMiddlewareOracle_BI1networkadmin). |
Creating a Business Model and Subject Area
| 1. | Drag BISAMPLE from the Physical layer to the Business Model and Mapping layer. |
|---|---|
| 2. | Expand the BISAMPLE business model to view logical dimensions, logical tables, and logical table sources. |
| 3. | In this section, you modify the names in the business model to facilitate ease-of-use when reporting. In the BISAMPLE business model, expand the Product logical table. Right-click the Brand - Short Description logical column and select Rename. Rename the column Brand Name. |
| 4. | Expand the Time logical table and use the same process to rename the Fiscal Year logical column Year. Note: For the purposes of this tutorial, you rename only the logical columns you will use in the final report. In a real-world scenario, you would customize the business model fully. |
| 5. | Drag BISAMPLE from the Business Model and Mapping Layer to the Presentation layer. |
| 6. | Expand the BISAMPLE subject area to view presentation tables and presentation columns. |
| 7. | Select File, and then Save. At the prompt, click Yes to check global consistency. |
| 8. | At the message that BISAMPLE is consistent, click Yes to mark it available for queries. Consistency Check Manager is displayed. |
| 9. | Verify that no errors are displayed in Consistency Check Manager, then click Close. Note: You may receive a warning that the features of your database do not match the defaults. You can disregard this warning. |
| 10. | Select File, and then Close to close the repository. Leave BI Administration Tool open. |
Uploading the Repository to the BI Server
| 1. | Open the Enterprise Manager login screen (http://localhost:7001/em). Note: Your server name may vary from the example. Enter an administrator user name and password, then click Login. |
|---|---|
| 2. | Under farm_bifoundation_domain, expand Business Intelligence, then select coreapplication. |
| 3. | Select the Deployment tab, then select the Repository subtab. |
| 4. | Click Lock and Edit Configuration to enter edit mode. At the confirmation prompt, click Close. |
| 5. | Under Upload BI Server Repository, browse to select the OLAP.rpd repository. Enter and confirm the password Admin123. |
| 6. | Click Apply to update the default repository, then click Activate Changes to upload the repository to the BI Server. At the confirmation prompt, click Close. |
| 7. | You must now restart all the BI services to apply your changes. Select the Capacity Management tab and then the Availability subtab. |
| 8. | Click Restart All. At the confirmation prompt, click Yes. |
| 9. | At the confirmation prompt that services have started, click Close. |
| 10. | Log out of Enterprise Manager. |
Setting User Logging Levels
In this topic, you open the online repository in BI Administration Tool and set user logging levels so you can view the SQL query generated by the BI Server.
| 1. | In BI Administration Tool, select File, then Open, and then Online. The Open Online dialog box is displayed. |
|---|---|
| 2. | Enter the following information:
Click Open to continue. |
| 3. | Select Manage, and then Identity. Identity Manager is displayed. |
| 4. | In Release 11.1.1.5, Identity Manager filters users from the LDAP server. By default, no users are retreived. To view users, you must set the online user filter. Select Action, and then Set Online User Filter. |
| 5. | In the text box, enter a wildcard that will retrieve an administrator-level WebLogic user (for example, bi*), then clickOK. Users matching the wildcard are displayed in Identity Manager. |
| 6. | Double-click an administrator user (in this example, biadmin) to open the User dialog box. Set the query logging level to 3. Click OK. At the Check Out Objects prompt, click Check Out. |
| 7. | Select Action, and then Close to return to the repository. |
| 8. | Select File, and then Check In Changes to check in your changes. |
| 9. | Select File, and then Save to save the online repository. |
| 10. | Close the repository and exit BI Administration Tool. |
Querying Oracle OLAP in Oracle BI EE
This topic shows you how to build queries, view the results, and examine the corresponding log files.
| 1. | Log in to Oracle Business Intelligence with the administrator user name and password for which you enabled query logging in an earlier topic. |
|---|---|
| 2. | Select New, and then Analysis. The Select Subject Area box is displayed. |
| 3. | Click BISAMPLE. Analysis Editor is displayed |
| 4. | Create the following query: Time: Year, Product: Brand Name, Revenue Cube: Gross Margin. |
| 5. | Select the Results tab to display the analysis. Gross Margin for product brands is displayed for the years 2008, 2009, and 2010. Note: the Gross Margin values are automatically returned for the 'All' level in the other three dimension, as follows: Customer = 'All Customers'; Employees = 'All Employees'; Office = 'All Offices'. The correct level of aggregation for these dimensions is returned because of the level-aware OLAP cube metadata that is part of the repository. |
| 6. | Click the Save Analysis() button to save your analysis as OLAP. Click OK to save the analysis. |
| 7. | In the top right corner of the window, click Administration. Administration links are displayed. |
| 8. | Under Session Management, click Manage Sessions. |
| 9. | Under Cursor Cache, in the row for your query, click View Log. The query log is displayed. |
| 10. | Scroll down to view the SQL issued by the BI Server to the BISAMPLE_OLAP databse (entire query not shown). As you can see, the power and performance of the Oracle OLAP option is easily accessible to Oracle BI EE. Additionally, creating the appropriate metadata is quick and easy in Oracle BI Administration Tool. |
Summary
- Create a metadata repository for Oracle BI EE
- Import Oracle OLAP metadata into an Oracle BI EE repository
- Create business models and subject areas in an Oracle BI EE repository
- Upload Oracle BI EE repositories to the Oracle BI server
- Query Oracle OLAP data in Oracle BI EE