DATA VISUALIZATION LAB Semester V

Course Code BAIL504 CIE Marks 50

Teaching Hours/Week (L:T:P: S) 0:0:2:0 SEE Marks 50
Credits 01 Exam Hours 100
Examination type (SEE) Practical

Sl.N Experiments
O
1 Getting Started - Tableau Workspace, Tableau terminologies, basic functionalities.
2 Connecting to Data Source – Connecting to Database, Different types of Tableau Joins
Creating a View - formatting charts, adding filters, creating calculated fields and
3
defining parameters.
Dashboard Design and Storytelling – Components of Dashboard, Understanding how
4
to place worksheets in Containers, Action filters and its types.
Introducing Power BI –Components and the flow of work. Power BI Desktop
5
Interface-The Report has five main areas.
Querying Data from CSV - Query Editor, Connecting the data from the Excel Source,
6
Clean, Transform the data
Creating Reports & Visualizations - Different types of charts, Formatting charts with
7
Title, Colors.
8 Dashboards - Filters in Power BI, Formatting dashboards.
Analysis of revenue in sales dataset:
i) Create a choropleth map (fill the map) to spot the special trends to show the state
which has the highest
revenue.
ii) Create a line chart to show the revenue based on the month of the year.
iii) Create a bin of size 10 for the age measure to create a new dimension to show the
revenue.
iv) Create a donut chart view to show the percentage of revenue per region by creating
9 zero access in the
calculated field.
v) Create a butterfly chart by reversing the bar chart to compare female & male revenue
based on product
category.
vi) Create a calculated field to show the average revenue per state & display profitable
& non-profitable
state.
vii) Build a dashboard
10 Analysis of GDP dataset:
i) Visualize the countries data given in the dataset with respect to latitude and longitude
 along with
country name using symbol maps.
ii) Create a bar graph to compare GDP of Belgium between 2006 – 2026.
iii) Using pie chart, visualize the GDP of India, Nepal, Romania, South Asia, Singapore
by the year 2010.
iv) Visualize the countries Bhutan & Costa Rica competing in terms of GDP.
v) Create a scatter plot or circle views of GDP of Mexico, Algeria, Fiji, Estonia from
2004 to 2006.
vi) Build an interactive dashboard
Analysis of HR Dataset:
i)Create KPI to show employee count, attrition count, attrition rate, attrition count,
active employees, and
average age.
ii) Create a Lollipop Chart to show the attrition rate based on gender category.
iii) Create a pie chart to show the attrition percentage based on Department Category-
Drag department
into colours and change automatic to pie. Entire view, Drag attrition count to angle.
Label attrition count,
11
change to percent, add total also, edit label.
iv) Create a bar chart to display the number of employees by Age group,
v) Create a highlight table to show the Job Satisfaction Rating for each job role based on
employee count.
vi) Create a horizontal bar chart to show the attrition count for each Education field
Education field wise
attrition – drag education field to rows, sum attrition count to col,
vii) Create multiple donut chart to show the Attrition Rate by Gender for different Age
group.
Analysis of Amazon Prime Dataset:
i) Create a Donut chart to show the percentage of movie and tv shows
ii) Create a area chart to shows by release year and type
12 iii) Create a horizontal bar chart to show Top 10 genre
iv) Create a map to display total shows by country
v) Create a text sheet to show the description of any movie/movies.
vi) Build an interactive Dashboard.
Program: 1.
Getting Started - Tableau Workspace, Tableau terminologies, basic functionalities .

Step: 1. Download Tableau Desktop Application.

After Downloading you will see the following screen box.

 Simply accept the license agreement then will move to home screen of tableau.

 Next we move into the main tableau workspace you can see below screen box.
