Digital Data Management & Warehousing

• Study notes for Data Warehousing

Data Lakes

• Centralized location for storing raw, structured, and unstructured data.

Data Warehouse (DW)

• Large collection of organized data to help organizations make decisions.

Data Marts

• Subset of a data warehouse that is specific to a business domain like HR, Operations, or Finance.
• Heterogeneous information sources, different interfaces, data representations, and duplicate/inconsistent information can be problematic.
• Data management in large enterprises is problematic due to vertical fragmentation of informational systems (vertical stove pipes).
• User-driven development of operational systems leads to data marts.

Systems Used in Organization

• Operational Systems: used for day-to-day operations with current, real-time data and high-speed transactional processing, for use by operational staff, with examples like POS, ERP and inventory systems, with immediate response time and highly structured/detailed.
• Informational Systems: used for decision-making and analysis with historical, summarized data, analytical and complex queries, for use by analysts and decision makers, with examples like BI tools and data warehouses, with less time-sensitive structure is aggregated and multidimensional.

Goal: Unified Access to Data

• Goal to collect and combine information.
• Designed for integrated view, uniform user interface and support sharing.

Warehousing Approach

• The approach integrates information in advance.
• Data is stored for direct querying and analysis.

Advantages of Warehousing Approach

• High query performance.
• Does not interfere with local processing at sources.
• Allows complex queries at the warehouse.
• Stores copied information, so it can be modified, annotated, summarized, and restructured.
• Supports storage of historical information.
• Provides security without auditing.

When to Consider an Alternative to Warehousing

• When dealing with rapidly changing information and sources.
• When there are truly vast amounts of data from numerous sources.
• When requirements are for clients with unpredictable needs, a query-driven approach may be better.

Data Warehouse Definition

• Defined as a complete and consistent store of data obtained from multiple sources.
• Data warehouses are made available to end-users in a business-context understandable way.

Data Warehouse Attributes

• Subject-oriented, integrated, time-variant, and non-volatile collection of data for management's decision-making.
• Repository containing integrated, cleansed, and reconciled data.
• Emphasis on online analytical processing (OLAP).
• Typically multidimensional, historical, and non-volatile.
• Provides a solution to data integration problems.

Data Warehouse Design

• Organized by subject, not application.
• Includes a user interface aimed at executives.
• Historical data that is non-volatile, with updates that are infrequent and may be append-only.

Data Warehouse Examples

• Includes all transactions at SM or Ayala Malls.
• Encompass complete client histories at insurance firms.
• Incorporates stockbroker financial information and portfolios.

Characteristics - Standard DB

• Focuses on mostly updates.
• Handles many small transactions.
• Holds Mb - Gb of data.
• Provides a current snapshot.
• Includes index/hash on the primary key.
• Contains raw data.
• Supports thousands of users, like clerical staff.

Data Warehouse Characteristics

• Focuses on mostly reads
• Queries are long and complex.
• Contains Gb - Tb of data.
• Holds history.
• Requires lots of scans.
• Consists of summarized, reconciled data for hundreds of decision-makers and analysts.

Data Warehousing Market Insights, 2028

• The global market size was at $2 billion in 1995, and $21.18 billion in 2019
• Projected to reach $51.18 billion by 2028
• Snowflake holds the biggest data warehousing share with 3,174 domains

Data Volume

• Approximately 2.5 quintillion bytes of data are created daily

Types of Data

• Business Data: represents meaning.
• Real-time data: the ultimate source of business data.
• Reconciled data.
• Derived data: new information created from existing data sets.

Metadata

• Describes meaning.
• There is build-time, control, and usage metadata.
• Data Product: has intrinsic meaning, produced and stored for its own intrinsic value.

Data Warehouse Conceptual Views

• Single-layer: stores every data element only once and include a virtual warehouse.
• Two-layer: incorporates real-time and derived data, a commonly used industry approach.

Three-Layer Architecture

• Transformation of real-time data to derived data requires two steps.

Data Warehousing Architectures

