Data Warehouse
Why use data warehouses and why they are a vital component
Data warehouse tools collect, clean and re-organize large amounts of data from different sources for analysis and business insight. Data warehouse software plays a critical role in managing today’s data analytics process in businesses. Data warehouses provide several important data processing services that are needed for reporting, data analysis and BI including making data more understandable for non-technical users (data modeling), pre-calculating common fields, combining data from many sources and keeping historical data.
The data in data warehouses is almost always re-structured and fields re-named in order to:
Make finding and understanding data for reporting easier. This includes re-naming fields with more commonly understood names and re-structuring data.
Data modeling & transformation. Re-structures data specifically for analytical queries rather than transaction processing. This design allows for faster data retrieval, which is crucial for complex BI reports and dashboards.
Pre-calculate values to support a single-version-of-the-truth and for efficiency.
Historical Data Storage. Retains and compiles so-called historical data that might otherwise be lost or hard to retrieve.
Combines Data from Multiple Sources. Compiles and combines data from several sources into one place.
Optimizes Data for Query Performance. The cumulative result of data modeling, pre-calculation, and the combining of data in one place.
Data Quality and Consistency. The ETL (Extract, Transform, Load) processes and a data warehouse that is separate from the source databases allows data to be cleaned, standardized, and transformed into a consistent format.
Why Not Use Tableau, Power BI’s Data Models or other Tools?
Tableau’s data sources and Power BI’s semantic data models do provide some of the services provided by data warehouses with their proprietary data storage formats. The limitations of these formats include:
Restricted data access. Data in data warehouses is much more accessible by the entire organization because it can be read using standard query tools such as SQL. Data in Ta
Single version of the truth. Formulas and calculations performed by proprietary reporting/BI tools are only available from those tools.
Limited Data Services. Some capabilities especially storing data history, storing cleaned data after data cleaning aren’t supported.