The In-Memory Technologies Behind Business Intelligence Software

If you follow trends in the business intelligence (BI) space, you’ll notice that many analysts, independent bloggers and BI vendors talk about in-memory technology.

There are technical differences that separate one in-memory technology from another, some of which are listed on Boris Evelson’s blog.

Some of the items on Boris’ list are just as applicable to BI technologies that are not in-memory (‘Incremental updates’, for example), but there is one item that merits much deeper discussion. Boris calls this characteristic ‘Memory Swapping’ and describes it as, What the (BI) vendor’s approach is for handling models that are larger than what fits into a single memory space.

Understanding Memory Swapping

The fundamental idea of in-memory BI technology is the ability to perform real-time calculations without having to perform slow disk operations during the execution of a query. For more details on this, visit my article describing how in-memory technology works.

Obviously, in order to perform calculations on data completely in memory, all the relevant data must reside in memory, i.e., in the computer’s RAM. So the questions are: 1) how does the data get there? and 2) how long does it stay there?

These are probably the most important aspects of in-memory technology, as they have great implications on the BI solution as a whole.

Pure In-Memory Technology

Pure in-memory technologies are the class of in-memory technologies that load the entire data model into RAM before a single query can be executed by users. An example of a BI product which utilizes such a technology is QlikView.

QlikView’s technology is described as “associative technology.” That is a fancy way of saying that QlikView uses a simple tabular data model which is stored entirely in memory. For QlikView, much like any other pure in-memory technology, compression is very important. Compressing the data well makes it possible to hold more data inside a fixed amount of RAM.

Pure in-memory technologies which do not compress the data they store in memory are usually quite useless for BI. They either handle amounts of data too small to extract interesting information from, or they break too often.

With or without compression, the fact remains that pure in-memory BI solutions become useless when RAM runs out for the entire data model, even if you're only looking to work with limited portions of it at any one time.

Just-In-Time In-Memory Technology

Just-In-Time In-Memory (or JIT In-Memory) technology only loads the portion of the data into RAM required for a particular query, on demand. An example of a BI product which utilizes this type of technology is SiSense.

Note: The term JIT is borrowed from Just-In-Time compilation, which is a method to improve the runtime performance of computer programs.

JIT in-memory technology involves a smart caching engine that loads selected data into RAM and releases it according to usage patterns.

This approach has obvious advantages:

1. You have access to far more data than can fit in RAM at any one time
2. It is easier to have a shared cache for multiple users
3. It is easier to build solutions that are distributed across several machines

However, since JIT In-Memory loads data on demand, an obvious question arises: Won't the disk reads introduce unbearable performance issues?

The answer would be yes, if the data model used is tabular (as they are in RDBMSs such as SQL Server and Oracle, or pure in-memory technologies such as QlikView), but scalable JIT In-Memory solutions rely on a columnar database instead of a tabular database.

This fundamental ability of columnar databases to access only particular fields, or parts of fields, is what makes JIT In-Memory so powerful. In fact, the impact of columnar database technology on in-memory technology is so great, that many confuse the two.

The combination of JIT In-Memory technology and a columnar database structure delivers the performance of pure in-memory BI technology with the scalability of disk-based models, and is thus an ideal technological basis for large-scale and/or rapidly-growing BI data stores.

---

By: Elad Israeli | The ElastiCube Chronicles - Business Intelligence Blog

BI-as-a-Service - Some Questions Worth Asking

For a couple of years now, there has been a substantial amount of hype in the business intelligence (BI) space regarding “cloud BI,” or business intelligence systems hosted by Internet “cloud computing” service providers. This “cloud BI”, which is actually SaaS (software-as-a-service) BI, has been riding the wave of cloud computing in general, with the lower startup costs, faster deployment and easier scalability that cloud-based software implementations promise business customers. Several new companies have emerged and are promoting a new golden age of BI which they say will be faster, easier and cheaper than conventional business intelligence.

While this sounds fantastic at first glance, it might be a good idea to look beyond the hype to determine if deploying a BI solution in the cloud offers the same types of advantages of other SaaS solutions, such as CRM, accounting and email.

