Get started with Azure SQL Data Warehouse

Objective

In this lab, we will:

Learn how to connect to Azure Portal
Get familiar with the Azure Portal and how to use it for Azure SQWL Data Warehouse
Exercise 1: Create Azure SQL Data Warehouse database
Configure the Azure Database Server

Scenario

Virtual Machines

TR23DPIL201_Client_1

Exercise 1 : Create Azure SQL Data Warehouse database

Log onto the Virtual Machine

Click the Commands Menu along the top of the Lab Console. Click Ctl+Alt+Delete to bring up the Login screen. Click the Commands Menu again, click Paste > Paste Password and press Enter
Browse to the Azure Portal

On the Taskbar, click the Internet Explorer icon to load Internet Explorer. Once the browser loads, type https://ms.portal.azure.com/ into the Address Bar and press Enter
```
https://ms.portal.azure.com/
```
Sign into the Portal

Populate the Username and Password fields with the information provided or the credentials used to redeem your Azure Pass
Create new Azure SQL Data Warehouse

Once the Portal loads, click New. Click Data + Storage and then click SQL Data Warehouse.
Configure Server Settings

Scroll dow the blade and click on "Configure Required Settings". A new blade will open - type a Server Name, Admin Name and Password that you will remember, select Japan West as your location since this location has V12 resources available. Click Select
Populate the Data Warehouse details

Configure the remaining Data Warehouse blade values as below:
Database Name: AdventureWorksDWResource Group: IgniteGroupLeave Select Source as Blank database, check Pin to Dashboard and click Create
Validate the Data Warehouse

Once the Data Warehouse has been provisioned the blade will open with the Data Warehouse details and information.
Configure the Firewall

In the open blade, click on the Server Name and then under the Settings tab, click on Firewall. Once the blade opens, click inside the text box under Rule Name, type Lab-Rule and then enter 0.0.0.1 as the Start IP and 255.255.255.254 as the End IP. Under the Firewall Settings header, click Save and once the rule is Saved, click OK

Exercise 2 : Creating Tables and Loading Data

In this lab, we will create tables in an Azure SQL DW database and migrate Data from a regular SQL Server Database.

Start SQL Server Data Tools

Click the Start Menu and then click on the icon for SQL Server Data Tools
Add a SQL Server

If not already open, open SQL Server Object Explorer by clicking View and then SQL Server Object Explorer. Right click the SQL Server node and then click Add SQL Server. A dialog will launch, populate the values with the information from the SQL Data Warehouse created in the first exercise. You will need to use SQL Server Authentication and the Database Name may take a moment to populate. Click Connect
Browse the Database

Once the Database has loaded, click on the arrow to expand the databas information
Query the database

Right click the Database Name and then click New Query. In the query window that open enter the following and click execute (the green arrow)
select @@version
```
select @@version
```
Open a query

In SQL Server Data Tools, click File > Open File. Browse to C:\Labs_Students and then select the LAB2_Exercise01.dsql file and click Open. Once the query loads, click on the Connect icon above the query. The same connect dialog will load but this time you can click on History and then select the AdventureWorks connection. Click Connect
Execute the Query

Click the Green Arrow to execute the query. Ensure the query completes successfully.
Open the Migration Utility

On the Virtual Machine desktop, double click the Data Warehouse Migration Utility.
Connect to Migration Tool

Select SQL Server for Source Type and Azure SQL Data Warehouse for Destination Type, click Next. Populate the dialog with the Local Server Name and then click Connect using Windows Authentication. The connection will show as a green dot icon with the local Server Name and the AdventureWorks Data Warehouse should be listed.
Migrate the database

Select the AdventureWorks DW and then click on the Migrate Selected icon. This will load all the tables, select all the Tables by clicking the check box next to each table. Click on Migrate Data and then click on Run Migration. The package will load, click next and then verify connection information to the Data Warehouse and click Generate. Click OK

The .bat files are created to load the data into SQLDW.
Update a Lab File

Click on Explorer and then browse to C:\Labs_Students. Right click on the LAB2_Exercise02.bat file and then select Edit. Maximize the file and then edit the following lines and click Save:
• set server=tcp:
• set user=
• set password=
• set database=AdventureworksDW
• set schema=dbo
Execute the script

Open a Command prompt by clicking on the Command icon on the taskbar. Type CD C:\Labs_Students\ and press Enter. Type Lab2_Exercise02.bat and press Enter. The script should execute without errors if all details are correct.

