OLAP technology has continued to develop, a good indicator of its broad applicability in the software solution market. And though newer doesn’t always mean improved, our opinion is that the most recent OLAP technologies are faster and (generally) better than their predecessors. These recent OLAP advances include aspects of in-memory OLAP combined with hybrid systems that couple the benefits of multidimensional modeling with the steadfastness of a relational database.
Which OLAP is best for your business?
When a company commits to purchasing an OLAP-based BI system, it’s essential that the system meets present and potential future needs. With the wide variety of OLAP technologies available, it has become critical to know the differences between the main types, MOLAP, ROLAP, HOLAP–and a new entrant, HTAP. While there are other versions of OLAP, with this post we have tried to help make the decision-making process a bit easier, by providing descriptions of each type, along with their advantages and drawbacks.
MOLAP: Multi-dimensional OLAP
Data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats (one example is PowerOLAP’s .olp file). MOLAP products can be compatible with Excel, which can make interacting with the data very easy to learn.
- Excellent performance: MOLAP cubes are built for fast data retrieval, and are optimal for slicing and dicing operations.
- Can perform complex calculations quickly: often calculation logic can be handled by users (meaning, no relational database programming skills needed), and the main reason for MOLAP is precisely to speed up calculations in a multidimensional environment optimized for fast data calculation.
- Sometimes limited in the amount of data it can handle: because all calculations are performed when the cube is built, it might not be possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this is possible, but only summary-level information will be included in the cube itself.
- MOLAP products are typically proprietary systems.
- Relevant data must be transferred from relational tables, which can be cumbersome and, by definition, redundant.
ROLAP: Relational OLAP
ROLAP products access a relational database by using SQL (structured query language), which is the standard language that is used to define and manipulate data in an RDBMS. Subsequent processing may occur in the RDBMS or within a mid-tier server, which accepts requests from clients, translates them into SQL statements, and passes them on to the RDBMS.
- No data limitation, can handle large amounts of data
- Can access use functionality of inherited relational databases
- Performance can be slow because of large size of data sets
- Can be limited to SQL functions, which can be inflexible
- Data may need to be reformatted for end-users
HOLAP: Hybrid OLAP
The merger of the best features of MOLAP and ROLAP allowing for fast calculations from RDBMS by using pre-calculated cubes. (New HTAP systems–see further below–may be considered HOLAP products, though they function differently from previous products that people may recognize as HOLAP.)
- Has the best features of both MOLAP and ROLAP: scalability, flexibility, and speed
- Uses RDBMS SQL functionality
- Can “drill-down” from a cube to a relational table
- Fast to use because of pre-calculated cubes
- Has the limitations of both MOLAP and ROLAP: as it is fast, it may not be as fast as pure MOLAP, and as it is scalable, it may not be as scalable as pure ROLAP.
What’s New in the Market?
Hybrid Transaction / Analytical Processing (HTAP)
Gartner coined the term HTAP in a paper in early 2014 to describe new in-memory data systems that do both online transaction processing (OLTP) and online analytical processing (OLAP). HTAP represents a new way to tie data together in a way that hasn’t been possible before. Combining analytical engine capabilities with relational data tables is a the root of HTAP, and we at OLAP.com think it is the way data will be managed in the future.
- The technology is sited in the relational database
- Powerful, often distributed, processing–which means it is fast
- No more data replication
- New transactional information becomes part of an analytical model as fast as technologically possible
- Unites the relational data tables with the models that are used for decision making by business leaders
- Change in existing architectures can be disruptive
- New technologies and accompanying skills may have to be learned
For an example of an HTAP product, check out Olation® from PARIS Tech, the sponsor of OLAP.com. Olation can be categorized as an HTAP product — even the name Olation implies the combination of “OLAP” and “relational” technologies.
The article, The File-less Organization: Why Excel Isn’t Enough for Businesses, from Dataversity.net is quite astute in the way it identifies Excel as a problem–noting that each manager gathers his or her own version of the numbers to bring to a meeting. And so in the meeting everyone has a different version of the state of affairs, and things can easily devolve into an argument over whose numbers are the “most right”. Sound familiar?
I was so excited when I began reading because this article gets it exactly right in the beginning, but at the end, it seems to recommend dashboards as the solution to this problem. Except that dashboards are usually representations of those same error-prone, manually compiled spreadsheets. The ‘replace-Excel-with-dashboards’ scenario is more of a band-aid theory: once you get past the pretty graphics, you’ll discover that you traded one problem for another! Don’t get us wrong, we love dashboards, but we think they should be real-time, fed from the data source directly. This requires a sophisticated BI solution, which the article confuses a little bit with dashboards. Typically, dashboards have limited calculation capability. With the newest, advanced business solutions, like Olation, the relational data source is combined with a data calculation engine and modeling solution that, yes indeed, works with Excel, not against it.
Some vendors have run from the four letters OLAP…and yet, articles continue to be published, and assertions made, that “analytical processing” is entering a new phase. There’s a new acronym in the market, HTAP, which stands for Hybrid Transactional/Analytical Processing. Given the close similarity (two out of four words ain’t bad—and it’s three if we consider the once-upon-a-time category HOLAP), is there a difference? This post from one of our favorite bloggers, Timo Elliott, may help you decide…
What is HTAP?
Read more about PARIS’ take on the HTAP technology on the PARIS Tech Blog
You may not think that business analytics as a concept applies to academics, but access to meaningful and timely information is critical to the proper function of a dean’s office. Anyone who has been a dean or worked in the dean’s office knows that. And, like in any other business, certain kinds of data seem to percolate to the top of the needs list, but that data is all too frequently not easily accessible.
Take, for example, the need to project student enrollment for the ensuing year. As new students arrive on campus, there must be an adequate number of required courses as well as qualified faculty to teach those courses. If enrollment is low because of a glitch or error in the Admissions Office, or there is an unexpected fluctuation in applications, the number of courses can be adversely impacted.
Similarly, enrollment projections are required several years forward, suggesting the need for a strong analytics tool, and one that allows for “what if” eventualities. The dean’s office needs to be able to monitor the number of applications, the number of offers tendered, the number of offers accepted, the number of paid acceptances, the cumulative amount of financial aid and the average aid per applicant.
These are just a few of the desired “data intelligence” items to have available at one’s fingertips; each one of these belongs to its own “dimension” for measuring performance. These enrollment statistics and other metrics drive the university, and the university president regularly seeks to be updated, just like any other CEO who needs “business analytics” to run a company. Of course, a graph showing how the current numbers compare to past numbers is necessary.
The challenge is delivering up this intelligence, as by nature it is “multidimensional,” a word one encounters in typical definitions of OLAP technology.
For example, one obvious dimension is “school” or “college,” since typically colleges are subdivided into various schools while universities are subdivided into colleges. Thus, university X might have a College of Medicine, College of Business, and the like. And so all the metrics above might be required by each school or college as well.
Other dimensions might concern staff [faculty] allocation to courses as well as course availability for student selection. Tracking the number of students progressing through the colleges from year to year, especially at the undergraduate level, is desirable so that sufficient resources can be properly allocated in a timely fashion. When faculty are absent from the university for the purpose of a sabbatical (another dimension), their classes and student advisees must be accommodated either through existing faculty movement or the hiring of adjunct faculty. If adjunct faculty are to be hired, the problem of recruiting an academically or professionally qualified individual can be a formidable challenge, not to mention the impact on a budget that might need to be crafted two or three years in advance. This means that access to appropriate historical and current data must be available.
To consider another factor impacting faculty allocation: students, especially undergraduates, change majors at least once and it is not altogether unusual for a student to change majors several times. As students move from one major to another, frequently within the same college, demand for certain courses might unexpectedly increase or drop. Having as much advance data intelligence as possible for the potential impact on staff teaching needs in turn would lessen any adverse effect on financial budget plans.
Of course, the need for financial information is forever present in higher education as it is in business. The usual data such as major budget categories and current progress should be easily available along with a comparison with past budgets. Of equal importance is the projection to end-of-year. At a different level the projected liability of the university to support the scholarships granted is something the financial vice president would want to know. And it is not uncommon for faculty to approach their dean with requests for additional travel funds so they can take advantage of some unforeseen opportunity. As in the business world, in the face of unavailable or incomplete data, the answer is typically in the negative!
The list of information needs continues with the number and kind of publication each faculty member writes, the total yield for each faculty member over the number of years necessary to meet accreditation requirements, the percentage of publications in high quality journals, and perhaps some ranking of faculty by academic production.
I have indicated these information needs not only to suggest that in the academic environment they can be vast and various, but also to suggest that it behooves us to look beyond products with limited single information source availability. A really productive solution would also provide modeling with 1) robust “dimensionality”, 2) “what if” capabilities, and 3) the ability to compare Current v. Historical v. various Planned scenarios. These are hallmarks of products featuring OLAP technology, which, though more widely adopted in the business community, are clearly well-suited and necessary to provide an application-oriented academic “business analytics” program as well.
(Professor Bill Tastle is a regular contributor to OLAP.com. At Ithaca College he teaches a course in Business Analytics, and uses PowerOLAP from PARIS Technologies for in-class exercises.)
By Bill Tastle, our newest OLAP.com contributor
As a Professor at an AACSB accredited business school, I am responsible for preparing business students who are eager to enter the marketplace upon graduation. Naturally, I spend a lot of time researching about what skills will best serve them. This can be more of a challenge than one might think because today’s skills may not be in demand tomorrow but one thing appears to be quite apparent; the need for graduates trained in the methods of business analytics will be in demand for at least the next decade. Thus, my business school faculty members and I have been discussing how best to incorporate Business Intelligence (BI) skills into our existing program.
This is a bit exasperating if one’s business school/college is AACSB accredited, because the required set of courses that comprise the curricula have very little room for extras. BI, I must argue, is not an “extra” but rather, a critical component to a student’s undergraduate business education.
So, I carefully examined the landscape for suitable software tools to bring into my undergraduate classroom environment. I spent considerable time reading everything I could find from many, many companies and multinational corporations hawking tools for BI. Eventually, I happened upon a company featuring a strong “Excel user-friendly” product.
There seemed to be a natural logic to doing BI in Excel, given the pervasive use of spreadsheets in the business community, but what I had discovered is that many software vendors try to address the Excel “problem” by doing away with Excel altogether.
This company had a different approach, which is to embrace what Excel has to offer as a familiar front end. I sent off an email asking for information and virtually immediately was in contact with someone at the company and quickly received their literature. I read it! Carefully! The more I read, the more excited I became.
It is pretty obvious that all business schools teach Microsoft Excel, some at a trivial level, which is little more than a superficial introduction, and others are more advanced levels. Perhaps I am a statistical outlier, but at my school, sophomores must become Microsoft Expert certified to pass their required business technology course.
So, the ability to bring a BI tool directly into Excel in the form of a simple add-in has made all the difference in the world. Students receive an introduction to the world of BI and quickly discover what is meant by “multi-dimensionality,” an important concept when business performance models (“cubes” in BI terminology) are being constructed.
I could not do as good of a job teaching Business Analytics if I had to teach an enterprise database program first, just to get to their dashboard graphs. Furthermore, being able to attach the BI software program to other sources of data made it all the more fascinating to students who are amazed to discover they can access data not only from other worksheets, but also from database tables. This is also a fantastic achievement, getting data from multiple sources with different data structures.
I am about as pleased as can be. In fact, I am offering an additional course in BI and I have other courses undergoing development. The pedagogy involved in the skills of Business Intelligence is being written in this decade, and the benefit of using an “Excel-friendly” tool in the classroom brings daylight to the mysterious area of “intelligence gathering” and how it is used in decision-making solutions. For me, it is particularly exciting because it is the beginning of a thrilling direction in business education.
Next time: Analytics in the Business Dean’s Office
OLAP applications have, by most accounts, made their biggest impact in Finance Departments. Of course, financial analysts aren’t the only staff who can benefit from OLAP capabilities. There are applications that provide reports and analyses to sales and marketing staff too. But one important under-served community for OLAP solutions are managers who need to report, analyze and even plan based on employee time and other resource allocations.
In the case of measuring employee time and resources, employees rely on MS Outlook to schedule their time the same way Finance departments rely on Excel to get the reports they need. It’s basically second nature for people to schedule their time in their Outlook. So an optimal OLAP solution would include Outlook as well as a company’s accounting system, and it would provide for dynamic connectivity to Excel and other reporting front ends.
To fulfill the “online” promise of OLAP, such a system would provide the real-time intelligence to gauge the productivity of the sales team, or list the top projects that are still under budget, or are getting into trouble. In true OLAP-multidimensional fashion, there would be the opportunity to understand time and resource allocation by Project, Activity, Manager, Consultant, Billable, and Version (e.g., Actual vs. Budget). And for the financially inclined, P&Ls by Project, Team, or Employee.
The benefit for end-users would include a true fathoming of how they contribute to the business; for managers, the ability to monitor results in order to make the best-informed plans about allocating resources to the most profitable projects.
Time is Money, as the saying goes—and so, as far as OLAP applications go, there’s every reason in the world to benefit from an OLAP solution that can deliver live time and resource performance data. And after all, why should Finance professionals have all the fun?