|Oracle® Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition
11g Release 1 (11.1.1)
Part Number E10540-02
This preface describes changes to metadata repository features for Oracle Business Intelligence Enterprise Edition 11g Release 1 (11.1.1). If you are upgrading to Oracle BI EE from a previous release, read the following information carefully, because there are significant differences in features, tools, and procedures.
This preface contains the following topics:
This section contains the following topics:
New metadata repository features in Oracle BI EE 11g Release 1 (220.127.116.11) include:
Access to Oracle OLAP Data Sources
Oracle BI EE now supports Oracle OLAP as a data source. See "Working with Oracle OLAP Data Sources" for more information.
Access to TimesTen Data Sources
Oracle BI EE now supports Oracle TimesTen In-Memory Database as a data source. See "Setting Up Oracle TimesTen In-Memory Database Data Sources" for more information.
Ability to Connect to SAP/BW Data Sources Using a Native Connection
You can now use the SAP BW Native connection option to connect to SAP/BW data sources over BAPI. See "Setting Up SAP/BW Data Sources" for more information.
New Diagramming Capability
The Physical and Business Model Diagrams have enhanced capabilities in this release, including an improved look-and-feel, the ability to display columns in table objects in the diagram, and new diagram options like auto-layout, marquee zoom, and zoom to fit. See "Using the Physical and Business Model Diagrams" for more information.
Validation Utility to Validate Metadata Objects in a Repository
Oracle BI EE now includes a validation utility to validate metadata objects. See "Using the validaterpd Utility to Check Repository Consistency" for more information.
Be aware of the following upgrade considerations when upgrading to Oracle BI EE 11g Release 1 (18.104.22.168):
Joins in the Physical and Business Model Diagrams are represented by a line with an arrow at the "one" end of the join, rather than the line with crow's feet at the "many" end of the join that was used in previous releases.
For example, the following image shows a join in the diagram as it was represented in previous releases:
This join is represented as follows in 11g Release 1 (22.214.171.124):
When creating joins in the Physical and Business Model Diagrams, you now select the "many" end of the join first, and then select the "one" end of the join. In previous releases, joins in the diagrams were created by selecting the "one" end of the join first.
This new gesture direction (from many to one) matches the direction of the new join arrow, described in the previous bullet.
The Consistency Check Manager now provides a warning when the same connection pool is being used for both queries and for initialization blocks. This configuration is not recommended. Instead, create a dedicated connection pool for initialization blocks. Otherwise, query performance might suffer, or user logins might hang if authorization initialization blocks cannot run. These warnings appear similar to the following:
 Initialization Block 'Authorization' uses Connection Pool '"My_DB". "My_CP"' which is used for report queries. This may impact query performance.
