1 Introduction to Building Your Metadata Repository

This chapter explains how to plan and design your Oracle Business Intelligence metadata repository, including how to plan your business model, how to work with the physical content for your business model, and general repository design guidelines.

To effectively plan and build your metadata repository, you need to have experience with SQL queries and be familiar with reporting and analysis. You should also have experience with industry-standard data warehouse modeling practices, and be familiar with general relational entity-relationship modeling.

This chapter contains the following topics:

About Oracle BI Server and Oracle BI Repository Architecture

The architecture of the Oracle BI Server and the Oracle BI repository provides a layer of abstraction that lets users send simple Logical SQL queries against complex federated data sources.

This section contains the following topics:

About Oracle BI Server Architecture

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 Oracle BI 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 create and edit your Oracle BI 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 "Using Online and Offline Repository Modes" for more information.

Figure 1-1 shows how the Oracle BI Server interacts with query clients, data sources, the Oracle BI repository, and the Administration Tool.

Figure 1-1 Oracle BI Server Architecture

Description of Figure 1-1 follows
Description of "Figure 1-1 Oracle BI Server Architecture"

Example 1-1 shows how the Oracle BI Server interprets and converts Logical SQL queries.

Example 1-1 Logical Requests Are Transformed Into Complex Physical Queries

Assume the Oracle BI Server receives the following simple client request:

"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

The Oracle BI Server can then convert the Logical SQL query into a sophisticated physical query, as follows:

select T986.Per_Name_Month as c1, T879.Prod_Dsc as c2,
   sum(T835.Units) as c3, T879.Prod_Key as c4
   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

About Layers in the Oracle BI Repository

An Oracle BI repository has the following layers:

  • Physical layer. This layer defines the objects and relationships that the Oracle BI Server needs to write native queries against each physical data source. You create this layer by importing tables, cubes, and flat files from your data sources.

    Separating the logical behavior of the application from the physical model provides the ability to federate multiple physical sources to the same logical object, enabling aggregate navigation and partitioning, as well as dimension conformance and isolation from changes in the physical sources. This separation also enables the creation of portable BI Applications.

  • Business Model and Mapping layer. This layer defines the business or logical model of the data and specifies the mapping between the business model and the physical schemas. This layer determines the analytic behavior seen by users, and defines the superset of objects and relationships available to users. It also hides the complexity of the source data models.

    Each column in the business model maps to one or more columns in the Physical layer. At run time, the Oracle BI Server evaluates Logical SQL requests against the business model, and then uses the mappings to determine the best set of physical tables, files, and cubes for generating the necessary physical queries. The mappings often contain calculations and transformations, and might combine multiple physical tables.

  • Presentation layer. This layer provides a way to present customized, secure, role-based views of a business model to users. It adds a level of abstraction over the Business Model and Mapping layer and provides the view of the data seen by users building requests in Presentation Services and other clients.

    You can create multiple subject areas in the Presentation layer that map to a single business model, effectively breaking up the business model into manageable pieces.

Before you build any repository layers in the Administration Tool, it is important to create a high-level design of the Business Model and Mapping layer based on the analytic requirements of your users. After you have a conceptual design to work toward, you can then build your metadata objects.

The typical order is to create the Physical layer objects first, the Business Model and Mapping layer objects next, and the Presentation layer objects last. However, you can work on each layer at any stage. After you complete all three layers, you can set up security when you are ready to begin testing the repository.

Figure 1-2 shows how a Logical SQL query traverses the layers of an Oracle BI repository.

Figure 1-2 Logical SQL Query Traversing the Layers in an Oracle BI Repository

Description of Figure 1-2 follows
Description of "Figure 1-2 Logical SQL Query Traversing the Layers in an Oracle BI Repository"

Note that a single Oracle BI repository can contain two or more independent semantic models, rather than a single, integrated, enterprise-wide model. A semantic model consists of one business model, its related objects in the Presentation and Physical layers, and additional related objects like variables, initialization blocks, and application roles. A semantic model is also known as a Common Enterprise Information Model.

See also Figure A-4 for a visual representation of multiple semantic models.

Planning Your Business Model

