Logical Layer Design Tips

The logical 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.

When designing the logical 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.

  • Join every logical fact table to at least one logical dimension table. When the source is a fully denormalized table, you must map its physical fact columns to one or more logical fact tables, and its physical dimension columns to logical dimension tables.

  • Define relationships between dimension attributes by creating hierarchies within a logical dimension.

  • Map all appropriate fact sources map to the appropriate level in the hierarchy using data aggregation when creating level-based measures.

  • Create aggregate sources as separate logical table sources.

  • Create a unique level key for each dimension level in a hierarchy. Each logical dimension table must have a unique primary key. The key is also used as the level key for the lowest hierarchy level.

  • Ensure that each logical level of a dimension hierarchy contains the correct value. 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 Analytics query engine.

Logical Fact Tables

  • Logical fact tables can contain measures of different grains. Don't use the grain as a reason to split up logical fact tables.

  • Logical fact tables shouldn't contain any keys, except when you need to send Logical SQL queries against the Oracle Analytics query engine 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 exist in a logical dimension table.

  • You can use multiple logical fact tables in a single business model. For Logical SQL queries, the multiple logical fact tables behave as if they're one table. Reasons to have multiple logical fact tables include: to automatically create small subject areas in the presentation layer, and to organize and simplify them within the business model.

Calculations

You can define calculations in 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 workbooks, dashboards, analyses, or in Logical SQL queries.