8 Modifying the Oracle Business Intelligence Repository for Customized Analytics

The Oracle BI repository contains the metadata modeled for and associated with Oracle Fusion Business Intelligence.

The repository contains the connectivity information between the Oracle BI Server and various data sources, details of physical data, business models and mappings, and presentation structures that are available for users to build their analyses.

In Oracle Fusion applications, the repository forms the foundation on which Oracle Fusion Business Intelligence is modeled. The repository contains Application Development Framework (ADF) View Objects as the prebuilt physical data sources which access the Oracle Fusion application transactional data. The repository also contains connections to the Oracle Fusion applications data warehouse (if the warehouse has been enabled). The pre-built Oracle Transactional Business Intelligence (OTBI) analyses that are rendered as part of the Oracle Fusion applications installation are designed against the model in the repository file.

The Oracle BI Server is an Oracle Business Intelligence component that processes user requests and queries underlying data sources. The Oracle BI Server maintains the logical data model and provides client access to this model through ODBC.

The Oracle BI Server uses the metadata in the repository to perform the following two tasks:

• Interpret Logical SQL queries and write corresponding physical queries against the appropriate data sources

• Transform and combine the physical result sets and perform final calculations

The Oracle BI Server connects to the underlying data sources through either ODBC or through native APIs, such as OCI for Oracle Database.

The Administration Tool client is a Windows application that you can use to edit your repository. The Administration Tool can connect directly to the repository in offline mode, or it can connect to the repository through the Oracle BI Server. Some options are only available in online mode. See "About Modifying the Oracle BI Repository In Online or Offline Mode" for more information.

The following figure shows how the Oracle BI Server interacts with query clients, data sources, the repository, and the Administration Tool.

Figure 8-1

Description of "Figure 8-1 "

The following example shows how the Oracle BI Server interprets and converts Logical SQL queries.

SELECT
"D0 Time"."T02 Per Name Month" saw_0,
"D4 Product"."P01 Product" saw_1,
"F2 Units"."2-01 Billed Qty (Sum All)" saw_2
FROM "Sample Sales"
ORDER BY saw_0, saw_1

WITH SAWITH0 AS (
select T986.Per_Name_Month as c1, T879.Prod_Dsc as c2,
   sum(T835.Units) as c3, T879.Prod_Key as c4
from
   Product T879 /* A05 Product */ ,
   Time_Mth T986 /* A08 Time Mth */ ,
   FactsRev T835 /* A11 Revenue (Billed Time Join) */
where ( T835.Prod_Key = T879.Prod_Key and T835.Bill_Mth = T986.Row_Wid)
group by T879.Prod_Dsc, T879.Prod_Key, T986.Per_Name_Month
)
select SAWITH0.c1 as c1, SAWITH0.c2 as c2, SAWITH0.c3 as c3
from SAWITH0
order by c1, c2

The repository defines data, its relationship to the data sources, and determines the calculations and analysis that users can create. The repository contains three layers: Physical Layer, Business Model and Mapping Layer, and Presentation Layer.

The required modifications to the repository are determined by the reporting needs of the content designers and business analysts. In some cases, to support reporting requirements, you will need to create new repository objects such as physical columns, logical table sources, logical columns, and presentation columns.

You can make repository modifications within any layer of the repository:

• Physical Layer –You import metadata from your data sources into the Physical layer of the repository. The Physical layer of the repository contains objects that represent physical data constructs from back-end data sources. The Physical layer defines the objects and relationships available to the Oracle BI Server for writing physical queries.

• Business Model and Mapping Layer – The Business Model and Mapping layer of the repository defines the business, or logical, model of the data and specifies the mapping between the business model and the Physical layer schemas. Business models are always dimensional, unlike objects in the Physical layer, which reflect the organization of the data sources. The Business Model and Mapping layer can contain one or more business models. Each business model contains logical tables, columns, and joins.

