Interacting with your data warehouse in real-time

The two most important factors in the success of a company’s data analysis are the query performance of the data warehouse and the quality and timeliness of the information provided by it. Only a data warehouse that is fast and flexible enough to allow real-time interaction with all enterprise data can meet the highest level of enterprise demands.

Gerhard Rumpff

“With EXASolution, we offer a high performance database that helps companies to analyze and evaluate enterprise data at enormous speed - reliably, powerfully and cost-effectively”
-Gerhard Rumpff, CEO of EXASOL AG

How do users behave on an online retailer's website? How are they responding to today's special offer? How are the sales of any particular product going compared to others on the same site, or those on competitive sites? These are all questions that such an enterprise has to be able to answer quickly and precisely in order to optimize marketing on the one hand and the customer experience on the other. Management needs rapid and up-to-date information in order to support accurate and timely decision-making, for both operational and strategic reasons. After all, only companies that are able to respond in an ad-hoc fashion to fast-changing business realities can retain and improve their competitiveness. Knowing in real-time what is happening in the company and in the market is a key factor, especially in the current economic environment.

In its latest BI Survey,  BARC - Business Application Research Center found that 20 percent of the more than 2,600 BI users questioned were very dissatisfied with how long it takes an analytic query to return results. The situation is typically as follows: the data needed for analysis is provided by a data warehouse (with multiple terabyte warehouses now commonplace) and one of a variety of analytical tools is used to visualize the data. Unfortunately, users are generally disappointed by the performance of these solutions and often assume that their front-end solution is to blame. But that is a false assumption: the real reason for performance problems is almost always inadequate provision of data from the existing data warehouse or data marts.

Data warehouses based on traditional database management systems have a number of inherent weaknesses in coping with time-critical tasks that change frequently and that cannot be predicted in advance. Part of this is due to the fact that they cannot adapt flexibly enough to different scenarios that include standard reporting, ad-hoc queries, data mining, complex analytics and frequent updates within a single environment.

Users of existing data warehouses have to come to terms with the necessity of extra work and high costs if they want to meet requirements for time-critical analyses. To speed up queries, aggregations are often performed and partial sections pre-calculated beforehand.
If the task then changes, the resources have to be reorganized and re-balanced. This tuning not only increases the administrative overhead involved, it also slows down overall performance. The only remedy is to use a data warehouse architecture that has been designed from top to bottom to handle analyses of complex data quantities in real-time.
"In website analysis, we currently process around 50 billion datasets per year. For this vast amount of data, we need a powerful database management system," explains Christian Sauer, CEO at Webtrekk GmbH. It had become increasingly obvious to the company that analysis of these large volumes of data was pushing the existing MySQL database to its limits. For segmentation of customers according to certain properties, such as 'visitors from Berlin with DSL access' or 'first-time visitors', the company was unable to use all the available raw data. Instead it was necessary to aggregate the data in an additional step before being able to process it, thereby slowing down the whole process. Webtrekk had also become dissatisfied with the performance it was achieving with regard to response times: "so our goal was a data warehouse solution which could analyze, segment, and graphically display such complex data volumes in real time," says Christian Sauer.

"After an extensive benchmark test, we selected the solution from EXASOL with Intel Xeon processors.The high performance of the system, the price/performance ratio, and the service from EXASOL totally convinced us," explains Christian Sauer.

The new data warehouse solution offers several benefits for Webtrekk. For instance, the vastly improved system performance makes it possible to process raw data for segmentation purposes - it is no longer necessary to precalculate and aggregate data in advance. That saves an enormous amount of administrative effort and enables much more timely analysis of user behavior on websites. As a result, Webtrekk is now able to calculate and visualize subsets of a much larger data volume in real-time.

And Webtrekk is no exception. Most companies face the same challenges. They have to analyze and assess their business performance on the basis of a huge and steadily growing volume of data, supplied by a large number of applications such as ERP and CRM systems as well as from external sources, while also keeping costs under control.

The core expertise of IMS Health, a leading market research company for the pharmaceutical industry, is in the analysis of huge volumes of data from countless sources and with different granularity, in near-real-time.

For more than 50 years, IMS Health has been providing decision-makers and researchers in the pharmaceutical and healthcare industries with relevant and near-real-time data on the market performance of their products compared with those of the competition, to enable strategic planning and portfolio development. To do that, the market researchers use data from innumerable sources, such as hospitals, pharmacies, pharmaceutical wholesalers and physicians.

IMS Health requires not only a very large storage capacity, but above all powerful applications and a data warehouse that allows the data to be analyzed, extracted and loaded very quickly. The systems used must also be highly scalable, since the volume of data grows steadily, new data sources are added constantly and the different sources have to be integrated with each other.

To handle all these tasks in a professional manner, IMS has been using the highly scalable database management system EXASolution for more than two years. EXASolution has increased the performance of data analyses by up to a factor of 80. Thanks to this significant increase in performance, IMS can address customers' requirements better, since the time needed to supply and visualize data has been reduced from several days to just one - or from hours to minutes.

"The shorter time needed to deliver data means a considerable gain for our customers - particularly in the pharmaceutical industry, where fast and low-risk decisions are a must," says Michael Kempke, Manager Operations Germany at IMS Health.

Up-to-date data that enables ever faster yet sound decisions is vital to many organisations, and it is increasingly becoming a strategic competitive factor. The update cycles of today's data warehouses have been dramatically reduced. While data used to be loaded every week or month, several updates a day or even real-time updates are no longer a rarity. To ensure lasting benefits, it must be possible to submit both simple and complex analysis queries to the database simultaneously.

EXASolution has been developed specifically for analytical use and is being deployed successfully by well-known large and medium-sized enterprises for data warehousing, web analytics and data mining applications. This horizontal database solution scales as needed and can be used for data marts in individual departments or for data warehousing throughout the enterprise.