{"id":2073,"date":"2015-08-24T00:00:00","date_gmt":"2015-08-14T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/working-with-sql-server-data-in-power-bi-desktop\/"},"modified":"2021-05-17T18:33:14","modified_gmt":"2021-05-17T18:33:14","slug":"working-with-sql-server-data-in-power-bi-desktop","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/working-with-sql-server-data-in-power-bi-desktop\/","title":{"rendered":"Working with SQL Server data in Power BI Desktop"},"content":{"rendered":"<div class=\"article-content\">\n<p class=\"start\">In July, Microsoft announced the general availability of Power BI 2.0, the official replacement for Power BI for Office 365 (Power BI 1.0). Since then, much ado has been made about the upgraded service and its extensive line of reporting and analytical capabilities.<\/p>\n<p>Power BI delivers self-service business intelligence (BI) to knowledge workers, business analysts, and anyone else who needs to gain quick insight into data. With Power BI, you can create reports that contain a variety of rich visualizations, post those visualizations to your dashboard, share the information with other users, and access the dashboard and reports from your mobile devices. <\/p>\n<p>Your Power BI visualizations can be based on data from a wide range of sources. For example, you can retrieve data from services such as Google Analytics, GitHub, Zendesk, or Visual Studio online, or you can pull data from local files or OneDrive. Power BI also supports several database options, including Azure SQL Database, Azure SQL Data Warehouse, a tabular instance of SQL Server Analysis Services (SSAS), and Spark on Azure HDInsight, as you can see in the following figure. <\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-576877e6-b9e6-4858-b6b1-eedd1f15a78e.png\" alt=\"2266-576877e6-b9e6-4858-b6b1-eedd1f15a78\" \/><\/p>\n<p>The figure shows the Power BI page you navigate to in order to connect to one of the database source types. You start by clicking the source type you want to use and then following the online prompts to provide the necessary information and credentials. <\/p>\n<p>What&#8217;s interesting about all this, however, is not which database source types are included or how you set them up, but rather what source type is missing: SQL Server. Power BI does not provide a connector for an on-premises instance of SQL Server. For that, you must turn to Power BI Desktop.<\/p>\n<h1>Introducing Power BI Desktop<\/h1>\n<p>Power BI Desktop provides an integrated environment for retrieving and transforming data in order to define datasets that you can use to create reports containing different types of visualizations. After you&#8217;ve created your datasets and reports, you can publish them to the Power BI website, where you can work with them or share them as you would those created through the web tools. <\/p>\n<p>Although similar to the Power BI service in many ways, Power BI Desktop provides a more robust environment for transforming data, in addition to being able to retrieve data from a wider range of sources, including SQL Server.<\/p>\n<p>To get started with Power BI Desktop, you first need only download the program from the <a href=\"https:\/\/www.microsoft.com\/en-us\/download\/details.aspx?id=45331\">Microsoft Download Center<\/a> and install it as you would any other desktop application. When you start it up, you&#8217;re presented with the user interface (UI) shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-ff7787d0-8ba1-4983-9445-87572fe521de.png\" alt=\"2266-ff7787d0-8ba1-4983-9445-87572fe521d\" \/><\/p>\n<p>We&#8217;ll get more into many of the specifics of the UI as we work through the article. For now, the important feature to note is the left toolbar, which contains three options for accessing the views that define the report-creation workflow.<\/p>\n<ul>\n<li><strong>Report<\/strong><strong> view:<\/strong> Create reports that contain visualizations based on the transformed datasets. This is the default view when you open Power BI Desktop.<\/li>\n<li><strong>Data view:<\/strong> Manage the datasets you created from the data you retrieved from the target sources. You can create data sets without this view being active.<\/li>\n<li><strong>Relationships<\/strong><strong> view:<\/strong> Define relationships between the datasets.<\/li>\n<\/ul>\n<p>Another important component of the report-creation workflow is Query Editor, which opens in a window separate from the main UI. This is where you transform your datasets. <\/p>\n<p>The rest of the article demonstrates how you can use Power BI Desktop to retrieve and transform data from SQL Server. I created the examples based on data from the <strong>AdventureWorks2014<\/strong> database, which in my case, is on a local instance of SQL Server 2014.<\/p>\n<h1>Retrieving data from SQL Server<\/h1>\n<p>The place to start, of course, is to get the data we need. To initiate a connection to SQL Server, click the <strong>Get<\/strong> <strong>Data<\/strong> button on the <strong>Home<\/strong> ribbon, and then click <strong>SQL<\/strong> <strong>Server<\/strong>. If you want to see all available data sources, you can instead click <strong>More<\/strong> to open the <strong>Get<\/strong> <strong>Data<\/strong> dialog box (shown in the following figure). From there, you can navigate to the <strong>SQL<\/strong> <strong>Server<\/strong> option.<\/p>\n<p> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-f3e06ebc-3f07-4498-9f52-f93e1c02713d.png\" height=\"547\" width=\"496\" alt=\"2266-f3e06ebc-3f07-4498-9f52-f93e1c02713\" \/><\/p>\n<p>Whichever approach you take, you&#8217;ll be prompted to provide connection information, including the SQL Server instance and, optionally, the database name. You can also specify a T-SQL query at this time, if you want to be more precise in defining the data you initially return.<\/p>\n<p>After you&#8217;ve connected to the SQL Server instance, the <strong>Navigator<\/strong> dialog box appears, where you can select the tables and views you want to import into Power BI Desktop, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-5f7ca96b-fade-4248-9e1a-a400de835116.png\" alt=\"2266-5f7ca96b-fade-4248-9e1a-a400de83511\" \/><\/p>\n<p>For our demo, we&#8217;ll pull the following three tables into Power BI Desktop:<\/p>\n<ul>\n<li><strong>Person.Person<\/strong><\/li>\n<li><strong>Sales.<\/strong><strong>SalesOrderHeader<\/strong><\/li>\n<li><strong>Sales.SalesTerritory<\/strong><\/li>\n<\/ul>\n<p>Notice at the bottom of the <strong>Navigator<\/strong> dialog box you have options for either loading the data or editing the data. If you click <strong>Load<\/strong>, you will load the data as is, and the tables will be added as datasets, which you can view in Data view. From there, you can launch Query Editor to transform the data or start using the data to create visualizations. Or you can instead click the <strong>Edit<\/strong> button to launch right into Query Editor, which is what we&#8217;ll do here.<\/p>\n<h1>Transforming data in Query Editor<\/h1>\n<p>Transforming the data is where all the fun begins. In Query Editor, you apply a series of steps to each data set in order to transform the data. Query Editor records each transformation in the <strong>Applied<\/strong> <strong>Steps<\/strong> box at the bottom of the <strong>Query<\/strong> <strong>Settings<\/strong> pane. The following figure shows the <strong>Person<\/strong> dataset, with two steps applied.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-69abda2b-1af1-457f-9cab-9533bb5601a0.png\" alt=\"2266-69abda2b-1af1-457f-9cab-9533bb5601a\" \/><\/p>\n<p>By default, a dataset starts with the steps&#160;<strong>Source<\/strong> and <strong>Navigation<\/strong>, which define the initial connection and data retrieval. At this point, the data is just as we imported it from the source database.<\/p>\n<p>Our goal for the <strong>Person.Person<\/strong> table is to create a dataset that includes only the names and IDs of the Adventure Works sales reps. To define the dataset, we can start by using the <strong>PersonType<\/strong> column to filter out all rows except those with a value of <strong>SP<\/strong>. To apply the filter, click the arrow at the top of the column and, in the pop-up dialog box, clear all checkboxes except the <strong>SP<\/strong> option, as shown in the following figure.<\/p>\n<p> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-1e644677-c8e4-4b89-a79f-62f143f82ce5.png\" height=\"330\" width=\"440\" alt=\"2266-1e644677-c8e4-4b89-a79f-62f143f82ce\" \/><\/p>\n<p>After you click <strong>OK<\/strong>, Query Editor removes all but the sales rep rows and adds a step named <strong>Filtered<\/strong> <strong>Rows<\/strong> to the <strong>Applied<\/strong> <strong>Steps<\/strong> box. <\/p>\n<p>You can now continue to the next step, which is to remove all but the <strong>BusinessEntityID<\/strong>, <strong>FirstName<\/strong>, and <strong>LastName<\/strong> columns. To remove the other columns, click the header of the first column, press and hold the Control key, and click the other column headers. After you&#8217;ve selected all the columns to be deleted, right-click the header of one of the selected columns, and then click <strong>Remove<\/strong> <strong>Columns<\/strong>. Query Editor will remove the columns and add the <strong>Removed<\/strong> <strong>Columns<\/strong> step to the <strong>Applied<\/strong> <strong>Steps<\/strong> box.<\/p>\n<p>The only transformation left is to sort the rows, based on the <strong>LastName<\/strong> value. To do so, once again click the arrow at the top of the column and click <strong>Sort<\/strong> <strong>Ascending<\/strong>. This sorts the rows and adds the <strong>Sorted<\/strong> <strong>Rows<\/strong> step to the <strong>Applied<\/strong> <strong>Steps<\/strong>&#160;box. You should be left with a dataset similar to what&#8217;s shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-2f2e6d65-13b2-435a-b74d-c6ddec23a185.png\" alt=\"2266-2f2e6d65-13b2-435a-b74d-c6ddec23a18\" \/><\/p>\n<p>There are a couple items worth noting in the figure. First, you have a set of columns in your dataset that contain oddly colored values called <strong>Table<\/strong> and <strong>Value<\/strong>. Query Editor provides this information to point you to data related in other tables in the source data. However, this information does not show up in your final dataset.<\/p>\n<p>You might have also noticed that I renamed the dataset to <strong>SalesReps<\/strong>, to make it more suitable to the type of data. In addition, we now have six steps in the <strong>Applied<\/strong> <strong>Steps<\/strong> box. Power BI Desktop retains these steps so we can return to the query at any time to view how the data was transformed as well as modify those transformations if needed. We can even move steps up or down or remove them altogether. Be careful, however, when you start changing or moving steps around because there can be dependencies between steps and you could end up with unexpected results.<\/p>\n<p>I won&#8217;t walk you through each step for transforming the other two datasets, but I&#8217;ll provide you with an overview so you can do it on your own. For the <strong>SalesOrderHeader<\/strong> table, change the name to <strong>Sales<\/strong> and apply the following transformations:<\/p>\n<ul>\n<li>Remove all columns except <strong>SalesOrderID<\/strong>, <strong>OrderDate<\/strong>, <strong>SalesPersonID<\/strong>, <strong>TerritoryID<\/strong>, and <strong>Subtotal<\/strong>.<\/li>\n<li>Filter out all rows with a <strong>SalesPersonID<\/strong> value of <strong>NULL<\/strong>.<\/li>\n<li>Change the data type of the <strong>OrderDate<\/strong> column to <strong>Date<\/strong>. To change the type, select the column and then, on the <strong>Transform<\/strong> ribbon, click <strong>Data<\/strong> <strong>Type<\/strong> and then click <strong>Date<\/strong>.<\/li>\n<\/ul>\n<p>After you apply these transformations, you should end up with a dataset that looks similar to the one shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-1c1f7037-4746-4b97-b57b-59397fe79c07.png\" alt=\"2266-1c1f7037-4746-4b97-b57b-59397fe79c0\" \/><\/p>\n<p>For the <strong>SalesTerritory<\/strong> table, shorten the name to exclude the schema and apply the following transformations:<\/p>\n<ul>\n<li>Remove all columns except <strong>TerritoryID<\/strong>, <strong>Name<\/strong>, <strong>Country<\/strong><strong>Region<\/strong><strong>Code<\/strong>, and <strong>Group<\/strong>.<\/li>\n<li>Rename the <strong>CountryRegionCode<\/strong> column to the <strong>CountryCode<\/strong> column.<\/li>\n<\/ul>\n<p>Your final table should look something like what&#8217;s in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-08ce9e07-d9f1-48ab-b6de-5eff5fe321a3.png\" alt=\"2266-08ce9e07-d9f1-48ab-b6de-5eff5fe321a\" \/><\/p>\n<p>That&#8217;s all there is to applying basic transformations to a dataset. Overall, Power BI Desktop makes this an intuitive and painless process. These might not be particularly complex transformations, but for some users, they will be plenty. <\/p>\n<p>Query Editor includes lots of other types of transformations for performing such tasks as removing duplicates, splitting columns, merging queries, replacing values, pivoting data, or applying mathematical expressions. You can even access the DAX code underlying the dataset, as shown in the following figure, as well as access the DAX expressions used to transform the data.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-29fb76c9-0a38-4d48-9bc9-2b567ae7e275.png\" alt=\"2266-29fb76c9-0a38-4d48-9bc9-2b567ae7e27\" \/><\/p>\n<p>For now, working with these expressions, like other advanced features, is beyond the scope of this article (but will perhaps get covered in a subsequent article or two). For now, I encourage you to play around with the transformations and get a sense of what you can do in Query Editor. In the meantime, let&#8217;s look at a couple other steps we can take right now.<\/p>\n<h1>Grouping data in Query Editor<\/h1>\n<p>Power BI Desktop lets us group data just like we can in SQL Server. For example, suppose we want to include a visualization in our report that aggregates sales data across years. One approach we can take is to duplicate the <strong>Sales<\/strong> dataset and use the copy to create a separate, aggregated dataset. <\/p>\n<p>To duplicate the <strong>Sales<\/strong> dataset in Query Editor, right-click the dataset in the left pane, and then click <strong>Duplicate<\/strong>. Change the name of the new dataset to <strong>AnnualSales<\/strong>, and then remove all but the <strong>OrderDate<\/strong> and <strong>SubTotal<\/strong> columns. <\/p>\n<p>The next step is to extract the years from the values in the <strong>OrderDate<\/strong> column. To do so, select the column, and then, on the <strong>Transform<\/strong> ribbon, click <strong>Date<\/strong>, point to <strong>Year<\/strong>, and then click <strong>Year<\/strong>.<\/p>\n<p>The next thing we need to do is to change the data type of the <strong>OrderDate<\/strong> column to text so our visualizations don&#8217;t treat them as numbers and try to aggregate them. To change the type, select the column and then, on the <strong>Transform<\/strong> ribbon, click <strong>Data<\/strong> <strong>Type<\/strong> and then click <strong>Text<\/strong>.<\/p>\n<p>We are now ready to group the data. To do so, click the <strong>Group<\/strong> <strong>By<\/strong> button on the <strong>Transform<\/strong> ribbon. This launches the <strong>Group<\/strong> <strong>By<\/strong> dialog box, shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-e4181d8d-8c70-474c-918a-23b4cbb63894.png\" alt=\"2266-e4181d8d-8c70-474c-918a-23b4cbb6389\" \/><\/p>\n<p>The figure shows how you should configure the settings to aggregate the <strong>SubTotal<\/strong> values based on the <strong>OrderDate<\/strong> column. We will end up with a column named <strong>YearlySales<\/strong> that shows the total amount of sales for each year.<\/p>\n<p>Next, change the name of the <strong>OrderDate<\/strong> column to <strong>OrderYear<\/strong> by right-clicking the current column name and clicking <strong>Rename<\/strong>. You should end up with a dataset that looks similar to the one in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-39dd4f48-b9ac-431c-b70f-16b01e7c54a2.png\" alt=\"2266-39dd4f48-b9ac-431c-b70f-16b01e7c54a\" \/><\/p>\n<p>That&#8217;s all there is to grouping data. You might have noticed that the <strong>Applied<\/strong> <strong>Steps<\/strong> box includes all the original transformations from the <strong>Sales<\/strong> dataset. You can play with these if you like, or leave things just as they are. You could have also re-imported the table and grouped the data that way.<\/p>\n<h1>Retrieving data from the Web<\/h1>\n<p>Before we finish up with Query Editor, let&#8217;s add one more dataset. Suppose we want to create a visualization or two based on the <strong>CountryCode<\/strong> values in the <strong>SalesTerritory<\/strong> dataset, but want to display the actual country name. One approach we can take is to pull those names from someplace off the web and add it to our datasets.<\/p>\n<p>It turns out the Wikipedia has exactly what we need. Start by clicking the <strong>New<\/strong> <strong>Source<\/strong> button on the <strong>Home<\/strong> ribbon and then navigating to the <strong>Web<\/strong> connector type. (It&#8217;s part of the <strong>Other<\/strong> category.) When you click <strong>Web<\/strong>, the <strong>From<\/strong> <strong>Web<\/strong> dialog box appears, where you enter the following URL: https:\/\/en.wikipedia.org\/wiki\/ISO_3166-1.<\/p>\n<p>When the <strong>Navigator<\/strong> dialog box appears, you&#8217;re presented with a list of tables available on that webpage, as shown in the following figure. Select <strong>Officially<\/strong> <strong>assigned<\/strong> <strong>code<\/strong> <strong>elements<\/strong>, and then click <strong>OK<\/strong>.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-c7ea901a-ad1c-46a7-b82c-8e3fadc78991.png\" alt=\"2266-c7ea901a-ad1c-46a7-b82c-8e3fadc7899\" \/><\/p>\n<p>The new dataset opens in Query Editor. First, change the dataset name to <strong>CountryCodes<\/strong> and then apply the following transformations:<\/p>\n<ul>\n<li>Remove all but the <strong>English<\/strong> <strong>short<\/strong> <strong>name<\/strong> <strong>(upper\/lower<\/strong> <strong>case)<\/strong> column and the <strong>Alpha-2<\/strong> <strong>code<\/strong> column.<\/li>\n<li>Change the name of the <strong>English<\/strong> <strong>short<\/strong> <strong>name<\/strong> <strong>(upper\/lower<\/strong> <strong>case)<\/strong> column to <strong>CountryName<\/strong>. You can also change the name of the <strong>Alpha-2<\/strong> <strong>code<\/strong> column if you want, but I chose to leave it as it is for now.<\/li>\n<\/ul>\n<p>You should end up with a dataset that looks similar to the one shown in the following figure:<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-a07604f2-16f5-41e9-a3d9-a459c549e424.png\" alt=\"2266-a07604f2-16f5-41e9-a3d9-a459c549e42\" \/><\/p>\n<p>Notice that a <strong>Changed<\/strong> <strong>Type<\/strong> transformation has been added to the <strong>Applied<\/strong> <strong>Steps<\/strong> box. When Power BI Desktop imported the data from Wikipedia, it updated the column types to conform to the Power BI Desktop environment.<\/p>\n<p>That&#8217;s all we need to do in Query Editor for now. Close Query Editor and return to the main Power BI Desktop window. To get out of Query Editor, click the <strong>Close<\/strong> <strong>&amp;<\/strong> <strong>Load<\/strong> button on the <strong>Home<\/strong> ribbon. The datasets will now be available for viewing in Data view.<\/p>\n<h1>Adding a calculated column to a table<\/h1>\n<p>In Data view, you can review your datasets, using the <strong>Fields<\/strong> pane at the right to navigate through the datasets and their columns. You can also perform a few transformations, such as adding calculated columns or measures. Be aware, however, that elements you change in Data view are not reflected within the saved queries that you access through Query Editor.<\/p>\n<p>That said, let&#8217;s look at how to add a calculated column in Data view. Suppose we want to add a column to the <strong>SalesReps<\/strong> dataset that concatenates the first and last names, with the last name first. We&#8217;ll call the column <strong>Full<\/strong> <strong>Name<\/strong>. Start by clicking the <strong>New<\/strong> <strong>Column<\/strong> button on the <strong>Home<\/strong> or <strong>Modeling<\/strong> ribbon. This will add a blank column named <strong>Column<\/strong> and open a DAX formula bar at the top of the main window. To concatenate the two values, enter the following expression in the formula bar, replacing the current values:<\/p>\n<p>Full Name = SalesReps[LastName] &amp; &#8220;, &#8221; &amp; SalesReps[FirstName]<\/p>\n<p>Next, click the checkmark to the left of the formula. This will verify that the DAX expression, update the new column, and populate it with the correct data, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-d513ea08-c5d9-44fd-b61f-e96cccad8ab5.png\" alt=\"2266-d513ea08-c5d9-44fd-b61f-e96cccad8ab\" \/><\/p>\n<p>That&#8217;s all you need to do to add a calculated column. You can, of course, define far more complex DAX expressions than what we did here, but this should give you a sense of what you need to do and how easy it really is.<\/p>\n<h1>Adding measures to a table<\/h1>\n<p>It&#8217;s just as easy to add a measure to a dataset. A measure lets you aggregate data across different and changing dimensions. The aggregation can be based on one or more columns and applied to your visualizations to provide summarized views of the data.<\/p>\n<p>For our example, we&#8217;re going to add three measures to the <strong>Sales<\/strong> dataset. To create a measure, click the <strong>New<\/strong> <strong>Measure<\/strong> button on the <strong>Home<\/strong> or <strong>Modeling<\/strong> ribbon and then define the DAX formula. The first measure calculates the sum of the <strong>SubTotal<\/strong> values. Name the measure <strong>Total<\/strong> <strong>Sales<\/strong> and add the following DAX expression to the formula bar:<\/p>\n<pre>Total Sales = SUM(Sales[SubTotal])<\/pre>\n<p>The next measure, named <strong>Average<\/strong>, uses the following expression to determine the average number of sales:<\/p>\n<pre>Average = AVERAGE(Sales[SubTotal])<\/pre>\n<p>Finally, we&#8217;ll add a measure that counts the number of sales, which we&#8217;ll name <strong>Count<\/strong>. Use the following expression for this measure:<\/p>\n<pre>Count = COUNT(Sales[SalesOrderID])<\/pre>\n<p>After you&#8217;ve added the three measures, your <strong>Sales<\/strong> dataset should now look similar to the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-72b519dc-0e95-4c3a-b6e9-61f9864ffbc9.png\" alt=\"2266-72b519dc-0e95-4c3a-b6e9-61f9864ffbc\" \/><\/p>\n<p>Just like calculated columns, the DAX expressions you use to create measures can be far more complex than the ones we&#8217;ve added here. The more familiar you are with DAX, the more varied the type of measures you can create.<\/p>\n<h1>Managing relationships between data<\/h1>\n<p>Before you start creating your visualizations, you need to take one more step: define the relationships between our datasets. When you first set up your datasets, Power BI Desktop attempts to identify relationships that are already apparent. For example, if you go to the Relationships view, you should see all your datasets, with a relationship defined between the <strong>Sales<\/strong> and <strong>SalesTerritory<\/strong> datasets, based on the <strong>TerritoryID<\/strong> column in each dataset, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-ffeb9e1e-ade7-49d9-8dab-f56019112ff5.png\" alt=\"2266-ffeb9e1e-ade7-49d9-8dab-f56019112ff\" \/><\/p>\n<p>You can view details about the relationship by clicking the <strong>Manage<\/strong> <strong>Relationships<\/strong> button on the <strong>Home<\/strong> ribbon. When the <strong>Manage<\/strong> <strong>Relationships<\/strong> dialog box appears, select the relationship if not selected, and then click <strong>Edit<\/strong>. The <strong>Edit<\/strong> <strong>Relationship<\/strong> box appears, providing details about the relationship, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-19de53ac-ba90-4653-ba3b-85e7518dc4db.png\" alt=\"2266-19de53ac-ba90-4653-ba3b-85e7518dc4d\" \/><\/p>\n<p>As you can see, the <strong>TerritoryID<\/strong> column in <strong>SalesTerritory<\/strong> is related to the <strong>TerritoryID<\/strong> column in <strong>Sales<\/strong>, defined as a one-to-many relationship between the two. If necessary, you can edit the relationship, although for one as straightforward as this one, it likely will not be needed.<\/p>\n<p>If you return to the <strong>Manage<\/strong> <strong>Relationships<\/strong> dialog box, you&#8217;ll also find the <strong>AutoDetect<\/strong> button, which allows you to find other potential relationships. This feature will sometimes detect relationships that were not detected initially or find relationships after datasets have changed or new ones added. <\/p>\n<p>If after all this undetected relationships still exist, you can manually define them by clicking <strong>Add<\/strong> in the <strong>Manage<\/strong> <strong>Relationships<\/strong> dialog box. When the <strong>Create<\/strong> <strong>Relationship<\/strong> dialog box appears, you can select the tables and columns on which to base the relationship. For example, we can define a relationship between the <strong>BusinessEntityID<\/strong> column in the <strong>SalesReps<\/strong> dataset and the <strong>SalesPersonID<\/strong> column in the <strong>Sales<\/strong> dataset, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-020396f3-e824-4394-9861-6d86e1005cdf.png\" alt=\"2266-020396f3-e824-4394-9861-6d86e1005cd\" \/><\/p>\n<p>In this case, we&#8217;re defining a one-to-many relationship, from <strong>SalesReps<\/strong> to <strong>Sales<\/strong>. A relationship should also exist between the <strong>TerritoryID<\/strong> column in the <strong>SalesTerritory<\/strong> dataset and the <strong>Alpha-2<\/strong> <strong>code<\/strong> column in the <strong>CountryCodes<\/strong> dataset (one-to-many from <strong>CountryCodes<\/strong> to <strong>SalesTerritory<\/strong>). The following figure shows the Relationships view with all the necessary relationships defined.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-413c501a-2643-4c30-9e5f-a35322e87db8.png\" alt=\"2266-413c501a-2643-4c30-9e5f-a35322e87db\" \/><\/p>\n<p>Having clearly defined relationships lets you use data from different datasets in a single visualization. Once you&#8217;ve defined your relationships, you&#8217;re ready to start building your reports. Note that you don&#8217;t have to have all your datasets and relationships completely defined before you start adding visualizations. You can always go back and tweak things. However, making changes after the fact can sometimes impact your visualizations in strange ways. But as you&#8217;ll see, creating visualizations so easy that having to redo a little work is no big deal.<\/p>\n<h1>Building reports<\/h1>\n<p>The report building capabilities in Power BI are fairly extensive and could easily justify an article or two of their own, but we can at least look at a few of them to help you get started.<\/p>\n<p>When you first go to Report view, you&#8217;re provided with an empty slate (the report designer), as shown in the following figure. Here you can add visualizations, using the datasets you defined earlier, which are displayed in the <strong>Fields<\/strong> pane to the right. Just to the left of that pane is the <strong>Visualizations<\/strong> pane, which gives you the tools you need to add and configure your visualizations.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-d226344b-bbf8-4435-80bb-1489211850a0.png\" alt=\"2266-d226344b-bbf8-4435-80bb-1489211850a\" \/><\/p>\n<p>Let&#8217;s start by adding a simple table to our report that shows the full name of each sales rep, along with their sales figures (totals, averages, and number of sales). First, in the <strong>Fields<\/strong> pane, click the <strong>Full<\/strong> <strong>Name<\/strong> column in the <strong>SalesReps<\/strong> dataset. This will add the initial table to the report, with a column showing the full names. (The Table visualization is the default visualization type.)<\/p>\n<p>Next, we add the measures we created in the <strong>Sales<\/strong> dataset, starting with <strong>Total<\/strong> <strong>Sales<\/strong>, then <strong>Average<\/strong>, and finally <strong>Count<\/strong>. Once again, you need only select the checkboxes associated with the three measures. Just be sure to check them in the order specified, or in the order you want them displayed in the visualization. <\/p>\n<p>That&#8217;s all you need to do to create a Table visualization. The figure below shows what the table looks like and the fields selected to create it. You can resize or move the table, add or remove columns, or create filters on the individual columns.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-2118f628-cf30-48a4-a1c4-eace26df013c.png\" alt=\"2266-2118f628-cf30-48a4-a1c4-eace26df013\" \/><\/p>\n<p>If you want to display the data in a different visualization, simply click one of the visualization buttons in the top section of the <strong>Visualizations<\/strong> pane. In fact, try out a number of the visualizations. This can be a good way to get a feel of how the different visualizations work and what type of data works well in different situations.<\/p>\n<p>Now let&#8217;s add a Donut Chart visualization to the report (the bottom right visualization button). To add the visualization, first make sure the table is not selected, and then click the <strong>Donut<\/strong> <strong>Chart<\/strong> button. A gray version of the chart is added to the report. Next, select the <strong>CountryName<\/strong> column in the <strong>CountryCodes<\/strong> dataset, and then select the <strong>Average<\/strong> measure in the <strong>Sales<\/strong> dataset. You should now have a visualization that looks like the one in the following figure. <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-2effb36c-7dc7-4340-acee-278669d5180c.png\" alt=\"2266-2effb36c-7dc7-4340-acee-278669d5180\" \/><\/p>\n<p> <img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-f87bea66-8e13-4b7a-b641-6ade5daa6cab.png\" height=\"513\" width=\"317\" class=\"float-right\" alt=\"2266-f87bea66-8e13-4b7a-b641-6ade5daa6ca\" \/><\/p>\n<p>As with the table (or any visualization), you can resize and move it as necessary. The fun part of these types of visualizations, when used in conjunction with a table, is that you can select a section of the chart, such as <strong>France<\/strong>, and the data in the table will be updated to display sales amounts specific to that country <\/p>\n<p>You can also modify the appearance of the visualizations. The degree to which you can make changes depends on the visualization type. To modify format settings, go to the <strong>Format<\/strong> tab in the <strong>Visualizations<\/strong> pane by clicking the <strong>Format<\/strong> button just below the list of visualization buttons. (It looks like a paintbrush.) On the <strong>Format<\/strong> tab for the Donut Chart visualization, you can change settings related to such elements as the data and category labels or data colors, as shown in the figure to the right.<\/p>\n<p>For this example, I changed the title to read &#8220;Average Sales by County,&#8221; set the font color to black, and centered the label, but you can change the formatting however you like. Experiment with the options available to the various visualizations to see what you like. Whenever you make a change here, it is reflected immediately in the visualization.<\/p>\n<p>Now let&#8217;s add a Map visualization to the report. Once again, make sure that no visualization is selected, and then click the <strong>Map<\/strong> button in the <strong>Visualizations<\/strong> pane. For the Map visualization, select the <strong>CountryCode<\/strong> column from the <strong>SalesTerritory<\/strong> dataset, the <strong>Total<\/strong> <strong>Sales<\/strong> measure from the <strong>Sales<\/strong> dataset, and the <strong>CountryName<\/strong> column from the <strong>CountryCodes<\/strong> dataset. You can then configure the title or apply any other formatting that&#8217;s available to the visualization. When you&#8217;re finished, your visualization should look similar to the one shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-2187c831-0a95-4a62-a58c-95f688602a76.png\" alt=\"2266-2187c831-0a95-4a62-a58c-95f688602a7\" \/><\/p>\n<p>Finally, let&#8217;s add a Line and Clustered Column Chart visualization. For this, you need include only the <strong>OrderYear<\/strong> column and <strong>Year<\/strong><strong>ly<\/strong><strong>Sales<\/strong> measure from the <strong>AnnualSales<\/strong> dataset, giving you a visualization that looks similar to the one shown in the following figure. Again, you can format the visualization however you like, based on the formatting options available to that visualization.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-f6102ea8-34c7-4796-afbd-c53ae3f349a4.png\" alt=\"2266-f6102ea8-34c7-4796-afbd-c53ae3f349a\" \/><\/p>\n<p>When you&#8217;re finished, you should have a report that contains the same elements as those shown in the following figure. Be sure to save your Power BI Desktop file often to preserve all your fine work. When naming the file, keep in mind that this will be the name used if you publish the report and datasets to the Power BI site.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-742a21b0-f889-4025-8843-37b67c038af0.png\" alt=\"2266-742a21b0-f889-4025-8843-37b67c038af\" \/><\/p>\n<p>As you can see, adding visualizations to a report is relatively painless, once you have your datasets in place. And you can change the visualizations, resize and move them around, or add more pages for additional visualizations. You can also add text boxes or images.<\/p>\n<h1>Publishing reports to Power Bi<\/h1>\n<p>Once you&#8217;ve set up your reports and datasets the way you want them, you can publish them to the Power BI site. To do so, click the <strong>Publish<\/strong> button on the <strong>Home<\/strong> ribbon. If you&#8217;re already signed into Power BI, that&#8217;s all you need to do. Otherwise, you will also need to provide your Power BI credentials.<\/p>\n<p>On the Power BI site, your report will be added to the <strong>Reports<\/strong> section in the left pane, and your dataset will appear in the <strong>Datasets<\/strong> section. Each one will be named whatever you named your Power BI Desktop file. From there, you can work with the reports and datasets just like you can with those created on the Power BI site.<\/p>\n<p>Another task you can perform in the BI site is to pin your report&#8217;s visualizations to a dashboard. For example, suppose you&#8217;ve created a dashboard named <strong>Adventure<\/strong> <strong>Works<\/strong> <strong>Sales<\/strong> and you want to add our four visualizations to the report. To do so, simply click the <strong>Pin<\/strong> <strong>Visual<\/strong> icon at the top right of the visualization and it will be added to the dashboard, as shown in the following figure.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-a3723fac-9e01-4b3f-813e-3e0cff4dab63.png\" alt=\"2266-a3723fac-9e01-4b3f-813e-3e0cff4dab6\" \/><\/p>\n<p>When working with visualizations on a dashboard, you&#8217;re limited to containers that are of a fixed size, but you can move the visualizations around and size them to fit the containers. As you can see in the figure, I&#8217;ve used three different sized containers. Notice too that I added a couple visualizations from another report (a sample available to Power BI).<\/p>\n<p>There&#8217;s more you can do in Power BI, of course, but that will have to wait for a different article. In the meantime, another fun feature in Power BI is the ability to access your Power BI reports and dashboards from a mobile device. For example, the following figure shows the dashboard as it looks on an iPad.<\/p>\n<p> <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-29fdedf8-bffc-4910-b3cb-dc343765eb85.png\" alt=\"2266-29fdedf8-bffc-4910-b3cb-dc343765eb8\" \/><\/p>\n<p>Like the reports in Power BI Desktop, you can interact with the visualizations. For example, if you touch the Donut Chart visualization, it pops out and lets you spin the circle to so you can view the data about a specific country, as shown in the following figure. <img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/2266-a8011bbb-7d97-4f64-867c-adc3aceb13f0.png\" alt=\"2266-a8011bbb-7d97-4f64-867c-adc3aceb13f\" \/><\/p>\n<p>You can perform a number of other tasks as well, such as view the reports or specify certain visualizations as favorites. Microsoft provides apps for iOS, Android, and Windows mobile devices. The best part about the Power BI platform, which includes the mobile apps, Power BI Desktop, and Power BI site, is that a basic account is free and you have full access to everything we&#8217;ve covered here. And if you opt for a Power BI Pro account at $9.99 per month, you have more options for consuming data and integrating your reports.<\/p>\n<h1>Plenty more where they came from<\/h1>\n<p>We&#8217;ve only skimmed the surface of the many things you can do with Power BI and Power BI Desktop, especially when it comes to transforming data. But what we covered should give you a sense of the basic workflow and how easy it is to get started. <\/p>\n<p>The ability to use Power BI Desktop to connect to an on-premises instance of SQL Server makes the platform all the more attractive. You can create SQL Server datasets and reports locally, publish them to the Power BI site, and share the site with other users, who can then create reports of their own based on the data. Power BI and Power BI Desktop also provide other options for sharing. Indeed, as a tool for providing users with self-service BI, the Power BI platform has much to offer.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>What&#8217;s the best way of providing self-service business intelligence (BI) to data that is held in on-premise SQL Server? Not, it seems, Power BI 2.0 the hosted cloud service,  but Power BI 2.0 Desktop. If moving your database to Azure isn&#8217;t an option, Power BI 2.0 desktop could still bring smiles to the faces of your BI hotshots.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[5364,4178,5336,4168,4379,4150,4151],"coauthors":[],"class_list":["post-2073","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-azure","tag-bi","tag-cloud","tag-database","tag-reporting-services","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2073","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2073"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2073\/revisions"}],"predecessor-version":[{"id":91006,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2073\/revisions\/91006"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2073"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2073"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2073"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2073"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}