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.