Tableau Terminology:
1 Alias
An alternative name that you can assign to a field or to a dimension member.
2 Bin
A user-defined grouping of measures in the data source.
3 Bookmark
A .tbm file in the Bookmarks folder in the Tableau repository that contains a single worksheet.
Much like web browser bookmarks, .tbm files are a convenient way to quickly display different
analyses.
4 Calculated Field
A new field that you create by using a formula to modify the existing fields in your data source.
5 Crosstab
A text table view. Use text tables to display the numbers associated with dimension members.
6 Dashboard
A combination of several views arranged on a single page. Use dashboards to compare and
monitor a variety of data simultaneously.
7 Data Pane
A pane on the left side of the workbook that displays the fields of the data sources to which
Tableau is connected. The fields are divided into dimensions and measures. The data pane also
displays custom fields such as calculations, binned fields, and groups. You build views of your
data by dragging fields from the data pane onto the various shelves that are a part of every
worksheet.
8 Data Source Page
A page where you can set up your data source. The data source page generally consists of four
main areas − left pane, join area, preview area, and metadata area.
9 Dimension
A field of categorical data. Dimensions typically hold discrete data such as hierarchies and
members that cannot be aggregated. Examples of dimensions include dates, customer names, and
customer segments.
10 Extract
A saved subset of a data source that you can use to improve performance and analyze offline.
You can create an extract by defining filters and limits that include the data you want in the
extract.
11 Filters Shelf
A shelf on the left of the workbook that you can use to exclude data from a view by filtering it
using measures and dimensions.
12 Format Pane
A pane that contains formatting settings that control the entire worksheet, as well as individual
fields in the view. When open, the Format pane appears on the left side of the workbook.
13 Level Of Detail (LOD) Expression
A syntax that supports aggregation at dimensionalities other than the view level. With the level
of detail expressions, you can attach one or more dimensions to any aggregate expression.
14 MarksA part of the view that visually represents one or more rows in a data source. A mark
can be, for example, a bar, line, or square. You can control the type, color, and size of marks.

15 Marks Card
A card to the left of the view, where you can drag fields to control mark properties such as type,
color, size, shape, label, tooltip, and detail.
Program: 2.
Connecting to Data Source – Connecting to Database, Different types of Tableau
Joins.

Connecting to Database:
To connect the database or data source we first go to the tableau home page and at the left side of
page will see connection pane. There are list of data source types example file type, server type,
Saved server types.

Take the any file here we just use the excel file which is inbuilt one by tableau .
Once we select the excel file we will get the data connect with tableau than tables of the data
displays. In this one we have three tables Orders,People,Returns.

Next step is drag the any table on to the canvas then it will show the detail information of the
table automatically lets see now here

In this example I just brought people & order tables in canvas view it showing all the
information of people table.
 After getting the information go to sheet field then you will get the contents to visualize the data
through charts,maps etc.
To draw charts or maps its easy by just clicking one dimension and measure in columns and
rows from tables list respectively which is located left side of page.
Dimension is “state and measure is “profit” so tableau made it easy for us regarding analysis of
data through charts as we can see in above figure.

Types of Tableau Joins:

Lets begin joining the data in tableau

Step 1 :Create simple data and connect it with tableau These can be in the same data source (such
as tables in a database or sheets in an Excel spreadsheet) or different data sources (this is known
as a cross-database join).
Step 2:Drag the first table to the canvas.
Step 3. Select Open from the menu or double-click the first table to open the join canvas (physical layer).

Step 4: Double-click or drag another table to the join canvas.

Program: 3.
Creating a View formatting charts, adding filters, creating calculated fields and
defining parameters.
Step-1 Create a view

Figure-1 apply data to canvas

Figure-2 select the any field from data to represent view ex:(order date—column).
Figure-3 visualization of the data using graphs in view .

Step 2: Modify your view.

 For example lets show quarter in addition to years lets see how view changes to become
nested tables.

Figure 4: allowing modification in any column field

Note: Just click on the plus button on order date field will convert into quarters.
Step 3: create small multiple views.

 Just add the segment dimension field to profit field in views.

Figure 5: Updating new dimension in the view.

Step 6: Now we can add filters.

Figure 6: Adding Filter options to the view.

Here you just select the order field and drop into filter field.
Later you will get list of options after that select the year level.

Figure 7: Modifying the filters data.

Here you can select the year accordingly .

Figure 8: After modifying the segment data through filters.

Figure 9: Using colors to the view

 Just add the region field to color field will give depth analysis of data source which you
are looking for. You can see in above chart.

Step 4: Creating simple calculated fields.

 select Analysis > Create Calculated Field

 In the Calculation Editor that opens, give the calculated field a name. In this example, the
calculated field is called Profit Ratio.

Figure-9: Adding calculation field and formula.

Program: 4.
Dashboard Design and Storytelling – Components of Dashboard, Understanding
how to place worksheets in Containers, Action filters and its types.

You create a dashboard in much the same way you create a new worksheet.

At the bottom of the workbook, click the New Dashboard icon

Figure 1: Simple dashboard

Step 2: Swapping the sheets in dashboard.

Figure 2: swapping sheets between sheet 1 & sheet2.

Step 3: Add Dashboard Objects

Figure 3: Adding Navigation to dashboard(placing worksheet in container)

Dashboard > Copy Selected Dashboard Item.

To paste selected dashboard item go to File>Paste

Program: 5.
 Introducing Power BI –Components and the flow of work. Power BI
Desktop Interface-The Report has five main areas.

