What is OLAP?

Summary

In this informative video, Jared Hillam explores the decision-making process behind implementing OLAP (Online Analytical Processing) in business intelligence. He explains the history of OLAP, its benefits like instant data analysis through pre-processed combinations of dimensions and measures, and its challenges, such as reliance on IT for structural changes and the difficulty of managing too many dimensions. Hillam introduces the Dimensional Relational Model as a flexible alternative and suggests that OLAP can complement this model in structured environments like finance. He concludes by emphasizing the importance of a balanced solution tailored to an organization's needs.

Takeaways

• 📚 OLAP stands for Online Analytical Processing, a technology designed to facilitate fast and flexible querying of data in business environments.
• 🕵️‍♂️ OLAP was introduced in response to the difficulties businesses faced in querying data from relational databases in the 90s, which were slow and inflexible.
• 🛠️ A critical goal of OLAP is to minimize on-the-fly processing by pre-processing and storing every possible combination of dimensions, measures, and hierarchies for quick data access.
• 🔧 OLAP faces challenges such as reliance on IT for managing changes to the OLAP structure, which can hinder flexibility in data analysis.
• 🏢 OLAP is highly accepted in structured analytical environments like Finance and Accounting, but may not be as suitable for areas requiring more freedom in data analysis.
• 🤔 The success of OLAP implementations requires close collaboration between IT and business units, as IT needs to anticipate user data paths, which is challenging without foresight.
• 🧩 Balancing the number of dimensions in OLAP is crucial; too many can confuse users, while too few limit data analysis capabilities.
• 🧐 Humans typically struggle with understanding more than three dimensions, and more than seven dimensions are considered overwhelming.
• 🔄 An alternative to OLAP is the Dimensional Relational Model, which optimizes data for live queries rather than pre-calculating all combinations.
• 🆓 The Dimensional Relational Model offers greater flexibility by allowing users to select dimensions for analysis without pre-calculating permutations.
• 🔗 OLAP can complement the Dimensional Relational Model, particularly in highly structured analysis paths like finance and accounting.
• 🌐 Intricity specializes in building information infrastructure and can guide organizations to a balanced solution that optimizes decision-making investments.

Q & A

What is OLAP and why was it introduced?

OLAP stands for Online Analytical Processing. It was introduced in the mid to late 90's to address the difficulties businesses faced in querying data from their relational databases. OLAP aimed to minimize on-the-fly processing by pre-processing and storing every possible combination of dimensions, measures, and hierarchies to allow for fast and flexible data navigation.

What are the main goals of OLAP vendors?

The main goals of OLAP vendors are to minimize the amount of on-the-fly processing needed during data navigation and to provide a system that allows data to appear instantaneously when users investigate the information.

Why might OLAP be less suitable for certain business environments?

OLAP might be less suitable for environments that require a lot of freedom to analyze data due to its reliance on IT to manage any changes to the OLAP structure. This can make it challenging in areas like Sales, Operations, Marketing, and R&D.

Which business areas typically have a high acceptance rate for OLAP?

OLAP has a high acceptance rate in very structured analytical environments like Finance and Accounting.

What is the relationship between IT departments and the success of OLAP implementation?

IT departments play a crucial role in the success of OLAP implementation. They need to have a close relationship with the business to precisely determine not just what data is needed, but also what path the user might take with the data.

What are the challenges faced in balancing the number of dimensions in an OLAP structure?

The challenges include avoiding confusion with too many dimensions and ensuring there are enough dimensions to work with the data effectively. OLAP cubes pre-calculate all resulting combinations between dimensions, but humans have difficulty understanding more than three dimensions, making more than seven dimensions too much to keep track of.

What is a Dimensional Relational Model and how does it differ from OLAP?

A Dimensional Relational Model is a data model that does not pre-calculate every possible combination of dimensions. Instead, it stores data optimized for live queries, allowing for greater flexibility and control by the end user. Unlike OLAP, it does not require pre-calculation of all permutations ahead of time.

How can a Dimensional Relational Model offer more flexibility than OLAP?

A Dimensional Relational Model offers more flexibility by processing data at run time, allowing end users to select the dimensions they want to see without pre-calculating all their permutations. This provides the ability to handle a larger number of dimensions and puts users in control of their data requests.

Can OLAP be used in conjunction with a Dimensional Relational Model?

Yes, OLAP can be a complementary solution to a Dimensional Relational Model, especially in cases like finance and accounting where there is a highly structured analysis path. Cubes can be created from the data stored in Dimensional Relational Models.

What role does Intricity specialize in regarding information infrastructure?

Intricity specializes in helping organizations build the right information infrastructure. They have a deep understanding of the tactical, strategic, and cultural impacts of different solutions and can guide organizations to a balanced solution that maximizes their investments in making better decisions.

