Because of the endless confusion from decision makers on establishing data driven decision making in their organization at all levels this post seeks to explain one of the fundamentals in mastering business analytics. Again a Data Warehouse is a critical component to any business where insights are required to manage, gain better insights, or create action for the business. It is a look backwards for trending analysis, it is a curated set of information for quick analysis by slicing and dicing relationships in the data, it is a means for basing forecasting/planning activities with actuals side-by-side, and it is a consistent means for comparative analysis across calendars and changes to meaningful data point attributes across time. Regardless of sewing circle conversation of building a data lake storage, using ETL vs ELT, hiring a data scientist or engaging in machine learning, a data warehouse (call it a data mart if you’d like based on size or scale) is a fundamental asset to any organization that uses data to make decisions.

Since a Data Warehouse needs data to aggregate and build relationships, a database is often that source of data for the Data Warehouse. A Data Warehouse can combine multiple sources of data together to one holistic view of the curated need for the analytical power required of the Data Warehouse. One or more data sources for the Data Warehouse can come from a database such as an ERP or CRM system (an example would be customer, financials, GL, accounting, sales, etc. data). And through a transformation process the data elements are combined, aggregated, parsed, and otherwise enriched based on business logic the company needs to facilitate the aforementioned analysis. Traditionally a Data Warehouse is a special type of database that is infrequently updated compared to the transactional nature of most system of record operational databases used to support ERP and CRM systems such as SAP, Oracle, PeopleSoft, SAGE, MS Dynamics, etc. Each of those systems would act as sources for the Data Warehouse for example.

Here is a basic breakdown of some simple characteristics of a Data Warehouse and a Database for your review.

Characteristic ItemData WarehouseDatabase
Purpose / Identified ByTrend Analysis, Analytics, ReportingTransactional Events, Data Entry
Transactional / AnalyticalAnalyticalTransactional
Sources of InformationStructured data, any logical data source, CSV, Flat File, Operational DatabaseData input from users via some form based application, usually a single source transactional system
Automation TypeTransformation process, usually in batch, micro-batch, or near real-time, typically ETL or ELT processesNone, usually. People or triggered events are the means for adding event records to this transactional repository
Database Model FormDenormalized, typically to create fast data relationship queries of the stored data. Typically dimensionally structured (Star schemas, satellites, etc.)Usually Third Normal Form (3NF), a normalized approach to application development
Storage FormFile system based and often in memory for faster query performance, often columnar storage is used for MPP capabilitiesCaters to operations requiring fast write, high-throughput into relational column-row structures baked by file system storage
Security / AccessBased on model developed, but often follows constraints of database vendor or any attached BI/Analytics/Reporting application used to query the data.Based on the database vendor, and the application developed, usually a GUI, that simplifies the data writing/input process of the operational requirement, ex: GL account entries.
Data Warehouse vs. Database (Operational Relational Database)

Please comment and leave feedback to what you think are some additional characteristics that delineate a typical Database from a Data Warehouse and we’ll seek to add them to the list.

Share & Comment:

Share on facebook
Share on twitter
Share on pinterest
Share on linkedin
Scroll to Top