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.
You should not make performance tuning changes until importing and testing your databases. Execute performance tuning tasks during the final steps in completing the setup of your repository. See Completing Oracle BI Repository Setup.
This section contains the following topics:
Use 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 Applying Data Access Security to Repository Objects.
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 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.
The following is a list of tips to use when designing the Physical layer:
You should use table aliases 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.
A circular join involves using different joins from the same table to get results, for example, you have a Customer table that is used to look up ship-to addresses, and you use a different join to the Customer table to look up bill-to addresses. You can avoid the circular joins by creating an alias table in the Physical layer so that only one table instance is used for each purpose, with separate joins.
If you do not eliminate circular joins, you could get erroneous report results. In addition, query performance is negatively impacted by circular joins.
You should use alias tables to create separate physical joins when you need the join to perform as an inner join in one logical table source, and as 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.
Note:
To display the original name of a table that has an assigned alias, select Tools, select Options, select General, and then select Display original name for alias in diagrams.
Use an opaque view only if there is no other solution to your modeling problem. You should create a physical table or a materialized view. Opaque views prevent the Oracle BI Server from generating optimized SQL because opaque views contain fixed SQL statements that are sent to the underlying data source.
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. You must define relationships between all the logical tables. See:.
Use the following tips 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 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. 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, or 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.
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.
Logical fact tables should not contain any keys, except 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.
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.
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:
To assign projects, see Setting Up Projects.
To automatically create small subject areas in the Presentation layer, see Automatically Creating Subject Areas Based on Logical Stars and Snowflakes.
For organization and simplicity of understanding.
Unlike relational fact tables, logical fact tables can contain measures of different grains. Do not use the grain as 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. 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. Each logical dimension table must have a unique primary key. The 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. Renaming Presentation layer columns directly when Use Logical Column Name is not selected creates an alias.
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.
Use these guidelines 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. See Understanding Null Suppression in User's Guide for Oracle Business Intelligence Enterprise Edition.
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. See Creating and Maintaining the Presentation Layer.
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 are 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 About Creating Subject Areas. 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 and BI Composer.
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 Applying Data Access Security to Repository Objects .
When setting permissions on presentation objects, you can change the default permission by setting the NQSConfig.INI file. See NQSConfig.INI File Configuration Settings in System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.