q

Project3_MicorsoftBI.pdf

I. Title: Microsoft Power BI Training

II. Introduction: Woohoo! You had a great time working with your team on the FLWC project with your colleagues at Levin & Associates, LLP and Mr. Lee couldn’t have been happier with the client deliverables. He was so pleased that he called you in to his office this morning because he wants to attend a Microsoft Power BI Training that the company will be paying for. You are very happy to hear this since it won’t cost you any money and the additional skill will look good on your resume.

As soon as you leave Mr. Lee’s office your hurry to your desk to start exploring this new and exciting software package.

III. Steps to Completion

1. Go to Appendix A to get an introduction to Microsoft Power BI.

2. Go to Appendix B – Creating Card Dashboards

3. Go to Appendix C – Creating Table Dashboards

4. Go to Appendix D – Creating Stacked Bar Chart Dashboards

5. Go to Appendix E – Creating Slicer Dashboards

6. Go to Appendix F – Creating Map Chart Dashboards

IV. Deliverables

1. A Word document that includes screenshots of all the following dashboards: Cards, Tables, Stacked bar charts, Slicers, and Map charts.

2. A glossary of 15 relevant terms with definitions written in your own words. For example: BI is an abbreviation for Business Intelligence, which can be defined as a set of tools, technologies, and techniques that when used together can transform data into useful information for analyzing various business opportunities and/or problems that result in better decision making.

3. One advanced Power BI quiz question with your answer. 4. A list of 4 items you would add to item V. Hints and Tips.

V. Hints and Tips

▪ Read the entire project and review the grading rubric before beginning the project to fully understand the requirements.

▪ This is an individual graded project. All work must be completed by you without the assistance of anyone other than your professor.

▪ Ask questions about the project requirements as needed.

▪ Submit the deliverables to your Assignment folder on or before the due date. ▪ Review the late policy in your syllabus; it will be enforced.

VI. Rubric Before beginning this project, review the rubric to learn how the project will be graded.

Rubrics can be found in Content/Course Resources/Projects & Rubrics/Project 3 Rubric

Your deliverables will be assessed on the following competency:

• Technology and tools

Appendix A – Introduction to Microsoft Power BI

Microsoft Power BI – Part 1 Visualization For this training, we will be building the following dashboards using Microsoft Power BI:

1. Cards 2. Tables 3. Stacked Bar Charts 4. Slicers 5. Map charts

Please take screenshots of the above dashboards when you complete each one. These will be used as your deliverable to show you completed all the training. Creating powerful interactive dashboards is the fun part of big data. Let’s start by building a few amazing dashboards using a small data set from the KaDo case. We will do this in three steps:

1) First, you need to understand the data set. 2) Next, you need to learn about the tools you have available to define

visualizations and dashboards—i.e., the Report workspace. 3) Finally, you will build a series of dashboards using the following five

visualizations: cards, tables, stacked bar charts, slicers, and map charts. We will use the ABILITY.PBIX data set for this exercise, which you can find in the classroom under Course Resources/Projects & Rubrics/Project 3. I have organized and enriched this data set for you. You will use the data set to build dashboards only. In-depth, hands-on exercises for the following five visualizations: 1) cards, 2) tables, 3) stacked bar charts, 4) slicers, and 5) maps. For each visualization, you will learn how to specify the visualization before adding data to and formatting the visualization. A problem many companies face is knowing who (i.e., which vendors) can deliver the products they need. Products can be raw materials in the case of manufacturers, finished goods in the case of retailers, and so on. A couple of questions: “From whom (which vendor) can I buy a computer (item)?” or “Who (which vendor/airline) can fly me from New York to Seattle (item/service)?” Power BI makes it easy to look at data and data structures, as is illustrated in figure 4.2–1 below. Select Data view in the left panel. Then click on any of the tables in the Fields panel. As shown, if you click on the “Item Type” table its content will be shown in the Data panel in the middle section. Also, if you click on the arrow preceding a table name in the Fields panel, the table’s specific fields will be shown.

The figure above shows the structure of the ItemType table. The ItemType table describes the types of products (dolls) KaDo buys and sells. Information recorded about item types includes a unique identifier (code), name, and description. As mentioned above, “Number of item types” is a measure that determines how many different products there are. First, you must understand Power BI’s report workspace (i.e., your toolbox):

1) what a canvas is, 2) what a visualization is, 3) how to link data to a visualization, and 4) how to format a visualization.

