Power BI Phase 1: Defining The Data Relationship Video Tutorial
NOTE: This is a continuation of the blog “Connect the Dots: Why Dashboarding Your Data Can Tell a Compelling Story”. If you haven’t had a chance to read that, it might be useful to take a look, as I’ll be building off the elements we discussed in that piece.
There is a video tutorial at the end of this blog that shows how to upload data and connect it via relationships.
The message is set, you have your metrics in mind, and you’ve identified your audience. You’re ready.
Now: let’s engage with Microsoft Power BI, and start creating your dashboard. The first step is connecting your data. From the previous article, you should have identified your data reports. (Pro tip: the best datasets for beginners are the raw data reports or SharePoint lists. These have very simple connections, in my opinion, and require the least amount of data modeling when uploaded.) Man-made reports—designed to be visually appealing—may not be the best, as those reports will need to be transformed into raw, column-by-column data. While we might normally consider this data format a visual eyesore, this raw data is easier for Power BI to digest.
TYPES OF DATA: Look-up and Source
There are two types of data Power BI needs to work effectively. You upload these two types of data and develop a relationship between them (we’ll get into the details below), and then there is virtually no combination that Power BI cannot link.
Here are some of the differentiations between the two types of data:
These are lists that give Power BI the answer to the following questions:
- Who? Clients, businesses, etc.
- What? List of products and their costs, computers in circulation, for example
- Where? Territories, store locations
- When? This gives dates to correlate (this is just a date table, we’ll show you how to create this).
- How? Any extra items that could catalog data, like a list of different cubicles in your building.
These datasets contain the metrics that will help tell your story. You should have found this data from the evaluation explained during our last post on dashboarding your data.
The look-up data is very important for Power BI. It helps catalog the source data, so when you throw two reports together in a bar graph, it can easily define the relationship. For example, what if you have one report that uses abbreviations for companies, instead of their full names? What if you want this to correlate with another report using the full business name, but not the abbreviation? For instance, imagine that SUCCESS is labeled in some reports as “SCCNET”. Power BI does not have your workers’ years of historical knowledge, and can’t make that correlation. It’d be like being dropped into a foreign country without any idea of the language and being asked to translate a conversation.
However, if Power BI has a list it can reference with all the company names (SUCCESS) and their abbreviations (SCCNET), it will able to connect the two reports by name. It also enhances the capabilities of reports, and the date table will allow you to take the data and reformat it based on the date. Instead of the bar graph showing the data for each specific date, you can organize it in quarters, for instance, or compare the data based on fiscal year instead of calendar year. It also allows you to apply unique labels, such as having the month only use three letters or label by “Q1 2009”. If you don’t give Power BI that date table, you’ll find yourself putting in more work for those extra labels.
In this and subsequent posts on Power BI and the importance of data dashboarding, I’ll be using a fictional example to help walk you through the steps to creating the dashboard/report. This example will not match your data specifically, but it should be enough to get you started.
The scenario: a company called “Al’s Produce” has multiple vendors supplying them with produce. The demand seems to be raising for all produce, but most specifically for apples and bananas. Lisa, the store’s manager, has asked the eponymous owner Al and the vendor relationship managers to increase produce deliveries to meet the demand. In return, Al and the executive staff requested data that they can bring to these vendors, to combat pricing. Lisa has begun evaluating her story (demand is on the rise for bananas and apples) and intent (increase in vendor deliveries) for her presentation. Her decision is to use Power BI as the vehicle for telling this data’s story.
Message: The past three months have seen an increase in produce sold to customers.
Intent: Convince owner, executive staff of the need for more produce shipped, specifically apple and bananas.
Data to be used:
Metrics: Amount of sales per item, with dates for the past three months
Look-Up Data: List of products and vendor pricing, including the name of vendors
To go through this I have created a video that will walk you through uploading the data and connecting it via relationships: