多维数据库模型与应用05(英文)

EE6435 -Lecture 05 Data Warehouses•Data needed for analysis: typically spread across multiple heterogeneous sources, e.g., operational systems, web•Data warehouse is a repository of integrated data obtained from several sources for multidimensional data analysis• A collection of subject-oriented, integrated, nonvolatile, and time-varying data to support management decisions•Subject-oriented–Data warehouses focus on the analytical needs of different areas of an organization– e.g., in a retail company the analysis may focus on product sales or inventory management•Integrated–Data obtained from several operational and external systems must be joined together•Nonvolatile–Durability of data is ensured by disallowing data modification and removal–A data warehouse gathers data from several years, (typically 5 to 10 years)–Data in operational databases is often kept for only a short period of time (e.g., 2 to 6 months)•Time-varying–Indicates the possibility of retaining different values for the same information, as well as the time when changes to these values occurred•Data Mart–A subset of corporate-wide data that is of value to a specific groups of users–Its scope is confined to specific, selected groups, such asmarketing data mart•From the top-down perspective, data marts are obtained from the data warehouse, a data mart is just a logical view of a data warehouse•From the bottom-up perspective, a data warehouse is built by first building the smaller data marts and then merging them to obtain the data warehouse•Differences between online transaction processing (OLTP) and online analytical processing (OLAP) systems•User Type and Usage–OLTP systems users perform predefined operations through transactional applications (e.g., payroll or ticket reservation systems)–Data warehouse users employ interactive OLAP tools to perform data analysis, for example, to detect salary inconsistencies or most frequent tourist destinations•Data Content and Organization–Data for OLTP systems should be current and detailed, while data analytics in OLAP systems require historical, summarized data•Data structures for OLTP are optimized for rather small and simple transactions,which will be carried out frequently and repeatedly•Access Type–OLTP requires read/write operations, e.g., insert new orders, modify old ones, and delete orders if customers cancel them, thus requiring access to small number ofrecords only–OLAP complex aggregation queries, thus requiring read access to all the records in one or more tables•Access Frequency–OLAP systems are not accessed so frequently as OLTP systems•Response Time–OLTP systems usually have a short query response time–OLAP queries are more complex and can take long time to complete•Concurrency Level–Number of concurrent users in an OLAP system is usually low when compared to the OLTP system–OLTP systems have a high number of concurrent accesses•Require locking or other concurrency management mechanisms to ensure safetransaction processing–OLAP systems are read only, therefore queries can be submitted and computed concurrently•No locking or complex transaction processing requirements•Update Frequency–OLTP systems are constantly updated online; OLAP systems are updated offline periodically•Data Modeling–Data modeling in OLTP•Highly normalized schema, e.g., ER model and its variation•Support frequent transactions•Guarantee consistency and reduce redundancy–Data modeling in OLAP•Multidimensional model•Denormalized schema•High level of redundancy but favors query processingData Warehouse Architecture•General data warehouse usually consists of several tiers•Back-end tier–It contains of the extraction, transformation, and loading (ETL) tools : Feed data into the data warehouse from operational databases and internal and externaldata sources–The data staging area: An intermediate database where all the data integration and transformation processes are run prior to the loading of the data into the data warehouse•Data warehouse tier–It contains an enterprise data warehouse and/or several data marts– A metadata repository storing information about the data warehouse and its contents•OLAP tier–It contains an OLAP server which provides a multidimensional view of the data, regardless the actual way in which data are stored•Front-end tier–It is used for data analysis and visualization–It contains client tools such as OLAP tools, reporting tools, statistical tools, and data-mining toolsTypical Data Warehouse Architecture•Performs Extraction, Transformation, and Loading•It is a three-step process•Extraction–Gathers data from multiple, heterogeneous data sources (can be operational databases or files in various formats)–May be internal or external to the organization•Transformation•Modifies the data from the format of the data sources to the warehouse format; this includes:–Cleaning: Removes errors and inconsistencies in the data and converts it into a standardized format–Integration: Reconciles data from different data sources, both at the schema and at the data level–Aggregation: Summarizes the data obtained from data sources according granularity of the data warehouse•Loading–Feeds the data warehouse with the transformed data, including refreshing the data warehouse–Performs updates from the data sources to the data warehouse at a specified frequency•Data staging area(usually called operational data store)– A database where data extracted from the sources undergoes successive modifications before being loaded into the data warehouse–Increases efficiency of ETL processes and ensures data integrity•It contains an enterprise data warehouse, several data marts, and a metadata repository•Enterprise data warehouse–Centralized and encompassing an entire organization•Data mart– A specialized data warehouse targeted toward a particular functional or departmental area–Data in a data mart can be either derived from an enterprise data warehouse or collected directly from data sources•Metadata repository–Business metadata describes the semantics of the data, and organizational rules, policies, and constraints related to the data–Technical metadata describes how data are structured and stored in a computer system, and the applications and processes that manipulate the data•The metadata repository may contain information such as:–Metadata describing the structure of the data warehouse and the data marts, at the conceptual/logical level (facts, dimensions, hierarchies, ...) and at the physical level (indexes, partitions,...)–Security information (user authorization and access control), and monitoring information (usage statistics, error reports, audit trails)–Metadata describing data sources: schemas, ownership, update frequencies, legal limitations, access methods–Metadata describing the ETL: data lineage, data extraction, cleaning, transformation rules, etc.–Data lineage refers to tracing warehouse data back to the source data from which it was derivedOLAP Tier•It contains an OLAP server, which presents business users with multidimensional data from data warehouses or data marts•Most database products include OLAP extensions and tools allowing building, querying, and navigating cubes, analysis, and reporting•MDX(MultiDimensional eXpressions) is a query language for OLAP databases•Although no standardized language for defining and manipulating data cubes, MDX is supported by a number of OLAP vendors and became a de facto standard for querying OLAP systems•SQL has also been extended for providing analytical capabilitiesFront-End Tier•It contains client tools that allow users to exploit the content of the data warehouse •OLAP tools–Allow interactive exploration and manipulation of the warehouse data–Formulation of complex ad hoc queries•Reporting tools–Enable the production, delivery, and management of reports, which can be paper-based, interactive, or web-based–Reports use predefined queries which asking for specific information in a specific format that are performed on a regular basis•Statistical tools–Used to analyze and visualize the cube data using statistical methods•Data-mining tools–Allow users to analyze data in order to discover valuable knowledge such as patterns and trends–Allow to make predictions based on current data•In some situations, there is only an enterprise data warehouse without data marts, or alternatively, an enterprise data warehouse does not exist• A data mart is typically easier to build than an enterprise warehouse, but there will be problems when several data marts that were independently created need to be integrated into a data warehouse for the entire enterprise •In other situations, an OLAP server does not exist and/or the client tools directly access the data warehouse (indicated by the arrow connecting the data warehouse tier to the front-end tier)•Virtual data warehouse–Extreme situation where there is neither a data warehouse nor an OLAP server–It defines a set of views over operational databases that are materialized for efficient access–It is easy to build but does not provide a real data warehouse solution (does not contain historical data, centralized metadata, etc.)– A virtual data warehouse can severely impact the performance of operational databases•Data staging area may not be needed when the data in the source systems conforms very closely to the data in the warehouse, e.g., only one or a few data sources and the data quality is high。