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

1. TR23DPIL201_Client_1

Exercise 1 : Create Azure SQL Data Warehouse database

1. 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
   
2. 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/

3. Sign into the Portal
   Populate the Username and Password fields with the information provided or the credentials used to redeem your Azure Pass
4. Create new Azure SQL Data Warehouse
   Once the Portal loads, click New. Click Data + Storage and then click SQL Data Warehouse.
   
5. 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
   
6. 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
   
7. Validate the Data Warehouse
   Once the Data Warehouse has been provisioned the blade will open with the Data Warehouse details and information.
8. 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.

1. Start SQL Server Data Tools
   Click the Start Menu and then click on the icon for SQL Server Data Tools
   
2. 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
3. Browse the Database
   Once the Database has loaded, click on the arrow to expand the databas information
   
4. 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

5. 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
   
6. Execute the Query
   Click the Green Arrow to execute the query. Ensure the query completes successfully.
   
7. Open the Migration Utility
   On the Virtual Machine desktop, double click the Data Warehouse Migration Utility.
   
8. 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.
   
9. 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.
   
10. 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
11. 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.
    
12. 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.
    
13. 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.
    
14. 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.

1. 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:
   
   
   
   
   
2. 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:
   
   
   
   
   
3. 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:
   
   
   
   
4. 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:
   
   
   
   
   
   
   
5. 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;
6. 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.

1. 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:
   
    
2. 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!

1. Open Power BI Desktop
   On the Desktop, click on the PowerBI shortcut to open PowerBI
   
2. 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. 
   
3. Enter credentials
   On the following screen, ensure you are on the Database tab and then enter your SQL DW credentials and click Connect
   
4. Identify required Tables
   In the Navigator that loads, select the DimCustomer, DimProductSubcategory and FactInternetSales tables and then click on Load
   
5. 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.
   
6. 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.
   
7. 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.
8. 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.
   
9. 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.
   
10. Save the Report
    From the File menu, click Save As and save the Report to the Desktop
    

Experience Power BI reporting from an on-premises SQL Server database

Objective

In this lab you will learn how to:

• Install and configure the On-Premises Data Gateway
• Create a Power BI Desktop solution that uses a DirectQuery connection
• Interact with a Power BI report by using current data sourced from an on-premises SQL Server database

Scenario

This lab is based upon the sales activities of the fictitious Tailspin Toys company.

The Tailspin Toys company:

• Represents a specialist toy sales business that sells model airplanes and helicopters to global markets
• Accumulates core sales data in a SQL Server database, and is interested to explore and discover deeper insight from this data

You are required to create a Power BI Desktop solution to enable up-to-date reporting and analysis of US sales activity directly from an on-premises SQL Server database.

Virtual Machines

1. PowerBI-DC
2. PowerBI

Exercise 1 : Sign In to Power BI

In this exercise, you will sign in to the Power BI service.

1. Open Internet Explorer
   To open Internet Explorer, on the taskbar, click the Internet Explorer program shortcut.
   A personal trial account has been created for you, and it is recommended that you use this account to complete this lab.
   
2. Navigate to Power BI
   In Internet Explorer, navigate to http://powerbi.com.
   Tip: You can also use the Power BI Site Internet Explorer favorite.
   
3. Sign In to Power BI
   Click Sign In (located at the top-right corner).
   
4. Enter Credentials
   Enter the account details provided within the virtual machine environment (located at the right, in the Content tab).
   Tip: You can click the paste icon to insert the user name and password into the text boxes.
   Check the Keep Me Signed In checkbox, and then click Sign In.
   
5. Update Password
   When prompted to update the password, reenter the provided password, and then enter and confirm a new password.
   Click Update Password and Sign In.
   It is important that you remember the new password, as you will be required to authenticate with the Power BI service again later in this lab.
   
6. Manage Personal Storage
   To upgrade the trial account to the Power BI Pro trial license, at the top right corner, click the Settings command (cog), and then select Manage Personal Storage.
   
7. Upgrade to Power BI Pro
   At the top-right corner, click Try Pro for Free.
   A Power BI Pro license is required to work with live connections to on-premises data sources.
   
8. Start Power BI Pro Trial
   In the dialog window, if you agree to the terms, click Start Trial.
   When the trial extension has been confirmed, click Close.
   
9. Navigate to Workspace
   To navigate to your workspace, click the Power BI banner.
   Leave the Internet Explorer window open.
   

In this exercise, you signed in to the Power BI service.

Click Continue to proceed to the next exercise.

Exercise 2 : Set Up SQL Server

In this exercise, you will setup the environment by creating a dedicated SQL Server login for use by the gateway.

1. Open SQL Server Management Studio
   To open SQL Server Management Studio, on the taskbar, click the SQL Server Management Studio shortcut.
   
