To use a source correctly, the Oracle BI Server has to know what each source contains in terms of the business model. Therefore, you need to define aggregation content for each logical table source of a fact table. The aggregation content rule defines at what level of granularity the data is stored in this fact table. For each dimension that relates to this fact logical table, define the level of granularity, making sure that every related dimension is defined. For more information, refer to Example of Creating Sources for Each Level of Aggregated Fact Data.
If a logical table is sourced from a set of fragments, it is not required that every individual fragment maps the same set of columns. However, the server returns different answers depending on how columns are mapped.
- If all the fragments of a logical table map the same set of columns, than the set of fragmented sources is considered to be the whole universe of logical table sources for the logical table. This means that measure aggregations can be calculated based on the set of fragments.
- If the set of mapped columns differ across the fragments, than we assume that we do not have the whole universe of fragments, and therefore it would be incorrect to calculate aggregate rollups (since some fragments are missing).
Use the Content tab of the Logical Table Source dialog box to define any aggregate table content definitions, fragmented table definitions for the source, and Where clauses (if you want to limit the number of rows returned).
NOTE: For examples of how to set up fragmentation content for aggregate navigation, refer to Setting Up Fragmentation Content in an Oracle BI Repository for Aggregate Navigation.
This source content information tells the Oracle BI Server what it needs to know to send queries to the appropriate physical aggregate fact tables, joined to and constrained by values in the appropriate physical aggregate dimension tables. Be sure that joins exist between the aggregate fact tables and the aggregate dimension tables in the Physical layer.
One recommended way to verify joins is to select a fact logical table and request a Business Model Diagram (Selected Tables and Direct Joins). Only the dimension logical tables that are directly joined to this fact logical table appear in the diagram. It does not show dimension tables if the same physical table is used in logical fact and dimension sources.
Figure 12 is an example of how the Fact - Asset fact logical table appears in a Business Model Diagram (Selected Tables and Direct Joins) view.
Table 17 contains a list of the logical level for each dimension table that is directly joined the Fact - Assess fact table shown in Figure 12.
- In the Business Model and Mapping layer, double-click a logical table source.
- In the Logical Table Source dialog box, click the Content tab and perform the following steps using Table 18 as a guide.
- If a logical source is an aggregate table and you have defined logical dimensions, perform the following steps:
- If a logical source is an aggregate table and you want to define content for columns, do the following:
- Select Column from the Aggregation content, group-by drop-down list.
- In the Table pane, select each logical dimension table that defines the aggregation level of the source.
- In the Column pane, select the logical column for each dimension that defines how the aggregations were grouped. When there are multiple logical columns that could be used, select the one that maps to the key of the source physical table. For example, if data has been aggregated to the Region logical level, pick the logical column that maps to the key of the Region table.
- To specify fragmented table definitions for the source, use the Fragmentation content window to describe the range of values included in the source when a source represents a portion of the data at a given level of aggregation. You can type the formula directly into the window, or click the Expression Builder button to the right of the window. In the Fragmentation Content Expression Builder, you can specify content in terms of existing logical columns. For examples of how to set up fragmentation content for aggregate navigation, seeSpecify Fragmentation Content.
- Select the following option:
- (Optional) Specify Where clause filters in the Where Clause Filter window to limit the number of rows the source uses in the resultant table. For more information, refer to About WHERE Clause Filters.
- Select the option Select distinct values if the values for the source are unique.
A description of the contents of a data source in business model terms. Data is fragmented when information at the same level of aggregation is split into multiple tables depending on the values of the data. A common situation would be to have data fragmented by time period. For examples of how to set up fragmentation content for aggregate navigation, seeSpecify Fragmentation Content.