Daniel Calbimonte

Create Azure Data Warehouse reports using Azure Power BI

April 11, 2016 by

Introduction

Power BI is an extremely powerful tool to create dashboards and reports. In the last article, we learned how to create a Data Warehouse in Azure. In this new chapter, we will learn how to create reports from the Data Warehouse in Azure using Power BI in Azure.

With Power BI, you have your information in real time, it is easy to learn and use. In Azure, you do not need to install anything!

In this article, I will show you how to create charts, work with maps and show some limitations of the tool.

Requirements

Getting started

  1. Open the Azure Portal and go to the SQL Database:


    Figure 1. SQL databases

  2. Select your SQL Data Warehouse created in earlier chapters and press “Open” in Power BI icon:



    Figure 2. The Data warehouse in the Portal

  3. If it is your first time using Power BI, you may need to specify your credentials. Otherwise, login with your credential:


    Figure 3. The login window.

  4. The system will show the Server name and the Database name. You can optionally enable the advanced option to specify the frequency to refresh the data (by default 15, which is the minimum value). Just press “Next”, unless you want to change the advanced options:


    Figure 4. The Data Warehouse Connection information

  5. You will need to specify the username and password to connect to the Database:


    Figure 5. The username and password

  6. You will have the following image created. Double click on it:


    Figure 6. The Power BI

  7. You are now in Power BI! You can create the report now.


    Figure 7. The Power BI Window

  8. Check the “FactInternetSales” in the “Fields” section:


    Figure 8. The Fields

  9. Select the “SalesAmount” field:


    Figure 9. Selecting the field for the chart

  10. In the legend, select the “Currency key”:


    Figure 10. Selecting the Legend for the chart

  11. The graph created is the following:


    Figure 11. The chart created

  12. You can easily change the bars to pies using the visualization option:


    Figure 12. The pie chart in Power BI

  13. You can add text boxes to your chart and add text by customizing the font, size, alignment:


    Figure 13. Adding the text box

  14. You have a button to analyze your information in Excel. This option is in the preview version. For this option, you will need to install the SQL_AS_OLEDB installer, which will be downloaded from the Azure Portal. The installer is a new version of the connector included in SQL 2016:


    Figure 14. Analyzing the report in Excel

  15. This new provider allows connecting to Power BI from Microsoft Excel. When you try to access, you will the the odc file. This file contains the connection information. The installer has 28 MB approx:


    Figure 15. The SQL_AS_OLEDB Provider

  16. To open the connection in Excel, in the menu go to “DATA”. Select the “Get External Data” and select “Existing Connections”. Then you will select the odc file:


    Figure 16. Connecting to Excel

  17. Your Power BI credentials will be required. You will be connected to Excel. You will be able to handle the Azure Data in Excel like any dynamic table:


    Figure 17. Using Excel to connect to Power BI

  18. In Power BI, you can also add Shapes to your charts, like rectangles, ovals, lines and arrows:


    Figure 18. The Power BI Shapes

  19. The format shape allows you to change the color, transparency, rotation, background, titles and other shape properties:

    Figure 19. The shape’s properties

  20. You can “Save” or “Save As” your report and create similar reports with some modifications:


    Figure 20. The File menu.

  21. In the menu, you can find the dashboards, reports, and datasets:


    Figure 21. The Power BI menu.

  22. When you click the “Save as” button, the report will be created in the report section:


    Figure 22. The reports

  23. By default, when you select a report, it is in a read-only mode. You’ll need to press the “Edit report” option to edit the report:


    Figure 23. Editing a report

  24. Another cool feature is the Map. In the “Fields” section, go to the “DimGeography” dimension and select the “City” field:


    Figure 24. The DimGeography dimension

  25. A map is displayed and it shows all the cities from the dimension:


    Figure 25. The Power BI maps

  26. Using the mouse wheel, you can Zoom in and Zoom out the map. You can see the names of the streets:


    Figure 26. The street names in Power BI

  27. In the a new report, create a chart of the “FactInternetSales” table of the “SalesAmount” field (as we did in step 9):


    Figure 27. The sales amount chart

  28. Now, I am going to try to get the chart of sales amount per currency name. In the “DimCurrency” table, select the “CurrencyName” field.


    Figure 28. The graph per Currency Name

  29. If you check the graph of the “SalesAmount” per currency name, you will notice that all the values are the same for each currency. This is because Power BI cannot detect the relationships between the Fact Table and the dimensions. This is not cool, but you can create views to get this information.



Conclusion

Power BI is a simple an intuitive tool. In Azure, you do not need to worry about the installation. You only need to take care of the information and create nice reports for your company. I hope you will have fun with it as I did.

In this article, we have learned how to create charts, add shapes, add text, and work with maps. We’ve also learned how to export the Power BI information to MS Excel. For Excel, you will need the last OLEDB provider. We’ve also noticed that Power BI cannot detect the relationships between the Fact and Dimension tables. The workaround is to create some views with the information required.

If you have any questions, please let me know – you may use the comments section below.

Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte
Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)

805 Views