To this end, consider the following questions:

• Does SaaS BI reduce dependence on IT staff?
• Does SaaS BI compromise your data security?
• Is all your business data already in the cloud?
• How much will hardware cost you in the cloud?
• Is BI backbone technology becoming more 'cloud-aware'?

---

1. Does SaaS BI reduce dependence on IT staff?

One of the main claims of SaaS software solutions in general is the dramatic reduction in dependence on expensive (and overworked) information technology (IT) professionals. In many organizations, the limited availability of IT staff is a major bottleneck when considering implementing a new software system that will be used across the organization. The cloud offers an appealing solution: since there is no hardware to install, no software to upgrade, no storage to backup and no new security mechanisms to implement, adopting a cloud solution (mostly) circumvents the need for extensive IT services (whether from in-house personnel or consultants).

In the realm of conventional BI systems, dependence on IT is a common and frustrating bottleneck for business users. The IT department (or consulting firm) is required for every piece of the BI puzzle, from the data warehousing to creating OLAP cubes to creating and customizing individual reports. In most companies, business users quickly discover that getting what they want from their company’s BI system, including incorporating new data sources, adding reports, customizing dashboards and extending the system to more users/departments, requires IT resources which are often unavailable when needed. The result is a frustrating and compromised system which fails to deliver on its full strategic potential.

So, does moving to the cloud solve this central problem of conventional BI?

The truth is that hosted BI solutions are really just outsourced IT departments which happen to come along with a bag full of their own home-grown or third-party software systems. All the stages of the familiar BI system deployment – from requirements specification consulting through data warehouse creation through report customization – still require the involvement of IT staff.Since these IT professionals are experts in their software and environments, they will likely be more efficient than hiring or retraining your company’s own staff (although they will not likely be more effective than any other dedicated outsourced technical BI team). However, think carefully about how much you want to be dependent on outsourced IT services for the lifeblood of your company’s strategic decision-making platform.

If your BI needs are modest and don’t change often, then, in theory, you will probably end up saving money as compared with hiring your own IT staff for BI. However, as the system grows and extends (as it always does), you will be at the mercy of the schedules and price rates of a third-party IT team which you are locked in to. If you thought internal IT can be a bottleneck, imagine how difficult it will be to get good and timely service from an external IT department located far away and busy with numerous other customers as well.

2. Does SaaS BI compromise your data security?

There are two unrelated issues to think about here. Read the rest of the article...

Thoughts about Business Intelligence and the Cloud

Business intelligence in the cloud is a hot topic recently, as part of the hype surrounding the cloud in general. I am not a big fan of cloud BI and I have mentioned that several times. However the topic does merit discussion.

The advantages of the cloud over on-premises are pretty straight forward. However, as far as business intelligence implementations are concerned, the question to me was always whether the benefits outweigh the unique challenges the cloud introduces. If all business data was in the cloud, there was a definite case to make for implement business intelligence software in the cloud. But since most business data isn’t, the benefits of cloud BI are not as obvious.

The blogosphere and analyst community in the business intelligence space are not sparing any words on the subject. There are several startups in this space as well, such as GoodData, PivotLink and others. But is the business intelligence space really heading in the direction of the cloud? I believe the answer is no.

The main reason I do not believe that the BI space is headed towards the cloud (at least for now) is because business intelligence backbone technology doesn’t seem to be headed there. In fact, it seems to be going in the opposite direction.

If you take a careful look at the new technology promoted by the established business intelligence vendors like SAP, IBM and Microsoft, and even those promoted by slightly less established vendors (yet successful) such as QlikTech and Tableau – it is all technology that is either ‘desktop enabling’ technology or in-memory technology.

These technologies, in-memory in particular, aren’t very cloud friendly and weren’t designed with the cloud in mind at all. They are designed to extract more juice out of a single computer, but very hard to distribute across multiple machines as in the case in most cloud implementations. Also, to benefit significantly from these types of technologies, you need very powerful computers, a premise which goes against proper cloud architecture that dictates that computing operations should be parallelized across multiple cheaper machines.