2. Connect to SQL Server
   In the Connect to Server window, configure the connection properties as displayed in the screenshot.
   Click Connect.
   
3. Open Script File
   On the File menu, select Open | File. In the Open File window, navigate to the D:\PowerBI\Lab09\Assets folder.
   Select the Script-01.sql file, and then click Open.
   Tip: For your convenience, you can click inside the File Name box, and then click Type Text.
   This script creates a SQL Server login named PowerBI to be used by the On-Premises Data Gateway. It also creates a database user for the TailspinToys-US database, and grants this user permission to only read data from the Sales table.

   D:\PowerBI\Lab09\Assets\Script-01.sql

4. Run Script
   First, review the script, reading the comments which precede each batch.
   To run the script, on the toolbar, click Execute.
   
5. Close Script
   To close the script, on the File menu, select Close.
   Leave SQL Server Management Studio open.

In this exercise, as a database administrator, you setup the environment by creating a dedicated SQL Server login for use by the gateway.

Click Continue to proceed to the next exercise.

Exercise 3 : Install On-Premises Data Gateway

In this exercise, as a database administrator, you will download and install the On-Premises Data Gateway.

1. Install On-Premises Data Gateway
   Switch to the Power BI Internet Explorer window.
   At the top-right corner, click the Download command (down-arrow), and then select Data Gateway.
   
2. Download Gateway
   Notice that new browser tab opens to display the Power BI gateway page.
   Click Download Gateway.
   
3. Install Gateway
   When prompted by Internet Explorer, click Run.
   
4. Configure Gateway Installation
   In the On-Premises Data Gateway Installer, click Next.
   Ensure that the On-Premises Data Gateway option is selected.
   Click Next.
   
5. Read Reminder
   When downloaded, and provided with a reminder, read the reminder, and then click Next.
   
6. Accept Terms of Use
   At the installation step, if you agree, check the I Accept the Terms of Use and Privacy Statement checkbox.
   Click Install.
   
7. Sign In to Power BI
   Once installed, click Sign In.
   In the Sign In to Your Account window, enter your Power BI credentials, and then click Sign In.
   Remember to use the password you assigned in the first exercise.
   
8. Configure Gateway Name
   In the New On-Premises Data Gateway Name box, enter your Power BI account name.
   For example, if your Power BI account is fred@tailspintoys.onmicrosoft.com, then enter fred.
   Using this name will ensure that your gateway is uniquely named within the tenant.
9. Configure Recovery Key
   In the Recovery Key box, enter Pass@word1. (Do not enter the period.)
   Enter the same value in the Confirm Recovery Key box also.
   Click Configure.
   
10. Verify Gateway Connection
    Verify that the gateway is ready and connected, and then click Close.
    The gateway is now running as a service on your machine, and is connected to your Power BI tenant.
    

In this exercise, you downloaded and installed the On-Premises Data Gateway.

Click Continue to proceed to the next exercise.

Exercise 4 : Create Gateway Data Source

In this exercise, as the gateway administrator, you will add a gateway data source.

1. Manage Gateways
   Return to the Power BI Internet Explorer window.
   To manage the gateway, at the top-right corner, click the Settings command (cog), and then select Manage Gateways.
   
2. Review Gateways
   In the left pane, notice that your gateway is listed.
   
3. Add Data Source
   Click Add Data Source.
   
4. Enter Data Source Settings
   In the Data Source Settings section, in the Data Source Name box, enter TailspinToys-US.
   In the Data Source Type dropdown list, select SQL Server.
   
5. Set Server and Database
   In the Server box, enter POWERBI.
   In the Database box, enter TailspinToys-US.
6. Set Gateway Credentials
   In the Authentication Method dropdown list, select Basic.
   In the Username box, enter PowerBI.
   In the Password box, enter Pass@word1. (Do not enter the period.)
7. Verify Data Source Settings
   Verify that your data source settings match the screenshot.
   
8. Add Data Source
   To add the data source, click Add.
   

In this exercise, you added a gateway data source.

Click Continue to proceed to the next exercise.

Exercise 5 : Create Power BI Desktop Query

In this exercise, as a business analyst, you will create a Power BI Desktop query to retrieve live data from a SQL Server database.

1. Open Power BI Desktop
   To open the Power BI Desktop, on the taskbar, click the Microsoft Power BI Desktop shortcut.
   Note that in this hosted lab environment, sometimes Power BI Desktop dialog windows do not fully display. If this happens during this lab, you can:
   

   • Press Enter, if the dialog window requires to you to agree to continue, or
   • Press Escape to close the dialog window, and then repeat the task instruction

   If you cannot overcome this issue, please request the assistance of a lab proctor.
   