Planning your business model is the first step in developing a usable data model for decision support. After you have followed the planning guidelines in this section, you can begin to create your repository.

Analyzing Your Business Model Requirements

Your first task is to thoroughly understand your business model requirements. You must first understand what business model you want to build before you can determine what the Physical layer needs to have in it.

In a decision support environment, the objective of data modeling is to design a model that presents business information in a manner that parallels business analysts' understanding of the business structure. A successful model allows the query process to become a natural process by enabling analysts to structure queries in the same intuitive fashion as they would ask business questions. This model must be one that business analysts inherently understand and that answers meaningful questions correctly.

Unlike visual SQL tools such as Oracle BI Publisher, the business model defines the analytic behavior of your BI application. In contrast, the Physical layer only provides the components used to assemble a physical query mapped from business model logic.

This requires breaking down your business into several components to answer the following questions:

  • What kinds of business questions are analysts trying to answer?

  • What are the measures required to understand business performance?

  • What are all the dimensions under which the business operates? Or, in other words, what are the dimensions used to break down the measurements and provide headers for the reports?

  • Are there hierarchical elements in each dimension, and what types of relationships define each hierarchy?

After you have answered these questions, you can identify and define the elements of your business model.

Identifying the Content of the Business Model

To determine what content to include in your business model, you must first identify the logical columns on which users need to query. Then, you must identify each column's role as either a measure column or a dimensional attribute. Finally, you need to arrange the logical columns in a dimensional model based on the relevant roles, relationships between columns, and logic.

Businesses are analyzed by relevant dimensional criteria, and the business model is developed from these relevant dimensions. These dimensional models form the basis of the valid business models to use with the Oracle BI Server.

Although not all dimensional models are built around a star schema, it is a best practice to use a simple star schema in the business model layer. In other words, the dimensional model should represent some measurable facts that are viewed in terms of various dimensional attributes.

After you analyze your business model requirements, you need to identify the specific logical tables and hierarchies that you need to include in your business model.

This section contains the following topics:

Identifying Logical Fact Tables

Logical fact tables in the Business Model and Mapping layer contain measures that have aggregations built into their definitions. Logical fact tables are different from physical fact tables in relational models, which instead define facts at the lowest grain of the table.

Measures aggregated from facts must be defined in a logical fact table. Measures are typically calculated data such as dollar value or quantity sold, and they can be specified in terms of dimensions. For example, you might want to determine the sum of dollars for a given product in a given market over a given time period.

Each measure has its own aggregation rule such as SUM, AVG, MIN, or MAX. A business might want to compare values of a measure and need a calculation to express the comparison. Also, aggregation rules can be specific to particular dimensions. The Oracle BI Server lets you define complex, dimension-specific aggregation rules.

You do not explicitly label tables in the Business Model and Mapping layer as fact tables or dimension tables. Rather, the Oracle BI Server treats tables at the "one" end of a join as dimension tables, and tables at the "many" end of a join as fact tables.

Figure 1-3 illustrates the many-to-one joins to a fact table in a Business Model Diagram. In the diagram, all joins have an arrow (indicating the one side) pointing away from the Fact-Pipeline table; no joins are pointing to it. For an example of this in a business model, open a repository in the Administration Tool, right-click a fact table, and select Business Model Diagram > Whole Diagram.

Figure 1-3 Diagram of Fact Table Joins

Description of Figure 1-3 follows
Description of "Figure 1-3 Diagram of Fact Table Joins"

Identifying Logical Dimension Tables

A business uses facts to measure performance by well-established dimensions, for example, by time, product, and market. Every dimension has a set of descriptive attributes. Dimension tables contain attributes that describe business entities (such as Customer Name, Region, Address, Country and so on). Dimension tables also contain primary keys that identify each member. Unlike logical fact tables, which are different from physical fact tables in relational models, logical dimension tables behave very much like relational dimension tables.

Dimension table attributes provide context to numeric data, such as being able to categorize Service Requests. Attributes stored in this dimension might include Service Request Owner, Area, Account, Priority, and so on.