On the other hand, the current cloud BI platform vendors are using the same traditional backbone technology the on-premises vendors do, and by that they suffer from the same drawbacks most BI vendors do such as complexity and long development cycles. And when these drawbacks come into play, whether the data is in the cloud or on-premises isn’t even the main issue.

Even if the ‘pure cloud’ BI platform vendors did develop better technology more suited for running BI in the cloud, it is still years away. So while you can use the cloud for some types of solutions (mainly around other cloud data sources) the fact of the matter is that the cloud BI hype is at least a few years too early.

---

By: Elad Israeli | The ElastiCube Chronicles - Business Intelligence Blog

The New Tableau 6.0 Data Engine – First Impressions

Tableau 6.0 is out, and according to Tableau Software’s CEO one of its main features is a new data engine. Here’s an excerpt from one of the articles covering Tableau’s latest release:

"Our new Tableau Data Engine achieves instant query response on hundreds of millions of data rows, even on hardware as basic as a corporate laptop... No other platform allows companies to choose in-memory analytics on gigabytes of data …" Christian Chabot, CEO of Tableau Software, said in a statement.

These are bombastic claims indeed and the underlined segments of the CEO’s quote are particularly interesting. So with the help of my friend, colleague and brilliant database technologist Eldad Farkash, I decided to put these claims to a real life test.

Since this data engine was claimed to be utilizing in-memory technology, we set up a 64-bit computer with adequate amounts of RAM (hardly a corporate laptop) and used a real customer’s data set consisting of 560 million rows of raw internet traffic data. To make it easier, we imported just a single text field out of this entire data set.

Initial Findings:
1. Surprisingly, and unlike what Tableau’s CEO claims, Tableau’s new data engine is not really in-memory technology. In fact, their entire data set is stored on disk after it is imported and RAM is hardly utilized.

2. It took Tableau 6.0 approximately 5 hours to import this single text field, out of which 1.5 hours was pure import and the rest a process Tableau calls ‘Column Optimization’ which we believe is creating an index very similar to that of a regular relational database. For comparison, it took QlikView 50 minutes and ElastiCube 30 minutes to import the same field. That is an x7 difference.  All products were using their default settings.

3. Once the import process completed, we asked Tableau to count how many distinct values existed in that field, a common query required for business intelligence purposes. That query took 30 minutes to return. For comparison, it took both QlikView and ElastiCube approximately 10 seconds to return. That’s an x180 difference.  Again, both products were used with their default settings.

Initial Conclusions:
Tableau’s new data engine is a step up from their previous engine which was quite similar to that which Microsoft Access had been using in Office 2007. That is good news for individual analysts working with non-trivial amounts of data using earlier versions of Tableau, which were quite poor in this respect.  This release, I imagine, also helps Tableau against SpotFire (Tibco), which until now was the only pure visualization player who could claim to have technology aimed for handling of larger data sets.

From a practical perspective, however, the handling of hundreds of millions of rows of data as well as the reference to in-memory analytics are more marketing fluff geared towards riding the in-memory hype than a true depiction of what this technology is or what it is capable of. Tableau’s data engine is not in the same league as in-memory technology, or pure columnar technologies like ElastiCube, when it comes to import times or query response times. In fact, it is slower by several orders of magnitude.

From OLAP Cubes to ElastiCubes – The Natural Evolution of BI

OLAP (Online Analytical Processing) technology is the most prevalent technology used in corporate BI solutions today. And while it does what it’s supposed to do very well, it has a bad (and accurate) reputation for being very expensive and difficult to implement, as well as extremely challenging to maintain. This fact has prevented OLAP technology from gaining wide popularity outside of Fortune 500-scale companies, which are the only ones who have the budgets for company-wide, OLAP-based BI implementations.

Since the inception of BI and consequent entrance of OLAP technology into the space, the need for BI has been rapidly growing. Recognizing that OLAP-based solutions were (and still are) hard to introduce into a wider market, thought leaders and visionaries in the space have been since then trying to bring BI down to the masses through technological and conceptual innovation.