• Single-Tier Architecture (Business Example: Small retail shop tracking sales in Excel): Used for basic reporting for small businesses
• Two-Tier Architecture (Business Example: Marketing agency tracking campaign data.): moderate analytics with direct data connections.
• Three-Tier Architecture (Business Example: Multinational retailer analyzing global sales data.): Advanced analytics, scalability, and BI insights.

Data Warehousing, Two Issues

• Getting information into the warehouse.
• What to do with data once it is there.

Issues in Data Warehousing

• Include warehouse design, extraction, wrappers, integration, cleansing, and merging.
• Requires specification, maintenance, optimizations, and handling miscellaneous issues

Data Extraction:

• Extraction needs to take place from various source types (relational databases, flat files, WWW, etc.).
• Key steps in the process are how to get the data out, using replication tools, dump files, or creating reports and Open Database Connectivity

Extraction Issues

• Data warehouse uses a relational or multi-dimensional data model (e.g., data cube).
• Source types can be relational, OO, hierarchical, legacy, or semi-structured (flat files, WWW).
• Data Warehouse must be kept current considering underlying source changes and how to detect updates in sources.

Wrapper Function

• This converts data and queries from one data model to another.
• It extends querying capabilities for sources with limited capabilities.

Wrapper Generation Solutions

• Hard code for each source is one solution
• Automatic wrapper generation another solution
• A source-specific adapter (a.k.a. wrapper, translator) depends on the source.
• Implementation depends on the data model and interface used.

Considerations for a Data warehouse

• The data warehouse includes degree of autonomy and active capabilities.
• Cooperation (friendly vs. uncooperative) with data sources

When Data Integration is Routine...

• Use when many tools are for "standard situations".
• Use when sources have full/many capabilities.
• Most commercial DBMSs and all ODBC-compliant sources are examples of this.

Standard Interactions

• Pass-through queries, extraction from relational tables, and replication (tools such as replication tools, ODBC,)
• Integration from cooperative sources under control.

Non-Routine Situations

• Exists in "non-standard situations".
• Appears in unstructured or semi-structured sources with little or no explicit schema.
• Involves uncooperative as well as sources with limited capabilities.
• Requires mostly research

Data Transformations in Data Warehousing

• Convert data to uniform format.
• Byte ordering, string termination.
• Adjust internal layout, remove/add attributes, add keys, add history, and sort tuples.

Monitors

• Detect changes of interest and propagate the change to the integrator with triggers, replication server, log sniffer, compare query results.
• Also with compare snapshots/dumps.

Data Integration

• Receive data (changes) from multiple wrappers/monitors
• Integrate into the data warehouse using rules and integrating actions.
• Resolve consistencies, eliminate duplicates, and integrate warehouse data.

Data Cleansing

• Find and remove duplicate tuples.
• Detecting inconsistent and wrong data, attribute values that don't match, patch missing, unreadable data and Notify sources of errors found.

Digital Data

• Digital describes electronic technology that generates, stores, and processes data in terms of two states: positive and non-positive.
• Creates value at the new frontiers of the business world, or in the processes that execute a vision of customer experience.
• Builds foundational capabilities that support the entire structure.

Data

• In computing, data can be translated into a form that is efficient for movement or processing.
• Data is information converted into binary digital form and can be used as a singular or plural subject.
• Raw data: in its most basic digital format.
• Digital data is the electronic representation of information in a format that machines read and understand.

Digital Data Characteristics

• Numeric codes (0,1) stored in computer systems.
• Codes are machine language systems that automatically convert them into information humans interpret.
• Represented as discrete rather than continuous data.
• Uncompressed data is very large and hard to transfer.

Digital Data Management

• Provides access to information so that it can be analyzed and used, while protecting it and giving users the access that they need
• Establishes policies and procedures for secure, efficient data access.
• Key aspects: Optimized storage, Data security and backup systems.
• Includes; Data retention policies and tools for data transfer between systems.

Features of Data Classification

• The purpose is to organize data to become fairly available to the users.
• Includes: Homogeneity, Clarity and Stability

Data Classification Types

