Creating a Cognos Analytics Dashboard

Sample data

On the website https://ourworldindata.org/coronavirus I found a data set that offers a wide range of figures for about 200 countries. It seems that the cloud version of Cognos Analytics does not support a direct connection to this Excel file, so you have to download it first.

Upload the xlsx-file

Uploading a file is really straightforward: press the New button and follow the instructions to upload the file.

Create a Data Module

A data module is the place where you can set properties and labels for your uploaded data, make transformations and/or joins to other files or database tables. In my example, I will only change some properties and labels, and add a couple of calculated values.
The data module can be created by pressing the New button again. Select the uploaded xlsx-file as the source for this data module.

Next, set some properties for the location column:

Select the location column, set the label to “Country”, and set the “Represents” properties. This way, Cognos Analytics knows what to do with the values in this column when used in a geographical map.
Set labels for the columns total_cases, new_cases, total_deaths, new_deaths, median_age and change the “Aggregate” property to Maximum for Total cases and Total deaths. These columns contain cumulative values per day, so a Sum of these columns gives the wrong result.
Save the data module by pressing the Save icon on the left top of the screen. When saving the data module, you can choose to save it in a personal folder or in a “Team” folder. For this sample dashboard it does not matter where you save it, as long as you can find it back.

Next, I want to add calculations to the data module. Press the three dots to the right of the data module and choose Calculation.

I want to calculate the number of tests performed that lead to one new COVID-19 case. This will give an idea on what scale people are tested in a country. Since the number of tests are not always supplied on a daily basis, I will use the total_tests value. Since this is a cumulative figure, I will apply the maximum function to avoid double counting. The calculation expression will be: maximum (Sheet1.total_tests) / maximum( Sheet1.total_cases)
Give a meaningful name for the calculation.

Next, I will create my own “per million” figures, so I can apply a weighted average to them:

Total cases per million =
maximum(Sheet1.total_cases)/(maximum(Sheet1.population)/1000000)
Total deaths per million =
maximum(Sheet1.total_deaths)/(maximum(Sheet1.population)/1000000)

Save the data module again. We are now finished with data preparation.

Create the dashboard

On the bottom left of the menu, press the New button (or the + icon when the menu is minimized) and choose Dashboard. For this sample, I just chose the tabbed template with no columns or header bar.

Press the OK button.
Select the data module by pressing the “Select a source” button.
You can now put visuals on the tab page. When dragging the Country field to the tab page, it will automatically create a map visualization.

When you add the “New cases” field, it will immediately give countries with the most COVID-19 cases a dark blue color.
If you drag individual measures on the tab page, they will automatically be visualized as KPI cards. You can format the displayed value by pressing the 3 dots to the right of the field name.

When you want to change the default visualization, you can change it in the toolbar at the top of the page.

You can add a title to a visualization by clicking the “T” icon in the toolbar:

Save the dashboard by clicking the save icon at the top left of the page, and click the pencil icon (which works as a toggle) to leave the edit-mode.

You can now test the dashboard by clicking, for example, on China:

See how the visualizations now show the country-specific figures? Click on Brazil and look at the differences in visuals and figures:

Refreshing the data

The data set on the Our World in Data website refreshes daily. You can refresh the data in the dashboard by downloading the new xlsx-file and then uploading it into your dashboard.
Cognos Analytics will ask you what to do with the file:

After a while (I think the data is cached somewhere), you will see the new figures in the dashboard.

About the author Peter de Boer

Peter de Boer is a consultant at Qualogy.

More posts by Peter de Boer