• Presentation layer – The Presentation layer provides a way to present customized, secure, role-based views of a business model to content designers. Presentation layer views are called subject areas. Subject areas are built to help you organize your content in a way that makes sense to the content designer. Subject areas contain presentation tables, columns, hierarchies, and levels.

  Note that Oracle Transactional Business Intelligence subject areas have "Real Time" appended to their names (for example, Costing - Inventory Valuation Real Time). If you are using Oracle Business Intelligence Applications (Oracle BI Applications) for historical reporting, then the corresponding subject areas do not have "Real Time" appending to their names (for example, Costing - Margin Analysis).

For more information, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

???Add content here .... Write last, after the rest of the chapter is finished???

???Include process flow here, or move to its own topic???

You can customize objects in the Oracle BI repository using the Administration Tool in either online or offline mode. Use online mode only for small changes that do not require running consistency checks. Use offline mode for complex changes that require consistency checks against a project extract of the repository.

The following table provides guidelines for when to perform online and offline edits.

Some Oracle Fusion applications product patches include updates to the Oracle BI repository. New objects are preserved during the patch process, and changes to existing objects are preserved when the patch does not include a new version of that object.

If you change an existing object and subsequent patches include a new version of the object, the Merge Wizard in the Administration Tool provides a method to merge the changes.

For most customizations, the merge process is straightforward. The exception is when presentation columns have been moved across presentation tables. In this situation, it is important to plan ahead and track the changes carefully to ensure your changes are preserved during the merge.

You can use JDeveloper to create ADF view objects containing data from custom tables, data warehouse tables, or other sources of data. You can import these ADF view objects into the physical layer of the repository and use them as data sources from which you can build logical columns.

Fusion Applications built using ADF obtain their data by querying the defined view objects using the ADF APIs. Using the ADF components as a data source quickly integrate Oracle BI EE with any application, such as a Fusion Application, that is built on top of the ADF framework.

For more information about installing JDeveloper, see About Installing Customization Tools

For more information about using JDeveloper to create and modify ADF view objects, see Using for Customizations .

You need to find and run the Oracle BI EE Plus Client Installer (biee_client_install_x64.exe) to install and configure the Administration Tool and the Oracle BI EE Catalog Manager on your computer. Use these tools to modify the Fusion Applications Oracle BI repository or objects in the Oracle BI EE Presentation catalog.

Note the following issues:

• You must run the Administration Tool on Windows. The Administration Tool should be installed on a 64 bit version of Windows. While it will run on 32 bit Windows, the user will likely experience memory issues opening and editing the large Oracle Fusion applications repository file.

• The version of the client tools that you install must match the version of Oracle BI EE included with your Oracle Fusion applications installation. Note that if the repository is opened in the incorrect version of Oracle BI EE, then the repository might be upgraded and rendered incompatible with the Oracle Fusion applications version.

  For example, if your instance of Oracle Fusion Applications includes Oracle BI EE 11.1.1.7, then you must be sure that the version of Presentation Services (the Oracle BI home page) from where you will download the Client Installer is 11.1.1.7. To check the Presentation Services version, click the Oracle BI EE home page's Help link, and then click About Oracle BI EE.

• An ODBC System DSN is required for you to connect from the Administration Tool to the Oracle Fusion applications repository in online mode.

Do one of the following tasks to obtain the Oracle BI EE Plus Client Installer file (biee_client_install_x64.exe):

• Access the Oracle BI EE Plus Client Installer file from the Oracle BI EE home page by selecting the Download BI Desktop Tools link and choosing Oracle BI Client Installer (64 bit).

  In most cases, you can display the Oracle BI EE home page from one of the following URLs:

  http://<host>:<port>/analytics/saw.dll?bieehome

  http://<host>/analytics/saw.dll?bieehome

  For example:

  http://computer1:9704/analytics/saw.dll?bieehome

  http://computer2/analytics/saw.dll?bieehome

• Access biee_client_install.exe in your FA_HOME directory and transfer a copy of it to the computer where you want to install the client tools. You can find biee_client_install.exe in the following location:

  FA_HOME/fusionapps/bi/clients/biserver