2. Close Startup Screen
   To close the startup screen, at the top-right corner, click X.
   
3. Save File
   To save the file, click the File tab, and then select Save As.
   In the Save As window, save the file as D:\PowerBI\Lab09\US Monthly Sales.pbix, and then click Save.
   Tip: For your convenience, you can click inside the File Name box, and then click Type Text.

   D:\PowerBI\Lab09\US Monthly Sales.pbix

4. Retrieve SQL Server Data
   On the Home ribbon, from inside the External Data group, click the Get Data dropdown, and then select SQL Server.
   
5. Configure Database Settings
   In the SQL Server Database dialog window, inside the Server box, enter POWERBI.
   In the Database box, enter TailspinToys-US.
   
6. Select DirectQuery Option
   Select the DirectQuery option.
   Click OK.
   It is also possible to import the SQL Server data by using Import. In this mode, the data is imported—or copied—into Power BI Desktop and would require periodic refresh to keep the data current in the Power BI service.
   
7. Establish Connection
   When prompted to authenticate, to use your Windows credentials, click Connect.
   When prompted to confirm the use of an unencrypted connection, click OK.
8. Select Sales Table
   In the Navigator window, check the Sales table.
   
9. Review Data
   Review the data in the preview pane (located at the right).
   The data represents US retail sales transactions.
10. Develop Query
    To develop the query, click Edit.
    Notice that the Query Editor window opens, and that this window has its own ribbon.
    This window is used to define query steps to transform data, and to preview query results.
    
11. Create Custom Column
    To create a custom column, on the Add Column ribbon, from inside the General group, click Add Custom Column.
    
12. Name Custom Column
    In the Add Custom Column dialog window, in the New Column Name box, replace the text with Revenue.
13. Add Column Reference
    To add a column reference to the formula, in the Available Columns list, double-click the Quantity column.
14. Append Asterisk
    In the Custom Column Formula box, append a space and an asterisk (*), and another space.
    
15. Complete Formula
    In the Available Columns list, double-click the UnitPrice column.
16. Verify Formula
    Verify that the completed formula looks like the screenshot, and then click OK.
    Notice the addition of the Revenue column.
    
17. Format Revenue Column
    Select the Revenue column header, and then on the Home ribbon, from inside the Transform group, in the Data Type dropdown, select Decimal Number.
    
18. Remove Unnecessary Columns
    To remove unnecessary columns, first select the OrderDate column header (second column), and then while pressing the Control key, select also the Revenue column header.
    Right-click the column selection, and then select Remove Other Columns.
    
19. Create Year Column
    Create another custom column named Year by using this formula: "CY" & Text.From(Date.Year([OrderDate]))
    Tip: For your convenience, you can click inside the Custom Column Formula box, and then click Type Text.
    This expression produces a text value representing the year, for example CY2016.

    "CY" & Text.From(Date.Year([OrderDate]))

20. Create Month Column
    Create an additional custom column named Month by using this formula: if Date.Month([OrderDate]) < 10 then [Year] & "-0" & Number.ToText(Date.Month([OrderDate])) else [Year] & "-" & Number.ToText(Date.Month([OrderDate]))
    Tip: For your convenience, you can click inside the Custom Column Formula box, and then click Type Text.
    This expression produces a text value representing the month of the year, for example CY2016-01, representing January, 2016.
    There are more concise—and elegant—ways to write this expression. However, when using DirectQuery mode there are restrictions on the functions that can be used.

    if Date.Month([OrderDate]) < 10 then [Year] & "-0" & Number.ToText(Date.Month([OrderDate])) else [Year] & "-" & Number.ToText(Date.Month([OrderDate]))

21. Remove OrderDate Column
    To remove the column, right-click the OrderDate column header, and then select Remove.
    
22. Verify Query Result
    Verify that the query result consists of three columns, and looks like the screenshot.
    
23. Close Query Editor
    To exit the Query Editor window, on the Home ribbon, from inside the Close group, click the Close & Apply icon.
    

In this exercise, you created a Power BI Desktop query to retrieve live data from a SQL Server database.

Click Continue to proceed to the next exercise.

Exercise 6 : Publish Power BI Desktop Report

In this exercise, as a business analyst, you will design the report layout, and then publish the Power BI Desktop file to the Power BI service.

1. Rename Page
   To rename the page, at the bottom-left corner, double-click Page 1.
   Replace the text with US Monthly Sales, and then press Enter.
   
2. Expand Sales Table
   In the Fields pane (located at the right), if necessary, expand the Sales table.
   
3. Create Visualization
   To create a visualization based on a field, from the Fields pane, drag the Year field and drop it to a blank area of the canvas.
   Notice that the field was used to create a table visualization, presenting the unique year values.
   
