Data Warehouse vs. OLAP Cube

What Is a Data Warehouse?

First things first: defining the two options. A data warehouse is simply a database that houses information to support decision-making, managed separately from a company's operational databases. It supports the processing of organizational information by offering a stable platform of consolidated and organized transactional data.

Home grown data warehouses historically have been a development project that can be pretty pricey just to build. However, data warehouses are now also being offered as commercial products – fully built, configurable, and able to house multiple types of data. Some data warehouse solutions require no coding to configure and can be managed by the business user.

A commercial data warehouse is organized with business user accessibility at the center of the design. It is subject-structured, meaning that it is organized around topics like financials, product, sales, and customer. Because data must be replicated from an Enterprise Resource Planning (ERP), Customer Relationship Management (CRM), or another data organization system, data warehouses are not used for live analysis. Instead, the information housed within data warehouses can be used for periodical reporting; planning, forecasting, and modeling; and in dashboards or graphical scorecards where trends and trajectories of company data can be visually analyzed. Moreover, a warehouse can house a wide variety of data types.

A data warehouse is crafted in such a way that it can integrate several disparate data sources to create a consolidated database. This is done through data cleaning and data integration techniques that are "smart" processes innate to the data warehouse. Therefore, a company can store personnel data, financial transactions, and any other organizational information all in one place – going beyond numbers and cash flow. It is a very accessible storage unit where data is replicated and transformed from the original data sources. There are really only two operations when accessing the data: the initial loading of the information and the access itself. Data warehouses do not require any formal transaction processing or concurrency control mechanisms – they stand simply and securely on their own. In particular, Microsoft SQL Server-based Data warehouses are so efficient in terms of business user accessibility because of their SQL server framework. SQL Azure as a data warehouse cloud platform has further simplified the accessibility and maintenance. However, although they can be bought directly already built, the price tag associated with developing a homegrown data warehouse over time by programmers or other IT professionals can be overwhelming.

Here in an example of a modern cloud-based data warehouse user interface:

What Is an OLAP Cube?

OLAP stands for online analytical processing, and cube is another word for a multi-dimensional set of data, so an OLAP cube is a staging space for analysis of information. Basically, a cube is a mechanism used to query data in organized, dimensional structures for analysis. A data warehouse and OLAP cube have different IT requirements.

An OLAP Cube takes a spreadsheet-like structure and three-dimensionalizes the experiences of analysis. Breaking it down, OLAP means analytical data as opposed to transactional, and the cube part of the nomenclature refers to the storage aspect. OLAP cubes are basically multi-dimensional databases. They store data for analysis, and a lot of classic BI products rely on OLAP cubes for access to company information for reports, budgets, or dashboards. For example, a CFO might want to report on company financial data by location, by month, or by product – these elements would make up the dimensions of this cube. However, OLAP cubes are not SQL server relational databases, like data warehouses are.

Two of the key essentials of OLAP cube architecture are:

1. They aggregate data. This aggregation is often done as a nightly process, especially if an OLAP cube is very large.
2. Different data sets (Sales, General Ledger, Inventory, Receivables, Payables, etc.) require a separate OLAP Cube as all data in a single cube need to be related so that it can be aggregated.

Since OLAP cubes are not an open SQL server data warehouse, they require someone with the know-how and the experience to maintain them using a modelling language refer to as "MDX", whereas a SQL server data warehouse can be maintained by most IT people who have regular database training. This aspect accordingly has a price tag attached to it. A company either needs to allocate time and energy from a current employee or consultant to focus on the management of the OLAP cubes or hire a new, perhaps full-time, employee. Additionally, OLAP cubes tend to be more rigid and limited when it comes to designing reports because of their table-like functionality. Aesthetics and capabilities could and arguably should be important to a company that is building its portfolio of BI solutions.

Data Warehouse or OLAP Cube: How to Choose for a CPM or BI Implementation

Both a data warehouse and an OLAP cube can provide you with the information you need to understand your business. The two options allow you to find patterns in your data, which you can use to grow and scale. But which one will be the best fit for your company? To answer that, it helps to look closely at the pros and cons of each one.

Data Warehouse: Pros and Cons

One of the most notable advantages of a data warehouse is that using one allows you to access any piece of information quickly. Data retrieval is a breeze with a data warehouse, as all you need to do is conduct a search for the information you're looking for. A SQL Server data warehouse typically offers a number of reporting, query and dashboard options to extract data and provide it to the business users.

Another advantage of using a data warehouse is that doing so makes it difficult to make a mistake. If you were to input data into the system incorrectly, it would call your attention to the problem, requiring you to fix it. What you see is what you loaded from the data source, because data is typically not aggregated like OLAP cubes do.

A notable disadvantage of data warehouses is that, if creating a home-grown database, they can be expensive and complicated to implement. Someone from your team would be responsible for inputting and organizing all of the raw data into the system. Another potential drawback is that traditionally, data warehouses did not necessarily "play nice" with a company's existing systems without significant technical integration expertise. Solver Data Warehouse solves some of these traditional problems by allowing for easy integration and menu-driven architecture design.

OLAP Cube: Pros and Cons

When the concept of an OLAP cube was first introduced in the 1990s, it was praised for being able to query a vast amount of data in much less time than a data warehouse required because data is always aggregated in a cube. OLAP cubes were also highly regarded for having more intuitive user interfaces than the first generation data warehouses, and for handling complex computations with ease. An OLAP cube provided businesses with insight into their company's inner-workings, letting them see the "how" and "why" of their data so they could make plans for the future.

But the cubes weren't perfect. One notable issue with them is that they require someone to translate data from a file or an intermediate relational database and into the cube format. Then power users would write formulas in the MDX language. Even today, using an OLAP cube requires a considerable amount of input from IT or consultants with specific OLAP training. For startups or small businesses, the costs of hiring a professional to manage the cubes can be prohibitive.

Let Solver Help You Find the Right Storage Solution

The take-home of this article could be that, in looking for BI tools, consider what format of data storage the product utilizes for analysis. Understanding OLAP cubes and the pros and cons of data warehouses should result in a preference – and the storage of company data, exponentially bigger amounts by the year, is arguably a foundation for successful analysis. Solver offers a fully built, configurable data warehouse stand-alone and as part of the comprehensive suite of CPM and BI modules and would be happy to answer questions and generally review Solver's easy-to-use Data Warehouse solution for collaborative, streamlined decision-making capabilities.