Define Content of Logical Table Sources
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. See Set Up Aggregate Navigation by Creating Sources for Aggregated Fact Data.
If a logical table is sourced from a set of fragments, it isn't 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, then 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, then the Oracle BI Server assumes that it doesn't have the whole universe of fragments, and therefore it would be incorrect to calculate aggregate rollups, since some fragments are missing. In this case, the server returns NULL as measure aggregates.
Note:
Oracle highly recommends that all the fragments map to the same set of columns.
Use the Content tab of the Logical Table Source dialog 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. See Set Up Fragmentation Content for Aggregate Navigation.
Verify Joins from Dimension Tables to Fact Tables
Joins tells the Oracle BI Server where to send queries for physical aggregate fact tables joined to and constrained by values in the physical aggregate dimension tables.
Oracle recommends that you use logical levels exclusively as the Aggregation content, group-by option. Don't mix aggregation by logical level and column in the same business model.
See About WHERE Clause Filters and Set Up Fragmentation Content for Aggregate Navigation.
You can type the formula directly into the Fragmentation content text area , or click Expression Builder. In the Expression Builder for Fragmentation Content, you can specify content in terms of existing logical columns. See Set Up Fragmentation Content for Aggregate Navigation.
Choose This source should be combined with other sources at this level if all fragments on this level are disjointed. Consider the following examples:
- Example 1 - Suppose you've two fragments, all sales including current year, and current year sales with the fragmentation predicate set to year = 2015. You shouldn't select the This source should be combined with other sources at this level option because the two fragments overlap. Oracle BI Server can use any single fragment based on query predicate or fragmentation predicate compatibility.
-
Example 2 - Suppose you've two fragments, sales for year 2000 and before, according to the fragmentation predicate, and sales for year 2001 and after, according to the fragmentation predicate. You should select the This source should be combined with other sources at this level option because the fragments don't overlap. The Oracle BI Server creates a union of all the logical table sources on this level that can't be disqualified based on query predicate or fragmentation predicate compatibility.
See Logical Table Source Options Reference to learn which option to use in the Logical Table Source dialog.
Joins from Dimension Tables to Fact Tables
You must create joins between the aggregate fact tables and the aggregate dimension tables in the Physical layer.
You can verify joins by selecting a fact logical table and opening 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 doesn't show dimension tables if the same physical table is used in logical fact and dimension sources.
The image shows a Fact - Assess logical fact table in a Business Model Diagram in the Selected Tables and Direct Joins view.
The table contains a list of the logical level for each dimension table that's directly joined the Fact - Assess fact table.
Dimension | Logical Level |
---|---|
Account Geography |
Postal Code Detail |
Person Geography |
Postal Code Detail |
Time |
Day Detail |
Account Organization |
Account Detail |
Opportunity |
Opty Detail |
Primary Visibility Organization |
Detail |
Employee |
Detail |
Assessment |
Detail |
Contact (W_PERSON_D) |
Detail |
FINS Time |
Day |
Positions |
Details |
Logical Table Source Options Reference
Learn how to use the options from the Logical Table Source dialog.
Options | Description |
---|---|
Aggregation content, group by |
Specifies how the content is aggregated. |
Copy |
The Copy option is only available with fact tables. Copies aggregation content to the Windows clipboard. You can paste the Copy isn't available if the expression is empty. |
Copy from |
The Copy from option is available for fact tables and dimension tables. Copies aggregation content from another logical table source in the same business model. You need to specify the source from which to copy the aggregation content. Multiple business models appear but only the logical table sources from the current business model are selectable. |
Get Levels |
The Get Levels option is only available for fact tables. Changes aggregation content. If joins don't exist between fact table sources and dimension table sources, for example, if the same physical table is in both sources, the aggregation content determined by the Model Administration Tool doesn't include the aggregation content of this dimension. |
Check Levels |
The Check Levels option is only available for fact tables. Checks the aggregation content of logical fact table sources, not dimension table sources. The information returned depends on the existence of dimensions and hierarchies with logical levels and level keys, and physical joins between tables in dimension table sources and the tables in the fact table source. If the same tables exist in the fact and dimension sources and there are no physical joins between tables in the sources, Check Levels doesn't include the aggregation content of this dimension. |
Fragmentation content |
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. |
This source should be combined with other sources at this level |
Select this option when data sources at the same level of aggregation don't contain overlapping information. In this situation, all sources must be combined to get a complete picture of information at this level of aggregation. |
Select distinct values |
Used if the values for the source are unique. |
About WHERE Clause Filters
The WHERE clause filter is used to constrain the physical tables referenced in the logical table source.
If there are no constraints on the aggregate source, leave the WHERE
clause filter blank.
Each logical table source should contain data at a single intersection of aggregation levels. You wouldn't want to create a source, for example, that had sales data at both the Brand and Manufacturer levels. If the physical tables include data at multiple levels, add an appropriate WHERE
clause constraint to filter values to a single level.
Any constraints in the WHERE
clause filter are made on the physical tables in the source.