For more information about upgrading to Oracle BI EE 11g, see Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.
This section contains the following topics:
New metadata repository features in Oracle BI EE 11g Release 1 (126.96.36.199) include:
New SampleApp.rpd Demonstration Repository
A new demonstration repository called SampleApp.rpd is available for this release of Oracle BI EE. This repository provides best practice information about modeling many different types of objects.
A basic version of SampleApp.rpd, called SampleAppLite.rpd, is automatically installed as the default repository when you choose the Simple Install option. The full version of SampleApp.rpd, which contains many additional examples and features, is available on the Oracle Technology Network. See "About the SampleApp.rpd Demonstration Repository" for more information.
Hierarchy Objects in the Presentation Layer
You can now define presentation hierarchies and presentation levels in the Presentation layer. These objects provide an explicit way to expose the multidimensional model in Oracle BI Answers and enables users to create hierarchy-based queries. Presentation hierarchies expose analytic functionality such as member selection, custom member groups, and asymmetric queries. See "Working with Presentation Hierarchies and Levels" for more information.
Support for Unbalanced (Ragged) and Skip-Level Hierarchies
Oracle BI EE now supports unbalanced and skip-level hierarchies. An unbalanced (or ragged) hierarchy is a hierarchy where the leaves (members with no children) do not necessarily have the same depth. A skip-level hierarchy is a hierarchy where there are members that do not have a value for a particular ancestor level. See "About Level-Based Hierarchies" for more information.
Support for Parent-Child Hierarchies
Oracle BI EE now supports parent-child hierarchies. Parent-child hierarchies (also called value hierarchies) contain members that all have the same type. For example, an organizational chart has a distinct parent-child hierarchy, but all members are employees. See "Creating and Managing Dimensions with Parent-Child Hierarchies" for more information.
Creating Subject Areas for Logical Stars and Snowflakes
You can automatically create one subject area (formerly called presentation catalog) for each logical star or logical snowflake in your business model. See "Automatically Creating Subject Areas Based on Logical Stars and Snowflakes" for more information.
You can configure logical columns so that Oracle BI EE users can select the currency in which they prefer to view currency columns in analyses and dashboards. See "Configuring Logical Columns for Multicurrency Support" for more information.
Generating and Applying XML Patch Files
You can now generate an XML patch file that contains only the changes made to a repository. This patch can be then applied to the old (original) version of the repository to create the new version. This is very useful for development-to-production scenarios, and can also be used for Oracle BI Applications customers to upgrade their repository. See "Performing Patch Merges" for more information.
You can also use the Oracle BI Server XML utilities to create a generic, XML-based representation of the Oracle BI repository metadata, on any supported Oracle BI Server operating system. See "About the Oracle BI Server XML API" in Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
Enhancements for Multiuser Development and Repository Lifecycle Management
The following list summarizes improvements and new features for multiuser development and repository lifecycle management:
MUD projects now explicitly include subject areas (formerly called presentation catalogs). Object security permissions no longer affect which presentation objects are included in a checked-out repository.
You can select options in the Merge Repository Wizard or set options in the MUD options file to automatically check consistency and equalize during merges.
Improved Merge Repository Wizard integration enables a single step input for repositories. You no longer have to select repositories one by one and wait for them to load.
You can now extract objects on all platforms using the command-line utility extractprojects.
The two-way merge feature (full merge without a common parent) enables easier consolidation of separate repositories.
Metadata Repository File Encryption
Repositories now have a repository password that encrypts the repository contents using a strong encryption algorithm. This feature ensures that repository metadata is secure, including data source credentials in connection pool objects. See "Changing the Repository Password" for more information.
Metadata Repository File Compression
To reduce storage needs, repositories are now stored in a compressed format. Because of this, you may notice that the size of an RPD file opened and saved in this release is significantly smaller than the size of RPD files from previous releases. See "About the Multiuser Development Environment" for more information about using and storing multiple repositories.
Embedded Database Functions
Users and administrators can create requests by directly calling database functions from either Oracle BI Answers, or by using a Logical column (in the Logical Table source) within the metadata repository. These functions provide the ability to access custom written functions or procedures on the underlying database. Supported functions include
EVALUATE_PREDICATE. See "Database Functions" for more information.
In addition, Evaluate functions can be leveraged significantly for Essbase users. See "Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions" for more information.
SQL Functions for Time Series Calculations and Level-Based Measures
The following new and revised SQL functions enable users to create time series calculations and level-based measures:
PERIODROLLING is a new function that computes the aggregate of a measure over the period starting x units of time and ending y units of time from the current time.
AGGREGATE AT is a new function that aggregates columns based on the level or levels specified.
AGO is a time series aggregation function that calculates the aggregated value from the current time back to a specified time period. In this release, the
time_level argument is optional, and there are additional syntax changes. This function was called
PERIODAGO in a previous release.
TODATE is a time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the currently displayed time. The syntax for this function has changed in this release. This function was called
PERIODTODATE in a previous release.
Support for SELECT_PHYSICAL
SELECT_PHYSICAL statements provide the functionality to directly query objects in the Physical layer of the metadata repository, and to nest such a statement within a query against the Business Model and Mapping layer or the Presentation layer. See "Syntax and Usage Notes for SELECT_PHYSICAL" for more information.
Support for Calculated Members
Calculated members are user-defined dimension members whose measure values are calculated at run time. You can define a calculated member within a dimension through a formula that references other members of the same dimension. See "Calculated Members" for more information.
Access to Essbase Data Sources
Oracle BI EE now supports Essbase as a data source. See "Working with Essbase Data Sources" for more information.
Access to Hyperion Financial Management Data Sources
Oracle BI EE now supports Hyperion Financial Management as a data source. See "Working with Hyperion Financial Management Data Sources" for more information.
Access to ADF Business Component Data Sources
Oracle BI EE now supports using ADF Business Components as data sources. With this feature, users can integrate operational reporting with any application that is built on top of the ADF Framework. See "Working with ADF Business Component Data Sources" for more information.
Access to Oracle RPAS Data Store Through ODBC
The Oracle BI Server can now access Oracle Retail Predictive Application Server (RPAS) data stores using the RPAS ODBC driver. See "Setting Up Oracle RPAS Data Sources" for more information.
Ability to Connect to Oracle Database Through OCI
Administrators can now use the native Oracle Call Interface (OCI) when importing physical objects from an Oracle Database. Because using OCI is faster than using an ODBC connection, administrators should always use OCI when importing from or setting up connections to an Oracle Database. See "Setting Up Oracle Database Data Sources" for more information.
Support for Dragging and Dropping XMLA Objects
You can now drag and drop XMLA objects (whole XMLA database or cube tables, at the same time or one by one) into the logical layer to automatically create a consistent business model. See "Creating the Business Model and Mapping Layer" and "Automatically Creating Business Model Objects for Multidimensional Data Sources" for more information.
Support for Using a Standby Database with Oracle Business Intelligence
You can now use a standby database with Oracle BI EE. A standby database is used mainly for its high availability and failover functions as a backup for the primary database. See "Using a Standby Database with Oracle Business Intelligence" for more information.
For greater performance, Oracle BI EE now creates indexes and statistics on relational tables when aggregates are persisted. See Chapter 12 for more information about aggregate persistence.
Pruning Utility to Delete Unwanted Repository Objects
Oracle BI EE now includes a pruning utility to remove unwanted objects from your repository. This utility is especially useful for Oracle BI Applications customers who only need to use a subset of the objects included in the shipped Oracle BI Applications repository. See Appendix E, "Deleting Unwanted Objects from the Repository" for more information.
Ability to Defer Execution of Session Variable Initialization Blocks
To improve session logon time and save resources, administrators can defer execution of selected session variable initialization blocks until the associated session variables are actually used within the session. See "Creating Initialization Blocks" for more information.
Improved Usability in the Administration Tool
Many Administration Tool screens, wizards, and other parts of the user interface were updated and streamlined for this release of Oracle BI EE. Updated wizards include the Create New Repository Wizard, Aggregate Persistence Wizard, and Merge Wizard. Expression Builder and the Consistency Checker also contain usability enhancements in this release.
Additional changes include updated and improved icons, a simpler process flow to create joins, improvements to the Query Repository feature, and a simplified way to add some repository objects. Also, shortcuts were added to enable keyboard access to menu options, dialog navigation, and navigation in the Physical and Business Model Diagrams.
Be aware of the following upgrade considerations when upgrading to Oracle BI EE 11g Release 1 (188.8.131.52):
You must upgrade your repository files from previous releases before they will work in this release of Oracle BI EE. See Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence for full information about upgrading a repository from a previous release.
Many configuration settings that affect repository development, including the default published repository, are now centrally managed in Fusion Middleware Control. You can no longer manually change these configuration settings in NQSConfig.INI. See Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.
Be aware of the following security-related changes:
Repositories now have repository-specific passwords that are used to encrypt the repository contents. The repository password is stored in an external credential store when you publish a repository in Fusion Middleware Control, so that the Oracle BI Server can retrieve the password to load the repository. See "Changing the Repository Password" for more information.
Note that a blank repository password is not allowed.
Groups no longer exist in the repository as objects. Instead, you implement data access security based on the application roles to which a user belongs.
Application roles are managed in an external policy store. Application role objects exist in the repository, but these objects are pointers (references) to the externally managed roles.
Users are managed in an external authentication provider and are no longer managed in the repository. User objects exist in the repository, but these objects are pointers (references) to the externally managed users.
See Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition for information about these and other security changes. See also Chapter 13, "Applying Data Access Security to Repository Objects" for additional information.
The connection parameters in the default ODBC DSN for the Oracle BI Server are now centrally managed by Fusion Middleware Control and cannot be manually changed.
In addition, Oracle BI EE is now deployed in a clustered configuration by default. Because of this, the default ODBC DSN for the Oracle BI Server points to the Cluster Controller by default, rather than to the Oracle BI Server.
See "Integrating Other Clients with Oracle Business Intelligence" in Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about ODBC DSNs for the Oracle BI Server.
This release of Oracle BI EE has additional dependencies for a running system, including:
The relational database specified upon installation must be running. This database must contain required Oracle BI EE schemas loaded using the Repository Creation Utility (RCU).
Oracle WebLogic Server must be running.
You can no longer restart the Oracle BI Server using the Administration Tool in online mode. Instead, you can restart the Oracle BI Server and other system processes using Fusion Middleware Control and the Oracle WebLogic Server Administration Console.
You can also use the BI Systems Management API to programmatically start and stop Oracle BI EE. This feature is especially helpful for automating rolling restart of Oracle BI Servers in a cluster, to enable repository upgrade with zero end-user downtime.
For more information, see "Starting and Stopping Oracle Business Intelligence" and "Starting and Stopping Oracle Business Intelligence Using the Oracle BI Systems Management API" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.
Before you can run any of the Oracle BI Server command-line utilities, you must now run bi-init.cmd (or bi-init.sh on UNIX) to launch a command prompt or shell window that is initialized to your Oracle instance. See "Running bi-init to Launch a Shell Window Initialized to Your Oracle Instance" for more information.
If you want to use a net service name in a connection pool for an Oracle Database data source, you must set up a tnsnames.ora file in the following location within the Oracle BI EE environment, so that the Oracle BI Server can locate the entry:
You can no longer open the Administration Tool by double-clicking a repository file. The resulting Administration Tool window is not initialized to your Oracle instance, and errors will result later in your session. Instead, always use the Start menu to open the Administration Tool, or launch the Administration Tool from the command line using bi-init.cmd. See "Opening the Administration Tool" for more information.
Presentation catalogs in the Presentation layer are now called subject areas.
Static repository variables must now have default initializers that are constant values. See "About Repository Variables" for more information.
If you configured write-back capability in a previous release of Oracle Business Intelligence, you must now explicitly select the Writeable option for each logical column for which you want to enable write-back. See "Enabling Write Back On Columns" for more information.
Bridge tables are now identified using repository modeling techniques. They are no longer identified using the Bridge table option in the Logical Table dialog that existed in previous releases. Check your repository to ensure that your bridge tables are modeled appropriately. See "Modeling Bridge Tables" for more information.
You might notice that some queries that used to return one result now return a different result. This behavior occurs because the determination of which logical table source to use for a query is now very ordered, whereas in previous releases, the determination was random. Examine and adjust the modeling to correct the behavior.
In the previous release, if two logical table sources for the same logical table map to the same physical table, and both logical table sources are used in a query, and both logical table sources have a WHERE clause filter, then the filter from only one of the logical table sources was applied. The other WHERE clause filter was ignored.
In the current release, in this situation, the WHERE clause filters from both logical table sources are applied to the query. Typically, this behavior produces the desired results. If you encounter errors related to this issue, you can correct them by using physical table aliases to ensure that the same physical table is not mapped to the same logical table at different levels.
The Consistency Check Manager now enforces additional validation rules to help ensure that your repository is consistent. In addition, some rules that existed in previous releases might now be displayed during consistency checks. The following table summarizes these rules:
|Validation Rule Example||Type||Description|
| The content filter of a source for logical table: FACT_TABLE_NAME references multiple dimensions.||Error||The given logical table has a logical table source with a WHERE clause filter that references multiple dimensions. A WHERE clause with multiple dimensions is invalid.|
| 'Logical Table' '"Technology - WFA"."Fact WFA WO "' has name with leading or trailing space(s).||Error||Identifies an object with leading or trailing spaces in the object name.
Repository objects can no longer have leading or trailing spaces in their names. Leading and trailing spaces in object names can cause query and reporting issues.
| Logical column DIM_Start_Date.YEAR_QUARTER_NBR does not have a physical data type mapping, nor is it a derived column.
 Logical column DIM_Start_Date.YEAR_QUARTER_NBR has no physical data source mapping.
