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.