Data Structures in SQL Server Analysis Services
Part two of a four-part series of blogs

This series of blogs introduces the basic data structures for storing data, including OLTP and OLAP databases.

  1. Types of Data Storage Structures
  2. Comparison of the Two Storage Methods (this blog)
  3. Cubes in SQL Server Analysis Services
  4. Summary of Jargon in SQL Server Analysis Services

Posted by Andy Brown on 16 May 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Comparison of the Two Storage Methods

Before we compare relational databases and the UDM (Unified Dimensional Model), we need to learn some terminology.

OLTP and OLAP

Two database terms that you may hear bandied about are OLAP and OLTP:

Term Stands for What it means
OLTP On-line Transaction Processing This is the term for a standard relational database used for processing day-to-day transactions (hence the name!).
OLAP On-line Analytical Processing The term for analysing data accumulated to spot trends and produce high-level reports. SQL Server Analysis Services used to be called OLAP Services.

A Diagram Showing OLAP versus OLTP

The diagram below shows how the two types of storage structure are typically used:

Two types of developer

Debbie Database and Andy Analyst

Typically data in the UDM is extracted from a company’s relational database, although it can also come from Excel spreadsheets, Oracle databases and many other courses.

To understand the reasoning behind Analysis Services, it helps to imagine that Debbie Database and Andy Analyst never speak to each other, and that Andy Analyst has no rights to make any changes to Debbie’s database.

This blog has 0 threads Add post