Work With Data Granularity

This topic provides information to help you understand and define logical table sources' data granularity.

Topics:

About Data Granularity

Data granularity indicates a logical table source's level of detail. When a query is issued, the Oracle Analytics query engine uses the logical table source's data granularity to find the required level of detail for the requested data.

The logical table's logical dimensions and hierarchies determine the granularity levels that you can assign to a logical table source. For example, years, months, weeks, days, or hours.

You need to specify data granularity for each fact table's logical table sources. This granularity defines at what level of granularity the data is stored in the fact table. You also need to define granularity for each logical table that joins to the fact table. The Oracle Analytics query engine assumes that if no logical table sources level is specified, then the most detailed level should be used. A data modeling best practice is to assign data granularity for each table source.

About Aggregate Tables

Aggregate tables are physical tables that store precomputed results from measures that have been aggregated over a set of dimensional attributes.

You must join the aggregate fact and dimension tables. See About Aggregate Table Joins.

Each aggregate table column contains data at a given set of levels. For example, a monthly sales table might contain a precomputed sum of the revenue for each product in each store during each month.

When you create a logical table source for an aggregate fact table, you should create corresponding logical dimension table sources at the same levels of aggregation.

You need to have at least one logical dimension table source for each level of aggregation. If the sources at each level already exist, you don't need to create additional sources.

For example, you might have a monthly sales fact table containing a precomputed sum of the revenue for each product in each store during each month. You need to have the following three dimension sources, one for each of the logical dimension tables referenced in the example:

A source for the Product logical table with one of the following content specifications:
- By logical level: ProductDimension.ProductLevel
- By column: Product.Product_Name
A source for the Store logical table with one of the following content specifications:
- By logical level: StoreDimension.StoreLevel
- By column: Store.Store_Name
A source for the Time logical table with one of the following content specifications:
- By logical level: TimeDimension.MonthLevel
- By column: Time.Month

At query time, the Oracle Analytics query engine first determines which sources have enough detail to answer the query. Out of these sources, the Oracle Analytics query engine chooses the most aggregated source to answer the query, because it's assumed to be the fastest. The most aggregated source is the one with the lowest multiplied number of elements.

See Create Logical Levels in a Logical Dimension Table to learn how to specify the number of elements at each level.

About Aggregate Table Joins

You must create physical joins between the aggregate fact tables and the aggregate dimension tables.

Joins tells the Oracle Analytics query engine where to send queries for physical aggregate fact tables joined to and constrained by values in the physical aggregate dimension tables.

You can verify joins by opening the fact logical table's logical diagram. The diagram displays only the dimension logical tables that are directly joined to the fact logical table. The diagram doesn't display dimension tables if the same physical table is used in logical fact and dimension sources.

The image shows the Fact - Assess fact table's logical diagram.

Description of fact-access-joins.gif follows

Description of the illustration fact-access-joins.gif

The table contains a list of the logical level for each dimension table that's directly joined to 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

About the Logical Table Source's Parent-Child Settings

When a logical table is part of a dimension with a parent-child hierarchy that's based on relational tables, the logical table includes both a physical source and a source for the parent-child relationship table required by the parent-child hierarchy.

Parent-child relationship tables explicitly define the inter-member relationships for parent-child hierarchies.

You can view details for the parent-child relationship table source in logical table's Hierarchy tab.

Relationship Table - The name of the parent-child relationship table that the source is based on.
Member Key - The name of the column in the parent-child relationship table that identifies the member.
Parent Key - The name of the column in the parent-child relationship table that identifies an ancestor of the member.
Relationship Distance - The name of the column in the parent-child relationship table that specifies the number of parent-child hierarchical levels from the member to the ancestor.
Leaf Node Identifier - The name of the column in the parent-child relationship table that indicates if the member is a leaf member (1=Yes, 0=No).

See Create Dimensions with Parent-Child Hierarchies.

Define Logical Table Source Data Granularity

Define granularity for the dimension tables and dimension and level information for the fact table joined to the dimension tables. The Oracle Analytics query engine uses this information to find the required level of detail for the requested data.

On the Home page, click Navigator and then click Semantic Models.
In the Semantic Models page, click a semantic model to open it.
Click Logical Layer.
In the Logical Layer pane, browse for and double-click the dimension table with the table source you want to define data granularity for.
In the table's tabs, click Sources.
In the table sources list, click the logical table source that you want to define data granularity for and then click Detail view to open the properties pane.
Scroll to Data Granularity, click the Defined by field, and choose a level. Repeat this step for other dimension tables as needed.
In the Logical Layer pane, browse for and double-click the fact table joined to the dimension tables that you defined data granularity for.
In the table's tabs, click the Sources tab.
In the logical table sources list table, click the logical table source that you want to define data granularity for and then click Detail view to open the properties pane.
Scroll to the pane's Data Granularity section and click Add Level.
In the new level's Dimension field, click the dropdown button and choose a dimension table. In the Level field, click the dropdown button and choose a granularity level. Repeat this step for other dimension tables joined to the fact table.
Click Save.