Module 2: Azure SQL Data Visualization with PowerBI

Lab Overview
Exercise 1: Connect to Jump VM via RDP
Exercise 2: Connect Power BI to AzureSQL & Prepare Dashboard’s

Module 2: Azure SQL Data Visualization with PowerBI

Lab Overview

In this lab, you will connect Power BI Desktop to Azure SQL DB and prepare dashboards for Adventure works sales and marketing team.
Please note Power BI Desktop is installed in Jump VM already, however you can also use your own computer which has Power BI desktop client installed to carry out labs in this module.
Adventure works is a Cycle company and their Sales & Marketing team is interested in getting following analysis.

Map view with Sales Revenue(Total Due) for each city. Sales team should be able to move over mouse cursor over map to see the total due amount for that city.
Marketing team is interested in learning about the total revenue based on marketing type i.e. sales reason.
In this module, we will setup dashboard for these requirements. If you’ve Power BI Desktop Client available in your laptop, you can use that to carry out these exercises or you can use Power BI desktop client installed on Jump VM.

Exercise 1: Connect to Jump VM via RDP

In this exercise, you will connect to the Jump VM, where you will find tools to perform load test on the Azure SQL DB.

Click on the Resource groups icon in the Menu navigation bar.

Click on the resource group in the Resource group blade.

In the blade that appears, click on Overview.

Under the overview section, click on virtual machine.

In the Virtual Machine blade , click on Overview.

Now click on Connect to work on the virtual machine. If you’re using Apple Mac, you can jump directly to Step-12.

On clicking on Connect, the rdp file automatically gets downloaded. Open the downloaded file.
In the remote desktop window that pops up, Click on Connect.

Now, you will choose the different account and log in with your credentials that you received in mail.

Enter the Vm Username and Password that you received in the mail.

Click on Yes in pop up window.

Now you are logged into your Virtual Machine
If you’re using Mac, Copy the Jump VM DNS Name from the email you received.
Open Microsoft RDP Client from Applications/Spotlight. If you don’t have RDP client installed, you can download it from here: https://itunes.apple.com/in/app/microsoft-remotedesktop/id715768417?mt=12
In the Connection Center, click + and then click Add PC or Server.
Enter the following information: a. PC name – Enter the DNS Name Copied from the e=mail b. User name - The user name copied from the email. c. Password – Enter the password copied from -email
You can also set these optional settings: a. Friendly name
Click Save. To start the connection, just double-click it. This should open remote session. Please accept certificate warning as required during the connectivity.

Exercise 2: Connect Power BI to AzureSQL & Prepare Dashboard’s

Exercise Overview

In this lab, you will add Azure SQL database as data source via Power BI desktop client.

Login with Jump VM via remote desktop. See Exercise 4 for more information. You do not need to do this if you’ve Power BI Desktop client available on your laptop.
On Desktop, you should find Power BI Desktop icon. Double click to launch that.

After open, the Power BI desktop you will below screen. Click on the sign in to sign in with power bi account.

In sign in window enter your Power BI id, use the azure username password received in email.

After entering the Power BI id, click on the Sign in.

Now you need to enter your Power BI password and then click on login as in below screen shot.

After Sign in you will see screen as the canvas. Now in the upper right corner see you Power Bi username labeled as 1. After that, click on the Get Data labeled as 2 in canvas.

You are connecting to Azure SQL database so click on Azure.

Now select the Azure SQL database and then click on the connect button.

Now you have to enter the SQL server URL and database name.

We need to have SQL server url to connect, you can find it in the e-mail you received with lab details or find from Azure portal. Following steps describes where you can find this in azure portal. Goto https://portal.azure.com, then inside your resource group get in SQL server.

Go to the Properties of SQL server.

In Properties blade make a note of server name.

Now go back to your Power BI desktop client and fill in SQL Server details as specified below • Server: As noted in last step • Database: Enter the database name i.e. “AdventureWorksDB1” • Username: Enter username you got in lab details mail. • Password: Enter password you got in lab details mail.
Now enter the server url and database name. Then select the DirectQuery for live connection with Azure SQL server and drop down the advance tap to for navigating in hierarchy of database.

Now check the checkbox: Navigate using full hierarchy. Now click ok.

Now to connect with Azure SQL you need to verify the window and SQL server credentials. So, select the radio button of Use my credentials and then click on Database to enter Azure SQL credentials.

Now enter the Username and Password of Azure SQL server that you received in lab detail mail after that click on the connect button.

Now you will see the all the data coming in AdventureWorks1 database. Expand the person folder to see the tables inside it.

Now, Inside the person folder select the table named as Address.
After that expand the sales folder also.