4. Switch Visualization Type
   To switch the visualization to a slicer, in the Visualizations pane, click the Slicer icon.
   Tip: Hovering over a visualization type icon will reveal a tooltip that describes the visualization type.
   
5. Resize Slicer
   Resize the slicer, and then reposition it as shown in the screenshot.
   
6. Select CY2014
   In the slicer, select CY2014.
   All data visuals on the page will now be filtered by CY2014.
   
7. Create Another Visualization
   From the Fields pane, drag the Revenue field, and drop it to a blank area of the canvas.
   Reposition and resize the column chart, as shown in the screenshot.
   
8. Configure Chart Axis
   To configure the chart axis, from the Fields pane, drag the Month field, and drop it inside the chart.
   Notice that the columns are ordered by descending revenue value.
9. Modify Sort Order
   To modify the sort order, click the ellipsis located at the top-right corner, and then select Sort by Month.
   To sort by month ascending, click the ellipsis again, and then select Sort by Month again.
   
10. Verify Chart Design
    Verify that the chart design looks like the screenshot.
    
11. Change Report Slicer
    In the slicer, select CY2015.
    Select CY2016, and then notice that there is only data for the first seven months of the year.
    When you publish the Power BI Desktop solution to the Power BI service, you will then load the sales history for the remaining 2016 months into the SQL Server database. The remaining objective for this lab, then, is to observe that the entire year 2016 data will become available from the Power BI service simply by refreshing the report (and not by refreshing the dataset).
12. Save Power BI Desktop File
    On the File menu, select Save.
13. Publish Power BI Desktop File
    On the Home ribbon, from inside the Share group, click Publish.
    
14. Sign In to Power BI
    In the Sign In to Power BI dialog window, click Sign In.
    In the Sign In to Power BI Desktop window, enter your Power BI credentials, and then click Sign In.
    
15. Finish Up
    When the Power BI Desktop file has been published, click Got It.
    To close Power BI Desktop, on the File menu, select Exit.
    

In this exercise, you designed the report layout, and then published the Power BI Desktop file to the Power BI service.

Click Continue to proceed to the next exercise.

Exercise 7 : Refresh Power BI Report

In this exercise, as a business analyst, you will open the report published to the Power BI service. You will then load additional data into the on-premises SQL Server database, and then refresh the Power BI report to observe the new data displayed in the report.

1. Open Navigation Pane
   Review the screenshot, and then click the highlighted icon to open the Navigation Pane.
   
2. Open Report
   In the Navigation Pane, select the US Monthly Sales report.
   Notice the slight delay as the Power BI service retrieves data from the on-premises SQL Server database.
   Notice also that only seven months of data appears in the chart.
   
3. Load Sales History
   Switch to SQL Server Management Studio, and then open the D:\PowerBI\Lab09\Assets\Script-02.sql file.
   Tip: For your convenience, you can click inside the File Path box, and then click Type Text.
   This script loads sales history data for the remaining months of 2016.

   D:\PowerBI\Lab09\Assets\Script-02.sql

4. Run Script
   To run the script, on the toolbar, click Execute.
   
5. Review Output Message
   In the query pane, review the output message, confirming that sales data has been loaded for the remaining months of 2016.
   
6. Refresh Report
   Return to the Power BI Internet Explorer window.
   On the menu bar, click Refresh.
   
7. Verify Report Data
   Verify that the chart now displays all 12 months for year 2016.
   There are two primary benefits of using DirectQuery:
   

   • DirectQuery lets you build visualizations over very large datasets, where it otherwise would be unfeasible to first import all of the data
   • Underlying data changes can require a refresh of data, and for some reports, the need to display current data can require large data transfers, making re-importing data unfeasible. By contrast, DirectQuery reports always use current data.

   There are currently a few limitations to using DirectQuery:
   

   • Not all data sources are supported (currently supported: SQL Server, Azure SQL Database, Azure SQL Data Warehouse, SAP HANA, Oracle Database, Teradata Database), and only on-premises data source require the On-Premises Data Gateway
   • All tables must come from a single database
   • If the Query Editor query is overly complex an error will occur. To remedy the error you can: Delete the problematic step in Query Editor, or Create a view on the source database and retrieve data from it, or Import the data instead of using DirectQuery
   • Relationship filtering is limited to a single direction, rather than both directions
   • By default, limitations are placed on DAX expressions allowed in measures

   For additional information, read the Use DirectQuery in Power BI Desktop Power BI Documentation topic.
   

In this exercise, you opened the report published to the Power BI service. You then loaded additional data into the on-premises SQL Server database, and then refreshed the Power BI report to observe the new data displayed in the report.

Click Continue to complete the lab.