• Discrete Data: is data with distinct and separate values that is countable, uses fixed, specific values and its precision is exact and finite such as the number of cars sold or a score in a test which is usually presented with frequencies, counts /Bar charts, pie charts
• Continuous Data: is the data that can take any value within a range that can be measured with any values, including fractions and decimals, that is infinite, such as weight and height usually presented with averages, variances, trends / Histogram, line graphs

Forms of Data

• Unstructured Data: 80%
• Semi-structured Data: 10%
• Structured Data: 10%

Unstructured Data

• Includes Data that doesn't follow a specific format/model (images, videos, audio files, social media posts).
• Use AI and ML for classification/retrieval.
• Stored in data lakes or NoSQL databases (Hadoop, MongoDB).
• Apply metadata for easier search/indexing.
• Sources: Word, PDF, Web pages, Memos, Videos (MP4, MPEG), Images (peg, GIF), Text,PPT, reports, chats, surveys)
• Characteristics: Does not conform to any data model, is not in any particular format or sequence, is not easily usable by a program, does not follow any rule or semantics

Semi-structured Data

• Data with elements of both structured and unstructured formats (e.g., XML, JSON, NoSQL databases).
• Use metadata to organize/retrieve data efficiently.
• Store in hybrid systems like NoSQL databases (e.g., MongoDB, CouchDB).
• Convert to structured data if deeper analysis is necessary.

Structured Data

• Data organized in a predefined schema (rows and columns in databases).
• Stored in relational databases (e.g., MySQL, PostgreSQL).
• Uses SQL for managing purposes.
• Needs regular backups and optimization.

Semi-structured Data Techniques

• Schemas describe the structure and content of data to some extent.
• Assign meaning to data
• Allow automatic searching and indexing.
• Graph-based Data Models: Used for data exchange among heterogeneous sources.
• XML (Extensible Markup Language): Models the data using tags and elements.

Digital Data Platforms

• Collect, analyze, and retrieve large volumes of data.
• Provides core functions that allow users to work securely, efficiently, and cost-effectively with digital data.
• Management tools to support common tasks: Identifying, alerting, diagnosing, and resolving faults in the digital data management platform or other related systems, allocating memory and storage resources, making changes, and enhancing system performance by optimizing responses to queries.

Importance of DMPs

• Platforms which house important digital data, such an cookie IDs, mobile identifiers) and campaign data, types of tools also help digital reles (eg marketers and advertisers
• help to build customer segments and their performance, these segments are but up based on demographic data, past browsing behavior, location, device and more.

DMP Benefits

• Unifies data and breaks down silos, bringing all data together on a single platform, offering a cohesive view of customers.
• Helps to identify new audiences and customers.
• Provides continuous results and strategies by constant and continuous reporting.

Uses for Digital Data Management

• Data is used in research areas like astrophysics, economics, genetics, particle physics, and population studies.
• DMP Examples: Google Marketing Platform, Nielsen DMP, Oracle BlueKai DMP

Managing Digital Data

• Requires broad tasks and polices within the organization.
• Manages how data is created and stored for multiple clouds and premises across the organization.
• Requires the ability to provide high availability and disaster recovery.
• Ensures data privacy and security but also archiving and destruction in accordance with retention.

Data Models

• This shows the logical structure of a database, including the relationships and constraints.
• Individual models are based on the rules/concepts of the broader data model adopt.
• This represents the framework of relationships within a database.
• The framework help support the analytical needs of decision makers

Types of Data Models

• Relational Model: sorts data into tables with columns and rows (tuples where each row includes data about a specific instance).
• Hierarchical Model: organizes data into a tree-like structure with a single root.
• Network Model: builds on the hierarchical model, allowing many-to-many relationships between linked records.
• Object-oriented database Model: defines a database as a collection of reusable software elements with features and methods.
• Object-relational Model: combines the simplicity of the relational model with functionality of the object-oriented model.

Test your knowledge of digital data, data management systems, and data classification principles. Explore data warehousing concepts, including data marts and the differences between operational and informational systems. Learn about the objectives and uses of data warehouses.