Understanding Data Warehousing

• Understanding Data Warehousing
• Data warehousing is pooling data to support decision-making, acting as a repository for current and historical data relevant to managers.
• It structures data for analytical processing, including OLAP, data mining, querying, reporting, and decision support applications.
• A data warehouse consolidates data from multiple databases for analytical needs in a central location.
• Data Warehouse (DWH) is maintained separately from an organization's operational database.
• End users access data warehouses when information is needed.
• Data is integrated and processed before visualization in a data warehouse.
• A data warehouse isn't loaded every time new data is added to a database.

Applications of Data Warehousing

• Sales analytics uses data warehousing and business intelligence to uncover sales trends, optimize operations, and improve forecasting.
• Integrating data from customer relationship management (CRM) systems, financial records, sales leaders can monitor key performance indicators (KPIs) to enable data-driven decisions.
• Customer analytics offers a 360-degree view by consolidating data from disparate channels for behavioral segmentation and predictive modeling.
• This aids strategic decision-making in resource allocation, pricing, product enhancement, and customer experience management.
• Human resources leverages analytics for workforce planning, analyzing hiring and attrition models and talent retention risks.
• This optimizes hiring, talent management, minimizes turnover, and aligns learning and development investments.
• Financial planning consolidates data from operations, sales, HR, and other areas for long-term planning, including budget forecasting and what-if modeling; this guides operational planning, investment decisions, and growth strategy.

Data Warehouse vs. Operational Database

• A Data warehouse stores historical information for analyzing business metrics and is used for reading data by business analysts and data analysts.
• Operational databases house current information needed to run the business, mainly used to write data by operations team members.
• Databases are for data retrieval, updating, and management using OLTP, are normalized, and provide current/real-time data.
• Data warehouses support data analysis and decision-making using Reporting and OLAP, are denormalized, and contain historical data.
• Normalization splits tables to reduce replication in databases.
• De-normalization is applied in data warehouses.

Data Warehousing vs. Data Mining

• Data mining analyzes data in different dimensions from a data warehouse to summarize it into useful information for business questions.
• Data warehousing stores analytical data in a structure suitable for data mining, extracted from operational systems usually on a daily basis.

Data lakes

• Data lakes are data platforms used with big data, providing large storage locations for vast quantities of mostly unstructured data in its native format.
• Data warehouses analyze archived structured data, while data lakes store big data of all structures.
• Data lakes use ELT (Extract, Load & Transform), unlike the ETL process in data warehouses.
• Data analysts and business analysts work in data warehouses with processed, pertinent data, requiring lower levels of programming and data science knowledge.
• Data engineers set up and maintain data lakes, while data scientists work closely with them due to the wider scope of current data.
• Data engineers use data lakes for incoming data storage; unstructured data offers flexibility and scalability for big data analytics via services like Apache Spark and Hadoop which is used for deep learning.
• Data warehouses are typically set to read-only for analysts, and data is already clean and archival as there is no need to insert or update data.
• Data lakes are large to retain all potentially relevant data, often reaching petabytes, while data warehouses are selective.
• Choose data lakes or data warehouses based on the best fit for the use case, but a combination may be necessary.

Data Lake vs. Data Warehouse

• Data lakes store unstructured and structured data to ensure their cost-effective big data storage from various company data sources.
• Data warehouses store historical data structured to fit a relational database schema for business decision analytics.
• Data scientists and engineers use data lakes, while data analysts and business analysts use data warehouses.
• Data lakes are used to store data and perform analytics for deep learning and real-time analytics.
• Data warehouses use read-only queries for aggregating and summarizing data.
• Data lakes store all data that might be used, it takes up petabytes, while data warehouses only store data relevant to analysis.

Benefits of Data Warehousing

• Trends in data can answer strategic questions.
• Data warehousing provides faster, more accurate data.
• Data warehouses must be tailored to suit an individual company.
• Data is taken from operational systems and integrated with the data from multiple source, the result is accessible
• Storing data in format suitable for easy access standardizes data and it removes inconsistencies.

Properties of Data Warehouse

• A data warehouse is a subject-oriented, integrated, time-variant, and non-volatile collection of data in support of management's decision-making process.
• Data is organized by subject (sales, products, or customers) containing only information relevant for decision support to determine how business is performing.
• Subject orientation provides a comprehensive view of the organization, unlike operational databases tuned for transactions.
• Data warehouses integrate data from different sources into a consistent format, addressing naming conflicts and discrepancies.
• All data warehouses are presumed and totally integrated.
• Warehouses maintain historical data to detect trends, deviations, and long-term relationships for forecasting and comparisons, which can lead to decision making.
• Time is a key dimension in all data warehouses; data for analysis contains multiple time points (daily, weekly, monthly views).
• After data entry, users cannot change or update data, obsolete data is discarded, and changes are recorded as new data.
• In data warehouses snapshots of data are loaded, therefore, when changes occur, a new snapshot is created.
• A historical record of data is stored within a data warehouse.
• Entity-Relationship Diagram (ERD) is not implemented in a data warehouse.
• Data warehouses use Dimensional Modeling.
• Amazon Redshift is a cloud-based data warehouse service offered by Amazon.
• Redshift starts with a small, single-node cluster and can scale to a larger, multi-node cluster.
• Amazon Redshift has an intuitive user interface to manage the warehouse.
• Informatica PowerCenter integrates data from almost any business system and is a widely-used tool in the data integration world due to its flexibility.
• Talend Open Studio (TOS) is an open source and free version of Talend for data integration, well suited for ETL tasks.
• Tableau is a data visualization business intelligence tool that helps people see and understand their data.