Dimensions in the business model are conformed dimensions. In other words, even if a particular data source has five different instances of a particular Customer table, the business model should only have one table. To achieve this, all five physical source instances of Customer are mapped to a single Customer logical table, with transformations in the logical table source as necessary. Conformed dimensions hide the complexity of the Physical layer from users and enable you to combine data from multiple fact sources at different grains. They also enable you to federate multiple data sources.

Also note that dimension and level keys in the business model should be business keys rather than generated surrogate keys. In other words, use "Customer Name" with values like "Oracle" instead of "Customer Key" with values like "1076823." Using business keys in the business model ensures that all sources for that dimension can be conformed to the same logical dimension table with the same logical key and level key.

Although generated surrogate keys can still exist in the Physical layer, where they are useful for their query performance advantages on joins, you typically do not have surrogate key columns in the Business Model and Mapping layer at all.

Identifying Dimensions

Dimensions are categories of attributes by which the business is defined. Common dimensions are time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Within a given dimension, there may be many attributes. For example, the time period dimension can contain the attributes day, week, month, quarter, and year. Exactly what attributes a dimension contains depends on the way the business is analyzed.

Dimensions typically contain hierarchies, which are sets of top-down relationships between members within a dimension. There are two types of hierarchies: level-based hierarchies (structure hierarchies), and parent-child hierarchies (value hierarchies). Level-based hierarchies are those in which members of the same type occur only at a single level, while members in parent-child hierarchies all have the same type. Oracle Business Intelligence also supports a special type of level-based dimension, called a time dimension, that provides special functionality for modeling time series data.

In level-based hierarchies, levels roll up from lower level to higher level; for example, months can roll up into a year. These rollups occur over the hierarchy elements and span natural business relationships.

In parent-child hierarchies, the business relationships occur between different members of the same real-world type, such as the manager-employee relationship in an organizational hierarchy tree. Parent-child hierarchies do not have explicitly named levels. There is no limit to the number of implicit levels in a parent-child hierarchy.

To define your hierarchies, you define the "contains" relationships in your business (geographical, product, time, and so on) to drive rollup aggregations in all calculations, as well as drill-down navigation in reports and dashboards. For example, if month rolls up into year and an aggregate table exists at the month level, that table can be used to answer questions at the year level by adding up all of the month-level data for a year.

It is important to use the right type of hierarchy for your needs. To determine which type to use, consider the following:

  • Are all the members of the same type (such as employee, assembly, or account), or are they of different types that naturally fall into levels (such as year-quarter-month, continent-country-state/province, or brand-line-product)?

  • Do members have the same set of attributes? For example, in a parent-child hierarchy like Employees, all members might have a Hire Date attribute. In a level-based hierarchy like Time, however, the Day type might have a Holiday attribute, while Month does not.

  • Are the levels fixed at design time (year-quarter-month), or can run-time business transactions add or subtract levels? For example, a level could be added when the current lowest-level employee hires a subordinate, who then becomes the new lowest level.

  • Are there constraints in your primary data source that require a certain hierarchy type? If your primary data source is modeled in one way or the other, you might need to use the same hierarchy type in your business model, regardless of other factors.

See Chapter 10, "Working with Logical Dimensions" for more information.

About Dimensions with Multiple Hierarchies

Sometimes, dimensions can contain multiple hierarchies. For example, time dimensions often have one hierarchy for the calendar year, and another hierarchy for the fiscal year. Note that dimensions with multiple hierarchies must always end with the same leaf table.

Figure 1-4 shows a dimension with multiple hierarchies in the Business Model and Mapping layer of the Administration Tool.

Figure 1-4 Dimension with Multiple Hierarchies

Description of Figure 1-4 follows
Description of "Figure 1-4 Dimension with Multiple Hierarchies"

Identifying Lookup Tables

When you need to display translated field information from multilingual schemas, you create a logical lookup table that corresponds to a lookup table in the Physical layer. A lookup table stores multilingual data corresponding to rows in the base tables. Before you can use a particular logical lookup table, you must designate it as a lookup table in the General tab of the Logical Table dialog. See "Localizing Oracle Business Intelligence" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about localization and lookup tables.

In addition to localization, lookup tables can be used any time you need to display one set of values to users, while using a different, corresponding set of values in the physical query. If necessary, the human-readable value can be looked up in a completely different data source.

