This week I’ll show you how to use Power BI to pull together data from multiple data sources that you can then use to create visualizations.
The first step is to open your copy of Power BI Desktop. Then click on the Get Data button. I’m going to begin with some data from SQL Server so I do not have to click the More… button to see all the available data sources, SQL Server is right on the main dropdown.
To work with a SQL Server database, I must first enter the name of the SQL Server that I want to connect to. Note that while the dialog shown below prompts for the name of the database, this information is optional. You might think, ‘How can the database name be optional?’ In a moment, I will show you that Power Bi first needs to connect to the server, but then it can retrieve the database names that you have access to and display them for you to select. This protects you from the possible misspelling of the database name in this dialog. Since I am running everything on a single machine, my SQL Server name is LocalHost.
Of course before you can connect to the server, you must provide credentials. I am running everything on a Surface Pro 3 so I will use my Windows credentials. However, if you need to connect to another server on your network, you may need separate credentials.
The following dialog may or may not appear depending on the database you are connecting to and your version of SQL Server. However, in most cases, clicking the OK button allows you to connect to your data as long as you have rights to the SQL Server box.
When Power BI connects to the server, it first begins with a list of the possible databases along the left side of the screen. By opening a database (clicking on the arrow to the left of the database symbol, I can see the tables in the database. I can select multiple tables from this database. I can even select multiple tables from multiple databases. With each table I select, I will see a sample of the data in the right panel. Note that unlike PowerPivot, there is no way to limit the columns or rows from the table at this point. Maybe in the next major release.
You’ll notice that this week I return to my favorite sample dataset, Contoso.
If I select multiple tables to load, they all appear in the following dialog that shows me the progress of loading my data. In the image below, I can see that I’ve selected five tables from the database. As the data load progresses, Power BI shows me the number of records loaded in the line beneath the table name.
After the data loads, I notice that the table names appears in the Fields column on the far right of the desktop. But you say that these are table names? If I click the arrow to the left of each table name, the table expands to show the names of the fields essentially grouped by table.
Next I can click on one of the icons on the left side of the desktop. The first icon is the report surface. I typically work there after I have loaded all my data and made any changes to the columns and/or rows. So some might think that this icon should be on the bottom. The second icon is the data icon. I can use this icon to edit the column definitions, add new columns, or remove columns that I no long need. I showed some of these techniques in prior weeks of this blog.
However, I usually start with the bottom icon, Relationships. Relationships are the heart of any multi-table analysis. If I do not define the relationships between the tables first, I really cannot do any analysis. I might not be able to create some column either. So let’s start there.
The diagram that Power BI creates shows the tables that have been loaded into the model and if relationships were defined in the source data, it tries to create those relationships for me automatically. Notice that the diagram clearly identifies the one side of the relations with a ‘1’ and the many side of the relationship with an asterisk.
Unlike the diagram view in Power BI, there is no ability to edit, delete, or create new relationships from this dialog. Nor can I delete columns, define hierarchies, or rename columns here. I can hide individual columns or entire tables from the report view of the model and I can maximize the view of the individual table diagram, but that’s it. Again, maybe in the next major release of Power BI these additional features will appear so that the functionality matches that of PowerPivot diagram views.
Next I’m going to load some additional data into my model beginning with some data from an Access database and then data from Excel. I’m not going to take the space here to show screen images of these processes because between the above description and prior week blog discussions, I believe that you can figure out how to load data from different data sources. The only problem you may encounter is the need for new or updated connectivity tools to access your other data sources.
Once I have loaded all my data, my first action is to go to the Relationships view. You can see in the following figure that Power BI automatically shows my additional tables that I’ve loaded, but they show no connection to the rest of the tables in the data model. I need to fix this before going forward.
Find the Manage Relationships button in the Relationships group in the ribbon and click on it.
A dialog appears that displays the current relationships between the tables. This view is similar to the manage relationships dialog in Power Pivot. I can add, edit and even delete relationships here. However, the interesting button is the one labelled Autodetect…
When I click Autodetect, Power BI attempts to find and create relationships between the tables that are not currently connected to the data model. It does this by analyzing the field names in the new tables with names in the tables of the data model to find other fields with the same name and the same data type definition. Of course, one table must serve as the parent (one-side) and the other table must serve as the child (many-side). In most cases, you will start with a fact table and create branching child tables so they usually serve as the one side in each of the new relationships. If Power IB is successful, it will attempt to create that relationship and will first tell you in a dialog box such as the one shown below how many relationships it detected.
After closing the information dialog reporting the number of relationships found, the relationships are created and the results for this model appear in the updated Manage Relationships dialog shown here.
Note at this point, I could edit any of the relationships that were not correctly created, I could also add relationships between tables that perhaps had different field names that could not be automatically detected. It is a good practice to check the Relationships diagram to insure that every table is connected to the model through at least one relationship.
I am now ready to go to the Reports desktop to create some simple charts.
Creating a report is as simple. First click in a blank area of the Reports desktop. Then click in the checkbox to the left of the value field you want to count, sum, or average such as Sales Amount. Then click on the field or fields by which you want to report Sales Amount (or any other value field). For example, I might want to see sales by year or by Product category or by Channel. You can create as many visualization as you want on the desktop although you should keep in mind that they need to be large enough to be readable. Let’s start with the three shown below.
Not a bad start. Here is a fun feature that Power BI supports that Power Chart does not. Just click on the Year 2009 column in the chart in the upper left. Immediately, all charts on the page change to shows the sales related to the year 2009 in a darker color and sales from other years in a light color.
Note that I did not have to do anything special to get this to work. I did not have to create a filter, a slicer, or anything. Similarly, I could click on columns in other charts to slice and dice the data different ways.
Filtering the data on the page is actually done entirely differently. To filter the data that appears on the page, you have to define a page filter by dragging the field or fields that you want to filter by into the area beneath Page Level Filters found in the column to the immediate left of the column with all the table fields. Filter fields typically are string values and typically, there are not a large number of unique value. To filter the page, just click in the checkbox to the left of each value that you want to include on the page. You can filter on one or many values at a single time.
After selecting a page level filter, the visualizations on the page change automatically to represent only the filtered data rather the entire dataset. The following desktop image represents only the data from the year 2009.
Well, that’s all for this week, but if you have been following along, save your data from this week because I’ll pick up this sample data from this point next time.