The most recently recognized innovation (even though it’s been around for quite a while) was in-memory technology, whose main advantage was cutting implementation time and simplifying the process as a whole (a definite step in the right direction). However, as described in my recent article, In-Memory BI is Not the Future, It's the Past, using in-memory technology for speedy BI implementation introduces significant compromises, especially in terms of scalability (both for data volumes and support for many concurrent users). Now, after in-memory technology has been on the market for some time, it is clear that it is not really a replacement for OLAP technology, but did in fact expand the BI market to a wider audience. In fact, it is probably more accurate to say that in-memory technology and OLAP technology complement each other, each with its own advantages and tradeoffs.

In that article I also briefly mentioned the new disk-based ElastiCube technology (invented by SiSense). ElastiCube technology basically eliminates the inherent IMDB tradeoffs by providing unlimited scalability using off-the-shelf hardware while delivering both implementation and query response times as fast (or faster) as pure in-memory-based solutions. This claim was the subject of many of the emails and inquires I received following the article’s publication. I was repeatedly asked how ElastiCube technology had achieved what OLAP technology had failed to do for so many years, and what role in-memory technology played in its conception.

Thus, in this article I will describe how ElastiCube technology came to be, what inspired it, what made it possible and how it has already become a game-changer in the BI space, both in large corporations and small startups.

A Brief History of BI and OLAP
OLAP technology started gaining popularity in the late 1990s, and that had a lot to do with Microsoft’s first release of their OLAP Services product (now Analysis Services), based on technology acquired from Panorama Software. At that point in time, computer hardware wasn’t nearly as powerful as it is today; given the circumstances at the time, OLAP was groundbreaking. It introduced a spectacular way for business users (typically analysts) to easily perform multidimensional analysis of large volumes of business data. When Microsoft’s Multidimensional Expressions language (MDX) came closer to becoming a standard, more and more client tools (e.g., Panorama NovaView, ProClarity) started popping up to provide even more power to these users.

While Microsoft was not the first BI vendor around, their OLAP Services product was unique and significantly helped increase overall awareness of the possibilities offered by BI. Microsoft started gaining market share fairly quickly, as more companies started investing in BI solutions.

But as the years passed by, it became very apparent that while the type of multidimensional BI empowered by OLAP technology was a valuable asset to any organization, it seemed to be used mainly by large corporations. OLAP is just too complex and requires too much time and money to be implemented and maintained, thus eliminating it as a viable option for the majority of the market.

See: Microsoft (SSAS), IBM (Cognos)

The Visualization Front-End Craze
As more companies began investing in BI solutions, many vendors recognized the great opportunity in bringing BI to the mass market of companies with less money to spend than Fortune 500 firms. This is where visualization front-end vendors started popping up like mushrooms after the rain, each of them promising advanced business analytics to the end user, with minimal or no IT projects involved. Their appeal was based on radically reducing the infamous total cost of ownership (TCO) of typical BI solutions. These products, many of which are still available today, are full of useful and advanced visualization features.

However, after years of selling these products, it became very clear that they are incapable of providing a true alternative to OLAP-based solutions. Since they fail to provide similar centralized data integration and management capabilities, they found themselves competing mainly with Excel, and were being used only for analysis and reporting of limited data sets by individuals or small workgroups.

In order to work around these limitations (and increase revenues), these tools were introduced connectivity to OLAP sources as well as to the tabular (e.g., spreadsheet) data they supported until then. By doing that, these products basically negated the purpose for which they were initially designed – to provide an alternative to the expensive OLAP-based BI solutions.

See: Tableau Software, Tibco SpotFire, Panorama Software

The In-Memory Opportunity
The proliferation of cheap and widely available 64-bit PCs during the past few years has somewhat changed the rules of the game. More RAM could be installed in a PC, a boon for those visualization front-end vendors struggling to get more market share. More RAM on a PC means that more data can be quickly queried. If crunching a million rows of data on a machine with only 2GB of RAM was a drag, users could now add more gigabytes of RAM to their PCs and instantly solve the problem. But still, without providing centralized data integration and management, this was not a true alternative to OLAP-based solutions that are still prominent in massive organization-wide (or even inter-departmental) implementations.

