CS 350 Chapter 9
Conformed dimension
One or more dimension tables associated with two or more fact tables for which the dimension tables have the same business meaning and primary key with each fact table
Describe the characteristics of a surrogate key as used in a data warehouse or data mart
- business keys change, often slowly, over time, and we need to remember old and new business key values for the same business object. Surrogate keys allow us to handle changing and unknown keys with ease - Using a surrogate key also allows us to keep track of different nonkey attribute values for the same production key over time - Surrogate keys are often simpler and shorter, especially when the production key is a composite key - Surrogate keys can be of the same length and format for all keys, no matter what business dimensions are involved in the database
ETL
- extracting, transforming, and loading - an important process in which extracts, transforms, and loads data from source systems into the data warehouse - extracting happens periodically. The data warehouse doesn't need to have current data
Trends that encourage the need for data warehousing
- no single system of record - multiple systems are not synchronized - organizations want to analyze the activities in a balanced way - customer relationship management - supplier relationship management
Two types of tables in star schema
- one fact table: contains factualy or quatitative data about a business - one or more dimensional tables: hold descriptive data about the subjects of the business
List four objectives of derived data
- provide ease of use for decision support applications - provide fast response for predefined user queries or requests for information - customize data for particular target user groups - support ad hoc queries and data mining and other analytical applications
Objectives of derived data
- provide ease of use for decision support applications - provide fast response for predefined user queries or requests for information - customize data for particular target user groups - support ad hoc queries and data mining and other analytical applications
List five claimed limitations of independent data marts
- you lose flexibility for the long term and the ability to react to changing business conditions - technical limitations for the size of the data warehouse
Two major factors that drive the need for data warehousing
1. A business requires an integrated, company wide view of the high quality information 2. The information systems department must separate informational from operation systems to improve performance dramatically in managing company data
Changes to be made to a warehouse data model
1. New descriptive attributes 2. New business activity attributes 3. New classes of descriptive attributes 4. Descriptive attributes become more refined 5. Descriptive data are related to one another 6. New source of data
Two debates as to the actual value of independent data marts
1. One debate deals with the nature of the phased approach to implementing a data warehousing environment. The essence of this debate is whether each data mart should or should not evolve in a bottom-up fashion from a subset of enterprise wide decision support data 2. The other debate deals with the suitable database architecture for analytical processing. This debate centers on the extent to which a data mart database should be normalized
Three layered architecture
1. Operational data are stored in the various operational systems of record throughout the organization 2. Reconciled data are the type of data stored in the enterprise data warehouse and an operational data store. Reconciled data are detailed, current data intended to be the single, authoritative source for all decision support applications 3. Derived data are the type of data stored in each of the data marts, Derived data are data that have been selected, formatted, and aggregated for end user decision support applications
Essential rules of dimensional modeling
1. Use atomic facts 2. Create single-process fact tables 3. Include a date dimension for every fact table 4. Enforce consistent grain 5. Disallow null keys in fact table 6. Honor hierarchies 7. Decode dimension tables 8. Use surrogate keys 9. Conform dimensions 10. Balance requirements with actual data
Factors the lead to the need to separate operational and informational systems
1. a data warehouse centralizes data that are scattered throughout disparate operational systems and makes them readily available for decision support applications 2. A properly designed data warehouse adds value to data by improving their quality and consistency 3. A separate data warehouse eliminates much of the contention for resources that results when informational applications are cofounded with operational processing
Three types of metadata
1. operational metadata: describe the data in the various operational systems that feed the enterprise data warehouse. Operational metadata typically exist in a number of different formats and unfortunately are often of poor quality 2. Enterprise data warehouse metadata: are derived from the enterprise data model. EDW metadata describe the reconciled data layer as well as the rules for extracting, transforming, and loading operational data into reconciled data 3. Data mart metadata: describe the derived data layer and the rules for transforming reconciled data to derived data
Enterprise data warehouse (EDW)
A centralized, integrated data warehouse that is the control point and single source of all data made available to end users for decision support applications
Logical data mart
A data mart created by a relational view of a data warehouse. They are not physically separate databases but rather different relational views of one physical, slightly denormalized relational data warehouse
Dependent data mart
A data mart filled exclusively from an enterprise data warehouse and its reconciled data
Independent data mart
A data mart filled with data extracted from the operational environment, without the benefit of a data warehouse
Subject-oriented
A data warehouse is organized around the key of subjects of the enterprise. Major subjects may include customers, patients, students, products, and time
Data mart
A data warehouse that is limited in scope, whose data are obtained by selecting and summarizing data from a data warehouse or from separate extract, transform, and load processes from source data systemd
Star schema
A simple database design in which dimensional data are separated form fact or even data. A dimensional model is another name for a star schema
Data warehouse
A subject-oriented, integrated, time-variant, nonupdateable collection of data used in support of management decision-making processes
Informational system
A system designed to support decision making based on historical point-in-time and prediction data for complex queries or data-mining applications
Operational system
A system that is used to run a business in real time, based on current data
Real-time data warehouse
An enterprise data warehouse that accepts near-real-time feeds or transactional data from the systems of record, analyzes warehouse data, and in near-real-time relays business rules to the data warehouse and systems of record so that immediate action can be taken in response to business events
Snowflake schema
An expanded version of a star schema in which dimension tables are normalized into several related tables
Operational data store (ODS)
An integrated, subject-oriented, continuously updateable, current-valued enterprise-wide, detailed database designed to serve operational users as they do decision support processing
Data warehoues vs data mart
Data Warehouse: Scope: - application independent - centralized, possibly enterprise-wide - planned Data: - historical, detailed, and summarized - lightly denormalized Subjects: - multiple subjects Other characteristics: - flexible - data oriented - long file - large - single complex structure Data Mart: Scope: - specific DSS application - Decentralized by user area - Organic, possibly not planned Data: - some history, detailed, and summarized - highly denormalized Subjects: - one central subject of concern to users Sources: - few internal and external sources Other characteristics: - restrictive - project oriented - short file - starts small, becomes large - multi, semi complex structures, together complex
Nonupdateable
Data in the data warehouse are loaded and refreshed from operational systems but cannot be updated by end users
Time-variant
Data in the data warehouse contain a time dimension so that they may be used to study trends and changes
Transient data
Data in which changes to existing records are written over previous records, thus destroying the previous data content
Periodic data
Data that are never physically altered or deleted once they have been added to the store
Derived data
Data that have been selected, formatted, and aggregated for end-user decision support application
Reconciled data
Detailed, current data intended to be the single, authoritative source for all decision support applications
Operational vs. Informational Systems
Operational: - Primary purpose: run the business on a current basis - Type of data: current representation of state of the business - Primary users: clerks, salespersons, administrators - Scope of usage: narrow, planned, and simple updates and queries - Design goal: performance - Volume: many constant updates and queries on one or a few table rows Informational: - Primary purpose: support managerial decision making - Type of data: historical point-in-time and predictions - Primary users: managers, business analysts, customers - Scope of usage: broad, ad hoc, complex queries and analysis - Design goal: ease of flexible access and use - Volume: periodic batch updates and queries requiring many or all rows
Explain the pros and cons of logical data marts
Pros: a. New data marts can be created quickly because no physical database or database technology needs to be acquired or created. Also, loading routines do not need to be written. b. Data marts are always up-to-date because data in a view are created when the view is referenced. Views can be materialized. Con: Logical data marts are only practical for moderate-sized data warehouses or when high performance data warehousing technology is used.
List four characteristics of a data warehouse
Subject-oriented, integrated, time-variant, and nonupdateable
Integrated
The data housed in the data warehouse are defined using consistent naming conventions, formats, encoding structures, and related characteristics gathered from several internal systems of record and also often from sources of external to the organization
In what ways are dimension tables often not normalized?
The fact table plays the role of a normalized n-ary associative entity that links the instances of the various dimensions, which are in second, but possibly not thid, normal form. Most experts view this as acceptable because dimensions are not updated and avoid costly joins
Grain
The level of detail in a fact table, determined by the intersection of all the components of the primary key, including all foreign keys and any other primary key elements
Briefly describe the major components of data warehouse architecture
The major components include: source data systems, data staging area, data and metadata storage area, and end-user presentation tools
Data warehousing
The process whereby organizations create and maintain data warehouses and extract meaning from and help inform decision making through the use of data in the data warehouses
Differences between transaction and reporting/analytic data warehousing
Transaction = A discrete unit of work that must be completely processed or not processed at all - May involve multiple updates - If any update fails, then all other updates must be cancelled - Transaction properties - Atomicity (works in its entirety or not at all) - Consistency (leaving the database in a consistent state) - Isolation (clearly separate from others) Durability (results kept permanently)
Is star schema a relational model? Explain why or why not
Yes because each dimension table has a one to many relationship with the central fact table
