Plan the Logical Layer

Use the topics in this section to determine the logical layer's content.

Topics:

Guidelines for Identifying the Logical Layer's Content

Use this sequence to determine what content to include in your semantic model's logical layer.

Identify the logical columns that users need to query.
Identify each column's role as either a measure column or a dimensional attribute.
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 Analytics query engine.

Although not all dimensional models are built around a star schema, it's 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.

Identify the Logical Fact Tables

The semantic model's logical layer contains logical fact tables containing measures with aggregations built into their definitions.

Logical fact tables are different from physical fact tables in relational models. Physical tables in relational models define facts at the lowest possible grain. Logical fact table can contain measures of different grains,

You must define measures aggregated from facts in a logical fact table. Measures are calculated data such as dollar value or quantity sold. You can specify measures 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. You can specify aggregation rules to specific dimensions. You can define complex, dimension-specific aggregation rules in the semantic model.

You don't explicitly label tables in the logical layer as fact tables or dimension tables. The Oracle Analytics query engine treats tables at the one end of a join as dimension tables, and tables at the many end of a join as fact tables.

The image shows the many-to-one joins to a fact table in a logical diagram. In the logical diagram, all joins have an arrow, indicating the one side, pointing away from the fact table. No joins are pointing to it.
Logical Layer Diagram

Identify the Logical Dimension Tables

Dimension tables contain attributes that describe business entities such as Customer Name, Region, Address and Country.

A business uses facts to measure performance by established dimensions such as by time, product, and market. Every dimension has a set of descriptive attributes. Dimension tables contain primary keys that identify each member.

Dimension table attributes provide context to numeric data, for example, by providing the ability to categorize Service Requests. Attributes stored in a service requests dimension table could include Service Request Owner, Area, Account, and Priority.

Dimensions in the business model are conformed dimensions. For example, if a specific data source has five different instances of a specific Customer table, the business model should only have one Customer table. To achieve conformance, 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 combining data from multiple fact sources at different grains. Conformed dimensions enable combining multiple data sources.

The business model uses business keys for a dimension and level keys instead of generated surrogate keys. For example, you would 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 conform to the same logical dimension table with the same logical key and level key.

Generated surrogate keys can exist in the physical layer where the keys are useful for their query performance advantages on joins. The logical layer doesn't have surrogate key columns.

Identify Dimensions

Dimensions are categories of attributes that define your business.

Common dimensions are time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. There are many attributes within a dimension. 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 contain hierarchies that are sets of top-down relationships between members within a dimension. There are two types of hierarchies:

Level-based hierarchies (structure hierarchies) - In these hierarchies, members of the same type occur only at a single level, while members in parent-child hierarchies all have the same type. Oracle Analytics supports a time dimension level-based hierarchy that provides functionality for modeling time series data.

In level-based hierarchies, levels roll up from a lower level to higher level, for example, months can roll up into a year. These roll ups occur over the hierarchy elements and span natural business relationships.
Parent-child hierarchies (value hierarchies) - In these 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 don't have explicitly named levels. There isn't a limit to the number of implicit levels in a parent-child hierarchy.

To define your hierarchies, you specify the contains relationships in your business to drive roll up 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, you can use the table to answer questions at the year level by adding up all of the month-level data for a year.

To determine the correct hierarchy type for your modeling needs, consider the following:

Are all the members of the same type such as employee, assembly, or account, or are they 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, the Day type might have a Holiday attribute, while the Month type doesn't have the Holiday attribute.
Are the levels fixed at design time (year-quarter-month), or can runtime business transactions add or subtract levels? For example, if you can add a level when the current lowest-level employee hires a subordinate, who then is 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, you might need to use the same hierarchy type in your business model, regardless of other factors.

Dimensions can contain multiple hierarchies. Dimensions with multiple hierarchies must always end with the same column. For example, time dimensions often have one hierarchy for the calendar year, and another hierarchy for the fiscal year.

Identify 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 using a specific logical lookup table, you must designate the table as a lookup table in the logical table's lookup tables.

You can use lookup tables to display one set of values to users, while using a different corresponding set of values in the physical query. You can use the lookup table to provide human-readable values that are looked up in a different data source.

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.

Model Outer Joins

Use this information to model outer joins.

Queries that use outer joins are usually slower. To avoid performance issues, 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 logical layer. Physical layer joins don’t 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 doesn’t. 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.