Introduction to Data Analytics on Amazon AWS Cloud

Learn how a data warehouse centralizes data from multiple sources, collects it through extraction, cleaning, conversion, sorting, consolidation, and summarizing, and powers business intelligence and analytics.

Learn about three main data warehouse types: enterprise data warehouse, operational data store, and data mart, and how each supports reporting, data integration, and accelerated business processes across business units.

Discuss data warehouse architectures, staging and tagging areas, data mart, metadata, and end-user tools for reporting and analysis.

Identify data warehouse components, including the process manager, load manager, warehouse manager, gateway, and query manager, and how they coordinate data loading, transformation, and query delivery.

Explore why data warehouses outperform conventional databases for analytics, handling large, multi-source data sets and supporting BI and OLAP for informed business decisions.

Explore Amazon Web Services as a comprehensive cloud computing platform offering compute, storage, and content delivery services with pay-as-you-go pricing, multi-region availability, and disaster recovery capabilities.

Amazon Redshift functions as a fully managed, petabytes-scale data warehouse service in the cloud for OLAP analytics, offering scalable clusters and a standard ESKIL language.

Explore the architecture of Amazon Redshift, including leader nodes, compute nodes, and clusters, and learn how massively parallel processing and data compression encoding schemes enable fast analytics.

Create an AWS account by entering contact and payment details, verify your phone, and sign in to the AWS management console. Explore analytics services like Amazon Redshift and AWS Glue.

Navigate the AWS web console to access services like Redshift and manage regions such as us east 2. Explore tutorials for building websites, web apps, and databases.

Learn how to create and configure IAM roles in the AWS console, assign read-only S3 access, copy the role ARN, and prepare for attaching policies to users.

Create an aws cluster by selecting a region, naming the cluster, choosing the free trial, and configuring nodes, storage, database settings, permissions, and network options.

Create an IAM user named Redshift User, attach the policies Amazon Graczyk and Amazon Richard Reid only EXIS to grant Redshift access, and enable API, CLI, SDK, and console access.

Connect aws redshift to a sql client using the jdbc driver, configure eskil workbench with the redshift driver, update security group to allow your IP, and verify the connection.

Explore the Eskild workbench interface to connect to a Redshift database, create schemas and tables, insert data, and run queries with the online editor and JDBC connection.

Learn to load a dataset into an Amazon S3 bucket by creating the bucket in a chosen region, uploading files, and managing objects and basic bucket properties.

Install the latest Windows 64-bit AWS CLI, complete the setup, and configure your access keys with a default region us-west-2 (Oregon).

Learn to create an s3 bucket, navigate local directories, and upload datasets to the bucket using the command line interface, including creating a folder and syncing files.

Copy data from s3 to redshift via iam role or access keys. Create a user with s3 policy and run the copy command to load sales_record into redshift, ignoring header.

Explore the second method to copy data from S3 to Redshift by configuring an IAM role and policy, applying it to the cluster, and truncating the table for reload.

Learn how the extract, transform, and load (etl) process moves data from multiple sources into a staging area, cleans and transforms it, and loads it into a data warehouse.

Explore AWS Glue, a serverless ETL service that extracts, transforms, and loads data via a central data catalog, Spark-based scripts, and a flexible scheduler.

Learn how the etl and edl processes power data warehouses with in-place transformations, incremental loading, and log replication across diverse sources, enabling transparent analytics in the escudo analytics database.

Explore ETL tools and data warehousing options like MarkLogic, Oracle, and Amazon Redshift to streamline data transformation, aggregation, and loading into a warehouse for reporting.

Learn how to fetch data from an S3 bucket using AWS Glue with a flight data crawler. Create a database, run crawls on demand, and generate tables and schemas.

Create an IAM role and use AWS Glue to crawl S3 data, store metadata in a Glue database, transform by dropping columns, and export the result back to S3.

Create a security group in the VPC and set access rules, then create a VPC endpoint for AWS Glue, and finally configure a new Amazon Redshift cluster with these settings.

Create a Redshift table, configure an AWS Glue connection, and test the end-to-end data transfer from an S3 bucket into Redshift to enable data analytics on AWS.

Perform a lab walk-through to crawl a Redshift table's metadata into AWS Glue using a Glue crawler, create a target database, and validate the fetched schema for downstream jobs.