When you install the client tools with the Oracle BI EE Plus Client Installer, you must configure a system DSN so that the Administration Tool can connect to the Oracle Fusion applications' Oracle BI system in online mode.

For more information about installing the client tools, see Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence in the Oracle Fusion Middleware Online Documentation Library.

For more information about setting up an ODBC System DSN, see Determining the Fusion Applications BI Server Port for the Oracle BI Administration Tool ODBC System DSN

When you install the client tools with the Oracle BI EE Plus Client Installer, you must configure an ODBC System DSN for the Administration Tool to connect to the Fusion Applications Oracle BI system in online mode.

When you set up the System DSN either during or after the client installation, you must specify the correct port on the Oracle Fusion applications' Oracle BI Server. Typically the port is 10206, but sometimes the default port was changed during the Oracle Fusion applications installation.

To determine the Oracle Fusion Applications' Oracle BI Server port:

You need the administrative login and password to perform this procedure. If you do not have the correct permissions to log into Oracle Enterprise Manager Fusion Middleware Control, then ask your system administrator for the port number required to set up the System DSN.

1. Go to a web browser and enter the URL for the Oracle Enterprise Manager Fusion Middleware Controller In most cases, the URL will be like the following:

   http://<host>:<port>/em

2. Login to the Oracle BI Server's Enterprise Manager Fusion Middleware Control console.

3. In the left pane, expand the Farm_BIDomain node, and then expand the Business Intelligence folder, and select coreapplication.

4. In the right pane, select the Availability tab, and then select the Processes subtab.

5. In the Processes table, locate and expand BI Servers.

6. Locate coreapplication_obis1 and note the port number in the Port column. You will need to provide this port number in the Primary Controller's Port field in the Oracle BI Server DSN Configuration wizard.

For more instructions about configuring a system DSN for the Administration Tool to connect to your Oracle Business Intelligence system, see Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence in the Oracle Fusion Middleware Online Documentation Library.

For information about installing the Administration Tool for use with Oracle Fusion applications, see About the Oracle BI EE Plus Client Installer

Use the Catalog Manager to manage the Oracle Fusion applications' BI objects in the Oracle BI EE Presentation Catalog. Catalog management tasks include organizing and presenting the Oracle Fusion applications BI objects to the end user, managing folders and shortcuts, or assigning permissions to BI objects.

Other tasks that you can use the Catalog Manager to perform include: view and edit objects in XML, preview objects, perform mass changes to catalog objects such as search and replace text, and localize captions.

When Oracle Fusion applications users create BI objects, the catalog stores the BI content in a directory structure of individual files. This content includes folders, shortcuts, Oracle Fusion applications BI objects (such as analyses, filters, prompts, KPIs, and dashboards), and Oracle BI Publisher objects (such as reports and templates).

Before you can access the Catalog Manager, you must run the Oracle BI EE Plus Client Installer to install and configure the Catalog Manager.

For more information about installing the Catalog Manager, see About the Oracle BI EE Plus Client Installer Also see Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence in the Oracle Fusion Middleware Online Documentation Library.

For more information about using the Catalog Manager, see "Managing Catalog Folders" in Oracle Fusion applications Administering Reports and Analytics. Also see Oracle Fusion Middleware System Administration Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

The Oracle BI EE Presentation Catalog contains shared folders, system folder, and users folders. Access to these folders and the BI objects they contain depends on your role and permissions.

Note what each folder type contains:

• Shared folders – These folders contain the shared objects created by all catalog users. The pre-built dashboards, analyses, and objects shared among all the users are also stored here.

• User folders – These folders contains the individual analyses of the users along with their allied objects like filters and prompts.

• System folders – These folders are internal folders and are not to be modified. These folders contains the privileges configured by the system administrator and privileges that are included when Oracle BI EE was installed with Oracle Fusion applications.

Two components are stored for each saved analysis: the analysis object itself and an attribute file with an .atr extension.