Identifying the Data Source Content for the Physical Layer

After you have determined the requirements for your business model, you can look at what data source content you need in the Physical layer. Unlike the Business Model and Mapping layer, which is always dimensional, each physical model mirrors the shape of the source (for example, normalized, cube, and so on).

This section contains the following topics:

About Types of Physical Schemas in Relational Data Sources

You can successfully model any physical schema in the Oracle BI repository, regardless of its type, because the model of any physical source can be broken down into overlapping subsets that are dimensional.

There are four types of physical schemas (models):

  • Star Schemas. A star schema is a set of dimensional schemas (stars) that each have a single fact table with foreign key join relationships to several dimension tables. When you map a star to the business model, you first map the physical fact columns to one or more logical fact tables. Then, for each physical dimension table that joins to the physical fact table for that star, you map the physical dimension columns to the appropriate conformed logical dimension tables.

  • Snowflake Schemas. A snowflake schema is similar to a star schema, except that each dimension is made up of multiple tables joined together. Like star schemas, you first map the physical fact columns to one or more logical tables. Then, for each dimension, you map the snowflaked physical dimension tables to a single logical table. You can achieve this by either having multiple logical table sources, or by using a single logical table source with joins.

  • Normalized Schemas. Normalized schemas distribute data entities into multiple tables to minimize data storage redundancy and optimize data updates. Before mapping a normalized schema to the business model, you need to understand how the distributed structure can be understood in terms of facts and dimensions.

    After analyzing the structure, you pick a table that has fact columns and then map the physical fact columns to one or more logical fact tables. Then, for each dimension associated with that set of physical fact columns, you map the distributed physical tables containing dimensional columns to a single logical table. Like with snowflake schemas, you can achieve this by having multiple logical table sources, or by using a single logical table source with joins. Mapping normalized schemas is an iterative process because you first map a certain set of facts, then the associated dimensions, and then you move on to the next set of facts.

    Note that when a single physical table has both fact and dimension columns, you may need to create a physical alias table to handle the multiple roles played by that table.

  • Fully Denormalized Schemas. This type of dimensional schema combines the facts and dimensions as columns in one table (or flat file), and is mapped differently than other types of schemas. When you map a fully denormalized schema to the star-shaped business model, you map the physical fact columns from the single physical fact table to multiple logical fact tables in the business model. Then, you map the physical dimension columns to the appropriate conformed logical dimension tables.

About Cubes in Multidimensional Data Sources

Cubes are made up of measures and organized by dimensions. Because they are already dimensional, each cube maps easily to the logical fact and dimension tables in the business model.

Note the following about measures and dimensions:

  • Measures in multidimensional cubes and relational fact columns both map to logical measures in the Business Model and Mapping layer. However, measures in multidimensional cubes already include calculations and aggregations, unlike relational fact columns, which require the calculations and aggregations to be applied in the business model. Rather than treating cubes like relational sources, the Oracle BI Server can take advantage of the pre-aggregated data and powerful calculations in the cube.

  • Multidimensional physical objects and relational physical objects both map to logical dimensions in the Business Model and Mapping layer. However, dimensional and hierarchical semantics are already built into multidimensional data sources, unlike relational sources. The Oracle BI Server can take advantage of the more complete hierarchy and dimensional support in the cube, both during import and at query time.

Identifying the Data Source Table Structure

The Administration Tool provides an interface to map logical tables to the underlying physical tables in your data sources. Before you can map the tables, you need to identify the contents of the physical data sources as it relates to your business model. To do this correctly, you need to identify the following contents of the physical data source:

  • Identify the contents of each table

  • Identify the detail level for each table

  • Identify the table definition for each aggregate table. This lets you set up aggregate navigation. The following detail is required by the Oracle BI Server:

    • The columns by which the table is grouped (the aggregation level)

    • The type of aggregation (SUM, AVG, MIN, MAX, or COUNT)

    For information on how to set up aggregate navigation in a repository, see Chapter 11.

  • Identify the contents of each column

  • Identify how each measure is calculated

  • Identify the joins defined in the database