|Error||Logical columns that are mapped to logical table sources that are disabled are reported as consistency errors, because the logical table source mappings are invalid and would cause queries to fail.
Both of the given validation rules are related to the same issue.
| The features in Database 'MyDB' do not match the defaults. This can cause query problems.||Warning||Some database feature defaults were changed in this release of Oracle BI EE. Unless you have specific customizations to your feature set, it is recommended that you reset your database features to the new defaults.|
| Missing functional dependency association for column: DIM_Offer_End_Date.CREATE_DT.||Warning||This warning indicates that the given column is not associated to any level. When this situation occurs, the column will be associated by default to the lowest level in the parent dimension. The warning brings this issue to the repository developer's attention in case the default behavior is not desired.|
| Logical fact table MY_FACT has an aggregate source MY_FACT.YRLY_SUM that does not join to a Dimension: Hier - Time source at the proper level of detail.
 Fact table "HR"."FACT - HC Budget" is not joined to tables in logical dimension "HR"."DIM - HR EmployeeDim". This will cause problems when extracting project(s).
 Logical dimension table MY_DIM has a source MY_DIM_DAILY at level Daily that joins to a higher level fact source MY_FACT_SUM.MTHLY_SUM
|Warning||Even though this fact logical table source has an aggregate grain set in this dimension, no valid physical join was found that connects to any logical table source in this dimension.
This means that either no join exists at all, or it does exist but is invalid because it connects a higher-level fact source to a lower-level dimensional source. Such joins are invalid and ignored by the Oracle BI Server because, if followed, they would lead to double counting in query answers.
For example, consider Select year, yearlySales. Even if a join exists between monthTable and yearlySales table on yearId, it cannot be used because such a join would overstate the results by a factor of 12 (the number of months in each year).
All three of the given validation rules are related to the same issue.
| Fact table "Sales - STAR"."Fact - STAR Statistics" is not joined to logical dimension table "Sales - STAR"."Dim - Plan". This will cause problems when extracting project(s).||Warning||This warning indicates that the aggregation content filter "Group by Level" in the logical table source of a fact table references logical dimension tables that are not joined to that fact table. If that fact table is extracted in the extract/MUD process, the dimensions that are not joined will not be extracted. In this case, the aggregation content of the extracted logical table source would not be the same as in the original logical table source.|
| There are physical tables mapped in Logical Table Source ""HR"."Dim - Schedule"."SCH_DEFN"" that are not used in any column mappings or expressions.||Warning||This warning indicates that the given logical table source has irrelevant tables added that are not used in any mapping. This situation will not cause any errors.|
Invalid objects are now deleted during Consistency Checks. This behavior might result in deleted expressions and filters on logical table sources and logical columns. Invalid references can occur when objects were deleted in the Physical layer without properly accounting for the references in the Business Model and Mapping layer objects.
See "About Converting Older Projects During Repository Upgrade" for information about how projects are upgraded when you upgrade a repository from Oracle BI EE versions before 10.1.3.2.
The environment variable OBIS_Essbase_CustomGroup_Generation, used in previous releases to customize the use of custom group syntax with Essbase, has been replaced by a new database feature called PERF_CUSTOM_GROUP_GENERATION_MODE. This database feature impacts how custom group syntax is generated on Essbase and other multidimensional sources. The set of valid values is the same as for the environment variable (0-2).
The environment variable OBIS_Essbase_NonEmptyTuples_Generation.Database.Catalog.CubeTable, used in previous releases to resolve issues with large query sets, has been replaced by a new database feature called PERF_PREFER_SUPPRESS_EMPTY_TUPLES. This database feature controls whether empty tuples with empty cell values are eliminated. Note that this database feature does not change the null suppression behavior on the final result set.
For more information about upgrading to Oracle BI EE 11g, see Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.