What is OLAP?
¡ Basic idea: converting data into information that decision makers need
¡ Concept to analyze data by multiple dimension in a structure called data cube
History
Edgar Frank |
¡ In 1993, Edgar Frank came up with the term online analytical processing (OLAP) and proposed 12 criteria to define an OLAP database
¡ Edgar Frank "Ted" Codd (August 23, 1923 – April 18, 2003) a British computer scientist who, while working for IBM, invented the relational model for database management, the theoretical basis for relational databases.
¡ The term OLAP seems perfect to describe databases designed to facilitate decision making (analysis) in an organization
Purpose of OLAP
¡ To derive summarized information from large volume database
¡ To generate automated reports for human view
Why need OLAP over Relational Database
¡ Consistently fast response
¡ OLAP obtains a consistently fast response is by prestoring calculated values
¡ Metadata-based queries
¡ Provide analysis functions that are difficult or impossible to express in SQL
¡ SQL was developed primarily for transaction systems, not for reporting applications
¡ Spreadsheet-style formulas
¡ design the data structure with users in mind.
¡ Spreadsheets are key components of business management because they are intuitive to create
Star Schema
Ralph Kimball |
§ Cubes are easily stored in relational databases, using a denormalized data structure called the star schema, developed by Ralph Kimball
- Ralph Kimball (Born 1944) is an author on the subject of data warehousing and business intelligence.
- He is widely regarded as one of the original architects of data warehousing and is known for long-term convictions that data warehouses must be designed to be understandable and fast. His methodology, also known as dimensional modeling or the Kimball methodology.
§ Cubes are easily stored in relational databases, using a denormalized data structure called the star schema, developed by Ralph Kimball
- Ralph Kimball (Born 1944) is an author on the subject of data warehousing and business intelligence.
- He is widely regarded as one of the original architects of data warehousing and is known for long-term convictions that data warehouses must be designed to be understandable and fast. His methodology, also known as dimensional modeling or the Kimball methodology.
§ A star schema classifies the attributes of an event into facts (measured numeric/time data), and descriptive dimension attributes (product ID, customer name, sale date) that give the facts a context. A fact record is the nexus between the specific dimension values and the recorded facts. The facts are stored at a uniform level of detail (the grain) in the fact table. Dimension attributes are organized into affinity groups and stored in a minimal number of dimension tables.
OLAP Inplementation
§ Multidimensional OLAP (MOLAP)
- The database is stored in a special, usually proprietary, structure that is optimized for multidimensional analysis.
- very fast query response time because data is mostly pre-calculated
- practical limit on the size because the time taken to calculate the database and the space required to hold these pre-calculated values
§ Relational OLAP (ROLAP)
- The database is a standard relational database and the database model is a multidimensional model, often referred to as a star or snowflake model or schema.
- more scalable solution
- performance of the queries will be largely governed by the complexity of the SQL and the number and size of the tables being joined in the query
§ Hybrid OLAP (HOLAP)
- The database is a standard relational database and the database model is a multidimensional model, often referred to as a star or snowflake model or schema.
- more scalable solution
- performance of the queries will be largely governed by the complexity of the SQL and the number and size of the tables being joined in the query
- A hybrid of ROLAP and MOLAP
- can be thought of as a virtual database whereby the higher levels of the database are implemented as MOLAP and the lower levels of the database as ROLAP
Post a Comment