After you have determined the requirements for your business model, you can look at what data source content you need in the Physical layer.
Unlike the Business Model and Mapping layer that is always dimensional, each physical model mirrors the shape of the source, for example, normalized, and cube.
This section contains the following topics:
You can successfully model any physical schema in the Oracle BI repository, regardless of its type, because the model of any physical source can be broken down into overlapping subsets that are dimensional.
There are four types of physical schemas (models):
Star Schemas. A star schema is a set of dimensional schemas (stars) that each have a single fact table with foreign key 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 can be 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 (or flat file), 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.
Cubes are made up of measures and organized by dimensions.
Because they are already dimensional, each cube maps easily to the logical fact and dimension tables in the business model.
Note the following about measures and dimensions:
Measures in multidimensional cubes and relational fact columns both map to logical measures in the Business Model and Mapping layer. However, measures in multidimensional cubes already include calculations and aggregations, unlike relational fact columns, which require the calculations and aggregations to be applied in the business model. Rather than treating cubes like relational sources, the Oracle BI Server can take advantage of the pre-aggregated data and powerful calculations in the cube.
Multidimensional physical objects and relational physical objects both map to logical dimensions in the Business Model and Mapping layer. However, dimensional and hierarchical semantics are already built into multidimensional data sources, unlike relational sources. The Oracle BI Server can take advantage of the more complete hierarchy and dimensional support in the cube, both during import and at query time.
The Oracle BI Administration Tool provides an interface to 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. To do this correctly, you need to 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 aggregate navigation. The following detail is required by the Oracle BI Server:
The columns by which the table is grouped (the aggregation level)
The type of aggregation (SUM, AVG, MIN, MAX, or COUNT)
To set up aggregate navigation in a repository, see Managing Logical Table Sources (Mappings).
Identify the contents of each column
Identify how each measure is calculated
Identify the joins defined in the database
To acquire this information about the data, you could refer to any available documentation that describes the data elements created when the data source was implemented, or you might need to spend some time with the DBA for each data source to get this information. To fully understand all the data elements, you might also need to ask people in the organization who are users of the data, owners of the data, or the application developers of applications that create the data.