The analysis object component is an XML file that provides the object's details such as query information. The attribute file provides the description and the access control for the analysis object.

Note that when the analysis object is edited, the system generates a temporary lock file. When the user is finished editing the analysis, the system deletes this lock file. However, in a rare event such as a system crash, the temporary lock files are not deleted and require the catalog administrator to manually delete these lock files.

You can access the Oracle BI EE Presentation Catalog in online or offline mode. The modifications you need to make determine which mode to use.

• Online mode – This mode connects to the catalog with the Oracle BI Server running. In this mode, permissions are verified when accessing objects. The user can only see those objects for which they have permission. Use this mode to make incremental changes, add items to the catalog, change permissions, update a single object, or migrate objects between environments.

• Offline mode – This mode connects to the local catalog and does not require the Oracle BI Server to be running. All the BI objects are visible to the user. This mode is used for mass changes to the catalog and moving multiple objects to reorganize the catalog's structure.

Use the Catalog Manager in either Windows or Linux to customize which Oracle Fusion applications BI objects are available to users and how the objects are organized and presented. You can perform tasks such as adding, deleting, collocating, archiving, and securing BI Objects.

To open the Catalog Manager:

On Windows, choose the Start menus, then Oracle Business Intelligence Enterprise Edition, and then Catalog Manager.

or

Using the command line, change to the following directory:ORACLE_INSTANCE\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obipsn\catalogmanagerthen run the appropriate script:runcat.cmd (on Windows)runcat.sh (on Linux)

For more information about installing the Catalog Manager, see About the Oracle BI EE Plus Client Installer Also see Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence in the Oracle Fusion Middleware Online Documentation Library.

For more information about using the Catalog Manager, see Oracle Fusion Middleware System Administration Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

Use the Administration Tool to perform tasks such as adding, removing, or modifying Oracle Fusion applications BI objects such as physical columns, logical table sources, logical columns, and presentation columns.

In general, the repository developer uses the Administration Tool to import source table metadata into the physical layer and then use this metadata to construct the logical and presentation layers. Other tasks the developer might perform with the Administration Tool are creating variables and adding security such as row-based security and subject area security.

Before you can access the Administration Tool, you must run the Oracle BI EE Plus Client Installer to install and configure the Administration Tool. When you perform the installation, be sure to configure a system DSN for the Administration Tool to connect to the Fusion Applications Oracle BI system.

For more information about installing the Administration Tool, see About the Oracle BI EE Plus Client Installer Also see Oracle Fusion Middleware Installation Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

For more information about using the Administration Tool to modify the Oracle Fusion applications' Oracle BI repository, see Modifying the Oracle Fusion Applications Oracle BI Repository Also see Oracle Fusion Middleware System Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

The Administration Tool is a Windows application that you use to modify the Oracle Fusion applications' Oracle BI repository.

To open the Administration Tool, choose Start > Programs > Oracle Business Intelligence Enterprise Edition > Administration.

You can also launch the Administration Tool from the command line, as follows:

1. In Windows Explorer, go to the location appropriate for your install type:

   • Client installations:

     ORACLE_HOME/bifoundation/server/bin
     

   • All other installations, such as an installation on the Windows server:

     ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup
     

2. Double-click bi-init.cmd (or bi-init.bat for client installations) to display a command prompt that is initialized to your Oracle instance.

3. At the command prompt, type admintool and press Enter.

The main window of the Administration Tool shows a graphical representation of the three layers of a repository (the Physical layer, Business Model and Mapping layer, and Presentation layer).

The Administration Tool main window also contains the following:

• Menus. Provides the menus which contain the many options you will use to modify the repository.

• Toolbar. Provides access to global functionality such as Open and Save, and also includes functions for the Physical Diagram and Business Model Diagram.

• Status bar. Provides contextual information about the current dialog or selected object, as well as other useful information.

• Title bar. In online mode, displays the DSN for the Oracle BI Server to which you are connected. In offline mode, displays one of the following:

  • repository files: The name of the open repository.

  • MDS XML files: The format and root folder location.