How can someone get in touch with Intricity's specialists for further guidance?

To get in touch with Intricity's specialists, one can visit their website and engage in a conversation to receive guidance towards a balanced solution for their information infrastructure needs.

Outlines

00:00

📊 OLAP: An Introduction and Its Historical Context

Jared Hillam introduces the concept of OLAP (Online Analytical Processing) and its significance in Business Intelligence deployments. He explains that OLAP emerged in the mid to late 90s as a solution to the slow and inflexible querying capabilities of relational databases. The goal of OLAP was to minimize on-the-fly processing by pre-processing and storing every possible combination of dimensions, measures, and hierarchies to provide instant data analysis. The video also touches on the challenges of OLAP, such as reliance on IT for structural changes and the difficulty of balancing the number of dimensions for effective analysis.

Keywords

💡OLAP

OLAP stands for Online Analytical Processing, which is a category of software tools for analyzing and retrieving data in a multi-dimensional manner. It is central to the video's theme as it discusses the historical context, challenges, and alternatives to OLAP systems. The script mentions that OLAP was introduced to address the slow and inflexible querying of relational databases, emphasizing its role in business intelligence deployment.

💡Dimensions

In the context of OLAP, dimensions are the different characteristics or categories that can be used to analyze data. They are fundamental to the video's discussion of how OLAP works, as they are part of the pre-processed combinations stored in OLAP cubes. The script uses dimensions to explain how users can analyze data instantaneously by navigating through various dimensions such as region, product type, and time period.

💡Measures

Measures refer to the quantitative data or numerical values that are associated with the dimensions in an OLAP system. They are essential for performing calculations and analyses within the video's theme of data querying and business intelligence. The script implies that measures are pre-processed with dimensions to allow for quick data analysis, as seen in the example of analyzing sales across various dimensions.

💡Hierarchies

Hierarchies in OLAP are the structured levels or ranks within a dimension, allowing users to drill down into data for more detailed analysis. The video script uses the term to describe part of the pre-processed data combinations in OLAP, which helps in navigating the data efficiently. Hierarchies are an important aspect of the video's explanation of how OLAP systems enable complex data analysis.

💡Business Intelligence

Business Intelligence (BI) is the process of analyzing data to help inform business decisions. The video script revolves around BI deployment, particularly the decision to use OLAP. It discusses how OLAP can be a part of BI by providing tools for data analysis, but also points out its limitations and when it might be inappropriate.

💡Proprietary Solutions

Proprietary solutions refer to products or services that are owned by a particular company and are not available to others without specific licensing. In the video, proprietary solutions are mentioned as the initial market response to the challenges of querying data from relational databases, leading to the rise of OLAP technologies.

💡Data Optimization

Data optimization in the context of the video refers to the process of arranging and storing data in a way that enhances its accessibility and usability for analysis. The script explains how OLAP achieves this by pre-processing and storing every possible combination of dimensions, measures, and hierarchies, which is crucial for the video's theme of efficient data analysis.

💡Dimensional Relational Model

A Dimensional Relational Model is an alternative to OLAP that stores data in a way that is optimized for live queries rather than pre-calculating combinations. The video script introduces this model as a flexible alternative to OLAP, allowing for greater user control and less reliance on IT for managing data structure changes.

💡IT Dependency

IT dependency in the video refers to the reliance on Information Technology departments to manage and make changes to the data analysis structures, such as OLAP. The script points out that this can be a challenge in environments needing flexibility, as IT must predict user needs and paths, which contrasts with the more user-driven Dimensional Relational Model.

💡Cultural Impacts

Cultural impacts in the video relate to how the choice of technology, such as OLAP or Dimensional Relational Models, can affect the work culture within an organization. The script suggests that Intricity, the company mentioned, understands these impacts and can guide organizations to choose solutions that align with their culture and needs.

💡Intricity

Intricity is the company mentioned in the video script that specializes in helping organizations build the right information infrastructure. It is relevant to the video's theme as it offers expertise in choosing between OLAP and other data analysis solutions, taking into account the tactical, strategic, and cultural factors.

Highlights

Introduction to the question of implementing Business Intelligence with OLAP or not.

Explanation of OLAP and its historical context in the mid to late 90’s.

OLAP’s critical goal of minimizing on-the-fly processing for data navigation.

The method of pre-processing and storing every possible combination of dimensions, measures, and hierarchies in OLAP.

Challenges of OLAP including reliance on IT for managing changes to the OLAP structure.

OLAP's high acceptance rate in structured analytical environments like Finance and Accounting.

The difficulty of IT departments with a distant relationship with the business in implementing OLAP.