Components and the flow of work:

 Power Query
 Power Pivot
 Power View
 Power Map
 Power Q&A
 Power BI Desktop
 Power BI Website
 Power BI Mobile Apps

Power Query: Power Query is one of the important components of Power BI. This can be
included in your Excel or can be used as a component of the Power BI Desktop. Using Power
Query, you can delete data from numerous data sources and extract data from a wide range of
different databases like Oracle, SQL Server, MySQL, and other different databases. You can also
fetch data from records like text files, CSV files, or Excel files.

Merging in power BI: Go to >Get Data>Select Excel>Select any Sheet>Click on Transform Data
>Select Columns to merge >Go to Transform Tab>Click Merge Columns.
Split the columns: Follow the same process Go to >Get Data>Select Excel>Select any
Sheet>Click on Transform Data> Select Columns to merge >Go to Transform Tab>Click
Split Columns.
Left-Right-Mid Function:
Here we can extract and add the first, last and middle characters using functions.

Power Pivot:
Power Pivot is a data modeling and calculation engine. It is used for modeling simple and
complex data. In Power Pivot, you can set or create relationships between different tables and
calculate values that can be viewed in Pivot tables. It provides you with a huge space to create
your design.

Power Map
Power Map is used to visualize geospatial data in 3D mode. As soon as the visualization renders
in 3D mode, it provides another dimension to it. In Power Map, you can assume one attribute as
the length of a column in 3D and another attribute as a heatmap view. Based on a geographical
location, the data can be highlighted. Geographical locations can be a country, state, city, or
street address.
Power BI Desktop
Power BI Desktop is a new component in the Power BI suit. It is an integrated development tool
for Power Query, Power Pivot, and Power View. It lets you build advanced queries, models, and
reports. With Power BI Desktop, it is easy to advance your skills in BI and your experience in
data analytics.

Power BI Website
The solutions of Power BI can be published on the Power BI Website. You can create
dashboards in Power BI for your reports and share them with others, and you can also create
reports directly on the Power BI Website. With Power BI Website, you can perform slice and
dice operations on data online using a web browser, and it doesn’t even require any other tool.

Power BI Mobile Apps

Power BI Mobile Apps have three mobile operating systems (OS) providers: Android, iOS, and
Windows. These applications provide you with an interactive view of reports and dashboards on
the Power BI site. You can even share them from your mobile app. A portion of the reports can
be featured and a note can be composed on it and shared with others.
Program 6:

Querying Data from CSV - Query Editor, Connecting the data from the Excel
Source, Clean, Transform the data.

Step 1: Load the CSV file in power query editor

Fig 6-1: Home screen of power BI

Step:2 Aftter Loading the data select -> Transform data

Fig 6-2: CSV Data in query editor

Step 3:Select any column and perform following actions

o Rename
o Remove Unnecessary Columns
o Filter the data
o Change Data Type

Fig 6-4: Remove Unnecessary Columns

Fig 6-5: Change Data Type(ex: Order date>change type>date to duration).

Step 4: Group Data: To summarize data, go to Transform > Group By and set the fields and
aggregation functions.

Fig 6-6: Grouping the data

Step 5: Add Calculated Columns: Create new columns with formulas by selecting Add Column >
Custom Column.

Step 6: Pivot or Unpivot Data: If needed, transform columns to rows or vice versa using
Transform > Pivot Column or Transform > Unpivot Columns.

Select column>right click>pivot or unpivot the column or row

Program 7: Creating Reports & Visualizations - Different types of charts,
Formatting charts with Title, Colors.
 Get the DataTransform DataSelect the Units Sold column. On the Transform tab,
select Data Type, then select Whole Number. Choose Replace current to change the
column type.

Fig 7-1:steps involved in creating report.

 Select the Segment column. We want to make the segments easier to see in the chart
later, so let’s format the Segment column. On the Transform tab, select Format, then
select UPPERCASE.
  Let's shorten the column name from Month Name to just Month. Double-click the Month
Name column, and rename to just Month.

Fig 7-3: Renaming of existed column(ex: Month name to Month)

 In the Product column, select the dropdown and clear the box next to Montana.We know
the Montana product was discontinued last month, so we want to filter this data from our
report to avoid confusion.
Fig 7-4:Filter the data from report

 Back on the Home tab, select Close & Apply. Our data is almost ready for building a
report. You see the Sigma symbol in the Data list? Power BI has detected
that those fields are numeric. Power BI also indicates the date field
with a calendar symbol.
Fig 7-5: All calculation fields highlighted in yellow color.

 Writing measures and creating tables in the DAX formula language is super powerful for