For more information about the three repository layers and what they contain, see About Layers in the Oracle BI Repository

For more information about understanding and using the Administration Tool and its many options, see Oracle Fusion Middleware System Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

Working on a large Oracle BI repository in online mode can be slow. It can also be risky because while you are modifying the repository, Oracle Fusion applications users are probably working with and on analyses. To avoid problems, Oracle recommends that you work on a local offline copy of the repository.

After you finish modifying and testing your changes in offline mode, you can either use the Oracle Enterprise Manager Fusion Middleware Controller to upload the repository into the production system, or you can use the Oracle BI Server XML API to programmatically merge your changes into the production system.

To open the Administration Tool and copy the repository:

1. From the Oracle BI Administration Tool client installation, click File and then click Open and then click Online. The Open dialog displays.

   The default Oracle BI Server DSN that you configured displays.

2. In the Open dialog, provide the log in information and click Open.

   Note that when you open a repository in the Administration Tool in online mode, the title bar displays the DSN for the Oracle BI Server to which you are connected, not the name of the current repository

3. From the Administration tool, click File and then Save As and then Repository. The Save As dialog displays.

4. Save the copy of the repository to the default location for the Administration Tool client installation, which is:

   ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication\repository

   Or browse to the location where you want to save the repository copy. Click Save.

To locate and copy the repository from the Oracle BI Server:

1. On the Oracle BI Server, navigate to the following directory:

   ORACLE_INSTANCE\bifoundation\OracleBIServerComponent\coreapplication\repository

2. Identify the repository. Note that the \repository directory contains both the current and previous versions of the repository.

   Tip:

   To find out which repository version is the most current, go to Oracle Enterprise Manager Fusion Middleware Control, navigate to the Farm_BIDomain node, and then to Business Intelligence and then to coreapplication. In the right pane, click the Deployment tab and then the Repository subtab. The Default repository field will show the current repository version (for example, OracleBIApps_BI0011).

3. Copy the repository and paste it to either a local or shared directory, depending upon from where you want to modify it.

For information about finding and opening the Administration Tool, see Opening the Administration Tool

For information about merging your changes into the production system, see Programmatically Moving Oracle BI repository Changes to Production Systems

For more information about Oracle Fusion applications customizations including information about Sandbox Manager, see Understanding the Customization Development Life Cycle.

Connection pools contain information that the Oracle BI Server uses to connect to the databases that contain the metadata for the repository. The connection pool connection details such as database login credentials, number of concurrent users, connection scripts, and writeback properties.

In most cases, when you add database objects to the Physical layer, connection pools are created automatically. However, in certain cases you need to add a new connection pool or modify an existing connection pool.

To add or modify a connection pool:

1. In the Administration Tool, open the repository to which you want to add or modify a connection pool.

2. In the Physical layer of the open repository, right-click a database and select New Object, then select Connection Pool. Or, double-click an existing connection pool.

3. Specify or adjust the properties as needed, then click OK.

For more information about understanding connection pools or adding or modifying connection pool settings, see Oracle Fusion Middleware System Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

Whenever you create a custom fact or dimension table, you must create a BI view object for that table and incorporate it into Oracle Fusion Applications before you can import it into the Oracle BI repository.

To create a BI view object for a custom fact or dimension table:

1. From a JDeveloper application workspace in the developer role, define the custom view object for the custom table. You must follow the view object guidelines for Oracle Transactional Business Intelligence.

   Tip:

   When you create the custom table, you must grant the necessary privileges (such as SELECT) to the FUSION_BI schema user in addition to the FUSION_RUNTIME schema user. Otherwise, queries against the new table will fail.

2. Create an application module (AM) and add the custom BI view object instance to the AM.

3. Create an ADF Library JAR for the custom artifacts.

4. From a customization workspace, import the ADF Library JAR for the custom artifacts into the Oracle Fusion application and restart the host server for the application so that the customizations are incorporated.