Strangely enough, out of all the in-memory technology vendors out there, only one realized that using in-memory technology to empower individual users wasn't enough and that the way to gain more significant market share was to provide an end-to-end solution, from ETL to centralized data sharing to a front-end development environment. This vendor is QlikTech and it is no wonder that the company is flying high above the rest of the non-OLAP BI players. QlikTech used in-memory technology to cover a much wider range of BI solutions than any single front-end visualization tool could ever do.

By providing data integration and centralized data access capabilities, QlikTech was able to provide solutions that, for other vendors (in-memory or otherwise), required at least a lengthy data warehouse project if not a full-blown OLAP implementation. By utilizing in-memory technology in conjunction with 64-bit computing, QlikTech solutions work even on substantial amounts of data (significantly more than their traditional disk-based competitors could).

However, QlikTech has not been able to make a case for replacing OLAP yet. I believe this is not only because of the scalability issues and hardware requirements involved when large amounts of data and/or users are involved, but it’s also because they do not inherently support dimensional modeling like OLAP does. Apart from making life simpler for IT when maintaining multiple applications, OLAP’s implementation of a dimensional model also gives end users, via supporting front end tools, a broader range of flexibility in creating their own BI applications.

Microsoft, the newest entry into the in-memory BI game, also started marketing its in-memory PowerPivot solution as an alternative to OLAP, basically admitting it gives up on its Analysis Services as a viable solution for the wider mid-market.

See: QlikTech (QlikView), Microsoft (PowerPivot)

The SaaS/Cloud BI Hype
The SaaS/Cloud hype hasn’t skipped over the BI space, though running BI in the cloud does not dramatically change anything in respect to implementation time and/or complexity of implementation. In fact, cloud BI vendors use the same technologies that are widely used on-premises. There are several startup companies in this space, competing for niche markets.

It’s still hard to tell what impact the cloud would have on the BI space as a whole as none of these companies has yet to prove there’s even a viable business for hosting BI in the cloud. One thing is certain, though: these companies cannot rely on in-memory technology to grow significantly. The costs of hardware and the amount of work required to support the number of customers they would need to thrive are prohibitive, to say the least. For more on the problem with cloud BI, see my earlier post, Would I Use Cloud Business Intelligence?

See: GoodData, YouCalc, Birst, PivotLink, Indicee

ElastiCube: Convergent Technologies for an Optimum Solution
ElastiCube technology was officially introduced to the market in late 2009, after more than five years of research and development conducted in complete secrecy. After being proved practical and effective in the real world (by being successfully implemented at over 100 companies, paying customers in numerous industries, from startups to multinational corporations), SiSense secured a $4 million investment to continue the development of the ElastiCube technology, and to expand awareness of the Prism Business Intelligence product which is based on the technology.

ElastiCube is the result of thoroughly analyzing the strengths and weaknesses of both OLAP and in-memory technologies, while taking into consideration the off-the-shelf hardware of today and tomorrow. The vision was to provide a true alternative to OLAP technology, without compromising on the speediness of the development cycle and query response times for which in-memory technologies are lauded. This would allow a single technology to be used in BI solutions of any scale, in any industry.

Here are the 10 main goals on which SiSense focused when designing the ElastiCube technology:

1. A data warehouse must not be assumed to exist for effectively querying multiple sources.

2. A star schema must not be assumed to exist for effective querying large amounts of data.

3. The solution must provide unlimited scalability, both in terms of number of rows and number of fields, within a finite and reasonable amount of RAM.

4. The solution must be able to operate using off-the-shelf hardware, even for extreme data/user scenarios.

5. The solution must provide high-speed, out-of-the-box query performance, without requiring pre-calculations.

6. There must be a separation between the application layer and the physical data layer via a virtual metadata layer.

7. There must be support for a dimensional model and multidimensional analysis.