To acquire this information about the data, you could refer to any available documentation that describes the data elements created when the data source was implemented, or you might need to spend some time with the DBA for each data source to get this information. To fully understand all the data elements, you might also need to ask people in the organization who are users of the data, owners of the data, or the application developers of applications that create the data.

Guidelines for Designing a Repository

After analyzing your business model needs and identifying the database content that your business requires, you can complete your repository design. This section contains some design best practices that can help you implement a more efficient repository.

Typically, you should not make performance tuning changes until you have imported and tested your databases. These tasks are performed during the final steps in completing the setup of your repository. For more information about these final steps, see Chapter 15.

This section contains the following topics:

General Tips for Working on the Repository

Follow these recommended design strategies for structuring your Oracle BI repository:

  • If you work in online mode, save backups of the online repository before and after every completed unit of work. If needed, use Copy As on the File menu to make an offline copy containing the changes.

  • Use the Physical Diagrams in the Administration Tool to verify sources and joins.

  • Decide whether you want to set up row-level security controls in the database, or in the repository. This decision determines whether you share connection pools and cache, and may limit the number of separate source databases you want to include in your deployment. See Chapter 14, "Applying Data Access Security to Repository Objects" for more information.

Most dialogs in the Administration Tool have Help that provides information about how to complete a task. To access a help topic, click the Help button in a dialog, or select Help from some menus.

Design Tips for the Physical Layer

The Physical layer contains information about the physical data sources. The most common way to create the schema in the Physical layer is by importing metadata from databases and other data sources. If you import metadata, many of the properties are configured automatically based on the information gathered during the import process. You can also define other attributes of the physical data source, such as join relationships, that might not exist in the data source metadata.

The Physical layer can contain data sources of many different types, including multidimensional, relational, and XML sources. See "System Requirements and Certification" for information about supported databases.

For each data source, there is at least one corresponding connection pool. The connection pool contains data source name (DSN) information used to connect to a data source, the number of connections allowed, timeout information, and other connectivity-related administrative details. See "About Connection Pools" for more information.

The following is a list of tips to use when designing the Physical layer:

  • It is recommended that you use table aliases frequently in the Physical layer to eliminate extraneous joins, including the following:

    • Eliminate all physical joins that cross dimensions (inter-dimensional circular joins) by using aliases.

    • Eliminate all circular joins (intra-dimensional circular joins) in a logical table source in the Physical Model by creating physical table aliases.

      For example, suppose you have a Customer table that can be used to look up ship-to addresses, and using a different join, to look up bill-to addresses. You can avoid the circular joins by creating an alias table in the Physical layer so that there is one table instance for each purpose, with separate joins.

    If you do not eliminate circular joins, you might get erroneous report results. In addition, query performance might be negatively impacted.

  • It is recommended that you use alias tables to create separate physical joins when you need the join to be an inner join in one logical table source and an outer join in another logical table source.

  • You might import some tables into the Physical layer that you might not use right away, but that you do not want to delete. To identify tables that you do want to use right away in the Business Model and Mapping layer, you can assign aliases to physical tables before mapping them to the business model layer.


    To have the name of a table to which you assigned an alias appear, select Tools, then select Options, then select General, and then select Display original name for alias in diagrams.
  • Use an opaque view (a Physical layer table that consists of a SELECT statement) only if there is no other solution to your modeling problem. Ideally, you should create a physical table, or alternatively a materialized view. Opaque views prevent the Oracle BI Server from generating its own optimized SQL, because they contain fixed SQL statements that are sent to the underlying data source.

Design Tips for the Business Model and Mapping Layer

The Business Model and Mapping layer organizes information by business model. In this layer, each business model is effectively a separate application.

The logical schema defined in each business model must contain at least two logical tables. Relationships need to be defined between all the logical tables. See "About Layers in the Oracle BI Repository" for more information about business model schemas. See Chapter 9 for more information about setting up the Business Model and Mapping layer.

