An Ad-Hoc Query is a database query (or request for information) that is dynamically entered by a database user. Static queries that exist in production reports or provide data results for user-interface screens are not considered Ad-Hoc Queries.

An Agent is an application or automated routine that searches data sets for pre-defined thresholds or patterns. Upon encountering a targeted condition, the Agent performs a set action to report or record the event (also called an Alert). Examples of resulting actions include report generation, e-mail notification, and trigger execution.

An Aggregate is summary information stored in a Data Warehouse. Instead of repeatedly summarizing the same information to perform multiple analyses, information in a warehouse may be pre-summarized for performance. The two primary uses for Aggregates include:

• Saving storage space - Data warehouses can get very large. The use of aggregates greatly reduces the space needed to store data. Historical information aggregation saves storage when details are not relevant or required.

• Improving Performance – Less I/O means less processing time.

Warehouses must contain the lowest level of detail that is required to support specific analyses. Query Redirection, Materialized Views, or manually User Interface coding provides access to the level of detail requested by end-users. Having both the low-level and summarized information available provides the fastest query response time for the information There are five aggregate functions defined in standard SQL (Structured Query Language): SUM, COUNT, MIN (the lowest value), MAX (the highest value), and AVG (the average value).

Also Known As: Materialized View

• A store manager can be automatically informed when a certain product's sales fall below or rise above a specified range.

• A factory manager can be automatically informed when the failure rate of a product exceeds a specified level.

• A sales manager can be automatically informed when a member of his staff achieves a personal high level of sales for a time period.

See Data Model

Business Intelligence Tools are software programs enabling business users to see and use large amounts of complex information. Classes of BI tools include:

Cardinality is the degree of even distribution of values across a specific table column. Low cardinality distributions contain few unique values over a set or subset of rows. High cardinality distributions contain many unique values. A unique primary key represents the highest level of cardinality.

Client/Server is a distributed technology architecture where process load is distributed between two sides of a transaction. Software on a client (desktop PC) is responsible for much of the user-interface rendering activities. The server component is only responsible for providing the data sets needed by the client component.

A Dashboard provides an executive view of specific Key Performance Indicators within an organization. Dashboards typically display high-level measurements and do not provide drill-down capability.

Information is what a person is able to understand or deduce about data based on the available metadata.

Knowledge is the result of combining information with experience and is what a business uses to make decisions. The process of organizing information in such a way as to create data-based knowledge is called Data Warehousing. Business Intelligence Tools are software products that present knowledge to business users.

Data Marts may also be created after a Data Warehouse for a number of accessibility and performance reasons.

Data Mining is the process of finding hidden patterns and relationships in the data. Analyzing data involves the recognition of significant patterns and relationships. At a simple level, Business Analysts recognize relationships between common business objects such as: customer, product, and time relating to a specific sales transaction. Business Analysts can effectively see patterns in small data sets where viewing all factors simultaneously is possible. As information volumes grow, however, there is a need for complex analyses to identify patterns and relationships that are not apparent. Data Mining tools interrogate information and identify relationships that may become very strategic when planning campaigns or profiling behavior.

An Entity within a data model consists of logical groupings of attributes. Entities of a Physical Data Model directly corresponds to a table or view within a database system. Meta data for each attribute and entity for a data warehouse project should be contained within the data model.

The process of checking the quality of the data being imported into the data warehouse.

Data quality assurance is one of the greatest challenges in the process of data warehousing. If the data-based knowledge generated by the data warehouse is to be trusted, the data entered into the warehouse must be complete and accurate - "garbage in, garbage out".

Data quality can be a challenge for several reasons:

A significant portion of time in the development process should be set aside for setting up the data quality assurance process and implementing whatever data cleansing is needed.

In a production environment, there should be a data quality report generated after each data warehouse import. There should be provision for rolling back an import if data quality testing indicates that the data is unacceptable.

The term “Data Warehouse” was coined by Bill Inmon in 1990 as a database that is a subject-oriented, integrated, time-variant and non-volatile collection of data to support a decision making process. Specific definition of these terms is:

This enables management to gain a consistent picture of the business.

The DBMS is the software used to store, access, and manage data. Examples of these software include Oracle, SQL Server, and Sybase. There are two main types of Database Management Systems used for business intelligence and data warehousing - specialized Multi-Dimensional Database Management Systems (MDBMS) and the more widely used general purpose Relational Database Management Systems (RDBMS).

