Physical Layer Design Tips

You create the physical layer schema by selecting tables from the data source schema.

In the physical layer, you create alias tables that represent the physical tables and create joins between the tables. 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. Circular joins also negatively impact query performance and make it more difficult for the Oracle Analytics query engine to identify which physical tables are necessary in the query.

• 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. Be sure to create an alias for each table you use. You can use alias table names to provide information to developer regarding the purpose of the alias

• 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. This prevents Oracle Analytics from choosing which tables and columns are really necessary in the query. You can add filters to logical table source content and define joins in physical layer. If that's not enough then you can create a materialized view or a physical table.

• 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.