Let’s do it – let’s talk about OLAP cubes. With the amount of data only growing, exponentially for some, Business Intelligence (BI) data stores are becoming more and more prevalent – and are sensible ways for modern organizations, companies, and corporations to access, store, and organize company data for financial reports, budgets, and dashboards, as well as financial consolidations. Whether you’re relying on a data mart, data warehouse, or an OLAP cube, your data queries won’t slow down the Enterprise Resource Planning (ERP) system, and you can grab multiple types of data to enrich and broaden your analyses. But this article will specifically zoom in on OLAP cubes. Who manages them? What are they? When do OLAP cubes come into play? Where are they staged? Why would you choose an OLAP cube over another BI data store?
Let’s start by defining the term. OLAP is an acronym for online analytical processing, and the cube refers to the structure. More specifically, an OLAP cube is comprised of measures or things you can count or add. These measures are divided by dimensions, which are the attributes. In OLAP cubes, data (measures) are categorized by dimensions. In order to manage and perform processes with an OLAP cube, Microsoft developed a query language, known as multidimensional expressions (MDX), in the late 1990s. Many other vendors of multidimensional databases have adopted MDX for querying data, but with this specific language, management of the cube requires personnel with the skill set. Perhaps an example would help three-dimensionalize your understanding of an OLAP cube.
I was first introduced to the concept of an OLAP cube with a comparison of the tool to a loaf of bread – really. If you imagine a loaf of bread, with the whole totaling 1,000 calories, each slice makes up about 50 calories of that sum. The slices are your dimensions. And if you cut your slices into little chunks to make croutons or stuffing, this would make each piece about 5 calories each – and another dimension. If you think about taking this comparison and applying it to an aspect of your business, like sales, the whole loaf or cube could be comprised of all of your sales for all time, the slices could represent a year, and the croutons could represent a region. All of this data is organized and stored, and you can just query the year or region that you are seeking, and the database pulls it up for you. Once built, OLAP cubes definitely fall under the category of self-service BI, with one main qualification.
If we compare OLAP cubes to a competing device, like a modern commercial data warehouse solution, there are a few things that are easy to note. First of all, OLAP cubes are focused on analytical data as opposed to data warehouses that are housing multiple kinds of data that include transactional and operational information. In other words, data warehouses offer more breadth in terms of what you can store. While OLAP cubes are pretty prevalent and a common suggestion or requirement for BI tools, they are more technically complex and specific in their requirements regarding management and utilization of the solution. MDX language fluency is just one example of a requisite skill set for OLAP administrators, whereas most commercial data warehouse solutions are built with a Microsoft SQL server framework, making them extremely user friendly for BI analytics, like financial reporting, budgeting and forecasting, and data visualizations. Another difference is that, depending on your setup, the technological tools you’re using, and the data you are organizing, you might have multiple OLAP cubes that are not all connected. A data warehouse is a unified space for diverse data types. With all this said, why exactly would you go with OLAP cubes over another BI data store solution?
OLAP cubes provide a system that can store and present data that supports decision-making at a relatively quick speed. Furthermore, they provide the higher performance data integrations needed to support companies in their analytical processes without slowing down the ERP system (or other system) server. OLAP cubes are prevalent for a reason – they are powerful tools that might be technically complex, but they also deliver advanced analytics. However, this product category is aging and arguably waning in its relevance as the technology marketplace in general, including BI software, is moving toward business user friendliness, so the IT department doesn’t have to manage applications for departments.
Consumers might be moving away from OLAP cubes (and then, so follows the marketplace) – albeit gradually, but steadily – because the data storage concept has arguably improved in the form of modern data warehousing. OLAP cubes were built as a solution for speeding up financial reporting and other analytics. As a solution, they have always been managed by IT and full-time technical BI administrators, and that hasn’t changed, but the business culture in its consumer driven-ness has shifted toward more accessible, manageable technology for business end users. The definition of self-service BI has become more refined and streamlined in terms of software deliverables. While OLAP cubes are still common across the BI technology marketplace, data warehousing has definitely seen growth as it is serving consumers in more modern and complete ways.
Data warehouses are quickly growing in popularity as a way to manage the “hybrid cloud” situation that many companies now have to deal with as their data sources increasingly are located both in the Cloud and on-premises. A real data warehouse, as opposed to cubes with disparately organized data, is needed to consolidate all of the islands of information into a central business user friendly repository to create the foundation for modern BI to take place. OLAP cubes are still going to solve a lot of companies’ “problems”, but knowing more about the solution will help you to develop the strongest strategy for BI data storage as well as BI data analytics process improvement. Solver offers a fully built, configurable data warehouse stand-alone and as part of the comprehensive suite of BI modules and would be happy to answer questions and generally review BI360’s easy-to-use Data Warehouse solution for collaborative, streamlined decision-making capabilities.