The following is a list of tips to use when designing the Business Model and Mapping layer:

  • Create the business model with one-to-many logical joins between logical dimension tables and the fact tables wherever possible. The business model should ideally resemble a simple star schema in which each fact table is joined directly to its dimensions.

  • Every logical fact table must join to at least one logical dimension table. Note that when the source is a fully denormalized table or flat file, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.

  • Every logical dimension table should have a dimensional hierarchy associated with it. This rule holds true even if the hierarchy has only one level, such as a scenario dimension {actual, forecast, plan}.

  • When creating level-based measures, make sure that all appropriate fact sources map to the appropriate level in the hierarchy using aggregation content. You set up aggregation content in the Levels tab of the Logical Column dialog for the measure. Note that this is different from the Content tab of the Logical Table Source dialog, which is used to specify the grain of the source tables to which it maps.

    You only need to set up aggregation content in the Levels tab of the Logical Column dialog for level-based measures. For measures that are not level based, leave the Logical Level field blank.

  • Typically, logical fact tables should not contain any keys. The only exception is when you need to send Logical SQL queries against the Oracle BI Server from a client that requires keys. In this case, you need to expose those keys in both the logical fact tables, and in the Presentation layer.

  • Normally, all columns in logical fact tables are aggregated measures, except for keys required by external clients, or dummy columns used as a divider. Other non-aggregated columns should instead exist in a logical dimension table.

  • In some situations, you might want to have multiple logical fact tables in a single business model. For Logical SQL queries, the multiple logical fact tables behave as if they are one table.

    Reasons to have multiple logical fact tables include:

    Unlike relational fact tables, logical fact tables can contain measures of different grains. Because of this, grain is not a reason to split up logical fact tables.

  • You can define calculations in either of the following ways:

    • Before the aggregation, in the logical table source. For example:

      sum(col_A *( col_B))
    • After the aggregation, in a logical column derived from two other logical columns. For example:

      sum(col A) * sum(col B)

    You can also define post-aggregation calculations in Answers or in Logical SQL queries.

  • If you plan to use Oracle Scorecard and Strategy Management, it is a best practice to implement at least one time dimension in the Oracle BI repository you are using for your KPIs. This action is necessary because you use KPIs in scorecards to measure progress and performance over time. Note that an individual scorecard automatically includes any dimension used by KPIs in that scorecard.

  • Aggregate sources should be created as separate logical table sources. For fact aggregates, use the Content tab of the Logical Table Source dialog to assign the correct logical level to each dimension.

  • Each dimension level in a hierarchy must have a unique level key. Also, each logical dimension table must have a unique primary key. Normally, this key is also used as the level key for the lowest hierarchy level.

  • Renaming columns in the Business Model and Mapping layer automatically creates aliases (synonyms) for Presentation layer columns that have the property Use Logical Column Name selected. This occurs because Presentation layer columns with this option selected are automatically renamed so that the logical column and presentation column names are in sync. Note that renaming Presentation layer columns directly (when Use Logical Column Name is not selected) also causes an alias to be created.

  • To prevent problems with aggregate navigation, ensure that each logical level of a dimension hierarchy contains the correct value in the field named Number of elements at this level. Fact sources are selected on a combination of the fields selected as well as the levels in the dimensions to which they map. By adjusting these values, you can alter the fact source selected by the Oracle BI Server. See "Creating Logical Levels in a Dimension" for more information about setting this value.

Modeling Outer Joins

The following guidelines provide tips on how to model outer joins:

  • Due to the nature of outer joins, queries that use them are usually slower. Because of this, define outer joins only when necessary. Where possible, use ETL techniques to eliminate the need for outer joins in the reporting SQL.

  • Outer joins are always defined in the Business Model and Mapping layer. Physical layer joins do not specify inner or outer.

  • You can define outer joins by using logical table joins, or in logical table sources. Which type of outer join you use is determined by whether the physical join maps to a business model join, or to a logical table source join.

  • If you must define an outer join, try to create two separate dimensions, one that uses the outer join and one that does not. Make sure to name the dimension with the outer join in a way that clearly identifies it, so that client users can use it as little as possible.

  • Avoid using more than one outer join. Instead, to achieve the same effect as a logical outer join, Oracle recommends that the logical join be an inner join and that the analysis designer at design time selects the Include Null Value option in the corresponding analysis. For more about the Include Null Value option in the Analysis Editor, see "Understanding Null Suppression" in Oracle Fusion Middleware User's Guide for Oracle Business Intelligence Enterprise Edition.

Design Tips for the Presentation Layer

