Introduction

NOTE: Hierarchies + expand/collapse are available from daily build 2020.3.b21005, and will also be available in the next rock build.

A hierarchy in Omniscope is a logical, ordered breakdown of data. When configured inside a view, hierarchies can be expanded or collapsed, providing the ability to dynamically modify which level of the hierarchy is applied at any one time.

Expanding or collapsing a hierarchy improves your ability to analyse the data, allowing you to change the perspective of a view, switching from a birds-eye overview to a microscopic scrutiny as you see fit.

Omniscope supports two types of hierarchies:

• Field hierarchy. You can create any number of custom field hierarchies within your report. In a field hierarchy you decide which fields belong to that hierarchy and the order of those fields based on your knowledge of the relationships that exist within the data. An example of a field hierarchy we might create is: Department, Team, Sub-team. A view configured with this hierarchy would allow a user to breakdown the data by Department, Department/Team or Department/Team/Sub-team, expanding or collapsing the different levels as required.
  
  
• Implicit hierarchy. Implicit hierarchies are created on-the-fly within your reports for Date and Number fields. Each level of the hierarchy breaks the data down by the next logical interval. For example an Implicit hierarchy for a Date field would be Year, Quarter, Month, Day, Hour, Minute and Second.

In this article we will show you how to create and utilise hierarchies in Omniscope using an example data set.

Setup

For this example we are going to download a data-set from Kaggle:

https://www.kaggle.com/sudalairajkumar/daily-temperature-of-major-cities

This data-set contains daily temperature data from major cities around the world between 1995 and 2020. It is a 13MB Zip file containing a CSV file with approximately 3 million records. 

If you haven't done already, setup a new Kaggle account, then download the and unzip the CSV file.

Start Omniscope and create a new Omniscope file. Drag a file block into the workflow. Click on the file block and browse to the city_temperatures.csv file we previously downloaded in the block options. Now execute the block by clicking on the Play button and click on the Data tab.

An examination of the data shows we have 8 fields and 2.9 million records. Each record represents the temperature in a city on a single day.

Before we start creating our report, let's do a bit of cleanup. 

If we further examine the data we can see that we have separate fields for Month, Day and Year. We would like to create a single Date field combining all 3 values together.

Add a Field organiser block to your workflow and connect the file block to the field organiser block. Click on the Field organiser block to open the options and click the Add (+) field button to add a new field. Rename the field to Date and change the type to Date. Now we need to configure a formula to tell Omniscope how to populate the data inside this field. Click on the fx button and enter the following formula: 

DATE([Year], [Month], [Day])

The data preview at the bottom of the dialog should confirm that we have entered the formula correctly.

Now click OK.

Finally, we want to change the date format so only the year/month/day is visible. Click on the field options button and set the output format to:

dd/MM/yyyy

Note: Case is very important when configuring a date format. Ensure that you use the same case as described above.

Now we are ready to start creating our report. Add a Blank report block to the workflow and connect the Field organiser block to the report block. Click on the report block to get started!

Creating a field hierarchy

Let's start by creating a new field hierarchy containing the fields Region, Country, State and City.

Click the 3-dots button in the top right of the report and select Report data sources.... Click on the HIERARCHIES tab, then click on the Add hierarchy button. Select the first field in our hierarchy, in this case Region.

Now let's add the remaining fields to the hierarchy. Click the Add field button and add Country, State and City. If we make a mistake we can re-order or remove fields.

Note: The order of the fields is very important when creating a field hierarchy. The order defines the levels of the hierarchy, and the expansion/collapse sequence.

Our field hierarchy is now ready to go! Close the dialog, and we can now start configuring our report. Hierarchies in Omniscope are currently supported when views are paning or in the Pivot view. In the next section we will use the field hierarchy we created above, and an implicit Date hierarchy inside a Pivot view.

Pivot view setup

Add a Pivot view to your report. Change the Y-axis split to Date, the X-axis split to Region and the measure to AvgTemperature (Mean). 

Your Pivot view now shows mean average temperature data broken down by Year in the y-axis and Region in the x-axis. 

You may notice that both the Date split pill and the Region split pill both have a small "+" button next to the field name. The "+" button indicates a the pill is part of a hierarchy. Clicking the button will add a new split pill configured with the next level of the hierarchy. The hierarchy associated with the Region split pill is the field hierarchy we created earlier. The hierarchy associated with the Date split pill is an implicit Date hierarchy.

Click on the "+" button inside the Region split pill to add the next level of our field hierarchy. In our field hierarchy the next field after Region is Country, so you should see a new Country split pill added to the split levels. Continue to click the "+" button until you have added all levels of the hierarchy: Region, Country State and City.

In the y-axis the Date split pill is currently configured to breakdown the data into 1 year intervals. Omniscope detects that this is part of an implicit Date hierarchy. Click on the '+' button to add the next level of the hierarchy sequence. The next level after Year is Quarter. Continue to add split pills until we have added the following levels: 1-year, 1-quarter, 1-month and 1-day.

Now we have our pivot view configured with a hierarchy on the X and Y axes. How can hierarchies help an end-user to understand and navigate the data? The next section discusses expanding/collapsing splits as a way to facilitate this.

Expanding/collapsing splits 

In the paning framework and the pivot view you can configure the ability for users to expand/collapse the split levels on either axis.

You may have noticed that the Pivot view we configured in the previous step has a collapse (-) buttons on both the X and Y axes.

Click the collapse button (-) in the x-axis to remove the last split level (City) from that axis. You should now see an expand (+) and and a collapse (-) button, allowing us to either add or remove splits. Lets click the collapse button (-) once again to remove State. Now lets do the same on the y-axis. Click the collapse button (-) twice. Only the Year and Quarter splits should be visible.

A user interacting with your report can expand or collapse the split levels to explore the data. But what if I don't want to allow this, or what if I want to allow expand/collapse when I don't have a hierarchy of splits? Omniscope provides you with the ability to decide how expand/collapse will work when interacting with the view.

In the Pivot view open the View options and expand the Chart section. 

The Pane X expand/collapse and Pane Y expand/collapse options control the expansion behaviour for the X and Y axes respectively. Select one of the following:

• None. Expand/collapse will not be available.
• All levels. All the split levels will be expandable/collapsible as one, using a single pair of +/- buttons.
• Hierarchies only. This is the default choice. Each field hierarchy explicitly configured in the data source and each implicit date or number hierarchy will be independently expandable using a +/- pair of buttons. If there are no hierarchies, expand/collapse will not be available.
• Hierarchies and non-hierarchies. Each hierarchy and each non-hierarchy sequence of levels will be independently expandable using a +/- pair of buttons.

Future enhancements

We are excited about the potential of Field hierarchies and expand/collapse to enhance your views, and are planning to develop the functionality further. We intend to add support for expanding/collapsing inside views that support more than one split, for example within the Bar, Tile and network views. We also intend to add support for drill-down in views such as the Bar view. 

We would love to hear from you, so please let us know if you have any questions, comments or feedback.