8. The same application must be able to support a single user with a laptop to thousands of users via a central, server-based data repository.

9. Without running an SQL database, an SQL layer must be available to conform to industry standards.

10. The solution must offer the ability to incorporate additional/changed data (e.g., new rows, new fields) on the fly, without reprocessing the entire data model.

The presently available version of Prism, based on ElastiCube technology, delivers on every one of these requirements. Even though it would be a lot of fun for me, I unfortunately can’t delve into the nuts and bolts of how these goals are technologically achieved. What I can say is that ElastiCube utilizes columnar storage concepts as well as just-in-time in-memory query processing technology. If you want to read a little about it, you can take a look at SiSense’s ElastiCube technology page.

I can add that the feasibility of ElastiCube was greatly affected by the amazing CPU and disk technologies that now come with any run-of-the-mill personal computer.

ElastiCube is extremely powerful technology that enables speedy implementation of individual, workgroup and corporate-wide BI. As a solution that delivers the promise of OLAP-style BI without the cost, time and IT overhead of OLAP, it is no surprise that Prism is rapidly gaining popularity in the market. Businesses that use ElastiCube technology include household names such as, Target, Yahoo, Cisco, Samsung, Philips and Caterpillar. But a significant portion of business that use ElastiCube are significantly smaller, such as Wix and other startup companies - who otherwise could not afford BI at all.
See: SiSense (Prism)

---

By: Elad Israeli | The ElastiCube Chronicles - Business Intelligence Blog

In-memory BI is not the future. It’s the past.

In recent times, one of the most popular subjects related to the field of Business Intelligence (BI) has been In-memory BI technology. The subject gained popularity largely due to the success of QlikTech, provider of the in-memory-based QlikView BI product. Following QlikTech’s lead, many other BI vendors have jumped on the in-memory “hype wagon,” including the software giant, Microsoft, which has been aggressively marketing PowerPivot, their own in-memory database engine.

The increasing hype surrounding in-memory BI has caused BI consultants, analysts and even vendors to spew out endless articles, blog posts and white papers on the subject, many of which have also gone the extra mile to describe in-memory technology as the future of business intelligence, the death blow to the data warehouse and the swan song of OLAP technology.

I find one of these in my inbox every couple of weeks.

Just so it is clear - the concept of in-memory business intelligence is not new. It has been around for many years. The only reason it became widely known recently is because it wasn’t feasible before 64-bit computing became commonly available. Before 64-bit processors, the maximum amount of RAM a computer could utilize was barely 4GB, which is hardly enough to accommodate even the simplest of multi-user BI solutions. Only when 64-bit systems became cheap enough did it became possible to consider in-memory technology as a practical option for BI.

The success of QlikTech and the relentless activities of Microsoft’s marketing machine have managed to confuse many in terms of what role in-memory technology plays in BI implementations. And that is why many of the articles out there, which are written by marketers or market analysts who are not proficient in the internal workings of database technology (and assume their readers aren’t either), are usually filled with inaccuracies and, in many cases, pure nonsense.

The purpose of this article is to put both in-memory and disk-based BI technologies in perspective, explain the differences between them and finally lay out, in simple terms, why disk-based BI technology isn’t on its way to extinction. Rather, disk-based BI technology is evolving into something that will significantly limit the use of in-memory technology in typical BI implementations.

But before we get to that, for the sake of those who are not very familiar with in-memory BI technology, here’s a brief introduction to the topic.

Disk and RAM

Generally speaking, your computer has two types of data storage mechanisms – disk (often called a hard disk) and RAM (random access memory). The important differences between them (for this discussion) are outlined in the following table:

Most modern computers have 15-100 times more available disk storage than they do RAM. My laptop, for example, has 8GB of RAM and 300GB of available disk space. However, reading data from disk is much slower than reading the same data from RAM. This is one of the reasons why 1GB of RAM costs approximately 320 times that of 1GB of disk space.