You set up the user view of a business model in the Presentation layer. The names of folders and columns in the Presentation layer can appear in localized language translations. The Presentation layer is the appropriate layer in which to set user permissions. For complete information about working in the Presentation layer, see Chapter 12.

In this layer, you can do the following:

  • You can show fewer columns than exist in the Business Model and Mapping layer. For example, you can exclude the key columns because they have no business meaning.

  • You can organize columns using a different structure from the table structure in the Business Model and Mapping layer.

  • You can display column names that are different from the column names in the Business Model and Mapping layer.

  • You can set permissions to grant or deny users access to individual subject areas, tables, and columns.

  • You can export logical keys to ODBC-based query and reporting tools.

  • You can create multiple subject areas for a single business model.

  • You can create a list of aliases (synonyms) for presentation objects that can be used in Logical SQL queries. This feature lets you change presentation column names without breaking existing reports.

The following is a list of tips to use when designing the Presentation layer:

  • Because there is no automatic way to synchronize all changes between the Business Model and Mapping layer and the Presentation layer, it is best to wait until the Business Model and Mapping layer is relatively stable before adding customizations in the Presentation layer.

  • There are many ways to create subject areas, such as dragging and dropping the entire business model, dragging and dropping incremental pieces of the model, or automatically creating subject areas based on logical stars or snowflakes. See "Creating Subject Areas" for information about each of these methods. Dragging and dropping incrementally works well if certain parts of your business model are still changing.

  • It is a best practice to rename objects in the Business Model and Mapping layer rather than the Presentation layer, for better maintainability. Giving user-friendly names to logical objects rather than presentation objects ensures that the names can be reused in multiple subject areas. Also, it ensures that the names persist even when you need to delete and re-create subject areas to incorporate changes to your business model.

  • Be aware that members in a presentation hierarchy are not visible in the Presentation layer. Instead, you can see hierarchy members in Answers.

  • You can use the Administration Tool to update Presentation layer metadata to give the appearance of nested folders in Answers. See "Nesting Folders in Answers and BI Composer" for more information.

  • When setting up data access security for a large number of objects, consider setting object permissions by role rather than setting permissions for individual columns. See Chapter 14, "Applying Data Access Security to Repository Objects" for details.

  • When setting permissions on presentation objects, you can change the default permission by setting the DEFAULT_PRIVILEGES configuration setting in the NQSConfig.INI file. See "Appendix A: NQSConfig.INI File Configuration Settings" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Topics of Interest in Other Guides

Some topics that may be of interest to metadata repository builders are covered in other guides. Table 1-1 lists these topics, and indicates where to go for more information.

Table 1-1 Topics Covered in Other Guides

Topic Where to Go for More Information

Starting and stopping Oracle Business Intelligence processes

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

Using the Oracle BI Server XML API to work with your repository

Oracle Fusion Middleware XML Schema Reference for Oracle Business Intelligence Enterprise Edition

Using the Oracle BI Server web services

Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition

Setting up and managing query caching

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

Managing configuration settings that affect repository development in Fusion Middleware Control and NQSConfig.INI

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

Managing users, groups, and application roles

Oracle Fusion Middleware Security Guide for Oracle Business Intelligence Enterprise Edition

Moving from test to production environments

Oracle Fusion Middleware Administrator's Guide

Setting up DSNs for the Oracle BI Server

Oracle Fusion Middleware Integrator's Guide for Oracle Business Intelligence Enterprise Edition

Localizing Oracle Business Intelligence deployments

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

Information about the SA System subject area

Oracle Fusion Middleware Scheduling Jobs Guide for Oracle Business Intelligence Enterprise Edition

Managing logging

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

Managing usage tracking

Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition

General information about managing Oracle WebLogic Server

Oracle Fusion Middleware Administrator's Guide

System Requirements and Certification

Refer to the system requirements and certification documentation for information about hardware and software requirements, platforms, databases, and other information. Both of these documents are available on Oracle Technology Network (OTN).

The system requirements document covers information such as hardware and software requirements, minimum disk space and memory requirements, and required system libraries, packages, or patches:


The certification document covers supported installation types, platforms, operating systems, databases, JDKs, and third-party products: