{"id":79674,"date":"2018-07-17T22:52:34","date_gmt":"2018-07-17T22:52:34","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=79674"},"modified":"2022-04-24T16:12:27","modified_gmt":"2022-04-24T16:12:27","slug":"power-bi-introduction-working-with-sql-server-data-in-power-bi-desktop-part-5","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/power-bi-introduction-working-with-sql-server-data-in-power-bi-desktop-part-5\/","title":{"rendered":"Power BI Introduction: Working with SQL Server data in Power BI Desktop \u2014 Part 5"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Power BI Introduction: Tour of Power BI \u2014 Part 1<\/a><\/li>\n
  2. Power BI Introduction: Working with Power BI Desktop \u2014 Part 2<\/a><\/li>\n
  3. Power BI Introduction: Working with R Scripts in Power BI Desktop \u2014 Part 3<\/a><\/li>\n
  4. Power BI Introduction: Working with Parameters in Power BI Desktop \u2014 Part 4<\/a><\/li>\n
  5. Power BI Introduction:\u00a0Working with SQL Server data in Power BI Desktop \u2014 Part 5<\/a><\/li>\n
  6. Power BI Introduction:\u00a0Power Query M Formula Language in Power BI Desktop \u2014 Part 6<\/a><\/li>\n
  7. Power BI Introduction: Building Reports in Power BI Desktop \u2014 Part 7<\/a><\/li>\n
  8. Power BI Introduction: Publishing Reports to the Power BI Service \u2014 Part 8<\/a><\/li>\n
  9. Power BI Introduction: Visualizing SQL Server Audit Data \u2014 Part 9<\/a><\/li>\n<\/ol>\n\n

    Power BI Desktop provides a far more robust environment for developing business intelligence reports than the Power BI service. Power BI Desktop supports more data sources and offers more tools for transforming the data retrieved from those sources.<\/p>\n

    One data source available to Power BI Desktop that is not available to the service is SQL Server. Although the Power BI service provides connectors to Azure SQL Database and SQL Data Warehouse, it does not offer one for SQL Server. With Power BI Desktop, you can retrieve SQL Server data from entire tables or run queries that return a subset of data from multiple tables. You can also call stored procedures, including the sp_execute_external_script<\/strong> stored procedure, which allows you to run Python and R scripts.<\/p>\n

    This article walks you through the process of retrieving data from a SQL Server instance, using different methods for returning the data. The article also covers some basic transformations. The examples are based on the AdventureWorks2017<\/strong> database, running on a local instance of SQL Server, but the principles apply to any SQL Server database.<\/p>\n

    In fact, many of the topics covered here can apply to other relational database management systems and even other types of data sources. Because Power BI Desktop organizes all data into queries<\/em> (table-like structures for working with datasets), once you get the data into Power BI Desktop, many of the actions you carry out are similar despite the source. That said, you\u2019ll still find features specific to relational databases, particularly when it comes to the relationships between tables.<\/p>\n

    Retrieving SQL Server Tables<\/h2>\n

    In Power BI Desktop, you can retrieve SQL Server tables or views in their entirety. Power BI Desktop preserves their structures and, where possible, identifies the relationships between them. This section provides an example that demonstrates how importing tables works. If you follow along, you\u2019ll start by retrieving three tables from the AdventureWorks2017<\/strong> database.<\/p>\n

    To retrieve the tables, click Get Data<\/em> on the Home<\/em> ribbon in the main Power BI Desktop window. When the Get Data<\/em> dialog box appears, navigate to the Database<\/em> category and double-click SQL Server database<\/em>. In the SQL Server database<\/em> dialog box, provide the name of the SQL Server instance and the target database, as shown in the following figure.<\/p>\n

    <\/p>\n

    For this article, I created two connection parameters. The first parameter, SqlSrvInstance,<\/em> contains the name of the SQL Server instance. The second parameter, SqlSrvDatabase,<\/em> contains the name of the database. When providing the connection information, you can use the actual names or you can create your own parameters. For details about creating parameters, refer to the previous article<\/a> in this series.<\/p>\n

    Once you\u2019ve entered the necessary information, click OK<\/em>. When the Navigator<\/em> dialog box appears, select the Person.Address, Person.BusinessEntityAddress,<\/em> and Person.Person<\/em> tables, as shown in the following figure.<\/p>\n

    <\/p>\n

    You can review the data from the selected table in the right pane. You can also choose to include related tables by clicking the Select Related Tables<\/em> button. For example, if you select the Person<\/em> table and then select the related tables, you would end up with nine tables, a lot more than you need for this example. Whether you take advantage of this option will depend on your specific project. If you do, but don\u2019t want to keep all the selected tables, simply clear the checkboxes associated with those you want to exclude.<\/p>\n

    When you\u2019re ready to import the tables into Power BI Desktop, click the Load<\/em> button. Any tables and views you\u2019ve selected will be loaded into Power BI Desktop and listed as individual datasets in Data<\/em> view. From there, you can modify the datasets in Query Editor, just as you would data retrieved from any other source.<\/p>\n

    One of the first steps you\u2019re likely to take in Query Editor is to rename each dataset and from there, remove any extra columns:<\/p>\n