This lab demonstrates creating an AWS Glue job to transfer data from an S3 bucket to a Redshift table, configure source and target, and run and verify the load.

Load a movie dataset into S3, crawl with AWS Glue, and transform data with a custom script to create a decade column and mean ratings, then write results back.

Learn to load multiple datasets into Amazon Redshift via JDBC using AWS Glue crawlers and separate jobs, creating databases and tables for dispatch items and item sales data.

Learn the Eskil commands and essential functions to perform analytics on a relational database management system, including select, update, delete, insert, and generate tabulated views under ANSI and ISO standards.

Explore basic sql commands and practice selecting all records from the customer table in an Amazon Redshift environment, including semicolon usage and common sql arguments.

Learn to specify required columns from a complete dataset using a select statement, returning only country, item type, region, and ship date from the surgical dataset.

Select specific columns from a dataset, such as region, using a select statement. View distinct regions and understand paging with 50 results per page.

Explore how to use the distinct statement to retrieve unique region names from a dataset, and see how select distinct reveals seven distinct regions and counts.

Learn to use count to tally total countries (fifty thousand values) and distinct to count unique country names (185), then count distinct regions (seven) and alias the output with as.

Learn how to apply the where keyword to filter data by conditions, such as country equals Russia and units sold greater than 2000, and count matching rows on AWS cloud.

Describe how the and operator enforces multiple conditions to filter data by region and country, such as Europe and Moldova, and count the matching records in the dataset.

Explore the OR operator in SQL by selecting region, country, and order id where either region equals Europe or country equals Moldova, with counts illustrating the filtered results.

Explore using the not operator to filter data by excluding offline records and returning online results, with an example filtering region, country, and sales centers.

Learn how to use the update statement to modify records in a dataset, including setting country names and other fields for specific orders, with caution to avoid updating all records.

Learn how to delete a record from a dataset using the Eskil lab, verify the deletion with a select all query and a count check in Ezekial.

Learn to use the insert statement to add a new row to the sales dataset, mapping values to region, country, item type, and sales, then verify the insertion.

Learn how to join two department datasets using the order ID to create a unified data set for analyzing units sold, costs, profits, and regional trends.

Learn how to merge two data sets by sql joins on order ID, combining dispatch item details and account data to analyze cost, revenue, and regional insights.

Explore aggregation functions in Eskil, including count of distinct outputs and sum with conditional labeling, and compare online versus offline profits using example data.

Compute the average profit for household items using the sql average function, compare offline and online cases, and identify the maximum average profit.

Learn to compute extreme values by calculating minimums and maximums across numeric columns, group by item to compare profits and unit sales, and sort results ascending or descending.

Slice data by region, item type, and profit to reveal patterns and counts across regions. Limit results to five records for overviews and ready grouping and filtering in later steps.

Group data by region and sales channel to count totals, then apply having filters. Sum total costs by item type and explore visuals with AWS QuickSight.

Explore Amazon QuickSight, a serverless, scalable analytics service that lets you connect diverse data sources, build self-service dashboards, and share insights with embedded analytics and natural language questions.

Create your AWS QuickSight account with the standard edition and 60-day trial, set region and account details, load sample S3 analytics data, and prepare for importing data and visual analytics.

Import datasets into the AWS cloud analytics dashboard by uploading from local files or external sources, previewing and editing data, then visualize and apply basic aggregations.

Learn how to perform basic data aggregation in AWS QuickSight by applying sum, average, count, and distinct counts to visuals, exploring region and country fields, including min and max values.

Explore doughnut and pie charts in AWS analytics to visualize regional total cost and profit, adjust group and value fields, and switch between descending and ascending orders.

Explore bar chart visualizations in AWS cloud analytics, including horizontal and vertical charts with 100% stacked options, to compare online versus offline costs by region and item type.

Learn to use line charts and pivot tables to compare total cost and total profit across regions.

Learn to build a dashboard in quick site, show distinct regions and countries, cost and profit, and visualize regional profits by item type with online and offline sales, publish.

Describe the capstone project for data analytics on Amazon AWS Cloud, outlining goals, data workflow, and evaluation criteria to demonstrate cloud-based analytics competencies.