|
Oracle® Business Intelligence Server Administration Guide > Planning and Creating an Oracle BI Repository > Process of Oracle BI Repository Planning and Design >
Guidelines For Designing a Repository
This topic is part of the Process of Oracle BI Repository Planning and Design. After analyzing your business model needs and identifying the database content your business needs, you can complete your repository design. This section contains some design best practices that will help you implement a more efficient repository. Typically, you should not make performance tuning changes until you have imported and tested your databases. This would occur during the final steps in completing the setup of your repository. For more information about these final steps, refer to Completing Setup and Managing Oracle BI Repository Files. The following topics are discussed in this section:
General Tips For Working on the Repository
The Oracle BI Server stores metadata in repositories. The Oracle BI Administrator uses the graphical user interface (GUI) of the Administration Tool software to create and maintain repositories. An Oracle BI repository consists of three layers. Each layer appears in a separate pane in the Administration Tool user interface and has a tree structure (similar to the Windows Explorer). These layers are not visible to the end user. Most windows and dialog boxes have online help containing information to help you complete a task. To access a help topic, click the help button in a dialog box or select Help from some menus. The following list contains some recommended design strategies for the Oracle BI repository structure: - Perform the majority of repository editing in offline mode to make your results more consistent. In some environments, this might save time during the development effort.
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.
- For design independence, import databases for each business model separately. For example, you might have one database for each application. Some tables may exist in more than one database and you can customize each table for a different business model. This eliminates the need for cross-database joins.
- After importing a database, test it before importing another. Make sure the metadata is generating the correct record set before performing performance tuning activities.
- Use the Physical diagrams in the Administration Tool to verify sources and joins.
Design Tips For the Physical Layer (Schema)
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. There can be one or more data sources in the Physical layer, including databases and XML documents. For more information about supported databases, refer to System Requirements and Supported Platforms. 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. For more information, refer to Setting Up Connection Pools and Creating and Administering the Physical Layer in an Oracle BI Repository. The following is a list of tips to use when designing the Physical layer: - Before importing metadata from a data warehouse into the Physical layer of your repository, eliminate all outer joins in the data warehouse. Use Extract, Transform, and Load (ETL) lookups to find them. Eliminating outer joins results in a more consistent record set, a simplified business model, and improved performance.
- To reduce problems that might be caused by importing physical joins, import the physical data without foreign keys and then create the keys as needed.
- You will probably import some tables into the Physical layer that you might not use right away but that you do not want to delete. One way to identify tables that you do want to use right away in the Business Model and Mapping layer, is to assign aliases to physical tables before mapping them to the business model layer.
NOTE: To have the name of a table to which you assigned an alias appear, make sure you turn on the following option in the Tools > Options > General menu path: Display original names for alias in diagrams.
- Use a naming standard in the Physical Layer that identifies the logical components followed by the physical table name as shown in the following example:
Created By Employee (W_PERSON_D)
NOTE: The Physical layer object name is included in the Physical SQL. By using qualified Physical Layer names, debugging is made easier.
- An opaque view (a physical layer table that consists of a Select statement) should be used only if there is no other solution. Ideally, a physical table should be created, or alternatively a materialized view. A traditional database view is not needed because it is identical to the opaque view. For more information, refer to Deploying Opaque Views.
Design Tips for the Business Model and Mapping Layer
The Business Model and Mapping layer organizes information by business model. Each business model contains logical tables. Logical tables are composed of logical columns. Logical tables have relationships to each other expressed by logical joins. The relationship between logical columns can be hierarchical, as defined in business model hierarchies. Logical tables map to the source data in the Physical layer. The mapping can include complex transformations and formulas. The Business Model and Mapping layer defines the meaning and content of each physical source in business model terms. The Oracle BI Server uses these definitions to assign the appropriate sources for each data request. You can change the names of physical objects independently from corresponding business model object names and properties, and vice versa. Any changes in the underlying physical databases or the mappings between the business model tables and physical tables might not change the view in the end-user applications that view and query the repository. The logical schema defined in each business model needs to contain at least two logical tables. Relationships need to be defined between all the logical tables. For more information about business model schemas, refer to Process of Oracle BI Repository Planning and Design. For more information about setting up the Business Model and Mapping layer, refer to Creating and Administering the Business Model and Mapping Layer in an Oracle BI Repository. 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 complex 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 linked directly to its dimensions.
NOTE: If you use the snowflake model, you might have more flexibility (for example, the ability to use outer joins) but it may create more columns in the presentation layer. However, it is recommended that you minimize the use of snowflake schemas.
- Outer joins should be avoided in the reporting SQL. They can be eliminated in the ETL via a variety of techniques, but by doing so, not only can an additional table be removed from the report SQL, but the performance will also improve.
- Combine all similar dimensional attributes into one logical dimension table. Where needed, include data from other dimension tables into the main dimension source using aliases in the Physical layer tables. This should occur during the ETL process for optimal performance.
- Every logical dimension table should have a dimensional hierarchy associated with it. Make sure that all appropriate fact sources link to the proper level in the hierarchy using aggregation content. You set up aggregation content in the Content tab of the Logical Table Source properties window.
- Aggregate sources should be created as a separate Source in a logical table. For fact aggregates, use the Content tab of the Logical Table Source properties window to assign the correct logical level to each dimension.
- It is recommended that you use table aliases frequently in the Business Model 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.
- Renaming an element in the Business Model and Mapping layer will automatically create an alias.
- To prevent problems with aggregate navigation, make sure 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 link. By adjusting these values, you can alter the fact source selected by Oracle Business Intelligence.
- Outer joins in logical table sources are always included in a query, even if the table source is not used. If possible, create one logical table source without the outer join and another with the outer join. Order the logical table source with the outer join after the non-outer join so that it will be used only when necessary.
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 appear in localized language translations. The Presentation layer is the appropriate layer in which to set user permissions. 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 catalogs, tables, and columns.
- You can export logical keys to ODBC-based query and reporting tools.
For more information about setting up the Presentation layer, refer to Creating and Maintaining the Presentation Layer in an Oracle BI Repository. The following is a list of tips to use when designing the Presentation layer:
|
