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.

A Database Management System based on relational theory. Most modern Database Management Systems (Oracle, Sybase, Microsoft SQL Server) support a standard language, SQL, however, each database vendor has unique extensions to this standard.

ROLAP is an OLAP architecture based on a relational database. A ROLAP design typically uses a star schema architecture, which optimizes information for query response. ROLAP is ideal for large databases or legacy data for which analyses are infrequent or do not require rapid query response times.

Replication is the physical copying of data from one database to another. In data warehousing, replication takes place when data moves from the OLTP system into the data warehouse. Replication also occurs when a data warehouse feeds one or more data marts.

A database designed for storage of business Meta Data and access by end users and administrators.

See Also: Drill Up, Drill Down, Drill Across

Roll Up is the process of aggregating data to a level higher than the present level of detail. An example of a rollup of ticket line-items would be to summarize line-item revenues to a ticket-level total.

A row is a single record of a database table, view, or query result.

Scalability is the ability to grow or enhance performance to meet an increased demand. In Data Warehouse architecture, it pertains to a design’s ability to support current and future data volumes or concurrent user access.

A Schema is the logical organization of data in a database. Schemas consist of tables, views, indexes, and any other database object needed to define and manipulate a set of data.

Also Known As: Stock Keeping Unit

Also Known As: Drill Up, Drill Down, Drill Across

An SCD is a warehouse dimension whose non-key attributes change over time. There are three implementation strategies to implement SCDs, each based on a business rule. The three available implementations of SCDs are:

• Type 1 – Changes in non-key attributes result in updates to the database record. Previous values are lost. This approach only supports dimensions where history is irrelevant.

• Type 2 – Changes in non-key attributes result in the creation of new version of the database record. Time or sequencing values allow reconstruction of history. This approach is the most commonly used SCD implementation.

• Type 3 – The database record contains multiple columns for specific attributes to store previous or initial value along with current value. Changes in specific non-key attributes result in updates to the database record and updates to the tracking attributes. This approach only supports a fixed number of versions.

A snapshot is a point-in-time representation of data in a system. Databases offer this feature to facilitate replication.

Data Warehouses may represent history by either transactions or snapshots. Transactional representation formulates current business views by aggregating all past history. Snapshot representation contains the entire state at various points in time.

A snowflake is a Normalization of a dimension table within a star schema. In snowflaking, some of the fields of the dimension tables are split off into separate tables. This achieves a higher level of normalization, but makes the database design more complex and can reduce the performance of Business Intelligence Tools.

One application of snowflaking is to enforce referential integrity during data loads.

An operational, production database or a centralized warehouse that feeds into another database.

Sparsity is a measure of cardinality within an entire data set, not just a subset of attributes.

Space is logically reserved in cubes to represent business relationships between all analysis criteria. If business relationships between analysis criteria do not exist, there is an empty location within the cube. In OLAP Cube processing, sparsity is the degree to which the cells of a Cube are populated.

Staging is the process of preparing source data for warehouse loading. The warehouse stage area is a temporary database structure to hold consolidated information before a production data warehouse load process.

Also Known As: Hub and Spoke

The star schema is an intentional simplification of the database design that would be achieved by following the standard rules of normalization. The dimension tables are often de-normalized, to allow for more efficient querying. Another derivation of a star schema is selectively normalizing attributes within a dimension (See snowflaking).

Static Queries are pre-defined SQL statements routinely run against a database. Static Queries may be embedded within automated report or hard-coded SQL statements within a user-interface.

A Subject area is an area of interest to the business. For data warehousing, a Subject Area is a grouping of one or more fact tables and all related dimension tables.

Synchronization is the process of updating one or more sides of a distributed database to accurately mirror each other. Replication business rules define the extent and completeness of synchronization.

A table is a physical grouping of attributes (columns) into a common data set. The data set often contains multiple rows of information that define the state or properties of business objects.

Time-variance is classification of events or object states by time. Data Warehouses typically associate events to the particular version of a business object at the time of the event. For example, a time-variant view of a person keeps all changes undergone by that person and can provide an accurate description of the person at any given point in time.

A view is a logical representation of a table or tables. Views are SQL statements that appear like a table to an end-user. SQL queries referencing a view transparently execute the underlying SQL statement of the view.

Views are used to hide complex table join logic, change column names, or restrict access to columns.

A Virtual Cube is a logical combination of Multi-Dimensional Cubes. Virtual cubes are created to provide analyses across multiple cubes without requiring physical creation of new cubes.

A warehouse key is a primary key unique to a data warehouse that has no meaning to an operational system. Warehouse keys are integer numbers used for efficiency within warehouse dimensions to represent concatenated keys (often a source system primary key plus an effective date).

back to top