data modeling. There's lots to learn about DAX in the Power BI documentation. For now,
let's write a basic expression to create a new measure, and another expression to create a
whole table.
 On the Home ribbon, select New measure.
 Type this expression to add all the numbers in the Units Sold column.

Create Table:

Table View iconNew TableType this expression to generate a Calendar table of all
dates between January 1, 2013, and December 31, 2014.

 Calendar = CALENDAR(DATE(2013,01,01),Date(2014,12,31))
Fig 7-6: new created table

 Now select Model view on the left.

Fig 7-7: Model view

Drag the Date field from the financials table to the Date field in the Calendar table
to join the tables, and create a relationship between them.
Fig 7-8: Connecting relation between calander to financial(ex:Date)

 On the Insert ribbon, select Text Box. Type “Executive Summary – Finance
Report”.
 Select the text you typed. Set the Font Size to 20 and Bold.

Fig 7-9: Heading of report

  you create a line chart to see which month and year had the highest profit.
 From the Data pane, drag the Profit field to a blank area on the report canvas. By default, Power
BI displays a column chart with one column, Profit.
 Drag the Date field to the same visual. If you created a Calendar table in Extra credit: Create a
table in DAX earlier in this article, drag the Date field from your Calendar table instead.

Fig 7-9: profit with year representation

Fig 7-10: Representation of Calnder date Hierarchy

 From the Data pane, drag the Country field to a blank area on your report canvas to create
a map.
  Drag the Profit field to the map.

Fig 7-11: Applying Map to the canvas

Fig 7-12 segmenting data with product,sale,profit.

Format the report:

On the View ribbon, change the theme to Executive.

Program 8: Filters in Power BI, Formatting dashboards.

 Basic Filter: Basic filters show a list of all the values in the field. A filter with the
word All next to it is unfiltered, showing all the values in the field. For example,
Order Date is (All) means the report page includes data about all the OrderDate.

Fig 8.1 Basic Filter

Fig 8.2 Basic filter with Values

Fig 8.3 Basic filter with values

 Advanced filters : let you use more complicated filters. For example, you could search
for values that contain or don't contain, start with or don't start with, a specific value.

Fig 8.4 Advance Filter with value

Setting Filters in Three Level of Report:

 The visual level.

 The page level.
 The report level.

 The visual level : It's the stacked area chart on the Overview page. All the fields in
the visual are in the Visualizations pane. They're also listed in the Filters pane, under
the Filters on this visual heading.

Fig 8.5 Filter with visual level

  The page level :

Fig 8.6 Filter with Single page level

The report level :Filter All Page>Select All>Goto Values>OrderDate(Month)>X_axis(Sum of

unit price)>Y-axis(units)>Legend(Region)
Program 9: Analysis of revenue in sales dataset:
i) Create a choropleth map (fill the map) to spot the special trends to show the state which has
the highest revenue.
ii) Create a line chart to show the revenue based on the month of the year.
iii) Create a bin of size 10 for the age measure to create a new dimension to show the revenue.
iv) Create a donut chart view to show the percentage of revenue per region by creating zero
access in the calculated field.
v) Create a butterfly chart by reversing the bar chart to compare female & male revenue based on
product category.
vi) Create a calculated field to show the average revenue per state & display profitable & non-
profitable state.
vii) Build a dashboard

1. Upload Sales Dataset-> Transform->Apply & Save->Select choropleth Map(Filled Map)-

>Select State & Profit .

Fig 9.1 choropleth Map with state and profit(revenue).

2. Select Line Chart->Select Date(product id/Customer ID) & Profit.
Fig 9.2 Line chart with product id & profit.
3. Create bin size of 10:Select any numerical field>right click>new group

Fig 9.3 Setting bins size.

Fig 9.4 After setting bins size in discount Field.

Create a donut chart:

Fig 9.5 Donut Chart (Region and Profit)

Enter Below formula in Formula section of new Measure
Revenue Percentage =
VAR TotalRevenue = CALCULATE(SUM('YourTable'[Revenue]), ALL('YourTable'))
VAR RegionRevenue = SUM('YourTable'[Revenue])
RETURN
IF(TotalRevenue = 0, 0, RegionRevenue / TotalRevenue)

4. Create a butterfly chart by reversing the bar chart to compare female & male revenue
based on product category.
Get Data -> New ExcelFile -> Transform Data ->Apply & Save ->
Select Stacked Bar Chart ->Add Appropriate Values to X-axis and Y-axis(Female & Male
Revenue respectively)

Fig 9.6 Butterfly Chart

Create Negative Female Value Column

Goto Table Field >>New Column>> Write Formula(Female Revenue Negative = -[Female
Revenue])