0845 643 64 63

OLAP cubes

What is an OLAP Cube / Analysis Services?

OLAP, or 'On-Line Analytical Processing', is a way to quickly provide answers to complex analytical queries. At the heart of any OLAP system is an OLAP cube, which is essentially a multi-dimensional database designed specifically for high performance reporting systems. In the Microsoft SQL Server world, OLAP cubes are built in SQL Server Analysis Services (SSAS).

 

Is it a Database?

Yes and No!

It does store data, however it has a very different structure to a 'normal' database. Instead of using the SQL Server relational database engine, OLAP cubes use the SQL Server Analysis Services (SSAS) multi-dimensional engine.

OLTP Database
OLAP Database
An OLAP cube database is optimised for analytical processes. It is designed to report on millions of records at a time, providing totals, averages, period on period comparisons etc., analysing and comparing the results across any combination of business attributes you want.
A standard database (OLTP) is optimised for transactional processing (OnLine Transactional Processing). It is designed to ensure individual entries or records can be inserted and edited quickly and efficiently.
 

Multi-Dimensional

Historically databases have been structured according to the needs and requirements of the IT systems that use them. OLAP cubes however are designed with the business in mind. Their single purpose is to provide advanced, quick information to the business, in the way that the business wants and can use. They are therefore designed using business logic and understanding.

Each 'dimension' represents a business entity that is required in the reports. This may be product, employee, customer, warehouse, time, supplier etc. The cube stores a number of 'facts' or 'measures' such as sales, costs, tax, profit, stock level, marketing spend and production costs. Each of these can be viewed using any combination of the dimensions.

Think of your business data as a Rubik's cube; imagine being able to 'slice and dice' your data, using any combination of filters or demographics, comparing one thing against another. Rotate a side to see your data in a different way, compared against a different dimension.

This approach to modelling your business data results in a system that your employees can understand easily, they can ask it questions in plain English rather that having to understand the technical architecture of a database.

Cube

"What was my gross net profit ratio this month compared to this month last year?"

"How are my YTD sales in region X compared against region Y, for product groups A, B and C?"

"Has my sale growth to ABC1 customers increased or decreased in response to promotion X?"

Each dimension of the OLAP cube represents an understandable business concept, such as product, time, department, salesman, account etc. etc. Users are therefore able to understand the query without having to learn complex normalised database structures.

Nigel Pendse from The OLAP Report has suggested that an alternative and perhaps more descriptive term to describe the concept of OLAP is 'Fast Analysis of Shared Multidimensional Information (FASMI)'.

 

Time Based Analytics

AnalyticsA key element of functionality of cubes is their ability to understand company structures and logic, for example fiscal and calendar accounting periods. Data can be viewed and mapped by a number of different time catagorisations, depending on whether the user is interested in calendar months or period ends, or even your customer's or supplier's calendars.
This understanding of time extends to analytical tools such as year to date, year on year, period on period, daily and even hourly analysis.

 

Performance

The cube already knows the answers to any question you want to ask it, you don't have to wait for it to churn through years worth of data. Most questions can be answered almost instantly.
This works by pre-calculating different combinations of values when the cube is processed. By only doing this once, the results can be returned many times with very little work. Raw data as well as complex calculations are pre-aggregated to different levels of detail. When a query runs, it is these aggregations that are used, rather than having to churn through millions of rows of data.

 

Data Latency

One of the most common questions relating to cubes relates to the data latency. Can I get up to date information, or will it be a day out of date?
The correct answer is that is depends on what you want. In most common situations an overnight processing run provides data that is more than adequate. Cubes are normally used for high level data analysis, which don't necessarily need sales data from the last 5 minutes. However, if it is right for you, a cube can be designed to be real-time.