Descriptive Analytics

Descriptive Analytics

• Descriptive analytics tells "What happened in the past".
• It provides a summary of historical data to understand what happened.

Data Normalization

• Normalization is the process of organizing data in a database.
• It includes creating tables and establishing relationships between them according to rules designed to protect the data and make the database more flexible.
• Normalization eliminates redundancy and inconsistent dependency.

Extract, Transform, and Load (ETL)

• ETL is a data pipeline used to collect data from various sources.
• It transforms the data according to business rules and loads it into a destination data store.

Batch Processing

• Batch processing is a type of data processing that involves processing large amounts of data in batches.
• It may cause latency and is not suited for real-time or mission-critical applications.
• Batch processing is often used for data analysis and reporting.

Transcription

• Transcription is the process of converting audio or video files to text.
• It is similar to speech recognition and is a type of cognitive service.

Analytics Types

• Descriptive analytics answers the question "What's happening?"
• Diagnostic analytics answers the question "Why's happening?"
• Predictive analytics answers the question "What will happen?"
• Prescriptive analytics answers the question "What actions should we take?"

Star and Snowflake Schema

• A star schema is a type of data mart schema that consists of one or more fact tables referencing any number of dimension tables.
• A snowflake schema is a variation of the star schema that features normalized dimension tables.
• The star schema is more effective for handling simpler queries, while the snowflake schema is more effective for handling complex queries.

Indexes

• Clustered indexes sort and store data rows in a table or view based on their key values.
• There can be only one clustered index per table.
• Non-clustered indexes do not affect the physical order of the data rows.

Data Storage

• Relational databases are optimized for writes and are suitable for structured data.
• Non-relational databases are suitable for unstructured or semi-structured data.
• Relational databases are more suitable for ACID transactions.

Big Data Solutions

• Big data solutions often use long-running batch jobs to filter, aggregate, and prepare data for analysis.
• Batch processing can have output to SQL Database, Hive, HBase, or Spark SQL.

ELT (Extract, Load, and Transform)

• In ELT, data is extracted from the source, loaded into the target, and then transformed.
• ELT is different from ETL, where the transformation occurs before loading the data into the target.

Data Visualization

• Tree maps are charts of colored rectangles, with size representing value.
• Key influencer charts display the major contributors to a selected result or value.
• Scatter and Bubble charts display relationships between 2 or 3 quantitative measures.

Azure Storage

• Zone-redundant storage (ZRS) and read-access geo-redundant storage (RA-GRS) are two types of replication that can be used for Azure Storage.
• ZRS and RA-GRS provide automatic replication of data to a secondary region.

PaaS (Platform as a Service)

• PaaS includes infrastructure, middleware, development tools, and business intelligence services.
• PaaS supports the complete web application lifecycle.
• PaaS allows users to avoid the expense and complexity of buying and managing software licenses.

Data Manipulation Language (DML)

• DML statements affect the information stored in a database.
• Examples of DML statements include INSERT, UPDATE, and DELETE.

Views

• A view is a virtual table whose contents are defined by a query.
• Views act as a filter on the underlying tables referenced in the view.
• Views can be used to simplify complex queries.

Key/Value Store

• A key/value store associates each data value with a unique key.
• Key/value stores support simple query, insert, and delete operations.
• Key/value stores are suitable for storing large amounts of unstructured or semi-structured data.

Azure Data Lake Storage Gen2

• Azure Data Lake Storage Gen2 is a hierarchical storage system.
• It consists of a storage account, containers, and folders.

Real-time Data Processing

• Real-time data processing deals with streams of data that are captured in real-time and processed with minimal latency.
• Real-time data processing is used to generate real-time or near-real-time reports or automated responses.Here are the study notes for the text:

Azure Data Factory Components

• Dataset: Created from paths in Azure datastores or public web URLs, for data to be accessible by Azure Machine Learning.
• Linked service: Connection strings that define connection information needed for Data Factory to connect to external resources.
• Pipeline: Logical grouping of activities that together perform a task.

Types of Workloads

• Batch processing: Processing of blocks of data that have already been stored over a period of time.
• Stream processing: Processing of data in real-time as it arrives, detecting conditions within a small period of time from the point of receiving the data.

Extract, Load, and Transform (ELT) Process

