Dimensional database

Dimensional database

A dimensional database is one which, rather than representing data in multiple relations (as a relational database does), represents key data entities as different dimensions. That is, multidimensional database systems offer an extension to the relational system to provide a multi-dimensional view of the data (Rand). For example, in multi-dimensional analysis, data entities such as products, regions, customers, dates etc. may all represent different dimensions. This intrinsic feature of the database structure will be covered in depth in subsequent sections of this article.

Description

Dimensional database systems allow multidimensional data to be modelled natively. Or they can be modelled using the star schema or snowflake schema.

The star schema is a means of storing data based on a set of known database dimensions, attempting to store a multi-dimensional data structure in a database management system. A star schema model is a representation of a central fact table with foreign keys to many dimension tables. The snowflake schema is a normalized implementation of dimensional data with foreign keys in the primary dimension tables referencing additional dimensional data. A snowflake does not increase the dimensionality of the model as the dimensionality (or grain) is defined by the dimensional foreign keys in the fact table. Use of snowflakes in a relational dimensional model is generally discouraged as it can have a significant impact on query performance. Normally snowflakes are eliminated by denormalizing the 'outlying' dimensional data into a primary dimension table.

History

The relational database model uses a structure of attributes within tuples within relations to represent data (relations are erroneously referred to as tables in SQL-DBMSs). Tables can be linked by common key values. Edgar F. Codd first designed this model in 1970, while working for IBM, and its simplicity revolutionized database usage at the time. Codd's work was in many ways ahead of its time, as computing power could not support the overheads of his database system (Hasan 1999).

In the 1980s the power of computers had grown to the point where these overheads were no longer a problem, and today relational database management systems (RDBMS) are available on local desktops, as well as large organisational database management servers.

Why use dimensional databases?

Entity-Relationship (ER) modelling and the structuring of data into normalised tables have become popular and widely standardized among trained database administrators and designers, who routinely use relational DBMS to store huge volumes of organizational data with very high transaction rates.

Although deceptively simple to design and operate, ER modelling proves problematic for the non-technical end-user designing and executing queries. Accessing data may require complex joins of many tables and the untrained end-user may be forced to hire IT professionals to design the complex data queries using a query language such as SQL. When queries modifying data or table structures (e.g., INSERT, DELETE and ALTER TABLE) are executed, the probability of producing errors or undesirable consequences are dramatically increased.

In a multi-dimension database system, the data is presented to the user as a hypercube or multi-dimensional array, where each individual data value is contained within a cell accessible by multiple indexes. The multi-dimensional array structure represents a higher level of organization than the relational table. The structure itself represents a more intelligent view of the data it contains, because perspectives of the data are embedded directly into the structure as dimensions, rather than being placed into fields.

Advantages

Apart from the inherent advantages of using a multi-dimensional array structure, multi-dimensional databases also contain the following advantages.

; Enhanced Data Presentation and Navigation : Intuitive spreadsheet-like views of the data are the output of multi-dimensional databases. Such views are difficult to generate in relational systems without the use of complex SQL queries, while others cannot be performed by standard SQL at all, eg. top ten exam results.; Ease of Maintenance : Multi-dimensional databases are very easy to maintain, because data is stored in the same way as it is viewed, that is according to its fundamental attributes, so no additional computational overhead is required for queries of the database. Compare this to relational system, where complex indexing and joins may be used that require significant maintenance and overhead.; Increased Performance : Multi-dimensional database achieve performance levels that are well in excess of that of relational systems performing similar data storage requirements. These high performance levels encourage and enable OLAP applications. Performance can be improved in relational systems through database tuning, but the database cannot be tuned for every possible on-the-fly query. In relational systems, tuning is quite specific, therefore decreasing flexibility, and also requires expensive database specialists.

In summary, multi-dimensional database systems are a complementary technology to entity relational systems, and in some circumstances it makes more sense to use multi-dimensional arrays rather than relational tables.

Where multi-dimensional systems excel over their relational system counterparts is in the area of data presentation and analysis, where the data in question leads itself to being suitable for multi-dimensional systems, such as where complex inter-relationships exist.

The top-level views of data over many combinations of dimensions make multi-dimensional systems particularly useful for trend analysis over time by management staff of organizations, due to the ease of viewing the data in a more naturally intuitive way.

ee also


Wikimedia Foundation. 2010.

Игры ⚽ Поможем написать курсовую

Look at other dictionaries:

  • Dimensional modeling — (DM) is the name of a set of techniques and concepts used in data warehouse design. It is considered to be different from entity relationship modeling (ER). Dimensional Modeling does not necessarily involve a relational database. The same… …   Wikipedia

  • Dimensional Markup language — (DML) is an XML format definition tailored to the needs of dimensional results for Discrete manufacturing. The purpose is to haul the results between applications that generate or use dimensional information. A typical scenario is where an… …   Wikipedia

  • Database design — is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a Data Definition Language, which… …   Wikipedia

  • Database model — A database model is the theoretical foundation of a database and fundamentally determines in which manner data can be stored, organized, and manipulated in a database system. It thereby defines the infrastructure offered by a particular database… …   Wikipedia

  • Database models — A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join. Although these operations may not… …   Wikipedia

  • Database — A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality (for example, the availability of rooms in hotels), in a way that supports… …   Wikipedia

  • Database normalization — In the design of a relational database management system (RDBMS), the process of organizing data to minimize redundancy is called normalization. The goal of database normalization is to decompose relations with anomalies in order to produce… …   Wikipedia

  • Database management system — A database management system (DBMS) is a software package with computer programs that control the creation, maintenance, and the use of a database. It allows organizations to conveniently develop databases for various applications by database… …   Wikipedia

  • Database system — A database system is a term that is typically used to encapsulate the constructs of a data model, database Management system (DBMS) and database.[1] A database is an organised pool of logically related data. Data is stored within the data… …   Wikipedia

  • Table (database) — In relational databases and flat file databases, a table is a set of data elements (values) that is organized using a model of vertical columns (which are identified by their name) and horizontal rows. A table has a specified number of columns,… …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”