Siebel Business Analytics Server Administration Guide > Planning and Creating a Siebel Business Analytics Repository > Process of Analytics Repository Planning and Design >

Guidelines For Designing a Repository

This topics is part of the Process of Analytics 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, see Completing Setup and Managing Repository Files.

The following topics are discussed in this section:

• General Tips For Working on the Repository
• Design Tips For the Physical Layer (Schema)
• Design Tips for the Business Model and Mapping Layer
• Design Tips For the Presentation Layer

General Tips For Working on the Repository

The Analytics Server stores metadata in repositories. The administrator uses the graphical user interface (GUI) of the Administration Tool software to create and maintain repositories. An analytics 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 analytics 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 the Copy As feature 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 of the following: Oracle's Siebel Data Warehouse, Oracle's Siebel BBB Data Warehouse, and Oracle's Forecasting Siebel Data Warehouse. Some tables 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, see System Requirements and Supported Platforms on Oracle's Siebel SupportWeb.

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, see Setting Up Connection Pools and Creating and Administering the Physical Layer in a 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 use in the Business Model and Mapping layer, is to assign aliases to physical tables before mapping them to the business model layer.

  NOTE:  To see the name of a table to which you assigned an alias, 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:  In Version 7.7, 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 materialized view. A traditional database view is not needed because it is identical to the opaque view.

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 Analytics 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, see Process of Analytics Repository Planning and Design. For more information about setting up the Business Model and Mapping layer, see Creating and Administering the Business Model and Mapping Layer in a Repository.

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

• Physically join fact extension tables to their base tables using one-to-many foreign key joins, and include them in the existing source for the logical table.
• Create new fact tables to support requirements when existing fact tables do not adequately meet the dimensional needs. Do not alter an existing fact table because it was designed to support other requirements.
• Create new fact tables or use the _FX to physically store links to other dimensions when they are not in the existing data model.
• 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.

• Join to _WID values instead of codes or names. Avoid Coded Records where the meaning of a record or field changes depending upon the value of a field. An example is to create joins to the W_LOV_D table on Code and Type, not with the ROW_WID as is currently done in the Siebel applications.
• 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 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.
• Physically model dimension extension tables to their base tables using one-to-many foreign key joins, and included them in the existing source for the logical table.

  You might want to create a source for just the Dimension _DX table and then create a one-to-many physical join between it and the fact tables to which it applies. This may help some queries perform better in cases where the _D table is not needed in the query.

  NOTE:  The physical key for both the dimension base table and the dimension extension table are identical, and the relationship is required to be 1:1 (one-to-one). Thus, although a circular join will occur in certain instances, it does not alter the record set or negatively impact performance.

• 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 Siebel Business Analytics.
• 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, see Creating and Maintaining the Presentation Layer in a Repository.

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

• Column aliases. It is recommended that you do not use aliases for Presentation layer columns.

  NOTE:  Renaming an element in the Presentation layer will automatically create an alias. This prevents reports that reference the original element from failing.

• Single table model. For the greatest simplicity, you could construct a subject area that consists of a single table. To create a single table model, you first create a logical dimensional model, and then present it as a single table schema in the Administration Tool's Presentation layer. The logical dimensional model is required to set up the necessary metadata for the Analytics Server to navigate to the proper physical tables. For information about the Presentation layer, see Creating and Maintaining the Presentation Layer in a Repository.