Data Warehousing, how & why?
During Data Driven Guernsey 2022 we were joined by Marc Beavan from Cortex to talk through the core principles of data warehousing whilst giving a live demo of Bragi, their bespoke warehouse automation tool. You can watch the full livestream of the event at the bottom of this article.
What is Data Warehousing?
Making business decisions with data is not a new concept, and storing vast amounts of data in a way that is easy to use and highlight insights quickly requires a management system, or as its better known, a data warehouse. These warehouses are designed to store large amounts of data aggregated from several different sources within the organisation for reporting and analysis purposes, and help create the reports that assist business leaders make the complex twists and turns before making a critical business decision. These systems not only help save time and resources by being more efficient, but they also help ensure the success and growth of the business.
Using cloud storage for data warehouses is growing in popularity due to the flexibility offered by the cloud. When the data warehouse uses cloud storage, it’s scalable instantly, available from anywhere, and more secure than an on-premise data warehouse, which would be solely protected by an in-house IT team. Cloud data warehouses are an already and increasingly popular choice in the IT department. If you aren’t ready to jump to the cloud just yet; many companies use a combination of data warehouse types. Find out what suits your company’s needs and budget best and use that as a deciding factor.
So you think you want a data warehouse? Whether you’ve got 1 data source or 20, the reasons for creating a data warehouse are many and well-known enabling a wide range of operational and strategic benefits.
But where do you start?
First, you'll need to plan your data warehouse system - Do you know what questions users are going to ask of it? for example, how many registrations did our organisation receive in each quarter in 2021, or what demographic is purchasing our products in America? - because the sole purpose of a data warehouse system will be to provide your decision-makers with accurate data.
Decide your business objective's for your system, what defines success for your organisation? Next, collect and audit data from your organisation, (you don't want want any old data, just the relevant stuff) Start with the data sources your business leaders can offer, like the information they are currently using to make decisions. There is also significant data to be found in your accounting processes, customer relationship management (CRM) application etc. Your data warehouse needs copies of all these reports and knowledge of where they come from. You may also need to collect data from existing reports that can be overlooked, like a log of telephone calls, a weekly email report or a small desktop database. While it may seem unimportant or inaccurate, you need to understand why it exists before you disregard it.
Identify your core business processes to correlate. If you've already figured out your KPI's, next you need to identify the entities that create them for your connected data structures. Each structure will store key performance indicators (KPI's) for a specific business process and will correlate those indicators to the factors that generated them. To design your warehouse structure to track this process, you'll need to identify these entities that work together to create the KPI. This relationship forms a dimensional model and the entities generate facts into dimensional tables
After identifying the business processes, you can create a conceptual model of the data, determining the subjects that will be expressed as fast tables and dimensions that will relate to the facts. These initial models have to be created with care as data warehouse structures are difficult to populate and maintain, taking a long time to construct. Careful planing in the beginning will save you reconstructing your entire system.
Now you know what you need and you've collected it, it's time to think about the amount of storage space he structure of your data warehouse is going to consume. How are you going to archive the data as time goes on? Data warehouses retain data at various levels of detail or granularity. As data ages you can summarise it and store it with less detail in another structure. These stages can have a holding period of days, into months and then years.
Now you have your plan you need to implement it! Start a part as a data mart - a simple form of a data warehouse that is focused on a single object or line of business, such as sales, that draws data from fewer sources than data warehouses - to demonstrate what your new system is doing. As you complete new parts they can fit together like a jigsaw puzzle and add new capabilities and benefits to the system.
In the event with Cortex we explored Bragi, Cortex's Data Warehouse /SQL environment automation tool, that allows users to rapidly build out data models on top of datastores with stage tables, exporting the data in any shape to any file type or data connection. This tool also allows users to schedule jobs to do repeated ETL/ELT. With these sort's of tools your build and maintenance processes are simplified and streamlined.