5. Continuing in the customization role, nest the BI application module in the root application module. For information about defining nested application modules, see the Oracle JDeveloper Help Center.

6. In the customization role, create a MAR file and load the MAR file using WLST commands or the Oracle WebLogic Server Administration Console. Restart the Oracle Fusion application's host server so that the customizations are incorporated.

The following resources can be found within this book:

• Importing the JAR for the custom artifact. For more information, see Customizing Artifacts with .

• Creating view objects from an application workspace. For more information, see About Creating Custom Business Components .

• Customize the schema. For more information, see About Customizing and Extending the Schemas.

• Create an ADF Library JAR for the custom artifacts and create a MAR file. For more information, see About Deploying Customizations and Extensions.

For more information about the view object guidelines for Oracle Transactional Business Intelligence, see "Designing and Securing View Objects for Oracle Business Intelligence Applications" in the Developer's Guide.

In some cases, you might want to modify existing fact or dimension tables in the Oracle BI repository. For example, assume you want to deploy Oracle Fusion Project Portfolio Management, but use the PeopleSoft Procurement application as a source. In this situation, you would set up a custom table in Oracle Fusion Applications that populates Commitments data from PeopleSoft. Then, you would need to change the Commitments fact table in the repository file to point to the new custom table.

To accomplish the task described in this example:

1. Create a custom BI view object for the custom table and incorporate it into the application as described in Creating BI View Objects for Custom Fact and Dimension Tables.

2. Open the Administration Tool and use the Import Metadata Wizard to import the new view object into the Physical layer of the repository under the appropriate database object. Then, join the new view object to the existing dimension view objects. You must connect as the FUSION_APPS_BI_APPID user in the Select Data Source screen of the Import Metadata Wizard.

3. If you imported a new view object for Oracle Fusion applications, then in the Physical layer of the repository, locate the imported object, and double click it to open the Physical Table dialog. In the object's general properties, confirm that the Cacheable property is not selected.

4. Create a new logical table source under the existing Commitment logical fact table, and map all metrics to the physical columns from the new view object. Then, deactivate the existing Commitments logical table source.

5. Save the repository and choose Yes when prompted to check global consistency.

Using this approach, all Presentation layer metadata, analyses, and dashboards continue to work with data received from the new physical columns.

In other cases, you might want to extend existing fact or dimension tables using existing view objects that have new attributes. For example, you might want to incorporate fields on standard Oracle Fusion applications pages that are not currently being used for analysis into the repository.

To extend existing fact or dimension tables for existing view objects that have new attributes:

1. Use the Import Metadata Wizard in the Administration Tool to import the view objects that correspond to the Oracle Fusion applications fields into the Physical layer of the repository. You must connect as the FUSION_APPS_BI_APPID user in the Select Data Source screen of the Import Metadata Wizard.

2. Drag and drop the new physical columns into the Business Model and Mapping layer.

3. Drag and drop the new logical columns into the Presentation layer.

   (Optional) If you add new logical tables and columns for Oracle Transactional Business Intelligence, then consider changing the new repository objects' icons to match the icon used for existing Oracle Transactional Business Intelligence objects. Matching icons provide an easy way to quickly identify Oracle Transactional Business Intelligence objects in the repository.

4. Save the repository and choose "Yes" when prompted to check global consistency.

For more information, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

In some cases, you might want to add new fact or dimension tables to your Oracle BI repository. Possible sources include custom tables in Oracle Fusion applications, additional tables in the data warehouse, or new physical data sources.

When you add new tables, you need to add joins. Instances where you add joins include:

• If you are working with common dimensions that already exist on other logical tables and need to exist in new logical tables.

• If you added a new dimension, you need to add a join to the logical tables where the dimension is consumed.

For example, suppose there is a date on a base transaction that is not modelled as a dimension out of the box. One new view link is required even when the canonical time dimension view object exists out of the box before a new role playing dimension is created and anchored to the logical fact table.

To add new fact or dimension tables to your repository:

