Plan the Physical Layer

About Physical Schema Types

When you model data sources, you can break down the model of any physical source into overlapping dimensional subsets.

Each physical model mirrors the shape of the source. For example, snowflake or normalized.

Star Schemas

A star schema is a set of dimensional schemas (stars) that each have a single fact table with join relationships to several dimension tables. When you map a star to the business model, you first map the physical fact columns to one or more logical fact tables. Then, for each physical dimension table that joins to the physical fact table for that star, you map the physical dimension columns to the appropriate conformed logical dimension tables.
Snowflake Schemas

A snowflake schema is similar to a star schema, except that each dimension is made up of multiple tables joined together. Like star schemas, you first map the physical fact columns to one or more logical tables. Then, for each dimension, you map the snowflake physical dimension tables to a single logical table. You can achieve this by either having multiple logical table sources, or by using a single logical table source with joins.
Normalized Schemas

Normalized schemas distribute data entities into multiple tables to minimize data storage redundancy and optimize data updates. Before mapping a normalized schema to the business model, you need to understand how the distributed structure is understood in terms of facts and dimensions.

After analyzing the structure, you pick a table that has fact columns and then map the physical fact columns to one or more logical fact tables. Then, for each dimension associated with that set of physical fact columns, you map the distributed physical tables containing dimensional columns to a single logical table. Like with snowflake schemas, you can achieve this by having multiple logical table sources, or by using a single logical table source with joins. Mapping normalized schemas is an iterative process because you first map a certain set of facts, then the associated dimensions, and then you move on to the next set of facts.

When a single physical table has both fact and dimension columns, you may need to create a physical alias table to handle the multiple roles played by that table.
Fully Denormalized Schemas

This type of dimensional schema combines the facts and dimensions as columns in one table, and is mapped differently than other types of schemas. When you map a fully denormalized schema to the star-shaped business model, you map the physical fact columns from the single physical fact table to multiple logical fact tables in the business model. Then, you map the physical dimension columns to the appropriate conformed logical dimension tables.

Identify the Data Source Table Structure

When you build a semantic model, you map logical tables to the underlying physical tables in your data sources. Before you can map the tables, you need to identify the contents of the physical data sources as it relates to your business model.

Identify the following contents of the physical data source:

Identify the contents of each table.
Identify the detail level for each table.
Identify the table definition for each aggregate table. This lets you set up the aggregate navigation. The Oracle Analytics query engine requires the following:
- The columns the tables are grouped by (the aggregation level).
- The type of aggregation: SUM, AVG, MIN, MAX, or COUNT.
Identify the contents of each column.
Identify how each measure is calculated.
Identify the joins defined in the database.

To find this information, go to any available documentation that describes the data elements created when the data source was implemented. Or you could work with the DBA for each data source to gather this information.

To fully understand all of the data elements, you could ask the people who use or own the data, or the developers of the applications that create the data.

Physical Layer Design Tips

Use the information in this topic to help you design the semantic model's physical layer.

The most common way to create the schema in the Physical layer to import 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.

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.

Use these tips 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 layer by creating physical table aliases.
  
  A circular join involves using different joins from the same table to get results. For example, suppose you have a Customer table that's 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 don't eliminate circular joins, you could get erroneous report results. Also, 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 don't want to delete. To identify tables that you do want to use right away in the logical layer, you can assign aliases to physical tables before mapping them to the logical layer.
Use a SELECT statement only if there is no other solution to your modeling problem. You should create a physical table or a materialized view. SELECT statements prevent the Oracle Analytics query engine from generating optimized SQL because SELECT statements contain fixed SQL statements that are sent to the underlying data source.
Decide if you want to set up row-level security controls in the database or in the semantic model. This decision determines if you share connection pools and cache, and may limit the number of separate source databases you want to include in your deployment.