In the first part of this blog series, I discussed the traditional data warehouse and some of issues that can occur in the period following the initial project.
I concluded by outlining the 5 common challenges that I hear from customers:
This blog will focus on the third of these issues: SLOW PERFORMANCE.
How many times have you heard of business users complaining that the BI system is slow and that they wish the system can be faster?
It is easy to make broad statements about performance in relation to data warehouses and Business Intelligence solutions. However, it can be difficult to narrow down the cause of the performance issues as the BI/DW solution is a careful construction of multiple components which are interdependent of each other.
In an earlier post, I described the components of a typical traditional data warehouse implementation from the data sources to the Business Intelligence visualisation layer.
In order to fully understand the cause of slow performance let us consider the process of information value chain, the typical path from transaction, to information, to insight and ultimately to action.
The information value chain can be compared to the experience of the kitchen in a restaurant business. There are a number of distinct steps that needs to be followed from the menu being decided, customer placing an order and then finally delivery of the food and consumption by the customer (with the customer hopefully satiated and fully satisfied!).
Some of these activities should be completed before the customer decides on what to eat and therefore will have no bearing on how long it takes between placing the order and consuming the food. However, there is also a number of variables that can have impact on the wait time.
There are 2 distinct stages to be considered:
- The time it takes for the data to be ready to be queried - BACKEND (DATA PREPARATION)
- The time it takes for the data to be queried - FRONTEND (DATA CONSUMPTION)
This post will dig deeper into the causes of each of these issues and discuss the traditional solutions for addressing/ mitigating their effect.
BACKEND/ DATA PREPARATION ISSUES
Imagine that the order is in and the clock is ticking. The cooking process begins and any delays and issues introduced are immediately felt downstream.
However, there is one critical issue that can occur before the meal is cooked (preparing the data for consumption by the business users).
What if there are wrong ingredients on the kitchen shelf, no ingredients or they arrived too late!
Source to Staging
A common place to find performance issues in established data warehouse environments is in the extraction of data from the source systems, e.g. the process of getting quality ingredients on the kitchen shelf. However, we will cover Data Quality in a later post focussing on Governance within Data Warehouses.
Assuming that the source system is available and that the data structures have not changed, then what can negatively impact this performance and keep the business users waiting?
- Degradation in extract times due to source system transactional load - the supplier of the ingredient is too busy and thus unable to get the ingredients to the kitchen in time.
- Growth in source system data volumes - the supplier carries way more stock than before and it is taking too long to sort and pack the required delivery.
- Growth in extract volumes - the kitchen is asking for larger deliveries and the supplier or the goods receipt team is unable to cope.
Not only can these issues have a major effect on the time it takes for the ingredients (source data) to arrive at the back door of the kitchen (data warehouse). They also impact the amount of time it takes to move them into the kitchen storage (data warehouse staging area) itself.
In some cases, an explosion in volumes may lead to the kitchen no longer being capable of storing the ingredients which can then severely limit the ability to service customers.
When dealing with new data arriving for your data warehouse, it is essential that delta loads are used to ensure only fresh data is taken in. Delta loads are data that is new or has changed. This is akin to the kitchen analogy where the kitchen crew only receive ingredients that are required instead of a full delivery of all ingredients every day.
Staging to Presentation
This is where the main preparation and cooking happens, also invariably this is where a number of incremental performance issues can have a seismic effect.
A common architectural mistake is not to look at the ETL process as a value chain. This is often results in an ETL process that resembles a loose collection of semi-related tasks rather than an optimal and lean end-to-end process. In the kitchen, it is akin to a group of chefs operating independently without orchestration or leadership, not as a cohesive unit.
Common ETL processes should be standardised, using repeatable steps to lower maintenance and management costs. If common ingredients are required across multiple recipes then why not clean and prepare them in one go, in advance. Effort should be streamlined and not duplicated.
Unnecessary duplication of data sets can be a real problem, the culinary equivalent of taking multiple sets of ingredients to the kitchen preparation table for every dish. A decision that not only introduces overheads to the individual activity but can have knock on effects further down the process chain. In a traditional data warehouse, working and copying large data sets takes time ignoring the immediate issues around the disk space they take!
Traditional dimensionally modelled data warehouses often slow down considerably during the joins to the dimension tables that are used to assign key allocations within the fact tables. These generally rely on large complex queries that can be constrained by the raw processing and IO speed of the database server, in other words, how fast your chefs can perform complex functions.
In staging to Preparation, there are two factors that contribute to poor performance:
- Costly data read and write activities - shifting data back and fourth from slow disks
- Inefficient processes - whether hand coded or using an ETL tool, there is often unnecessary duplication of effort and minimal reuse
The end result is that the traditional data warehouse is often only prepared and made available overnight during an increasingly short batch window.
It cannot be overstated how many times complaints are heard from customers about scheduled overnight reports that have been delayed due to failed or late running data warehouse loads. Team members who silently pray before checking their email at 06:00 daily dreading the error report from their data warehouse.
Even if the process currently fits within the nightly batch window, it shouldn't be hard to appreciate that in today's fast moving business environment it is not too healthy to only be able to make decisions based on the position at the end of yesterday.
You wouldn't want your meal prepared the day before using old ingredients would you?
FRONTEND (DATA CONSUMPTION)
Before getting into the detail, let us introduce the concept of Decision Lag.
"The decision lag is the period between the time when the need for action is recognised and the time when action is taken."
Citation: Encyclopedia Britannica
In data warehousing and business intelligence terms; the elapsed time between business users realising the need for data to support a decision and when receiving data in a format that can be consumed.
Using the restaurant analogy; the period between when customers deciding what they want to eat, to when putting the food into their mouth!
Discussion about Decision Lag also introduces an interesting behavioural concept.
The majority of complaints are associated with the perceived elapsed time between the users requesting and receiving the information.
Under closer inspection, it is typical to find large amounts of time being wasted identifying the information required and then translating it in to the relevant action. Users however are unlikely to complain about this as it is dependent on their individual analytical and decision making abilities. Solving this particular issue is much more about educating users to be more analytical and BI savvy. A strong Business Intelligence competency centre should be driving this increase in business user analytical maturity.
It is now more common to hear of complaints on long running reports/ queries, or an inability to run requests on demand, impacting the ability to produce data that is ready to be consumed.
These performance issues can be grouped into two categories:
- Query execution - the time it takes to get the data back to the BI platform/ tool.
- Information Presentation - the time it takes for the data to be converted into and presented as usable information.
Users are no longer prepared to wait to get the data they need in the format they want it, and above and beyond all this, they expect it to be simple to consume.
Query Execution time is by far the most common performance problem that we hear complaints of or are asked to help with. Increasing data volumes, multiple data sets and more complex analytical requirements all lead to the necessity to execute increasingly complex, and as a result, longer running queries.
In the early days of Data Warehousing the focus was on preparing data that ended up in reports that were generally scheduled, distributed and consumed on daily, weekly and monthly cycles. Evolving requirements mean that report consumption can no longer be batch oriented and as a result far less predictable. This problem is made worse with the rise in popularity of predictive analytics.
The traditional performance optimisation activities such as pre-aggregation, indexing, faster disks, more cores, and OLAP servers is no longer sufficient. Particularly when there are requirements to support predictive models running over terabytes of unstructured log data that needs to be ingested in real-time.
In the restaurant analogy; the expectation is for the kitchen to prepare a complex haute cuisine dish in the time it takes to get a McDonalds Big Mac!
The Business Intelligence platform/tool plays an important role and is the crucial architectural layer that sits between the data warehouse and business users. Finger pointing is likely to happen when there are performance issues; e.g. due to the data warehouse or BI Apps. It is however important to take into consideration the whole architecture when considering this issue.
It is true that one poorly formed query from a loosely governed font end tool can wreak havoc on your data warehouse. With that in mind, here are some questions to consider in relation to the performance relationship between your data warehouse and front end BI tools:
- Do you know and understand all of the tools that are in use against your data warehouse?
- Do you genuinely understand from a business perspective the value of what people are trying to do with the data they get from the warehouse?
- Have you checked and benchmarked the SQL (or MDX) that the tools generate to see if it can be improved?
- Can you provide native connections to all the tools you have to support?
- Can your data warehouse provide open standard based connectivity for programmatic data access and interfacing?
- How much of their workload do your tools offload to the database?
- Does your data warehouse provide asynchronous connections?
- Where will data caching (if available) take place?
- Does your warehouse support in database processing to improve performance?
- If you provide mobile access, where does the data processing happen?
The bottom line is this: the technical requirements and performance expectations placed on a typical data warehouse have increased exponentially over recent years and many are struggling to keep up.
So what’s the answer?
Is it possible to have a data warehouse that can speed the ingestion of traditional transactional sources as well as being able to load real time streaming and unstructured data without grinding to a halt?
Can you have a data platform that is able to scale and leverage modern, high power (yet still commodity) hardware to accelerate the preparation of the data so that it can be made available for querying as soon as possible?
Is there a way to evolve your data warehouse in order to adopt new innovative technologies that have become available in recent times, to support new business workloads such as Predictive and Advanced Analytics?
Would it be possible to replace your existing aged, and frankly struggling data warehouse, without facing prohibitive costs or high barriers to accessing new technical skills?
The answer: Yes (Of course…).
The Modern Data Platform
Delivering on all the requirements for a next generation data warehouse. Enabling organisations to radically simplify their existing legacy or overly complex solutions in order to lower running costs, improve agility and gain breakthrough performance to deliver real business value.
Remember to follow the rest of the posts in this blog series where we will explore in detail the 2 remaining common challenges of traditional data warehousing.