Microsoft Excel is great for analyzing large datasets from the Enterprise Data Warehouse (EDW) on the fly (read article: Creating Excel reports using tabular models), but the gold standard for reporting is Power BI, especially when it comes to accessibility and visualization. So let's walk through how to use one of our tabular models in the EDW to build a Power BI report.
After opening Power BI Desktop on your machine, click "Get Data" from the Home tab and select "Analysis Services" from the list of options.
A dialog will appear asking you to enter the server name and database name (optional), as well as select the type of connection you want. Type "BI1" for your server, select "Connect live" for your connection type, then click "OK."
A dialog box named "Navigator" will appear asking you to select which data model you want to connect to. In this case, we'll connect to the Admissions Model.
Once the connection is established, you'll see the Fields pane on the right side of your screen update with the full list of tables that are available in the tabular model.
Let's start with a simple task: let's create a table report that we can export into a CSV file. To accomplish this, simply go to the Visualizations pane on the right side of your screen and find the "Table" visualization, which just looks like a 3x3 grid with a header row. Click on the icon to insert the table visualization onto your report canvas.
Success! We're now ready to add some data to our table visualization.
Now, suppose our ultimate objective is to create a report of all applicants and the academic disciplines they say they want to study if they gain admission to Davidson. To accomplish this, we just need to locate the appropriate fields inside our tabular model and add them to the table visualization we just created.
Using the Fields pane, you may browse the collection of objects in the tabular model and pick the fields you want to put into your table. To add a field to your table, first you need to make sure that you have selected the table in your report canvas (if it is selected, you'll see a border and brackets around it). Then, you can either add a field by clicking on the checkbox next to its name, or you can drag-and-drop it into the Values section of your visualization:
As you add fields from different tables, Power BI will detect what kind of relationship these tables have to each other, and will merge the data accordingly. For example, if you start by adding the Person > First Name field, you'll get every first name in the EDW. However, as soon as you add a field from the Applicant table, Power BI will filter the first names down to just those people who are applicants.
Add these fields to your table using the methods described above:
- Applicant >> Applicant_ID
- Person >> First Name
- Applicant >> Admission Cycle
- Applicant >> Academic Interest 1
- Applicant >> Academic Interest 2
- Applicant >> Academic Interest 3
You should now have a table in the report canvas that displays all these fields, and you can scroll down through it to look at all the applicants. There are tens of thousands of applicants to examine, though, so let's make it easier on ourselves by adding a common Power BI tool called a "slicer" to help us filter the data in our table.
T&I Tip: Microsoft Excel also has slicers that you can use to filter your PivotTables and PivotCharts. Read more: Creating Excel reports using tabular models |
To add a slicer to your Power BI report, go to the Visualizations pane once more and select the "Slicer" icon:
This will add a blank slicer to your report canvas. Select the blank slicer, then drag-and-drop the "Admission Cycle - Round" field from the Applicant table into your slicer's Field box (example below).
Now you should have a list of admission rounds displayed in the Slicer box, and each one will have a checkbox next to it. To filter the data in your table, simply click on the checkboxes within the Round slicer. (You may have to Ctrl+Click to select multiple rounds -- otherwise, this can be adjusted in the slicer settings.)
Suppose at this point we have the data we were looking for: a list of applicants for the 2018 Early Decision and Regular Decisions rounds, along with their academic interests. If we want to export the data into a CSV file so we can look at it in Excel or import it into another system, all we need to do is go to the table's "other options" menu. If you hover over the table, you should see an ellipsis icon (...) appear in one of the corners. Clicking on the icon will expose the menu, and you can click on the "Export data" option. This will let you save the data into a CSV (comma-separated values) file on your hard drive.