Business intelligence terminology

The Business Intelligence terminology that is used in Business Intelligence solution is described here.

Schema: is the logical and physical definition of data elements, physical characteristics, and inter-relationships in a database.

Data Warehouse: is a relational database that is designed for query and analysis, rather than for transaction processing. It usually contains historical data that are derived from the transaction data, and can include data from other sources too. It separates analysis workload from transaction workload, and enables an organization to consolidate data from several sources.

The schema that is designed for transactional processing is referred as online transaction processing (OLTP). The schema that is designed for analysis purposes is called online analytical processing (OLAP).

Data Mart: is also known as local data warehouse. A data mart is a database that has the same characteristics as a data warehouse, but is smaller, and is focused on data for one division or one work group within an enterprise. Following are the data mart objects:

• Dimension Table:

  Is also known as a lookup table or reference table. It contains relatively static data in a data warehouse, and stores the information that is used to satisfy queries. Dimension tables are textual and descriptive and can be used as row headers. Examples of dimension tables are Customers and Products.

• Fact Table:

  Is a large table in the data warehouse schema that stores business measurements. It typically contains facts and foreign keys to the dimension tables, and usually represents numeric and additive data that can be analyzed and examined. Examples of fact tables include Sales, Cost, and Profit.

• Aggregate Table:

  Is also known as a summary table. It contains data that is summarized up to a certain level of detail for improving query performance.

• Star schema:

  Is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star. The center of the star consists of one or more fact tables and the points of the star are the dimension tables. A star schema optimizes performance by keeping queries simple and providing fast response time. All the information related to each level is stored in one row. Star schema helps you analyze the metrics in a fact table by the attributes of the dimensions that are linked to it. For example, to calculate the total sales of a particular product in a quarter, the corresponding fact table is used to derive data. The product and time dimensions provide the contextual information about the particular product and the quarter.

• Snow Flake schema:

  Is a type of star schema in which the dimension tables are either partly or fully normalized.

• ETL

  The online analytical processing (OLAP) database is loaded regularly to facilitate the task of business analysis. To load the database regularly, data from one or more online transaction processing (OLTP) systems must be extracted and copied into the data warehouse. The process of extracting data from source systems and bringing it into the data warehouse is commonly called ETL, which stands for extraction, transformation, and loading.

• Denormalization

  The process of flattening the design of a database by adding redundant data or by grouping data is known as denormalization. A relational normalization database imposes a heavy access load on the physical storage of data even if the database is tuned for optimal performance. In some cases, denormalization helps to resolve these inefficiencies in the relational database.