The importance of IT in determining data needs and user paths for OLAP success.

Balancing the right number of dimensions in the OLAP structure to avoid confusion.

The human cognitive limit in understanding more than three dimensions and the impracticality of more than seven.

Introduction of the Dimensional Relational Model as an alternative to OLAP.

The flexibility of the Dimensional Relational Model in allowing live queries and user-selected dimensions.

How OLAP can complement a Dimensional Relational Model in highly structured analysis paths like finance and accounting.

Intricity's specialization in building the right information infrastructure and understanding the impacts of different solutions.

Invitation to visit Intricity’s website and consult with their specialists for a balanced solution.

The potential of making better decisions through optimized investments in information infrastructure.

Transcripts

Hi I’m Jared Hillam, Often when we seek to implement a Business

Intelligence deployment we’re faced with the question.

To OLAP or not to OLAP?

If you don’t know what OLAP is, you’ve come to the right place.

Not only are we going to explain what OLAP is, we’re also going to discuss where it

might be appropriate, and where you might want to avoid it.

Now I am going to use some terms like dimensions, measures, and hierarchies, which we explain

in an earlier video.

To explain what OLAP is, it’s probably best to consider its history.

You see, in the mid to late 90’s businesses found it very difficult to query data out

of their recently acquired relational databases transaction systems.

Not only were queries very slow, but they simply weren’t flexible enough to navigate

the data.

And remember, even the best processors at that time would be blown away by your average

laptop today.

Various vendors in the market place introduced proprietary solutions to address this, which

ushered in the rise of OLAP.

One of the critical goals that the OLAP vendors strived to achieve is to minimize the amount

of on the fly processing needed while the user was navigating the data.

This was achieved by pre processing and storing every possible combination of dimensions,

measures, and hierarchies before the user started his/her analysis.

This allowed the data to appear instantaneously when the user investigated the information.

While the market has matured greatly, and some standards have emerged, the data optimization

methods of OLAP are fundamentally still the same.

So let’s talk about some of the challenges encountered in OLAP, and then we’ll talk

about some possible alternatives or complements.

One of the challenges that OLAP users face is the reliance on IT to manage any changes

to the OLAP structure.

This can make it challenging in environments that need a lot of freedom to analyze data.

Consequently, you’ll find OLAP has a high acceptance rate in very structured analytical

environments like Finance, and Accounting.

Whereas, areas like Sales , Operations, Marketing, and R&D may look to other means of getting

their data.

This leads us to our second observation.

IT departments that have a distant over the wall relationship with the business, are unlikely

to succeed in implementing OLAP.

You could argue that this would be the case with any technology, but in the case of OLAP

it’s especially a challenge.

This is because IT has to precisely determine not just what data is needed, but what path

the user might take with the data.

And it’s hard to do that without a crystal ball handy.

The last issue we deal with in OLAP implementations is balancing the right number of Dimensions

in the OLAP structure.

Too many dimensions can just make it confusing to use.

Too few dimensions and you just don’t have enough to work with the data.

Because OLAP cubes pre calculate all the resulting combinations between dimensions, you can do

some amazing analysis.

For example all at once you could analyze sales by region, and by product type, and

by period of time, and by store, and by sales rep, and by budget vs plan.

However, when you get down to it, you find yourself going back to figure out exactly

what you’re looking at.

Humans have a hard enough time understanding more than 3 dimensions.

And we’ve found that anything more than 7 dimensions is just too much for people to

keep track of.

So we find ourselves seeking a way to strike a balance.

And this is probably a good point to introduce you to something called a Dimensional Relational

Model.

Unlike OLAP, a Dimensional Relational Model doesn’t seek to pre calculate every possible

combination of dimensions.

Rather, it stores the data in a data model that is optimized for live queries.

So even a very data intensive query will only take short period of time to process.

By processing the data at run time, a greater level of flexibility is opened up.

This is because I can allow the end user to select the dimensions He/She wants to see

without having to pre calculate all their permutations ahead of time.

This means you can give the user 50 dimensions to pick from and not even bat an eye.

Consequently, this relieves some of the pressure on IT to have a crystal ball in its back pocket,

and it puts the users in control of their data requests.

OLAP can actually be a complementary solution to a Dimensional Relational Model, particularly

in cases like finance and accounting where there is a highly structured analysis path.

And indeed cubes can be created from the data stored in Dimensional Relational Models.

Intricity specializes in helping organizations build the right information infrastructure.

We have a deep understanding around the tactical, strategic, as well as cultural impacts of

one solution over another.

I recommend you take an opportunity to visit Intricity’s website and talk with one of

our Specialists.

We can help guide you to a balanced solution that will make the most of your investments

towards making better decisions.