Videos, Data Warehousing, Business Intelligence, Blog

What is OLAP?

Jared Hillam

Jared Hillam

January 7, 2011

This video explores some of OLAP's history, and where this solution might be applicable. We also look at situations where OLAP might not be a fit. Additionally, we investigate an alternative/complement called a Relational Dimensional Model.

Text from the Video:

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.

Related Post

Case Study: Large Scale Entity Resolution Powered By Snowflake

The Client is a leader in transaction data analytics for the Financial Services industry and house analytics for large-scale fortune organizations.

Learn More

Data Sharks #22: Rhonda Fisher from Bell Textron

Join us as we talk data with Rhonda Fisher, the Director of Data & Analytics at Bell Textron.

Watch Now

Video: What is a Data Frame?

Check out our latest video: What is a Data Frame? Discover what a "data frame" is and how it helps data scientists and engineers design data processing programs.

Watch Now