Introduction to Data Analytics on Microsoft Azure Cloud

Learn how data warehouses collect data from multiple sources, extract, clean, and transform it, and store it in a central repository for reporting and business intelligence.

Explore the three data warehouse types, enterprise data warehouse, operational data store, and data mart, and how they centralize data and support decision making with ETL and data federation.

Analyze data warehouse architectures from basic to staging-area and data-mart designs; learn how online transaction processing data supports online analytical processing through extraction, cleansing, and metadata-driven organization for strategic insights.

Identify data warehouse components, including load, warehouse, and query managers, and explain gateways that extract and transform data for multi-level dimensional modeling with metadata.

Compare databases and data warehouses to show why data warehouses store very large, multi-source datasets for advanced analytics, enabling historical insights and informed decisions.

Create a free Azure cloud account by signing up, verifying email and phone, and completing a quick puzzle, then receive $100 credit for 30 days.

Create an Azure blob storage account in Europe, then set up a container and upload files. Configure public endpoint access and choose standard performance with appropriate redundancy.

Create and configure an Azure SQL Database from resource group to server, compute and storage, networking, and authentication, then deploy, connect via connection string, and run a sample table.

Learn to configure Azure SQL DB firewall settings to allow access from a local PC via SQL Server Management Studio, including adding your IP and understanding public network access.

Learn to install SQL Server locally and then install SQL Server Management Studio in a two-step process, selecting the developer option and using the basic installer.

Install the SQL Server Management Studio (SSMS), select the install location (default is fine), complete the installation, restart the PC, and prepare to continue in the next session.

Learn to connect SQL Server Management Studio to Azure SQL DB from your local machine, configure firewall and server authentication, and explore databases and tables with sample queries.

Learn the etl process: extract data from multiple sources into a staging area, transform and clean it, then load it into a data warehouse for analysis.

Explore the edl/etl process in cloud data warehouses like Redshift and BigQuery, enabling in-place transformations and data modeling, with change data capture and incremental loading across sources and auditing.

Explore ETL tools and their importance for data transformation, enabling leadership-friendly reports, scalable integration from multiple sources, automated aggregation, and efficient loading into a warehouse.

Learn to implement an Azure ETL workflow with data factory, moving a sales CSV from blob storage into a database using a pipeline, link services, and a copy activity.

During this lab, create an Azure data factory resource in the Azure portal, configure region and a unique name with public inbound connectivity, then explore the resource dashboard.

Create a data factory pipeline from scratch, set up a linked service for blob storage, and configure a copy activity to load data to SDL database.

Create a linked service and dataset to connect the Eskil database, test the connection, and prepare to copy data from blob storage into the sales records table.

Create a copy activity in an Azure Data Factory pipeline to move data from blob storage to the Ezekial database, map schemas, convert types, debug, publish, and verify.

Upload multiple data set files to blob storage, create a container, and copy the dispatch record and items records into the Eskil database using pipelines and copy activity.

Create database tables for two blob storage datasets and prepare to move their contents into the database using data factory pipelines.

Create and test link services for blob storage and Eskil database, configure datasets, and copy data from blob to the Eskil database using a copy activity.

Create a linked service to the zero Eskil database, test the connection, then create datasets from Eskil database tables and configure copy activities from blob storage to the tables.

Learn to create copy activities in an Azure Data Factory pipeline to load multiple datasets into a SQL database, mapping source and sink datasets and debugging before publishing.

Transform the movies dataset, upload to blob storage, and use data factory to apply filters and produce comedy films across a year range with ratings.

Create a zero data factory data flow to load the movies dataset from blob storage, configure a linked service and dataset, enable debug mode, and preview data before transforming.

Apply filters in an ADF data flow to select comedy movies from 1910–2000, then compute yearly average scores using the expression builder and two integer function.

Apply the average aggregation in a data flow with Azure Data Factory, using group by and aggregates, define an expression for the rating output, and store results in blob storage.

Save the transformed data to blob storage using a data flow in Azure Data Factory, creating a comedy ratings sink and a delimited text dataset saved as a single file.

Create an Azure Synapse workspace to unify data integration, enterprise data warehousing, and big data analytics on a single scalable platform with configurable storage and database options.

Explore the Azure Synapse workspace dashboard, navigate analytics pools like skill and spark, and use Synapse Studio to ingest, explore, analyze, visualize, and monitor pipelines and resources.

Upload a dataset to Azure linked storage in Data Lake Storage Gen2, create a container, and run a quick SQL query to view the data points.

Create a new SQL pool in Azure Synapse, configure performance level from 100 to 3000 data warehouse units, choose data source options, review, and deploy to a workspace.

Copy data from storage to the skill pool using the copy statement, then create a table with round-robin distribution and a clustered column store index, and verify with a select.

Learn sql basics for relational databases, including select, insert, update, and delete statements, creating tables and views, and managing permissions, as standardized by ANSI and ISO.

