The short answer is that there are three methods:
The long answer is that it depends on a lot of different factors (which is everyone’s least favorite response).
A data warehouse stores massive amounts of data (years of data). Because of its expansive size, it enables your data analyst to perform complex queries that help you dig deep.
But a data warehouse, while important, is not the beginning and end of business intelligence. Equally important are the systems that support and depend on a data warehouse: your ETL, your analytics software, your data visualization tools (to name a few). Whichever of the three building methods you choose in the list above, you’re going to have to configure your data warehouse with the rest of the tools in your stack.
In this blog post, we’ll discuss the process of building a business intelligence stack around a data warehouse.
Before your data can be stored in your data warehouse, it must be properly cleaned and prepped.
After data is stored in your data warehouse, it's queried and used to create data visualizations.
The data warehouse is sandwiched neatly between the cleaning and prepping layer (ETL), and the querying and visualization layer (BI).
ETL stands for Extract, Transform, Load – the three functions that can be combined into a single tool to prepare your raw data for storage and subsequent analysis.
Your reporting systems (your CRM, ERP, etc) will invariably report data in different formats. In order for your data to be queried all together, it needs to be normalized.
SQL-fluent data analysts should be in charge of your ETL process, ensuring integration with all of your data sources and transforming raw data to normalized data centralized in your data warehouse for subsequent retrieval.
Your data warehouse holds your cleaned and prepped data, typically organized in files and folders for easy querying, retrieval, and comparison.
How your data is organized inside your warehouse will dictate how easy and intuitive it is to create metrics. By normalizing your data from different sources into a single easily recognized format, you create optimal conditions for data retrieval, comparison, matching, and pattern spotting.
The business intelligence layer is designed to pull the prepped data from the data warehouse in order to build metrics and create visualizations.
Since a data warehouse can hold massive amounts of data that has been gathered from different sources and normalized, you can track patterns over the long term, helping to drive predictive analysis, identify “trigger points,” and suggest next actions.
Custom building your own data warehouse is a massive development project. It needs to be organized to align with the quantitative measurements used by your business to measure activity (the business objectives of a digital marketing agency are going to look very different from an ecommerce company’s business objectives).
Your data warehouse will also have to be built to communicate and integrate with your data sources, in addition to the other tools in your business intelligence stack (more on that below).
You will then need to configure your own server to support it, dedicate processing power to its management, and deploy a fast server connection to allow your users to access your data warehouse.
There are only a few cases where custom-building a data warehouse is the best option. In most cases, however, the cost and time required to build a data warehouse is prohibitive.
Unless you have the resources to build and maintain a data warehouse, exact knowledge of how you need your data warehouse to be built, and access to a team that understands the finer points of data warehouse construction, you’re probably better off using one of the services that provide data warehouses.
Alternately, you can select a cloud service to host your data warehouse. In this case, you remove the need to configure the hardware, and if you choose a quality service, access should be fast and easy. The downside to this option is the expense.
An end-to-end platform will not be as robust as a custom data warehouse (even if it does include data warehousing). That being said, unless you’re a massive enterprise business it’s likely that your best option is an end-to-end platform.
(If you’re still unsure whether you need a custom data warehouse or not, you can see our checklist).
An end-to-end platform combines data warehousing storage capabilities with ETL, data visualization, and analytics. It’s an effective one-stop shop.
One final word about data warehouses: they’re not absolutely necessary.
They’re a powerful tool and extremely helpful, but they aren’t vital to business intelligence now like they were a decade ago. If you’re on the fence about whether or not you should build a data warehouse, make sure you consider whether or not an alternative system is helpful.
If you're looking for a new, end-to-end business intelligence solution you could give Grow a try. Grow is designed to deliver the power of ETL, data warehousing, and business intelligence in a single SaaS solution, giving you and everyone on your team the tools you need to use big data to its full potential. Ready to see it in action for yourself? Let us know if you’d like to start a free trial.