We are not using the bat files from the Migration Wizard and are instead using a pre prepared file in the Labs_Students folder.
Query Database Settings

Using the File menu in SQL Server Data Tools, open LAB 2 - Exercise03 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again.
Query Table Properties

Using the File menu in SQL Server Data Tools, open LAB 2 - Exercise04 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again.
Query Table and Column Properties

Using the File menu in SQL Server Data Tools, open LAB 2 - Exercise05 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again.

Exercise 3 : Understanding Data Movement

Using SQL Server Data Tools, run these queries. Look at the DSQL query plan and watch out, or take note, of the DSQL operations. To get the Execution Plan, you will use the EXPLAIN command, which will produce the execution plans in XML. Execution Plans do help to identify different types of data movement operations and then you need to “explain” or think about why the operations did occur.

Create a Simple Query

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise01 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:
Distributed Table Query

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise02 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:
Distributed Table - Filtered Select

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise03 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:
Join two distributed tables

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise04 from the Labs_Student folder and then execute the Script up to the /*Setup comment. Execute the second part of the script seperately and then analyze results . You may need to connect to the SQL Server DW again. Once the query executes, click on the Links in the query results to see the Query Plans

Query Part 1
Important to watch out for:

SELECT [T1_1].[ProductKey] AS [ProductKey],
….
FROM [AdventureWorksDW].[dbo].[FactInternetSales] AS T1_1
[TEMP_ID_1]
CustomerKey;

Query 2

Important to watch out for:
Aggregation Query

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise05 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:

CalendarYear;
Incompatible Aggregation Query

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise06 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:

DateKey;

Exercise 4 : Optional Queries

Using SQL Server Data Tools, run these queries that select from only one table. Look at the DSQL query plan and take note of the dsql operations.

Inequality join - Broadcast move

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise07 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:
Inequality join - Shuffle move

Using the File menu in SQL Server Data Tools, open LAB 3 - Exercise08 from the Labs_Student folder and then execute it. You may need to connect to the SQL Server DW again. Once the query executes, click on the Link in the query results to see the Query Plan

Important to watch out for:

[TEMP_ID_3305]
CustomerKey;

Exercise 5 : Connecting to Power BI (Optional)

This lab will use Power BI to show how a Azure SQL Data Warehouse can be used together with a client like Power BI.

All of this Lab is OPTIONAL, this is for your convenience to continue and learn afterwards!

Open Power BI Desktop

On the Desktop, click on the PowerBI shortcut to open PowerBI
Connect to SQL Data Warehouse

Once PowerBI loads, click on the Get Data link. In the Get Data dialog, click on Azure and then higlight the Microsoft Azure SQL Data Warehouse option. Click Connect and then enter the Server and Database name and then ensure the Direct Query radio button is selected and click OK.
Enter credentials

On the following screen, ensure you are on the Database tab and then enter your SQL DW credentials and click Connect
Identify required Tables

In the Navigator that loads, select the DimCustomer, DimProductSubcategory and FactInternetSales tables and then click on Load
Create a Tree Map

Expand the FactInternetSales Table and then check the SalesAmount column. Expand the DimProductSubCategory table and check off the EnglishProductSubCategory column. Once the data has loaded to the canvas click on the Tree Map visualization.
Format the Chart

Click on the Pencil icon. Expand the Legend section from and click the Off button to turn the Legend on and then click the dropdown to Position the Legend on the right.
Create a Combo Chart

Click in the white space away from the existing chart and then select SalesAmount from the FactInternetSales table. From the DimCustomer table select the field CommuteDistance. The visualization should default to a bar chart of sales grouped by commute distance. Move the chart up to next to the Tree Map.
Change the Visualization

With the commute distance graph selected, switch the visualization to Line and Clustered Column Chart from the Visualization section. Drag the field ProductStandardCost from FactInternetSales in the field list to the Line Values section of the chart data section. Click the dropdown next to the ProductStandardCost field in the line values to change the aggregation type to Average.
Create a Gauge

Click anywhere in the blank space on the PowerBI design pane. In the field list select OrderQuantity from the FactInternetSales table. With the bar chart still selected, switch the visualization to Radial Gauge from the Visualize section of the ribbon. Assure the graph is in the bottom left section of the PowerBI design pane and resize as necessary.
Save the Report

From the File menu, click Save As and save the Report to the Desktop