Every so often, potential customers ask us whether they should store data within an OLAP Cube or a transactional database. Yurbi does not support OLAP Cubes which means we recommend using a transactional database instead, but we wanted to dive deeper into the pros and cons of using OLAP Cubes to store your data.
What is an OLAP Cube?
There are multiple ways in which a database can be built, and the two primary methods of building are Online Analytical Processing (OLAP) and Online Transactional Processing (OLTP). Both are valid methods of building databases, however, OLAP is becoming a method of the past. The OLAP method of building databases emerged in the 90’s because developers realized that querying data was incredibly cumbersome. OLAP allowed companies to take a large amount of data and structure it into a static, multi-dimensional format that included all of the necessary calculations built in. These dimensions include facts, measures (things you count or add), and dimensions (attributes of the data).
For example, within a database that stores financial data, the facts would include the raw numbers, the measures would include the number of sales and expense numbers, and the dimensions would include attributes such as by month, by product, or by location. From a reporting perspective, this made pulling data much easier because all of the data within an OLAP Cube is broken down into digestible and filterable chunks.
If you relate an OLAP Cube to a Rubix Cube, the colored squares on the outside of the cube would represent facts. The OLAP Cube model was designed to allow users to query and reach the facts faster. Historically, this was needed because of the size and speed limitations of querying data in the 90’s. As a result, all of the legacy BI tools such as Business Objects and Microsoft SQL Server Reporting Services (SSRS) were designed to work with OLAP Cubes.
Fast forward to the present and OLAP Cubes have become mostly obsolete. Working with OLAP Cubes often causes more challenges than they’re worth and the query speed issue they were initially built to solve is no longer a problem. Today, CPU power, memory, and powerful servers and desktops abound and are very affordable. OLAP cubes also require a ton of developer and technical support both to set up and maintain.
Within the current database storage landscape, the superior option to OLAP is OLTP. OLAP databases take the already established OLTP databases and add a separate database around them. However, the OLAP model is not really needed because current BI technology allows users to conduct advanced queries on their OLTP databases. OLTP databases have features such as SQL views and stored procedures that solve the query speed problems that were initially solved by OLAP.
The second reason users don’t need OLAP is that business intelligence is transitioning from a traditional BI market to an agile BI market simply because agile BI is faster and easier. OLAP Cubes take an extraordinary amount of time to set up and developers must define all user requirements (both now and in the future) before setting up the cube. Changing the structure of an OLAP cube essentially requires a rebuild, which means OLAP cubes make the most sense for organizations whose data will not change.
Contrarily, agile BI solutions work with OLTP databases and serve businesses much better because businesses and data change constantly. For the business user, an agile BI solution that works with their organization’s OLTP database is always going to be the better option because data is frequently updated and is easier to pull. Many of our potential customers are looking to transition from OLAP-based BI solutions to Yurbi, which is an agile solution, for this exact reason.
The third reason why OLAP Cubes are not the best database options is the advancement of most BI technology to provide in-memory analytics. Most modern BI tools like Yurbi, Qlik, and Tableau have in-memory analytics, which is an improvement upon the OLAP measures and dimensions model (mentioned above).
Though OLAP Cubes still exist, they are outdated technology. For any small-to-medium sized businesses, the cost of setup and maintenance of OLAP Cubes are not cost-effective. Agile BI solutions that work with OLTP databases are much better solutions, which is why Yurbi does not support OLAP Cubes.