We will briefly discuss the different parts of the report workspace below. Second, dashboards are constructed from visualizations, so it is important that you understand what the purpose of the different visualizations is (i.e., their functionality) and how to implement them.

To learn more about good dashboard design, go to https://docs.microsoft.com/en- us/power-bi/power-bi-visualization-best-practices.

https://docs.microsoft.com/en-us/power-bi/power-bi-visualization-best-practices

Appendix B – Creating Card Dashboards

The Report Workspace 1) Click on the Report view (#1 in figure above) button to open the report workspace. This is where you will develop your dashboards. 2) Report view has its own main menu and a specific tab. The #2 in the figure above shows the ribbon that goes with this tab. Among other things, it enables you to define a grid.

3) The canvas (#3) is the space in which you will design your dashboards. A dashboard consists of one or more visualizations that can interact with one another. You will develop a few interactive dashboards later in this chapter. 4) The same project can have multiple dashboards or pages; each page gets its own tab (#4). Tabs can be duplicated, renamed, or deleted. Each visualization has its own unique way of presenting data. 5) The Visualization panel (#5) shows the different visualizations you can currently choose from. 6) The Fields panel (#6) is identical to the panels shown in the Data view. In this case, the Fields panel is used to select the data that will become part of the dashboard.

7) With the field tab (#7) we define how the data selected in the Fields panel should be used as part of a visualization. Data can be filtered or aggregated, among other things. The nature of the definitions will vary across the different visualizations. 8) In the formatting tab (#8), you define how the data in the dashboard should look. Characteristics that can be defined include text size, background color, and borders, among other traits. Formatting options will vary across the different visualizations.

Next, we are going to create a few actual dashboards. Make sure to have the ABILITY.PBIX file open at this time. First, rename the Page 1 tab at the bottom of your canvas as “Cards.” Next, click on the card icon in the visualization panel.

An empty card will appear on the canvas. Make sure that the visualization is active. The selection of fields and formatting will apply to active visualizations only.

Cards are used to show a single number. We will create a dashboard with two cards that contain the following numbers: “number of item types” and “number of artists.” • Number of item types: the number of different products offered (assortment size).

• Number of artists: the number of different vendors (artists) from whom the products can be purchased.

We will start with the first card, which shows the number of item types (i.e., products) KaDo offers. Go to the Fields panel (see figure 4.4–3 below) and click on the box in front of the field for which you want the value to show up in the card. For this exercise, go to the Fields panel and click on the box in front of the “NUMBER OF ITEM TYPES” field in the item type table. Important: Make sure that the card on the canvas is active. You can only define the content of and/or format visualizations that are active.

Congratulations! You have just created your first card, visualization, and dashboard. Your canvas should now look as follows:

The content of a visualization is shaped in the Fields tab, as is illustrated in figure 4.4–5 below. When you select a field from the Fields panel (right side in figure 4.4–5), the field is automatically added to the list of fields in the Fields tab (middle of figure 4.4–5). The content can then be further shaped in the Fields tab; for example, filters could be defined. We are not making any further changes at this time, but we will discuss more complex content definitions later in this chapter. The content in the canvas (left side of figure 4.4–5) is the result of the definitions in the Fields tab. Each type of visualization has its own content definition settings.

Next, you will learn how to format cards, which we will do in a separate tab. Right-click on the Cards tab at the bottom of the canvas and choose “Duplicate page.” Then rename the new tab “Cards formatted.” Make sure your “number of item types” is active on the canvas and click on the “Format tab” in the Visualizations tab.

The formatting menu has many items. For most of these items, you first need to decide whether to activate the item by moving the slider from Off to On. Once activated, you can specify further details for most of the items. Our goal is to format the two cards as follows:

Step 1: Delete the “category label” and resize the cards. Cards use the associated field name as their default category label. To delete the label, make sure that the card is active and change the “Category” label from On to Off.

Next, resize the card using the handles and change the text size of the data label to 16. Use the “Text size” option under “Data label” and use this to decrease the size to 16.

Next, turn the background on and change the color to pink.

Finally, add a border to the card.

Step 2: Add a text box to the dashboard Power BI makes it easy to add text boxes, images (such as logos), rectangles, and other items to a dashboard.

Next, we will illustrate how to add text boxes to our dashboard. The text boxes will be used to describe what the numbers in the cards mean. Text boxes are visualizations themselves and can therefore be formatted. Create a text box. Click on the “Text box” icon shown in figure 4.4–13, and a text box will appear in your canvas:

Enter text, center it, and change the font;

Activate the background and change the color.

Add a border to the text box.

This is your result:

Make sure you take screenshots of your work and add them to your Word document.

Appendix C – Creating Table Dashboards How to Create a Table A table is a grid with rows and columns, which is especially useful for comparing the values of one or more categories. First, create a new page (tabs), name it Table Next, click on the Table icon in the Visualizations panel.

An empty table will appear on the canvas. Make sure the visualization is active. The selection of fields and formatting will always apply to active visualizations.

Tables can be used in many different ways—for example, to show detailed transaction records such as sales. Also, tables are often used to show summaries, such as the total revenue ($) generated for a product type, per division, etc. The example below shows this second use of tables; the table shows how many different item types (product types) an artist (vendor) is able to deliver. Click on the checkboxes in the Fields panel to determine which data should be included in the table. In the figure below two fields are selected: “Name” from the Artist table and Number of item types from the ItemType table.

The Fields tab in the figure above (the Visualizations panel) shows how the content of the table is defined. The two selected fields show up under Values. Each field in the Values section of the Fields tab (Visualization panel) is represented as a column in the table. You can easily add columns, delete columns, or rearrange the order of the columns, among other things. The resulting table (on the canvas) is shown in the figure below.

Both column names are the same as the field names you have selected. The “number of item types counts how many times an artist occurs in the AbleToCarve table—i.e., how many different types of kachina dolls the artist can carve. Totals are added at the bottom of the table by default. In this case, the 26 at the bottom refers to the total number of different item types and is therefore not the same as the sum of all numbers in the “Number of item types column.” Make sure that the table (canvas) is active, and then click the Format button in the Visualizations panel. As shown in the figure below, a number of formatting options, specific to tables, appear.

Our goal is to transform the raw table into the formatted table below. This exercise will teach you about some of the basic formatting options available for tables.

First, change the text size of the data in the table to 16 and change Totals to Off.

Next, turn on the horizontal grid to show lines between the different data rows:

Next, change the formatting of the column headers. Change the background to black and use white as the color for the text.

Then, add a border to the table

Finally, we want to sort the “Number of item types in descending order. Doing so will help with understanding who the most diverse artists (vendors) are—the ones who are able to carve many different types of kachina dolls. Click on the table on the canvas to make the table active. Then click on the triangle pointing down for the “Number of Item Types” column as shown below:

Make sure you take screenshots of your work and add them to your Word document.

Appendix D – Creating Stacked Bar Chart Dashboards

A bar chart shows how different categories relate to one another using a numeric field that is represented as a bar. A stacked bar chart uses multiple fields to compare the categories. How to Create a Bar Chart You can create a bar chart by clicking on the stacked bar chart icon in the Visualizations panel (see). But don’t click!

Instead of creating a new bar chart lets transform the table you just created into a bar chart. Transformations from one type of visualization to another type are common practice. Go to the “ASSIGNMENT TABLE: NUMBER OF ARTISTS PER ITEM TYPE” _ab, right-click, and select “Duplicate page.” _To replace the table with a bar chart, click on the Stacked bar chart icon in the Visualizations panel. In its raw format (i.e., without formatting), the bar chart will look as follows:

Adding Data to a Stacked Bar Chart Given that we are using the same data set for the definition of two different visualizations—a table and a stacked bar chart—we will next compare the two visualizations content definitions (see figure below). The left side shows the content definition for the table, while the right side shows the content definition for the stacked bar chart. The two fields involved— “Name” and “Number of artists”—are shaped in different ways in the Fields button. For the table, both fields are found under Values and represent two columns. For the bar chart, the field under Axis is used to show different categories on the Y-axis (name). The field under Value is used for comparison purposes; the length of

the bar is defined by “Number of artists” (X-axis). Dragging two or more fields into the Values section results in a “stacked” bar chart. The first figure is table definition and the second table is Stacked Bar Chart definition.

Formatting Bar Charts Formatting bar charts is similar to the formatting of cards and tables, discussed above. The following are a few formatting options specific to bar charts. As shown in figure 4.6–4 below, the formatting of both axes is straightforward. Among others, the Y-axis can be repositioned (left versus right), and the title (name) can be reformatted; for example, the color can be changed.

Also, as illustrated in the figure below, by turning the data label on, the actual numbers representing the length of each of the bars (i.e., the number of artists) are shown. The numbers can then be formatted further, by color, text size, etc.

Make sure you take screenshots of your work and add them to your Word document.

Appendix E – Creating Slicers Dashboards How to Create a Slicer Let’s use the assignment where we created a table. (4.5–A1) as a starting point again here. Go to the Assignment table: “Number of artists per item type” tab and duplicate it right-click and select duplicate page. Then rename it SLICER. Next, we will add a slicer. Create one by clicking on the slicer icon in the Visualizations panel. See below. Creating Slicer

An empty slicer will appear on the canvas (see figure below). Make sure the visualization is active. The selection of fields and formatting will always apply to active visualizations.

Adding Data to a Slicer Slicers are used to dynamically select a more focused data set, such as all sales transactions for a specific customer or all sales transactions for a specific region. The dashboard we are now creating makes use of two visualizations to demonstrate how slicers work. The first visualization, the slicer itself, allows us to select a state or states. The second visualization, a table (the focused data set), shows us the product types (items) and the number of different artists available per product (item) type. This is the visualization you created for your assignment in section 4.5 (4.5–A1). It currently should be on your canvas, since you duplicated the “ASSIGNMENT TABLE: NUMBER OF ARTISTS PER ITEM TYPE” page.

We will start with the content definition of the slicer. Click on the box preceding STATE in the Fields panel. The Field section of the Fields button in the Visualizations panel then shows which field will be used to instantiate the slicer in the canvas (STATE).

The result is the slicer shown in the figure below. The figure shows all the states in which vendors (artists) whom KaDo does business with live. No duplicates are shown.

Slicers are interactive visualizations that allow you to dynamically create focused data sets. As mentioned above, they always interact with other visualizations. At this point, your canvas should look as shown in the figure below. The right side shows the slicer. The left side shows the focused data set, of which the content dynamically changes, depending on the selections made in the slicer. The two visualizations thus interact. We are only using one slicer in this case, but you can include multiple slicers in the same dashboard if you wish. Test it out! Click on any combination of states to obtain a more focused data set. For example, to better understand the offerings in the mid-Atlantic states, click on DE, MD, and PA. The left side in figure below shows the focused data set for the mid-Atlantic area.

Formatting Slicers Similarly, to other visualizations, slicers have their own unique set of formatting options. Next, we will use some of these options to transform the dashboard we prepared into the dashboard shown below.

First, according to our previous discussion, add a text box to your dashboard. Second, change the appearance of the data in the slicer—both font color and text size—as shown in the figure 4.7 below shows. Refer to the discussion in the section “preparing a table” to help you format the table.

Third, draw a border around the slicer. Figure 4.9–10 shows how to do this. Do the same for the text box.

Finally, we need to add a rectangular box around the table. Choose Home→Shapes→Rectangle, as shown in the figure below.

Make sure you take screenshots of your work and add them to your Word document.

Appendix F – Creating Map Charts Dashboards Maps Power BI supports three types of maps: 1) bubble maps, 2) filled maps, and 3) ArcGIS maps. A bubble map represents specific geographic points. A filled map represents regions such as states. ArcGIS maps were more recently added and support the definition of sophisticated maps for advanced spatial analysis. We will limit our discussion here to bubble and filled maps. Maps have several uses, including: 1. the display of relative proportions across locations using different colors and color shades, and

2. as an interactive tool for geographical analysis. Both uses will be illustrated in this section. We will create a new dashboard. Name your tab Maps and add both a filled map and a bubble map.

Rearrange your canvas so that it looks like the following:

Adding Data to Maps The most important part of creating maps is having data available that will enable the accurate identification of geographic locations.12 Power BI relies on Bing Map Services.13 For this exercise, we have created two such fields: state (filled map) and address (bubble map). Let’s start with the content definition of the filled map. First, click the State field, which will go under Location in the Fields tab (Visualizations panel). This will enable geographic analysis at the state level.

At this point, your filled map (upper-left corner of your canvas) should look like the one shown below. All states that have artists with whom KaDo works are shaded.

Next, we will define the content of the bubble map. As shown below, use the Address field in the Artist table to instantiate the bubble map.

At this point, your canvas should look as follows:

Make sure you take screenshots of your work and add them to your Word document.