Cloud Optimization
Data Warehouses: The Backbone of Business Intelligence
- Article |
- November 14, 2023
- |
- Read time: 5 min
In today’s business landscape, data is a vital asset that is a central drivers for most large organization’s success. A key component for enterprise organizations to leverage their data effectively are Data Warehouses. In this article, we will examine Data Warehouses in detail, focusing on their structure, functionality, and the crucial role they play in the management and analysis of enterprise data.
If you prefer video format, please watch the video below and be sure to subscribe!
What exactly is a Data Warehouse?
But it’s not just about consolidation, Data Warehouses are also optimized for performance. They are structured in a way that makes complex querying faster and more efficient, ensuring that businesses can access insights quickly, which is crucial in today’s fast-paced market.
OLTP vs OLAP
While Data Warehouses are indeed a type of relational database, there are some slight differences from the conventional relational databases designed for day-to-day operations, which prioritize transactional data management.
The conventional relational databases that you’re likely used to if you’re a software developer are known as Online Transaction Processing (OLTP) systems. These databases are optimized to handle numerous users performing short and quick transactions, concurrently on up-to-date data. OLTP databases also ensure an elevated level of data integrity through normalized tables and frequent updates, deletions, queries, and insertions.
Now on the other hand, Data Warehouses are a specialized type of relational databases called Online Analytical Processing (OLAP) systems. OLAP databases are optimized for business intelligence and analytical activities, often featuring a more denormalized data structures to optimize for complex queries and to reduce the need for multiple joins.
Most of the clients we work with have pre-existing OLTP databases where we help set up their OLAP databases by using ETL processes (Extract, Transform, Load), enabling the flow of operational data to analytical platforms where it can be transformed into valuable insights.
***Normalized databases reduce data redundancy by organizing data into multiple related tables, whereas denormalized databases combine data into fewer tables to improve read performance at the expense of potential data redundancy.***
Difference from a Data Lake
Now if you’ve read our last blog on Data Lakes, you’ll likely be thinking to yourself, a date warehouse sounds awfully familiar to a data lake. Both data lakes and data warehouses are systems for storing large amounts of data in a centralized location, but they serve different purposes, have different architectures, and handle data differently.
| Data Lake | Data Warehouse | |
| Type of Data Stored | Data lakes are primarily designed to store raw, unprocessed data. This can be structured data (like databases), semi-structured data (like JSON or XML files), or unstructured data (like images, videos, etc.). | Data Warehouses, are designed to store structured, processed, and cleaned data. |
| Schemas | Data Lake are considered Schema-on-read, meaning that the schema is applied only when reading the data, allowing for greater flexibility in storing different data types and structures | Data Warehouse have Schema-on-write architecture. This means data has a defined schema when it’s loaded into the warehouse requiring upfront data modeling and integration work but helps in fast querying later. |
Data Processing
| Since data lake’s primarily store raw data, much of the processing (like cleaning, transforming, aggregating) happens during the read or consumption time. | Data Warehouse: Data is cleaned, transformed, and aggregated (ETL processes) before it is loaded, which means it is ready for querying and analytics once inside. |
Performance
| Data Lake are typically slower for specific analytics queries because it may involve reading and processing vast amounts of raw or unstructured data.
| Data Warehouse on the other hand are Optimized for fast query performance. Structures like star schema, columnar storage and indexing ensure that analytics queries are executed quickly. |
Scalability and Storage
| Data Lake are often built on top of distributed storage systems (like Hadoop’s HDFS or cloud storage like Amazon S3). This ensures high scalability and makes it more cost-effective for storing massive amounts of raw data. | Data Warehouse are built on high-performance database systems which might be more expensive per unit of data but offer fast and consistent performance.
|
It is important to note that Data lakes and data warehouses are not competing technologies and we often use them in tandem when setting up a client’s data strategy.
Data Lakehouse
One point worth mentioning is the recent industry trend in the data engineering and analytics world towards the concept of a “Data Lakehouse”. The Lakehouse is an architectural approach that combines elements of data lakes and data warehouses into a single, unified architecture.
It aims to support both the vast data storage and processing needs of a data lake with the management and structured querying features of a data warehouse, along with ACID transactions.
Cloud Data Warehouse Options
At LefeWare Solutions we focus primarily on cloud-based solutions, so I wanted to quickly list some of the most popular cloud-based data warehouse options that we’ve helped our clients set up:
AWS has Amazon Redshift which is a fully managed Data Warehouse service, known for its fast query performance.
Azure has Azure Synapse Analytics (with Dedicated SQL Pools), formerly SQL Data Warehouse.
Google Clous platform has BigQuery, which is a serverless, highly scalable, and cost-effective multi-cloud data warehouse.
There is also Snowflake which is a cloud-native platform that provides data warehouse-as-a-service.
Databricks does not offer a data warehouse in the traditional sense of the term; instead, it provides a data Lakehouse.
Data Modeling and Schemas
In Data Warehouses data is organized quite differently from how transactional databases are organized.
Here, the data is structured into two main types of tables: fact tables and dimension tables.
Fact tables record specific events or actions, capturing the essence of what happened. For example a patient visited a clinic, or a store made a sale.
Dimension tables complement this by providing additional details about these events, such as the patient or customer info and time and place they occurred.
This data structure forms what is known as a “Star Schema,” due to its central fact table and surrounding dimension tables. The structure is specifically optimized for historical data analysis. The star schema design simplifies queries and allows efficient aggregation of data across time and other dimensions, making it ideal for tracking and analyzing long-term trends.
Real World Scenario
- Since the client was primarily focused on knowing trends over time around patient visits, we wanted to center our model around this fact.
- We then defined a series of dimension tables around this visit including clinic information, patient information, doctor information etc.
- Finally, we created our star schema in AWS Redshift using SQL (see above schema).
- Next, we needed a way to extract our EHR data from the S3 buckets.
- We used AWS Glue to create a job that reads the semi structured HL7 data files from Amazon S3
- We utilized glue’s ETL capabilities to first anonymize the data including removing specific sensitive fields and replacing others with pseudonyms to ensure we were in compliance with HIPAA standards.
- Finally we transformed semi-structured HL7 data into a structured format using PySpark
- Once the data was anonymized and transformed into its proper structured format, we loaded it into the corresponding fact and dimension tables in an AWS Redshift cluster.
- We ran various SQL queries to our Redshift Datawarehouse and connected to our BI tools to perform the large scale analysis on the EHR Data.
Conclusion
In conclusion, Data Warehouses are more than just storage solutions; they are the engines powering business intelligence, helping organizations make informed decisions.
If you’re a growing organization that’s looking to gain actionable insights from your data, you should be aware that implementing a Data Warehouse comes with its own set of challenges like integrating various data sources, managing large data volumes, ensuring data consistency and accuracy, and implementing effective data security measures. Reach out and we can help streamline the setup process, helping your organization harness the full potential of a Data Warehouse without the usual hurdles.
