When your business has a mountain of data to deal with, it can be hard to manage and structure every source to gather valuable insights and drive decision-making.
With data coming from different places, the key is to collect your business data and load it into a service that's easy for any user in the business to access.
The solution quite often lies within a data warehouse. Let’s look at why.
What is a data warehouse?
Here’s a short definition of a data warehouse:
A data warehouse is a unified data structure that aggregates data from multiple sources into a single database location for business intelligence and decision-making, based on the needs of an organization.
What are the benefits of a data warehouse?
Organizations have been using data warehouses for their data storage since the 1990s. Once unwieldy projects, they’ve evolved into more agile models that provide multiple benefits. Let’s take a look at them in turn.
- More availability: Modern data warehouses sit in the cloud, which means services such as Amazon Redshift and Snowflake are available to a wider audience.
- Cheap to build: A data warehouse requires relatively little upfront spending, with some systems costing less than $50,000.
- It’s scalable: A data warehouse enables you to build scalable workflows that adapt to changes data demands. The capacity also stretches as your requirements increase.
- Easier access: A data warehouse stores large amounts of data from multiple locations in one place, simplifying access to information.
- Reduced operational costs: Data warehouses work with structured data in batch mode, which reduces operational costs. This is because they don't require specialized data entry clerks.
- Better quality data: The curated data comes with its full history, enabling fast and accurate answers to analytic queries.
- Standardized structure: Regardless of the source, a data warehouse standardizes all data into one format. This makes it easier for collaboration within organizations.
- Better insights: With a data warehouse, you can track historical data over time. This gives you key insights that will help to inform your business decisions.
- Up-to-date reporting: A data warehouse loads transactional information from operational systems, providing relevant information for up-to-date reports.
Businesses that need fast and accurate insights into core processes such as inventories and KPIs can benefit from a data warehouse. They’re particularly useful in marketing research, financial management and sales.
That said, data warehouses aren’t for everyone. Let’s look at why.
The disadvantages of a data warehouse
The specificity required for the analytics within a data warehouse is both a blessing and a curse. Here are a couple of factors that might swing your decision in another direction.
- Costly investment: While the data warehouse system isn’t expensive to build, cleaning and preparing the data is both time-consuming and costly.
- Lacks flexibility: A data warehouse benefits organizations that optimize their structure with their data needs in mind. If you don’t address all possible questions during set-up, it’ll likely call for an expensive update down the line.
While a data warehouse can provide fast and accurate answers to specific questions, it takes a lot of time, effort, and capital to enable this level of sophistication. As such, another model of enterprise data architecture might be appropriate for you.
Kimball vs Inmon: What are the different types of data warehouses?
“You can catch all the minnows in the ocean and stack them together — they still do not make a whale”. - Bill Inmon
Inmon means that you should feed data into the data warehouse right after the ETL process. Kimball’s method, however, involves loading the data into data marts to make up the structure of the warehouse.
But before we get into the ins and outs of Kimball vs Inmon, here’s a short definition of a data mart:
A data mart is an outlet in a data warehouse for a specific department or subject area. Data marts often structure data in a different way to a data warehouse, making it a better fit for department‑specific questions.
And yet, there’s more to each method than their approach to data marts. Let’s examine each in turn.
What is the Kimball method?
The Kimball method, or the bottom-up method as it’s also known, focuses on designing data marts first. These become the foundation of the data warehouse. Labels such as people, places and products allow you to connect to a particular department or specific function within your business.
What are the benefits?
- User-friendly: The Kimball method is all about accessibility, making it easy to track departmental metrics and KPIs.
- Quick set-up: Building a data warehouse using the Kimball method doesn’t need a lot of preparation.
- Low initial costs: As there isn’t a lot of preparation required, the initial spend is minimal.
- Ease of use: Business users can understand the schema of the Kimball method.
What are the disadvantages?
- High ongoing costs: Lack of preparation will mean you have to rewrite parts of the model at a later date. So, while the initial outlay is minimal, you pay more down the line.
- Performance issues: It’s difficult to change the model, with new additions to the fact tables expanding its dimensions and affecting its performance.
- Not enterprise-wide: As the model is department or process-specific, it lacks the ability to report on the entire enterprise.
The Kimball method is perfect for businesses that don’t need to connect with data from other departments in order to perform well. A marketing firm is a good example of this, as is the customer relationship management department within a financial institution.
What is the Inmon method?
Also known as the top-down method, the Inmon method involves building the data warehouse first and then categorizing it into different data marts. It’s considered to be the data-driven approach, providing more accuracy of data for the entire enterprise.
What are the benefits?
- More flexibility: It’s easy to apply updates to reflect changes in your business requirements and data.
- Reduced errors: There are fewer instances of redundant data and therefore a smaller chance of failure during the ETL process.
- Enterprise-wide: As it serves as a single source of truth, it can provide reports on the entire enterprise as well as specific departments and processes.
What are the disadvantages?
- Large upfront investment: The Inmon method needs a high-level analysis of both local and inter‑departmental data, which is costly. There’s also the fact that it needs specialists to implement it. With the current skills shortage, hiring these specialists isn’t always straightforward.
- Longer set-up: Even with the right people on hand to help you set up, preparing the data takes a lot of time.
- Ongoing support necessary: As the environment grows, it can become more complex and difficult to handle. So it’s best practice to have data specialists within your organization should issues arise.
While the Inmon method provides more accurate and far-ranging insights, it’s expensive and requires advanced skills even after set-up. That’s why it’s perfect for mature organizations that can afford the investment and will benefit from analytics that span the whole enterprise.
An insurance firm would be a good example of this, as it needs access to a range of data to form an accurate picture of the policyholder. Similarly, manufacturing companies need to access data across all processes to ensure everything runs smoothly.
Whether you choose the Kimball method or the Inmon method depends on your business. You’ll also need to determine what your budget is, what talent you can access, and how much time you have to set up your data warehouse.
How does a data warehouse work?
A data warehouse can involve:
- An ETL (extract, transform, load) process, where the formatting occurs before data hits the target data warehouse.
- An ELT (extract, load, transform) process, where the data formatting occurs within the target data warehouse.
- Or, a more modern pipeline that operates in real-time with micro-batches. These data warehouses are updated whenever a transaction takes place, e.g. a ticket booking system.
At CloverDX, we usually follow the ETL process, although we can (and have) utilized ELT.
With ETL, you extract data from your sources (marketing systems, CRM, ERP, etc.), transform it into a format and load it into the data warehouse. This allows any user to pull the necessary data and respond to queries quickly.
The premise of a data warehouse is its structure
The source data is restructured to match the dimensions of a data warehouse. This requires some upfront planning, such as thinking about the type of data that needs to be stored. However, you can reduce your manual effort by building a repeatable, visual approach to the data workflow.
How do you do this? By automating the data transfer process through a schedule, and/or specific event triggers (e.g. a new file arriving at a location) or APIs.
You can build and automate reliable data pipelines, removing any bottlenecks. And the automation doesn’t stop there. In addition to the ETL process, you can automate the monitoring and error handling around it to make it robust and fully capable of running on autopilot.Data Warehousing with CloverDX
What is a data warehouse example?
Here’s a successful data warehouse case study from one of our customers.
EE, the UK’s largest mobile network with more than 28 million, has a lot of data in multiple systems. This makes consolidating data difficult for small departments.
The ‘digital insights’ team within the company needed to find a new and easier way to access data. They decided to adopt an agile approach to growing a data warehouse as opposed to previously failed, large-scale and slow-paced traditional projects.
They wanted to demonstrate value quickly to secure further budget and generate buy-in from other departments, which would encourage access to more data and make their projects more attractive.
To achieve this quickly with limited budget and resources, they needed to onboard new data with full ETL automation.
A combination of CloverDX and Amazon Redshift fit the bill perfectly. The Amazon Redshift data warehouse gave them an easy place to store the data without too much upfront commitment, while CloverDX provided the level of productivity, flexibility and enterprise features they needed to ensure the project was a success.
Discover the right data method
If your business handles a lot of data from multiple sources, consider a data warehouse. After all, it aggregates the data in a centralized location, providing you with accurate insights that inform better business decisions.
That said, there’s a lot to think about before you take the plunge. Firstly, will the benefits offset the cost? And if a data warehouse is the right choice for you, what type do you need?
If you’d like to learn more about data warehouses and other enterprise data architecture options, read through our guide.