Drilling down and drilling up allow an analyst to move down and up levels of summary to see how the information at the various levels is related. A powerful use of this capability is to view information at its highest level of summarization to identify obvious trends in the information. To further investigate the cause of a trend a user simply drills down one or more levels to uncover the detail behind the summarized information

End users are the business users that interface to information through a front-end program or set of reports.

An EIS is a computer system or user-interface that presents a summary of a company's important data on a routine basis.

Granularity is the level of detail of facts stored in a data warehouse. The granularity of the fact table is one of the most significant design decisions in creating a data warehouse. Facts should be at an aggregation level low enough to support the lowest level of analysis. For example, if data warehouse requirements state it is necessary to summarize product SKU sales, the fact table granularity must be at an invoice line-item level, not the invoice ticket level.

A hierarchy is a relationship between attributes in a table consisting of sets and sub-sets. Hierarchies allow traversing of aggregation levels within data by querying with SQL using a GROUP BY clause. Moving between the levels of a hierarchy is called drilling up and drilling down.

HOLAP is a hybrid architecture that combines the advantages of Relational OLAP (ROLAP) and Multi-Dimensional OLAP (MOLAP) architectures. In HOLAP architecture, aggregated levels of hierarchies are stored in MOLAP cubes for query efficiency. Detail information (data at the lowest level of aggregation) that would be too cumbersome to store in cubes resides in ROLAP architecture in the production data warehouse. HOLAP provides connectivity to the large data sets in the relational database while taking advantage of the faster performance of the multi-dimensional aggregation storage.

Also Know As: Cube

Two common types of indexing strategies used by RDBMSs are Btree and Bitmap. Btree indexes are tree-like data structures sorted by the index key field(s). Theoretically, the tree structure has many branches to minimize the number of comparisons necessary to reach a leaf node. Bitmap indexes are effective for columns with low cardinality distribution of values. A bitmapped index contains a record for each record in the table. The bit count in a bitmap index record matches the number of distinct values in the column(s) indexed (plus overhead). Binary comparisons of bitmaps make searches extremely fast.

See Also: Source Database

A Legacy System is an established computer system that is an integral part of a company’s daily business operations. One of the biggest challenges of the data warehousing process is to extract and consolidate data from a variety of legacy systems through out an enterprise.

Also Known As: Aggregate Navigation

A Materialized view is an Oracle RDBMS feature that provides summary-level information using a database snapshot. Once created, Materialized Views use Query Redirection to transparently process a query request in the most efficient manner.

Also Known As: Fact

Meta Data is information describing the application, constraints, source, transformation, meaning, units, relationships, and relevance of data in the warehouse. A common definition of Meta Data is that it is data about data. In Data Warehousing, Meta Data is any information about a database attribute needed to support and manage the operation of the data warehouse.

Also Known As: OLAP (On-Line Analytical Processing)

Also Known As: Cube

An MDBMS is a database management system designed specifically to support efficient storage and retrieval of OLAP data. The MDBMS interfaces with or contains a user-interface that supports drilling through hierarchies in the data. The structure where an MDBMS stores information is a cube.

Also Known As: OLAP

A Natural Key is a primary key within a source system. Natural keys cannot always be primary keys in a data warehouse dimension because multiple versions of a record may need to be stored.

Non-Volatile data is data that does not change over time. Most all data in a data warehouse is non-volatile. Since data warehouses record and reconstruct history, changes to existing data are rare. Non-volatility is one of the original defining characteristics of a data warehouse.

Normalization is the process of organizing data to eliminate redundancy. In OLTP systems, normalization enforces referential integrity, minimizes storage requirements, and optimizes the data insertion performance.

The downside of normalization is the increased overhead required to assemble data sets at query time. An example of a normalized design is a contact database where addresses are stored in a separate table from contact name. Two contacts with the same address would join to a single address record rather than the address information duplicated in the same table as the contact name.

Normalization exists in Data Warehouse Fact Tables. Dimension Tables in a data warehouse de-normalized information to increase query performance.

The term OLAP distinguishes data warehousing activities from On-Line Transaction Processing (OLTP) business activities. In its broadest usage, OLAP is a synonymous to data warehousing. In a narrow usage, OLAP refers to the tools used for Multi-Dimensional Analysis.

