Data Warehousing

Data Warehousing Definition

• Data warehouses (DWs) are important for Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP).
• Data warehouses are subject-oriented, integrated, non-volatile, and time-variant.
• Data warehouses are used in many industries.
• A data warehouse is a pool of data used to support decision-making and contain current and historical data of interest to managers.
• Data within is structured for analytical processing such as OLAP, data mining, queries, reporting, and other decision support applications.
• Data warehouse is designed for analytical needs, like a relational database
• Data warehouse is a central location to consolidate data from various databases.
• A Data Warehouse is not loaded every time new data is added to database.
• Data must be integrated and processed before visualization can occur.
• Operational databases go into a data warehouse which then can be used for OLAP, business intelligence and ultimately data analytics

Use Cases and Applications of Data Warehouses

• Sales analytics helps companies leverage data warehousing and business intelligence for deeper sales analytics and optimized operations
• Sales leaders can track KPIs like pipeline trends, win/loss metrics, deal velocity, and forecast accuracy by integrating data from CRM and financial systems
• Data-driven decisions around resource allocation, quota setting, and sales process optimization become enabled through sales analytics
• Customer analytics gives a 360-degree customer intelligence through consolidation of data from various channels, supporting strategic decision making
• HR teams leverage analytics use cases such as workforce planning, analyzing training programs, ensuring pay equity, and building talent retention models
• Finance teams can consolidate data from various functions for long-term planning by utilizing data warehousing.

Data Warehouse vs Operational Database

• Data warehouses contain historical information to analyze business metrics, and operational databases contain current information to run the business.
• Data warehouses are mainly used to read data, operational databases are used to write data.
• Data warehouse integrates data from multiple sources, while operational databases provide current, real-time data
• In databases, normalization is applied to split tables, but in data warehouses, de-normalization is applied.
• Business analysts/data analysts are end users of data warehouses and ops team members are end users of operational databases

Data Warehouse vs Data Mining

• Data mining is the process of analyzing data in different dimensions and summarizing it into useful info.
• Data is searched, retrieved and analyzed from a data warehouse to answer business questions.
• Data warehousing involves storing analytical data in a structure suitable for data mining.

Data Lakes

• A data platform that is used with big data and holds amounts of unstructured data in its native/raw format.
• Data warehouses analyze archived, structured data, while data lakes store big data of all structures.
• Data lakes uses ELT, while data warehouses use ETL.
• Data warehouses require a lower level of programming compared to data lakes.
• Data engineers maintain data lakes, and data scientists work closely with them because they contain wider scope.
• Data lakes are for incoming data, better for big data analytics and scalability.
• Data warehouses are read-only for analyst users and are selective on data stored.
• Data lakes are bigger than data warehouses and can be a combination of both storage solutions.

Advantages of Data Warehouses

• Data warehousing is faster and more accurate and can answer strategic questions by studying trends
• Data warehouse is not a product that a company can purchase.
• Data needs to be taken from operations, integrated from all sources, stored in format and standardized

Properties of Data Warehouses (DWH)

• Subject-oriented: Data is organized by detailed subject and delivers insight beyond how a business is performing via subject orientation
• Integrated: It places data from various sources into a consistent format and must deal with naming conflicts and discrepancies.
• Time-variant: Data warehouse maintains historical data for detecting trends and relationships for decision making, and they contain multiple time points
• Non-volatile: Users cannot change or update the data; it is loaded as snapshots, and historical records of data is kept

ERD in Data Warehouse

• ER diagrams are not implemented in a data warehouse, as it uses Dimensional Modeling instead

Data Warehouse Tools

• Amazon Redshift is a cloud-based data warehouse service by Amazon allowing for single to multi node clusters
• Informatica PowerCenter helps with data integration and is Informatica's most widely used tool.
• Talend Open Studio (TOS) is open source and suited for ETL tasks
• Tableau is a data visualization BI tool.