The relational implementation of the multidimensional data model is typically a star schema, as shown in Figure, or a snowflake schema. A star schema is a convention for organizing the data into dimension tables, fact tables, and materialized views. Ultimately, all of the data is stored in columns, and metadata is required to identify the columns that function as multidimensional objects. In Oracle Database, you can define a logical multidimensional model for relational tables using the OLAP Catalog or AWXML, as described in Chapter. The metadata distinguishes level columns from attribute columns in the dimension tables and specifies the hierarchical relationships among the levels. It identifies the various measures that are stored in columns of the fact tables and aggregation methods for the measures. And it provides display names for all of these logical objects.
Diagram of a Star Schema
A star schema stores all of the information about a dimension in a single table. Each level of a hierarchy is represented by a column or column set in the dimension table. A dimension object can be used to define the hierarchical relationship between two columns (or column sets) that represent two levels of a hierarchy; without a dimension object, the hierarchical relationships are defined only in metadata. Attributes are stored in columns of the dimension tables.
A snowflake schema normalizes the dimension members by storing each level in a separate table.
Measures are stored in fact tables. Fact tables contain a composite primary key, which is composed of several foreign keys (one for each dimension table) and a column for each measure that uses these dimensions.
Aggregate data is calculated on the basis of the hierarchical relationships defined in the dimension tables. These aggregates are stored in separate tables, called summary tables or materialized views. Oracle provides extensive support for materialized views, including automatic refresh and query rewrite.
Queries can be written either against a fact table or against a materialized view. If a query is written against the fact table that requires aggregate data for its result set, the query is either redirected by query rewrite to an existing materialized view, or the data is aggregated on the fly.
Each materialized view is specific to a particular combination of levels; in Figure, only two materialized views are shown of a possible 27 (3 dimensions with 3 levels have 3**3 possible level combinations).
OLAP Related Interview Questions
|Informatica Interview Questions||Data Warehouse ETL Toolkit Interview Questions|
|PL/SQL Interview Questions||Data Warehousing Interview Questions|
|Testing Tools Interview Questions||SQL Database Interview Questions|
|MySQL Interview Questions||ERP Tools Interview Questions|
|Oracle 11g Interview Questions||Hyperion Financial Management Interview Questions|
|Hyperion Essbase 5 Interview Questions||Database Design Interview Questions|
|Data modeling Interview Questions||Oracle Hyperion Planning Interview Questions|
|Biztalk Esb Toolkit Interview Questions|
OLAP Related Practice Tests
|Informatica Practice Tests||PL/SQL Practice Tests|
|Data Warehousing Practice Tests||Testing Tools Practice Tests|
|SQL Database Practice Tests||MySQL Practice Tests|
|ERP Tools Practice Tests||Oracle 11g Practice Tests|
|Hyperion Financial Management Practice Tests||Hyperion Essbase 5 Practice Tests|
|Database Design Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.