Inside the sales folder select the following tables: • SalesOrderDetail • SalesOrderHeader • SalesOrderheaderSalesReason • SalesReason Now, click on the Load to load the selected tables.

You will see the selected tables loaded and we can see the tables under FIELDS blade, now click on the relationship icon marked in left side below the paste icon.

You can manage the relationships between tables data by Manage Relationships function under Home tab of menu bar. So, click on Manage Relationships

Now, select relationships one by one and edit these relations by edit button.

Now you must ensure that following properties as: • Make the relationship active • Cross filter direction: Both Then click on OK button. You have to do it for all the relationships in previous step.

After editing all the relationships click on Report icon below the paste icon as in below canvas.

Now in report blade under FIELDS area select Address table to choose columns for creating report.

Now, select the City Column and after that, expand the SalesOrderHeader table.

Now select the TotalDue column.

You can see the report blade in the left side under white background area. You can resize the report as select, drag and drop the right below corner of report blade.

When you move your mouse hover on the green icon in map view you can see the TotalDue by City as canvas.

Rename the Page 1 by right click on it and then select Rename page. Rename the page as Sales by City.

You can see the renamed page in below screen, now click on the + sign to add new page.

Now under SalesOrderHeader table select the TotalDue Column and under SalesReason table select Name for preparing report TotalDue by name.

You can see the report on screen, now click anywhere in black area to generate another report on same page.

Now select City under the Address table and then resize the map view report by expanding from the right lower corner. Now click in blank area as blue stamp in the below screenshot.

Now click on the OrderDate under SalesOrderHeader table. Then you can see the report Orderdate labeled as 3. Now click on the Slicer icon under Visualization labeled as 4 for filtering the report on page level by order date.

You can see the report area after selecting the slicer Visualization. Rename the current page as Sales by name and city [Filtered by Date].

Now you can see the renamed page than click on the + icon to add a new page.

Now select the ModifiedDate and SalesPersonID under SalesOrderHeader table in FIELD area blade. You can see the report labeled as 3. Now choose the Line Chart view under the visualization area to change the report in Line chart view.

Now resize the Line Chart report by expanding it from the lower right corner as in below canvas.

Now you can see the report on the whole page after resizing the report. Rename the page **SalesPersonID by ModifiedDate.

Now you can see the modified name of the page as SalesPersonID by ModifiedDate. Click on + icon to add a new page.

Now select the TotalDue under SalesOrderHeader table and Name under SalesReason. Now Change the view by clicking on the Pie Chart icon under visualization labeled as 4.

You can see the pie chart report of TotalDue by Name. Rename the page as PieChart [TotalDue by Name] and add a new page by clicking the + icon.

Now Select the TotalDue and ReasonType and click on Funnel icon under the visualization.

This is the Funnel view of report. on mouse hovering on the funnels you can see the TotalDue by ReasonType. Rename the page as TotalDue by Reason and then publish the report to the Power BI online by clicking on the Publish button in upper right corner.

When you click on the Publish Report it will ask you to save the report then click on the save button.

Give File Name as AdventureWorksDB1 and save the report.

Now you see a window pop up’s Published succeeded, Click on the Open ‘AdventureWorksDB1.pbix’ in Power BI.

Now you need to SIGN IN to Power BI online.

SIGN IN with same ID as you Signed IN in Power BI desktop. After entering your id click on Next button.

Enter your password and click on Sign In.

You need to enter the credentials of Azure SQL Data to connect with data source for live connection. Click on the Enter Credentials.

After clicking on edit credentials you will redirected to the below screen. Then ensure you are under Datasets tab AdventureWorksDB1 is selected. Then click on the Edit Credentials.

Enter Azure SQL server Credential you received by lab invitation mail. After entering the username and the Password click on the Sign IN button.

You will see the following notification after Sign In: SqlServer Data Source updated.

Now click on the drop down icon of My Workspace in left side of your screen.

Now click on the AdventureWorksDB1 under Reports to see you published report from the Power BI Desktop.

You can see the all reports you published now Click on Sales by City Report.

Now click on the Pin icon to pin the report on Dashboard.

As you don’t have any existing dashboard so click on the New dashboard radio button. Name the dashboard as Dashboard [AdvantureWorks] then click on the PIN.

Now Click on Dashboard [AdvantureWorks] under Dashboard Tab. Similarly, you can pin more report on your dashboard.

Look on below dashboard contains more the one report as I also pinned another report to the dashboard.

You can prepare report from here also by from your DATASETS AdventureWorksDB1.

Module 2: Azure SQL Data Visualization with PowerBI - akn9050/Power-BI-Integrated-Azure-SQL-DB GitHub Wiki