Another important distinction is what happens to the data when the computer is powered down: data stored on disk is unaffected (which is why your saved documents are still there the next time you turn on your computer), but data residing in RAM is instantly lost. So, while you don’t have to re-create your disk-stored Microsoft Word documents after a reboot, you do have to re-load the operating system, re-launch the word processor and reload your document. This is because applications and their internal data are partly, if not entirely, stored in RAM while they are running.

Disk-based Databases and In-memory Databases

Now that we have a general idea of what the basic differences between disk and RAM are, what are the differences between disk-based and in-memory databases? Well, all data is always kept on hard disks (so that they are saved even when the power goes down). When we talk about whether a database is disk-based or in-memory, we are talking about where the data resides while it is actively being queried by an application: with disk-based databases, the data is queried while stored on disk and with in-memory databases, the data being queried is first loaded into RAM.

Disk-based databases are engineered to efficiently query data residing on the hard drive. At a very basic level, these databases assume that the entire data cannot fit inside the relatively small amount of RAM available and therefore must have very efficient disk reads in order for queries to be returned within a reasonable time frame. The engineers of such databases have the benefit of unlimited storage, but must face the challenges of relying on relatively slow disk operations.

On the other hand, in-memory databases work under the opposite assumption that the data can, in fact, fit entirely inside the RAM. The engineers of in-memory databases benefit from utilizing the fastest storage system a computer has (RAM), but have much less of it at their disposal.

That is the fundamental trade-off in disk-based and in-memory technologies: faster reads and limited amounts of data versus slower reads and practically unlimited amounts of data. These are two critical considerations for business intelligence applications, as it is important both to have fast query response times and to have access to as much data as possible.

The Data Challenge

A business intelligence solution (almost) always has a single data store at its center. This data store is usually called a database, data warehouse, data mart or OLAP cube. This is where the data that can be queried by the BI application is stored.

The challenges in creating this data store using traditional disk-based technologies is what gave in-memory technology its 15 minutes (ok, maybe 30 minutes) of fame. Having the entire data model stored inside RAM allowed bypassing some of the challenges encountered by their disk-based counterparts, namely the issue of query response times or ‘slow queries.’

Disk-based BI

When saying ‘traditional disk-based’ technologies, we typically mean relational database management systems (RDBMS) such as SQL Server, Oracle, MySQL and many others. It’s true that having a BI solution perform well using these types of databases as their backbone is far more challenging than simply shoving the entire data model into RAM, where performance gains would be immediate due to the fact RAM is so much faster than disk.

It’s commonly thought that relational databases are too slow for BI queries over data in (or close to) its raw form due to the fact they are disk-based. The truth is, however, that it’s because of how they use the disk and how often they use it.

Relational databases were designed with transactional processing in mind. But having a database be able to support high-performance insertions and updates of transactions (i.e., rows in a table) as well as properly accommodating the types of queries typically executed in BI solutions (e.g., aggregating, grouping, joining) is impossible. These are two mutually-exclusive engineering goals, that is to say they require completely different architectures at the very core. You simply can’t use the same approach to ideally achieve both.

In addition, the standard query language used to extract transactions from relational databases (SQL) is syntactically designed for the efficient fetching of rows, while rare are the cases in BI where you would need to scan or retrieve an entire row of data. It is nearly impossible to formulate an efficient BI query using SQL syntax.

So while relational databases are great as the backbone of operational applications such as CRM, ERP or Web sites, where transactions are frequently and simultaneously inserted, they are a poor choice for supporting analytic applications which usually involve simultaneous retrieval of partial rows along with heavy calculations.

In-memory BI

In-memory databases approach the querying problem by loading the entire dataset into RAM. In so doing, they remove the need to access the disk to run queries, thus gaining an immediate and substantial performance advantage (simply because scanning data in RAM is orders of magnitude faster than reading it from disk). Some of these databases introduce additional optimizations which further improve performance. Most of them also employ compression techniques to represent even more data in the same amount of RAM.

Regardless of what fancy footwork is used with an in-memory database, storing the entire dataset in RAM has a serious implication: the amount of data you can query with in-memory technology is limited by the amount of free RAM available, and there will always be much less available RAM than available disk space.