Explore basic sql commands and table concepts using the sales record database. Learn to write select statements from customers and understand semicolon terminators and common sql operators.

Learn to write a select all statement to fetch all data from a table in a workspace and skill pool, run the script, and explore fetch all drawers and aggregation.

Select specific columns from a dataset with the select statement, e.g., country and item type from the sales 50k dataset, and decide between first 5000 or all rows.

Apply the distinct statement to reveal unique values in dataset columns like region and sales channel, count these distinct values, and understand the distribution across seven regions and two channels.

Learn to use count and count(distinct) to compute total records, identify seven distinct regions and 185 distinct countries in a 50,000-row dataset, and explore approx count distinct as an alternative.

Use the where statement to filter data on Azure data sets by conditions like country equals Russia or units sold greater than thousand, returning matched rows or counts.

Apply the and operator to filter data with multiple conditions, yielding online sales in europe and enabling column selection and total order count in analytics queries.

Explore the or operator in sql-like queries for data analytics on the Microsoft Azure cloud. Filter by either region Europe or online sales channel, yielding 31,426 matches from 50,000.

Demonstrates how to use the not operation to filter data by excluding offline sales channels and excluding the Europe region.

Master the update statement in sql to modify specific records, such as changing a country name for an order, and avoid updating all rows by using a proper where clause.

Learn to delete a record using a delete statement with an order id condition. Verify the deletion by counting the remaining records, reducing the dataset from 50,000 to 49,999.

Insert into the dataset table by mapping values to the column order, then verify the new record with a count and prepare for analytic queries and joins.

Merge two departmental datasets using a join function to create a single dataset with order ID, region, and profit, enabling analysis of unit cost, price, and regional trends.

Learn to use the SQL join statement to combine dataset tables on matching order IDs, producing a complete sales dataset and exporting it in CSV, text, or Excel formats.

Explore aggregation functions, including sum, count distinct, and conditional sums in queries, and compare online and offline profit by creating alias-labeled totals and two profit columns.

Learn to perform the average function in Eskil, computing average views and total profit from a 50k sales dataset, with conditional averages for household items and sales channels.

Learn to compute extreme values and sort results in SQL queries, using min and max on dates and units sold, grouping by item type and region to reveal profit patterns.

Group by sales channel and region to compute counts and totals, apply having filters, sum item type costs, and use Power BI visuals for analytics.

Learn to create an Azure Event Hubs namespace and an Event Hub, configure throughput units and partitions, and set shared access policies with connection strings for real-time data ingestion.

Configure the TelecoGenerator app by updating the event hub name and service bus connection string, generate sample fraud data for 2 hours, and validate ingestion in Azure Stream Analytics.

Create an Azure Stream Analytics job, configure resource settings and streaming units, monitor through the dashboard, and prepare for input and output setup in the next lab.

Create input jobs in Azure Stream Analytics by configuring event hub inputs from real-time data, choosing a subscription and consumer group, and using a connection string for authentication.

Configure an output job to save real-time event hub data to blob storage by creating a container, using connection string authentication, and storing as comma-separated data with UTF eight encoding.

Ingest real-time data from any source into Azure blob storage by configuring input and output jobs, and write a query to stream data into a blob container.

Learn to perform visually driven analytics on data sets and build dashboards using bar charts, stacked bar charts, clustered bar charts, and line charts, with step-by-step guidance and downloadable resources.

Import datasets from Azure blob storage into Power BI by connecting to a blob container, selecting datasets, and loading them while configuring public access and network settings.

Import a dataset from Excel by selecting the import from Excel option, locating the file, and loading fields to visualize totals like total cost and total profit.

Explore how card visualizations in power BI display single-number metrics and support aggregation functions—sum, average, min, max, count, distinct, standard deviation, variance, and median—and allow renaming fields for dashboards.

Explore bar and column charts, including stacked and clustered variants, and percentage stacked charts, to compare regions and item types using units sold data.

Explore line, area, and stacked area charts in Power BI, visualize unit sold by ship date, and add barometers like unit price with average or minimum aggregations.

Explore line and column charts as a powerful combo visualization that stacks columns and adds a line to compare online and offline sales and local revenue.

Explore waterfall charts to visualize the cumulative effect of sequential values and regional cost contributions, then analyze funnel charts showing user flow and online versus offline sales.

Learn to create and compare pie and donut charts in Power BI, visualizing region-wise units sold, total revenue and profit across online and offline channels.

Explore tables in Bar VI using the table visual to count item types by region, examine online and offline sales, and add totals like profit, cost, and revenue.

Analyze total profit by item type, sales channel, and region using a matrix table in Power BI, and compare online versus offline sales counts across the dataset.

Explore how a decomposition tree visualizes regional and sales channel breakdowns to reveal online and offline profit. Apply page and all-pages filters to analyze regions, items, and order priority.