Dimension Tables – Describe business entities. Lookup or cross-reference tables. Examples include categorical information such as item-lists or department lists.
Fact Tables – Describe business transactions. Detail tables. Include measures that are character or numeric columns of a fact table. A measure can be simple, computed or multi-table. Simple is a single column in a fact table. Computed is an expression including only simple measures. Multi-table is a computed measure that is defined across multiple tables.
Materialized Views(MV)
Used to implement data-warehouses (summaries), distributed computing and mobile computing.
System privileges required to create a MV in your own schema
- CREATE SNAPSHOT or CREATE MATERIALIZED VIEW
- CREATE TABLE
- CREATE INDEX
- CREATE VIEW
System privileges required to create a MV in a different schema
- CREATE ANY SNAPSHOT or CREATE ANY MATERIALIZED VIEW
The QUERY REWRITE privilege is required to enable or disable query rewrite on a MV that references base tables in your own schema. If in another schema, user must have GLOBAL QUERY REWRITE privilege.
Use the CREATE MATERIALIZED VIEW command to create and load a MV. Can specify BUILD IMMEDIATE to load the MV data immediately or BUILD DEFERRED to load the data at a time in the future.
The query used to load a MV contains what Oracle calls the master or detail tables. These tables live in what Oracle calls the master database.
Use DBMS_MVIEW.REFRESH package to populate a MV that has been created with the BULD DEFERRED option.
Can create an MV on an existing summary table by using the PREBUILT option of The CREATE MATERIALIZED VIEW command. This is called “Registration of an Existing MV”.
If a MV is based upon a prebuilt user-defined table, the name of the MV must be the same as the prebuilt table.
Use the DBMS_OLAP.ESTIMATE_SIZE procedure to get an estimate of space required for a MV. MV’s based upon existing tables do not require storage parameters.
The Query rewrite feature is disabled by default. Specify ENABLE QUERY REWRITE in the CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW commands to enable.
MV’s can be updateable only by using the Advanced Replication Feature of Oracle, otherwise they are read-only.
A difference between MV’s and regular views is that MV’s contain both the view definition and the execution results. The results of the view query and the data are stored in a table when an MV is populated. Regular views are just stored queries and no results.
MV’s ON DEMAND refresh options
Complete C – re-runs the defining views SQL query.
Fast F – applies only incremental changes from detail tables
Can not use fast refresh when:
- Multiple table aggregated materialized view and DML has happened on the fact tables since the last full refresh.
- MV has detail relations that are snapshots or views.
- If MV contains the AVG() function and does not contain the COUNT() function.
- If MV contains the VARIANCE() function and does not contain the COUNT() and SUM() functions.
- If MV contains the STDDEV() function and does not contain the COUNT() and SUM() functions.
Force ? – uses the default refresh option. If force is the default option will attempt a fast refresh. If fast refresh is not allowed will do a complete
Always A – always does a complete refresh.
DBMS_MVIEW package ON DEMAND MANUAL refresh procedures
DBMS_MVIEW.REFRESH – refresh one or more MV’s.
DBMS_MVIEW.REFRESH_ALL_VIEWS – refresh all MV’s.
DBMS_MVIEW.REFRESH_DEPENDENT – refresh all MV’s that are table-based and depend upon on a single or list of detail tables.
To use the DBMS_MVIEW package certain queues need to be made available by setting the following initialization parameters
JOB_QUEUE_PROCESSES – set to a value between 1 and 36
JOB_QUEUE_INTERVAL – set to a value between 1 and 3600
UTL_FILE_DIR – directory location of refresh log.
Use the DBMS_OLAP.SET_LOGFILE_NAME to rename the refresh.log file produced by ON DEMAND refreshes.
Monitor the progress of a MV refresh in the V$SESSION_LONGOPS view.
Monitor jobs on each queue in the DBA_JOBS_RUNNING table.
The ALL_MVIEW_ANALYSIS table contains moving avgs for time most recently refreshed and avg time to refresh for both full and incremental types of refreshes.
The ALL_REFRESH_DEPENDENCIES table contains the names of container tables for a MV in your own schema.
MVDATA Export/Import parameter – If set to NO the MV without contents is imported. If set to YES, MV contents are imported.
Summary Advisor
The Summary Advisor uses procedures within the DBMS_OLAP package to gather summary and dimension information. Results, except for estimate MV size, are stored in a table in the dB that can be queried.
The summary advisor collects 3 types of information:
· Summary usage
· Summary recommendations
· Space requirements
Summary management is the process of creating, retaining and dropping summaries (MV’s) using recommendations of the summary advisor.
Must create dimensions to use the summary advisor. Good reason to create them!
Some DBMS_OLAP procedures used for summary management:
Advisory procedures
RECOMMEND_MV – only uses structural stats to make recommendations.
RECOMMEND_MV_W – uses structural and workload stats to make recommendations.
Evaluation procedures
EVALUATE_UTILIZATION -
EVALUATE_UTILIZATION_W – analyzes data collected by the Trace facility and places results in the MVIEWS$_EVALUATIONS table.
Query the ALL_REFRESH_DEPENDENCIES view to find out the names of dependent detail or container tables for summaries in the current schema.
Dimensions
Dimension Tables – Describe business entities. Lookup or cross-reference tables. Examples include categorical information such as item-lists or department lists. A data dictionary structure that is used to define categorized hierarchies that are based on columns that currently exist in tables.
Benefits of creating Dimensions:
- enable query rewrites without the full use of constraints – desirable in a warehouse type of system.
- can explicitly document hierarchies and dimensions.
- Can be used by OLAP tools
- help query rewrite perform complex types of rewrites
- summary advisor uses dimensions to recommend the creation of MV’s based upon the dimension hierarchy
You can roll up (go up a level) or drill down (go down a level) through the levels of a dimension hierarchy.
Dimensions can be based upon multiple tables and dimensions can contain multiple hierarchies. A dimension level can contain up to 32 fields.
Must have CREATE DIMENSION system privilege to create a dimension in your current schema. Need CREATE ANY DIMENSION to create in a different schema.
Related data dictionary views:
USER_DIMENSIONS – contains name, owner, state and revision
USER_DIM_HIERARCHIES – contains owner, dim-name and hierarchy name
USER_DIM_CHILD_OF – level pair hierarchy details
USER_DIM_JOIN_KEY – level join keys
USER_DIM_LEVELS – single level detail
USER_DIM_LEVEL_KEY – column within a level
USER_DIM_ATTRIBUTES – relationship between level field and actual associated table field.
Execute SMDIM.SQL script to create the DEMO_DIM package. The DEMO_DIM package includes procedures to view existing dimensions:
DEMO_DIM.PRINT_DIM - print one dimension
DEMO_DIM.PRINT_ALLDIMS - print all dimensions
Use the DBMS_OLAP.VALIDATE_DIMENSOIN procedure to validate a dimension.
No comments:
Post a Comment