Data Warehouse and Azure SQL Database
Module 2
Data Warehouse and
Azure SQL Database
Data Warehouse and Azure SQL Database
Unit - 1
Data Warehouse
Data Warehouse and Azure SQL Database
Disclaimer
The content is curated from online/offline resources and is used for educational purpose only
Data Warehouse and Azure SQL Database
Learning Objectives
You will learn in this lesson:
• Understanding the concept of Data Warehouse on Microsoft
Azure Portal
• Learning different types of storage account on Azure Portal
• Working with data using different software's like SQL Server
Management Studio
Data Warehouse and Azure SQL Database
How Data Warehouse works ?
• A data warehouse centralizes and consolidates large amounts of data from multiple sources.
• In early 90s, a data warehouse was hosted on-premises connected to a mainframe computer mainly
focused on collecting, maintaining and loading data in relational database.
• More recently, a data warehouse might be hosted in the cloud with analytics and data visualization
facilities.
Data Warehouse and Azure SQL Database
Types of Data Warehouse
There are majorly three types of Data Warehouse
EDW (Enterprise Data
Operational Data Warehouse
Warehouse)
Data Marts
Data Warehouse and Azure SQL Database
Data Warehouse Architecture
Three-Tier Data Warehouse Architecture
is the most widely used Architecture of Data Warehouse. It consists of the Top, Middle and Bottom Tier.
Bottom Middle Top
Tier Tier Tier
OLAP server
which is
implemented
Front-End
Database using either
Client Layer
ROLAP or
MOLAP
model.
Data Warehouse and Azure SQL Database
Data Warehouse Components
There are four components of a Data Warehouse
Load manager
Warehouse
Query Manager
End-user access tools
Data Warehouse and Azure SQL Database
Why use Data Warehouse?
Data Warehouse provides a variety of facilities such as
Better data quality
Faster, business insights
Smarter decision-making
Gaining and growing competitive advantage
Data Warehouse and Azure SQL Database
Storage Account (Blob Storage)
An Azure storage account contains all your Azure Storage
data objects: blobs, files, queues, and tables. The storage
account provides a unique namespace for your Azure
Storage data that's accessible from anywhere in the world
over HTTP or HTTPS. Data in your storage account is
durable and highly available, secure, and massively
scalable.
Click here
Reference link
Data Warehouse and Azure SQL Database
Introduction to Azure Blob Storage
Azure Blob Storage is Microsoft's object storage solution for
the cloud. Blob Storage is optimized for storing massive
amounts of unstructured data. Unstructured data is data
that doesn't adhere to a particular data model or definition,
such as text or binary data.
Click here
Reference link
Data Warehouse and Azure SQL Database
About Blob Storage
Blob Storage is designed for:
Serving images or documents directly to a browser.
Storing files for distributed access.
Streaming video and audio.
Writing to log files.
Storing data for backup and restore, disaster recovery,
and archiving.
Storing data for analysis by an on-premises or Azure-
hosted service. Click here
Reference link
Data Warehouse and Azure SQL Database
Creating Azure Storage Account (Blob Storage)
Hands-on activity: Creating Azure Storage, Account
(Blob Storage)
Click here
Reference link
Data Warehouse and Azure SQL Database
Creating Azure Storage Account (Blob Storage)
Step 1: On the Azure portal menu, select all services. In
the list of resources, type Storage Accounts. As you begin
typing, the list filters based on your input. Select Storage
Accounts.
Step 2: On the Storage Accounts window that appears,
choose Add.
The following image shows the settings on the Basics tab
for a new storage account.
Click here
Reference link
Data Warehouse and Azure SQL Database
Creating Azure Storage Account (Blob Storage)
Step 3: Fill in the details:
Resource Group: Select an RG or create a new RG.
Storage Account Name: Select a unique name
between 3 to 24 characters.
Region: Select the region where the storage account
will be deployed. Note that, not all regions support all
the storage services.
Click here
Reference link
Data Warehouse and Azure SQL Database
Creating Azure Storage Account (Blob Storage)
Performance: Select premium and Block blobs in account type.
Redundancy: Select locally redundant.
Finally, Click Review and then Create after successful
validation.
Click here
Reference link
Data Warehouse and Azure SQL Database
Unit - 2
Introduction to SQL
Azure Database
Data Warehouse and Azure SQL Database
Create & Setup Azure SQL Database
Hands-on activity: Creating Azure SQL Database
Click here
Reference link
Data Warehouse and Azure SQL Database
Creating Azure SQL Database
Step 1: Browse to the Select SQL Deployment option
page.
Under SQL databases, leave Resource type set to
Single database, and select Create
Data Warehouse and Azure SQL Database
Creating Azure SQL Database
Step 2: Fill in the details:
Resource Group: Select an RG or create a new
RG.
Database Name: Select a unique name.
Server: Select an existing server or create a new
server.
Data Warehouse and Azure SQL Database
Creating Azure SQL Database
Compute + storage: Change it to server less,
standard 1 core 10 GB Storage. This will
reduce the bill significantly.
Keep the rest of setting to default.
Click on Review + create.
Data Warehouse and Azure SQL Database
Creating Azure SQL Database
If you have selected to create a new server then
• Set a unique server name.
• Preferred location.
• Under Authentication,
• Select Use SQL authentication.
• Set admin credentials.
• Click OK.
Data Warehouse and Azure SQL Database
Azure SQL DB Firewall Settings
Hands-on activity: Configuring DB Firewall
settings
Data Warehouse and Azure SQL Database
Azure SQL DB Firewall Settings
Step 1: Select Set server firewall
Data Warehouse and Azure SQL Database
Azure SQL DB Firewall Settings
Step 2: Select Set server firewall.
Step 3: Under Public access tab, select Selected
networks.
Step 4: Under Firewall Rules, click on Add your
client IPv4 address. This will allow you current
machine to connect to the DB server. No other
external machine can connect to the DB.
Click on Save.
Data Warehouse and Azure SQL Database
Installing SQL Server Management Studio
Windows 10 (64-bit) version 1607 (10.0.14393) or later
Windows Server 2022/ 2019/ 2016 (64-bit)
Supported hardware:
1.8 GHz or faster x86 (Intel, AMD) processor. Dual-core or better recommended
2 GB of RAM; 4 GB of RAM recommended (2.5 GB minimum if running on a virtual machine)
Hard disk space: Minimum of 2 GB up to 10 GB of available space
Data Warehouse and Azure SQL Database
Data Analytics on Microsoft Azure Cloud
• In the era of data as currency, companies
are looking to do more with their data.
• There are many ways to store, ingest and
process, and analyze data in the Azure
ecosystem.
• Tools like Azure Synapse Analytics, Power
BI, MS Excel can be linked with data in
cloud for analyses
Data Warehouse and Azure SQL Database
Quiz 1
................ describes the data contained in the data warehouse.
a) Metadata
b) Relational Data
c) Informational Data
d) None of the above
Ans : a) Metadata
Data Warehouse and Azure SQL Database
Quiz 2
Data warehouse is a ?
a) Subject oriented
b) Integrated
c) Time-variant
d) All of the above
Ans : d) All of the above
Data Warehouse and Azure SQL Database
Quiz 3
SQL Azure is a cloud-based relational database service that is based on ____
a) SQL Server
b) Oracle
c) MySQL
d) All of the above
Ans : a) SQL Server
Data Warehouse and Azure SQL Database
References
1. https://learn.microsoft.com/en-us/azure/storage/common/storage-account-overview
2. https://learn.microsoft.com/en-us/azure/azure-sql/database/connect-query-ssms?view=azuresql-mi
3. https://learn.microsoft.com/en-us/sql/ssms/quickstarts/ssms-connect-query-azure-sql?view=sql-server-
ver16
4. https://www.freetimelearning.com/online-quiz/microsoft-azure-quiz.php?page=7
Cloud Computing in MS Azure
Thank you!
