Attribute
Characteristic of a business entity, such as name or date of birth.
Examples: In a Payroll datamart, the attributes in the employee table would describe characteristics we would want to know about an employee, such as name, ssn, birthdate
Conformed/Conjoined Dimension
Common dimension tables that allow a data mart to have multiple fact tables.
Example: In a Payroll datamart, the "employee" dimension table is the conforming dimension table between the Pay Detail and Pay Check fact tables.
Data Mart
Relational database using a star-schema architecture; provides subject-oriented collection of data for use in decision support queries.
Examples: HR, Payroll, Undergrad Student Records
Data Warehouse
Collection of all data marts within a business within a single, unified database. Nearly impossible to build in "real life".
Dimension Table
Table that contains a primary key (unique identifier) and attributes (how you are trying to analyze the facts) about a business entity. The data in these tables is usually highly denormalized.
Example: In a Payroll datamart, the "employee" dimension table would contain fields (primary key) like an "employee key" to uniquely identfy a single row in the table and fields (attributes) like "name" or "birthdate".
ETL
Stands for "Extract", "Transform(ation)" and "Load". The process by which data is taken out of a source OLPT system (extract), restructured into formats uable for decision support queries (transformation), and put into target warehouse tables (load).
Fact Table
Central table in a star schema that contains two types of columns: foreign keys (which allow joins with dimension tables) and measures/"facts" (what you are trying to analyze). The data in these tables is usually highly normalized.
Example: In a Payroll datamart, the central "pay detail" table would contain columns (foreign keys) like an "employee key" to connect it to the employee dimension and columns (measures) like "earnings" or "current tax" that you are trying to analyze.
Foreign Key
A field that connects each particular fact to the appropriate value in each dimension
Example: In a Payroll datamart, the employee key on the pay detail table would connect a row in the pay detail table to a row in the employee table, enabling the value of employee name to be retrieved for that pay detail record.
Measure
aka "Fact"; a numeric value such as amount, price, count.
Examples: In a Payroll datamart, the measures in the pay detail table would be numeric values such as earnings, deductions, garnishments and taxes
Meta Data
Information about the data warehouse or data mart system. Meta data encompasses all aspects of the data warehouse or data mart system, including technical, human and data resources.
OLTP
Stands for "On Line Transaction Processing". Operational system that is optimized for rapidly processing individual transactions on line.
Primary Key
A field that connects each particular dimension table to a fact table; a unique identifier of a single row in a table.
Example: In a Payroll datamart, the employee key on the employee table would uniquely identify a single row of information about an employee.
Roll-up
Aggregating data by use of hierarchies within the data. Allows for "drilling" up and down (traveling up and down the hierarchies) within a dimension.
Example: In a dimension table that keeps geographical information, you may have a Country-State-County-City hierarchy that allows you to "roll up" data to do analysis at certain levels in the hierarchy.
Slowly Changing Dimension
A dimension table that changes "slowly" (i.e. less often than daily) and that captures those changes over time.
Snowflake Schema
A central star schema that has additional layers of dimension tables surrounding it (has been more normalized). Increases the complexity to writing queries (more table joins). It is desirable to avoid this type of design for performance purposes.
Star Schema
Simplified database architecture where by a central fact table is encircled by a single layer of related dimension tables. The model resembles a star-shaped picture. Future CU Administrative system data marts intend use this structure.
Example: In a Payroll datamart, the central table might be "pay detail" and some related dimension tables could be "employee", "tax" and "earnings."
SDM
Stands for Student Data Mart. Separate Student Administration-centric database fed from the PeopleSoft via an ETL process from which people can do ad hoc and standard reporting.
Summary Table
A table similar to the fact table, but rolled up on one or more dimension. Should be created when many users need the data at that level.
Examples:
· Accounting - monthly_summary is all transactions for a given month - the keys are account, object_code and timekey rolled to the month.
· Payroll - pay_check is realistically a rollup of pay_detail and therefore a summary table.