Relational VS MultiDimensional (OLAP) Databases

The key difference between OLAP dimensions and simple relational dimensions is the central role played by hierarchies in OLAP implementations.An OLAP dimension is strongly structured around its hierarchies, and the metadata of a cube definition includes the hierarchical levels.
This is one of the great strengths of an OLAP implementation.

OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.

The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.

Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server

Example explaining the advantage of Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.

Advantages are as follows
1. It provides an intuitive user interface for browsing data.
2. It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3. Parent-child dimension structures are easy and intuitive to implement.
3. It gives you server-defined rules for handling semiadditive and nonadditive measures.


The above explanation holds good for SSAS vs Sql Server Relational, Sql Server Relational vs Microstrategy,

Fact and Dimenisional Tables Relationships

Star schema, which maintains one-to-many relationships between dimensions and a fact table is widely accepted as the most viable data representation for dimensional analysis.

Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.

Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.

Two ad-hoc methods to resolve

1. Denormalizing the dimension tables.
2. Building the Bridge Table.

To find more information : go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling

Dimensional Modeling - Design Changes

The following changes are anticipated to the design after the data warehouse is up and running

1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.

2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.

3. Adding new, unanticipated dimensional attributes.

4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

--------------------------------------------------------------