The OLAP process organizes and aggregates data into levels of business information (facts) and reporting these levels by business classifications (dimensions). A spreadsheet is a two-dimensional analysis tool. OLAP provides spreadsheet-like analysis simultaneously spanning as many dimensions as the business needs to support. Several software packages facilitate manipulating and displaying this information that is too complex to represent in two-dimensions.

OLTP is a collective term encompassing the operations and systems that process transactions as part of a company’s daily business activities. OLTP systems (Also Known As: Legacy) are the information sources for data warehouses.

An ODS is an integrated database of operational data. ODS sources may include OLTP systems or any system containing current or near-term data. One qualifier of an ODS is data volatility (frequent updates) and the absence of historical information.

An ODS is a resource for operational reporting. Often, an ODS is created to consolidate information from multiple sources into a repository that is used only for short-term reporting. Once consolidated, the information in an ODS can be used as a source for a data warehouse system.

Also Known As: OLTP system.

Parsing is the act of translating commands into a language native to an interfacing system. In SQL, English commands are parsed into the host RDBMS by an interpreter. The query is translated, appropriate information retrieved, and results are returned to the user.

A table used to summarize data and rotate rows and column titles to obtain different views of the data. Rows can be “layered” to reflect the different levels of data that can be viewed.

A primary key is a column or columns of a data set containing as many distinct values as there are rows. Most tables in a relational database contain a single primary key.

A Query is a request for information asked of a RDBMS. The result of a query is typically a single row or set of rows from a table.

Query Redirection is an Oracle feature that automatically changes a user’s SQL statement to increase efficiency. This operation is done when the query optimizer determines that a materialized view or other table can process a query request with less overhead than the requested table. A practical example of query redirection is a user query that aggregates a base table and groups by a particular column. If a materialized view exists for the same table that has the same query summarized already, the query optimizer accesses the materialized view instead of the base table.

• RAID 0 – Provides non-redundant disk striping. Each disk is divided into smaller units (stripes) and stripes from multiple disks are concatenated into logical volumes. The benefit of RAID 0 is Input/Output is dispersed across multiple disks, thus removing the chance of performance problems due to disk saturation. Sequential read and write operations are performed well, but random read operations may be slower. The liability of RAID 0 is no fault tolerance.

• RAID 1 – Provides exact mirroring of one disk to another for fault tolerance. This approach allows one disk to fail while the data integrity remains intact. The benefits of RAID 1 are read performance (since smart controllers can retrieve the data sectors on the disk closest to the present head location) and complete redundancy. The liability of RAID 1 is the cost to implement because twice the physical disks are needed.

• RAID 01 or 10 – RAID 10 is a combination of RAID 0 and RAID 1 (striping with mirroring). This approach does not use any more disks than RAID 1, but provides performance gains with fault tolerance.

• RAID 2 – No longer used, technology was improved and replaced by RAID 3.

• RAID 3 – Provides RAID 0 striping with redundancy via parity checking instead of mirroring. The striping is performed at the byte-level. In RAID 3, a single disk is dedicated to storing parity information. If any single disk fails, the data can be reconstructed from the remaining disk using an XOR (exclusive OR) operation. Besides the redundancy, benefits of RAID 3 include superior performance doing random read operations. The liability of this approach is the additional write overhead to maintain the parity disk.

• RAID 4 – Similar to RAID 3 except that the striping is performed at the block-level.

• RAID 5 – Combines RAID 0 and RAID 4 techniques to provide block striping with redundancy via parity checking but instead of having a dedicated parity disk, the parity may exist on any of the disks in the stripe. The benefit of this approach is disk economy (as opposed to full mirroring) with security. The liability of this approach is the additional write overhead (especially for sequential write operations) to maintain the parity, but this liability is spread across all the disks in the stripe.

• RAID 6 – Extends RAID 5 to contain two distributed parity disks. Having two parity disks allows for dual disk failure with no loss of data integrity. The liability of this approach is that it takes three writes to save one block of information.

• RAID 7 or RAID S – Hardware implementations of RAID 5 with improved performance.

Referential Integrity is the process of ensuring that relationships between database objects are valid and remain valid. Enforcement of RI rules occurs automatically by the RDBMS during key field insertion, update, or delete.