1. For Oracle Fusion applications sources, create a custom BI view object for the custom table and incorporate it into the application as described in Creating BI View Objects for Custom Fact and Dimension Tables.

2. Open the Administration Tool and use the Import Metadata Wizard to import the new view object (for Oracle Fusion applications) or physical table (for warehouse or other physical sources) into the Physical layer of the repository. You must connect as the FUSION_APPS_BI_APPID user in the Select Data Source screen of the Import Metadata Wizard.

   For more information about using the Import Metadata Wizard, click the wizard's Help button.

3. Join the imported view object or physical table into a star schema.

4. If you imported a new view object for Oracle Fusion applications, then in the Physical layer of the repository, locate the imported object, and double click it to open the Physical Table dialog. In the object's general properties, confirm that the Cacheable property is not selected.

5. Define new logical dimensions and measures to extend the semantic model, and add physical and logical joins. The fact tables must be joined to the dimensions at the correct grain. If this structure is not joined properly, then Oracle BI EE Presentation Services returns an error message or return the incorrect data. Consider the following issues:

   • If you are also using Oracle BI applications for historical reporting, logical table sources for Oracle BI applications must appear first, and parent logical table sources must appear before child table sources.

   • If you add a new logical table source for Oracle Transactional Business Intelligence, then you must set its priority group number to a non-zero value.

   • (Optional) If you add new logical tables and columns for Oracle Transactional Business Intelligence, then consider changing the new repository objects' icons to match the icon used for existing Oracle Transactional Business Intelligence objects. Matching icons provide an easy way to quickly identify Oracle Transactional Business Intelligence objects in the repository.

6. Add corresponding Presentation layer metadata.

7. Save the repository and choose "Yes" when prompted to check global consistency.

For more information, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

In some cases, you might want to change how the names of facts and dimensions in the Presentation layer display in analyses and reports. For example, it might be a requirement that the column names that display in an analysis' pivot table use Oracle Fusion application terminology. Or in a case where you use multiple languages and want to ensure that an analysis or report displays in the language expected by the end user.

Note that Presentation layer object display name processes also apply to Presentation layer object descriptions.

For more information about modifying the repository's Presentation layer, open the Administration Tool, click the Help menu, and then click Help Topics.

Consider the following use cases:

• For warehouse sources, display names are typically externalized into a database table. In this use case, to externalize a name or a description means to source the names and descriptions from external sources to support multiple languages. To customize the names, you can change them in the externalized tables with no impact to the metadata itself.

  Note that for situations where display names are externalized into a database table, changing the names of Presentation layer objects in the Oracle BI repository has no impact on the names displayed in Answers reports.

• For Oracle Transactional Business Intelligence sources, display names are typically customized using UI hints (labels and tooltips) within Oracle Fusion applications. Changing the UI hint name does not impact metadata.

  Note that for situations where display names are customized using UI hints, changing the names of Presentation layer objects in the repository has no impact on the names displayed in Answers reports.

• For situations where Presentation layer names are not externalized or tied to UI hints, display names must be modified directly in the repository. Existing reports will continue to work because the old names are stored as aliases.

For more information, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

Use the Administration Tool to modify the Presentation layer to provide your users with subject areas specific to the analyses that they want to design.

Note the following about reorganizing Presentation layer metadata in the repository:

• Reordering presentation columns within a presentation table will not cause existing reports to break. When subsequent patches are applied, the new custom order is preserved when the patch does not include changes to the column order for that table.

• Moving presentation columns across different presentation tables can cause existing reports to break and is therefore not recommended. To prevent broken analyses and reports, Oracle recommends that you copy and paste an attribute from the source table to the designated table. If you do move presentation columns across tables, it is important to plan ahead and track the changes carefully.

For more information, see Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

Content here ....

Content here ....

Typically, data source connection pool settings are different in production repositories. You can use the Oracle BI Server XML API to programmatically update these settings in the Oracle BI repository when moving changes from test to production systems.

