What Are Subject Areas and Columns?

A subject area contains folders, measure columns, attribute columns, hierarchical columns, and hierarchy levels that represent information about the areas of an organization's business or about groups of users with an organization. Subject areas usually have names that correspond to the types of information that they contain, such as Marketing Contracts, Service Requests, and Orders.

A subject area corresponds to the presentation layer in an Oracle BI metadata repository. In a repository, the subject area is the highest-level object in the presentation layer and represents the view of the data that end users see when they create or edit an analyses.

Individuals who design and build metadata repositories (such as a Business Intelligence strategist, metadata provider, or ETL developer) create subject areas using the Oracle BI Administration Tool. Generally, rather than creating one large subject area for their company's data, they create multiple smaller subject areas. This enables them to provide a particular group of users or a particular area of a company with the most important data that they need in one small subject area and the less important data in one or more related subject areas created from the same business model layer. Having these smaller subject areas makes it easier for users to find the data they need. It also makes it easier to maintain the data. For more information, see "Creating Subject Areas" in Oracle Fusion Middleware Metadata Repository Builder's Guide for Oracle Business Intelligence Enterprise Edition.

Note:

The individuals who design and build metadata repositories can specify that a subject area, folder (and its children), or column (both attribute and hierarchical) is to be hidden. A hidden subject area, folder, or column is not visible in the "Subject Areas pane" but is visible elsewhere, such as in an analysis or saved filter contents. (Because the object is still visible elsewhere, hiding a subject area, folder, or column in this way is not a solution for security or access control.)

If the criteria of an existing analysis includes a subject area, folder, or column that is subsequently hidden, the analysis is still accessible but the subject area, folder, or column is no longer visible in the Subject Areas pane of the "Analysis editor: Criteria tab".

Columns contain the individual pieces of data that an analysis returns. Columns usually have names that indicate the types of information that they contain, such as Account or Contact. Together with filters and selection steps, columns determine what data an analysis contains.

When you create an analysis, filter, or dashboard prompt, you first select the subject area with which you want to work. This is known as the primary subject area and is displayed in the "Subject Areas pane". If, as you work, you find that you need more data, you can add additional subjects areas that are related to the primary subject area that you have chosen. (You can add related subject areas only if they are available for the primary subject area and only if you have permission to access them.)

Typically, when you query a single subject area, all the measure columns that are exposed in that subject area are compatible with all the attribute columns and hierarchical columns that are exposed in the same subject area. However, when you combine columns from multiple subject areas, you must ensure that you do not include combinations of measure columns with attribute columns and hierarchical columns that are incompatible with one another.

For example, a measure column in one subject area might not be associated with the Project attribute column. If measure columns associated with the Project attribute column from another subject area are added to the analysis along with columns that are not associated with Project, then the query might fail to return results, or cause the BI Server error "No fact table exists at the requested level of detail: XXXX."

For an analysis to return data, you must select at least one column to include in the analysis.

What Are the Types of Columns?

Subject areas contain the following types of columns:

Attribute Column — Holds a flat list of values that are also known as members. No hierarchical relationship exists between these members, as is the case for members of a hierarchical column. An attribute column was referred to as a presentation column in previous releases (prior to 11g).

Examples include ProductID or City.
Hierarchical Column — Holds data values that are organized using both named levels and parent-child relationships. This column is displayed using a tree-like structure. Individual members are shown in an outline manner. Hierarchies allow you to drill deeper into the data, to view more detailed information. Examples include Time or Geography. Figure 2-1 shows the Time folder and the Time and Fiscal Time hierarchies expanded in the Subject Areas pane.

Figure 2-1 Time and Fiscal Time Hierarchies in the Subject Areas Pane

A hierarchical column can be one of the following kinds:
- Level-based hierarchy — Consists of an ordered set of two or more levels. For example, a Time hierarchy might have three levels for Year, Quarter, and Month. Level-based hierarchies can also contain parent-child relationships.
- Parent-child hierarchy — Consists of values that define the hierarchy in a parent-child relationship and does not contain named levels. For example, an Employee hierarchy might have no levels, but instead have names of employees who are managed by other employees. Employees can have titles, such as Vice President. Vice Presidents might report to other Vice Presidents and different Vice Presidents can be at different depths in the hierarchy.
In addition to being level-based or parent-child, hierarchical columns can be one of the following:
- Ragged — A hierarchy in which all the lowest-level members do not have the same depth. For example, a Time hierarchy might have data for the current month at the day level, the previous month's data at the month level, and the previous 5 years' data at the quarter level. This type of hierarchy is also known as an unbalanced hierarchy.
- Skip-level — A hierarchy in which certain members do not have values for certain higher levels. For example, in the United States, the city of Washington in the District of Columbia does not belong to a state. The expectation is that users can still navigate from the country level (United States) to Washington and below without the need for a state.
Measure Column — Holds a simple list of data values. It is a column in an Oracle BI EE repository, usually in a fact table, that can change for each record and can be added up or aggregated in some way. Examples include Revenue or Units Sold.

Throughout this guide, the term "column" on its own generally refers to all three types. Names for specific types of columns are included where necessary.

How Are Columns Indicated Visually?

Each type of column is indicated by its own icon in places such as the Subject Areas pane and Layout pane. You can expand level-based hierarchies and see their levels. Parent-child hierarchies are shown as hierarchical columns that have no levels. Figure 2-2 shows the icons and names of various columns.

Figure 2-2 Column Types With Their Icons

This image is described in the surrounding text.

How Will Measure Columns Created in Previous Releases Be Upgraded?

In previous releases (prior to 11g) of Oracle BI EE, measure columns could easily be treated as attribute columns, which allowed you to move them freely among the edges of views. This release introduces functionality that specifies to not show all the detail when a measure column is moved to an edge but rather to aggregate the measure column to the grain of the edge. During upgrade, all measure columns have the Treat as an Attribute Column box selected in the "Edit Column Formula dialog: Column Formula tab". For more information, see "Upgrading Measure Columns" in Oracle Fusion Middleware Upgrade Guide for Oracle Business Intelligence.