• Extract: Data is extracted from the CRM system.
• Load: Data is loaded to the data warehouse.
• Transform: Transformation occurs in the target data store (data warehouse) using its processing capabilities.

Data Analysis Types

• Descriptive: Analyzes what's happening?
• Diagnostic: Analyzes why something happened?
• Predictive: Analyzes what will happen?
• Prescriptive: Analyzes what actions to take?

Data Manipulation Language (DML)

• Affects the information stored in the database.
• Includes statements such as: BULK INSERT, DELETE, INSERT INTO, UPDATE, MERGE.

Database Hosting Models

• IaaS (Infrastructure as a Service): Provides a virtual machine where you can install and configure your database.
• PaaS (Platform as a Service): Provides a managed platform for deploying and managing your database.

Storage Solutions

• Azure Data Lake Storage: Supports access control lists (ACLs) at the file and folder level.
• Azure Table storage: NO SQL, key-value pair storage.
• Azure Files: Provides a fully managed file system in the cloud.

Analytics

• Cognitive analytics: Uses machine learning, AI, and deep learning to identify patterns and make predictions.

Database Concepts

• Index: A data structure that improves the speed of data retrieval.
• View: A virtual table based on the result of a SELECT statement.
• Stored procedure: A set of instructions that can be executed on the database server.

Data Storage

• Graph database: Designed to store and query large amounts of graph data.
• Relational database: Stores data in tables with well-defined schemas.
• NoSQL database: Stores data in a variety of formats such as key-value, document, graph, and column-family stores.### Data Storage
• Key/Value stores:
  • Associate each data value with a unique key
  • Optimized for simple lookups
  • Not suitable for querying data across different key/value stores
  • Not optimized for querying by value
  • Can be extremely scalable, easily distributing data across multiple nodes
• Semi-structured data:
  • Bridge between structured and unstructured data
  • Does not have a predefined data model
  • More complex than structured data, yet easier to store than unstructured data
  • Examples: JSON, CSV, XML

Transactional Workloads

• Atomicity:
  • Guarantees that each transaction is treated as a single unit that either succeeds completely or fails completely
  • Purpose: prevent different transactions from interfering with one another
  • Enforced by refusing to start any transaction until the previous one has committed

Database Management

• SQL Server on Azure VM:
  • Falls under Infrastructure-as-a-Service (IaaS) category
  • Allows running SQL Server inside a fully managed virtual machine (VM) in Azure
  • Provides full control over the database engine
• Graph databases:
  • Store two types of information: nodes and edges
  • Edges specify relationships between nodes
  • Nodes and edges can have properties that provide information about that node or edge
  • Edges can also have a direction indicating the nature of the relationship

Data Analysis

• Data analyst:
  • Primary skill set: data acquisition, handling, and processing
  • Requires intermediate level understanding of programming to build algorithms
  • Mastery of statistics and math is also necessary
• Data engineer:
  • Requires intermediate level understanding of programming to build algorithms
  • Mastery of statistics and math is also necessary
• Data scientist:
  • Needs to be a master of both worlds (data, stats, and math) and in-depth programming knowledge for Machine Learning and Deep Learning

SQL Queries

• UPDATE statement:
  • Used to update existing rows
  • Example: change the inventory quantity of a product
• ALTER statement:
  • Used to add, delete, or modify columns
• INSERT statement:
  • Used to insert new rows
• CREATE statement:
  • Used to create new tables or databases

Azure Services

• Azure SQL Database:
  • Provides a managed relational database service
  • Minimizes ongoing maintenance
• Azure Data Studio:
  • A cross-platform database tool that allows embedding documents and query results into a SQL notebook
  • Supports Azure SQL databases, SQL Server, and other databases
• Azure Resource Manager (ARM) templates:
  • Used to automate the creation of an interdependent group of Azure resources in a repeatable way
  • Define infrastructure and configuration for a project in a JSON file
  • Use declarative syntax to specify what to deploy without having to write the sequence of programming commands

Database Security

• Azure SQL Database firewall:
  • Lets you decide which IP addresses can or cannot access your Azure SQL Server or Azure SQL Database
  • Needs to be configured before anyone can access the database
• Azure Defender:
  • Provides security alerts and advanced threat protection for virtual machines, SQL databases, containers, web applications, and more

This quiz tests your understanding of descriptive analytics, its role in analyzing past data, and its differences with other types of analytics.