For example, suppose you want to move changes made in a test repository to the production repository. First, make a back up of your test repository, then open your test repository in the Administration Tool. Next, compare your test repository with the production repository. Then create an XML patch and use biserverxmlexec or biserverxmlcli to apply the patch to the test repository. Finally, replace the production repository with the test repository.

To programmatically update connection pool test values to production values

1. Open the test repository in the Administration Tool.

2. In the Physical layer, open the connection pool for the relational source that needs to be changed.

3. Update the values for Data Source Name, User Name, and Password to the production values. Then, click OK.

4. Open the connection pool for the XML source that needs to be changed.

5. Update the value for Data Source Name to the production value. Then, click OK.

6. Select File, then select Save As. Then, save the repository under a new name.

7. Select File, then select Compare.

8. Select the original repository file to compare to the currently open repository file.

   The Compare repositories dialog lists the two connection pools you modified

9. Click Create Patch, enter the name of the patch file you want to create, and click Save. The XML patch file is created.

10. After you create the patch, your test repository might continue to change, including name changes that could affect the parentNames or other attributes in your patch code. If this occurs, regenerate the patch using the steps in this section.

11. You can use biserverxmlexec to execute the generated XML patch file against the test version of the repository to programmatically update the connection pool settings. For example:

    biserverxmlexec -I MyApp_diff.xml -B MyApp_test.repository 
    -O MyApp_prod.repository
    Give password: my_repository_password
    

    To execute the XML patch file against a repository loaded into the Oracle BI Server, use biserverxmlcli.

For more information, see Oracle Fusion Middleware XML Schema Reference for Oracle Business Intelligence Enterprise Edition in the Oracle Fusion Middleware Online Documentation Library.

You enable parameters between Oracle Fusion Applications and Oracle Business Intelligence Enterprise Edition (Oracle BI EE) on a page so that an Oracle BI EE analysis or dashboard automatically receives the proper contextual parameter at runtime. You must have an Oracle BI EE report (analysis or dashboard) that has a prompted parameter configured in the Filter field.

1. Create a string, for example, myPromptKeyValueStr, in this format:

   'Key1=ValueA;ValueB','Key2=Value2','Key3=Value3'
   

   where

   • Key = prompt filter name (the same name that you defined when adding the prompt filter in analytics), and

   • Value = prompt filter value

   Also add the option to pass multiple values, separated by semi-colons. For example:

   '"Products"."Type"=ELECTRONICS','"Periods"."Year"=2014;2013;2012'
   

2. Put the myPromptKeyValueStr string in a pageFlowScope variable named biAdfPromptKeyValue :

   ADFContext adfCtx = ADFContext.getCurrent(); Map pageFlowScope = adfCtx.getPageFlowScope(); pageFlowScope.put(biAdfPromptKeyValue, myPromptKeyValueStr);
   

   When any report or dashboard is added at runtime, the values passed through biAdfPromptKeyValue in pageFlowScope are used to pre-populate the prompt-filter values when you add the report or dashboard for the first time.

   When the report is added through Oracle WebCenter at runtime, the parameter form will look like this:

   Figure 8-2 Parameter Form

   

   • Periods.Year's prompt filter value = #{pageFLowScope.biFilterName_PeriodYear}

   • Products.Type’s filter value = #{pageFlowScope.biFilterName_ProductType}

   This value will be an EL expression pointing to the pageFlowScope variable that is internally generated in the following format:

   biFIlterName+ Filter’s Table Name + Filter Column Name
   

   Subsequently, if you wish to change the prompt filter values after adding an analysis or dashboard at runtime, then any change in pageFlowScope.biAdfPromptKeyValue automatically refreshes the analysis or dashboard.

   For example:

   • Period.Year’s filter value = 2001 (User changes this from pageFlowScope value to Static value)

   • Products.Type’s filter value = #{pageFlowScope.biFilterName_PersonDepartment}

   When you save and run, you will see that pageFlowScope value is picked only for Product.Type, and static value is used for Periods.Year.