The bottom line is that this limited memory space means that the quality and effectiveness of your BI application will be hindered: the more historical data to which you have access and/or the more fields you can query, the better analysis, insight and, well, intelligence you can get.

You could add more and more RAM, but then the hardware you require becomes exponentially more expensive. The fact that 64-bit computers are cheap and can theoretically support unlimited amounts of RAM does not mean they actually do in practice. A standard desktop-class (read: cheap) computer with standard hardware physically supports up to 12GB of RAM today. If you need more, you can move on to a different class of computer which costs about twice as much and will allow you up to 64GB. Beyond 64GB, you can no longer use what is categorized as a personal computer but will require a full-blown server which brings you into very expensive computing territory.

It is also important to understand that the amount of RAM you need is not only affected by the amount of data you have, but also by the number of people simultaneously querying it. Having 5-10 people using the same in-memory BI application could easily double the amount of RAM required for intermediate calculations that need to be performed to generate the query results. A key success factor in most BI solutions is having a large number of users, so you need to tread carefully when considering in-memory technology for real-world BI. Otherwise, your hardware costs may spiral beyond what you are willing or able to spend (today, or in the future as your needs increase).

There are other implications to having your data model stored in memory, such as having to re-load it from disk to RAM every time the computer reboots and not being able to use the computer for anything other than the particular data model you’re using because its RAM is all used up.

A Note about QlikView and PowerPivot In-memory Technologies

QlikTech is the most active in-memory BI player out there so their QlikView in-memory technology is worth addressing in its own right. It has been repeatedly described as “unique, patented associative technology” but, in fact, there is nothing “associative” about QlikView’s in-memory technology. QlikView uses a simple tabular data model, stored entirely in-memory, with basic token-based compression applied to it. In QlikView’s case, the word associative relates to the functionality of its user interface, not how the data model is physically stored. Associative databases are a completely different beast and have nothing in common with QlikView’s technology.

PowerPivot uses a similar concept, but is engineered somewhat differently due to the fact it’s meant to be used largely within Excel. In this respect, PowerPivot relies on a columnar approach to storage that is better suited for the types of calculations conducted in Excel 2010, as well as for compression. Quality of compression is a significant differentiator between in-memory technologies as better compression means that you can store more data in the same amount RAM (i.e., more data is available for users to query). In its current version, however, PowerPivot is still very limited in the amounts of data it supports and requires a ridiculous amount of RAM.

The Present and Future Technologies

The destiny of BI lies in technologies that leverage the respective benefits of both disk-based and in-memory technologies to deliver fast query responses and extensive multi-user access without monstrous hardware requirements. Obviously, these technologies cannot be based on relational databases, but they must also not be designed to assume a massive amount of RAM, which is a very scarce resource.

These types of technologies are not theoretical anymore and are already utilized by businesses worldwide. Some are designed to distribute different portions of complex queries across multiple cheaper computers (this is a good option for cloud-based BI systems) and some are designed to take advantage of 21st-century hardware (multi-core architectures, upgraded CPU cache sizes, etc.) to extract more juice from off-the-shelf computers.

A Final Note: ElastiCube Technology

The technology developed by the company I co-founded, SiSense, belongs to the latter category. That is, SiSense utilizes technology which combines the best of disk-based and in-memory solutions, essentially eliminating the downsides of each. SiSense’s BI product, Prism, enables a standard PC to deliver a much wider variety of BI solutions, even when very large amounts of data, large numbers of users and/or large numbers of data sources are involved, as is the case in typical BI projects.

When we began our research at SiSense, our technological assumption was that it is possible to achieve in-memory-class query response times, even for hundreds of users simultaneously accessing massive data sets, while keeping the data (mostly) stored on disk. The result of our hybrid disk-based/in-memory technology is a BI solution based on what we now call ElastiCube, after which this blog is named. You can read more about this technological approach, which we call Just-in-Time In-memory Processing, at our BI Software Evolved technology page.

---

By: Elad Israeli | The ElastiCube Chronicles - Business Intelligence Blog