Creating a dashboard with drill-down into a time series chart with SAP IT Operations Analytics
Note: The tutorial within this blog post will build up on a previous blog post, so make sure you have read and implement this one first: https://blogs.sap.com/2017/08/02/creating-a-simple-time-series-chart-with-sap-it-operations-analytics/
What is a drill-down?
In the previous blog post, we have built a simple dashboard with SAP IT Operations Analytics’ (SAP ITOA) Analytics Builder showing the number of errors by host over time. It would only be natural to drill down into one of these data points to understand what these error for a particular host are, wouldn’t it? So, the following pictures are giving you an example of what such a simple drill-down in a simple dashboard could look and behave like:
This is what we are referring to when we talk about drill-down or interactive dashboards. Such dashboards allow for a number of different use cases:
- Drilling-down on a large data set by incrementally enhancing the filter is one way of running a root-cause-analysis. You would start with a large data set visualized in a way that allows for identifying suspicious data points, anomalies etc., and then you would focus on these data points only. And so on and on, until you are looking at a small enough data set that would yield the actual root cause.
- Adding more information to or enriching selected subsets of data, e.g. adding master data to event data. Imagine you are having a CMDB holding more information about your assets. In our example above, e.g. by selecting one of the hosts, you can render more information about this host.
The number of drill-down steps is not technically limited. For this tutorial, however, we have chosen to keep it simple. The approach for subsequent drill-downs would be equal.
The start of our tutorial will be the results of our previous blog post on creating time series charts. Again, you can follow this tutorial also by drawing on your own data set and adapting the steps accordingly. This tutorial, however, was built using the data set that comes with the Cloud Appliance Library (CAL) image for SAP IT Operations Analytics 2.0 SP03.
Having completed the previous tutorial, we are now looking at this: one Timerange block and one Chart Container block. The former lets end users select a specific range of time to visualize and analyze events, and the latter allows for creation of various charts using the data that is prepared by the nested Select block.
Step 1: Planning your drill-down scenario
This might come across as an obvious or maybe even superfluous step for some of us. But experience has shown that this step is crucial, esp. with large and complex data sets. Pause for a moment and ponder on the following questions:
- Who are my users and what will be a typical use case for them when using this story?
- What will be the information need of these users when drilling down into one of the data points rendered in the chart?
- Where will I get this data from to satisfy this information need?
The first two questions will help us identify which UI elements we will need to add to the story and how to structure them, and the last question will help us identify the tables or views and the attributes we will need to query for the drill-down. The latter might even indicate that we might need to query information from different tables or views, create joins over multiple tables etc.
For this tutorial, we will keep it simple and we will try to implement the following use case:
“As an IT operator looking at the number of errors by host over time, I would like to select a group of errors and understand what those are all about by inspecting their error status and error details.”
For this use case, rendering the information in a table will be sufficient and (in the case of our CAL image demo data set) all attributes required to convey this information are being held in the same table.
Step 2: Adding a table
Building up on what we had before, we are now dragging a Table block from the palette on the left and drop it inside the existing ITOA Grid block. Similarly to the Chart Container block before, we now need to specify which data is supposed to be rendered in the table by adding another Select block.
One way of doing so would be to drag and drop the individual blocks from the palette and building the Select block structure from scratch. In our case, however, we are going to implement a drill-down into the same table, i.e. we can reuse the previous Select block to a large extent, and we only need to amend the Where-clause and adapt the return columns.
To do so, right-click on the existing Select block in the Chart Container block and select Duplicate. Then drag the duplicated block structure and drop it on the Data socket of the newly added Table block.
Step 3: Amending the WHERE clause
The current Where-clause is filtering for the time range defined in the Timerange selector. This condition still holds true. I.e. we need to add additional criteria by concatenating the additional conditions with additional “And” blocks.
We will need to add three more conditions in our case:
- The error status must not be null, because we are showing the number of errors (count of error status) in the chart and we would like to drill down into these errors. This is partially owned to the data set and data structure we are working with.
- The timestamp or rounded timestamp of the events we are querying must be the same as the ones selected in the chart.
- The host name must be the same as the one selected in the chart.
Hint: For your own use cases, always have a look at the return columns of the charts you are selecting data points to drill-down into, but also make sure which of these columns are actually chosen for display in the GUI.
Filtering for non-null error statuses
To filter for events where the error status is not null, we can simply use one of the functions offered in the palette. I.e. let’s add the “is not null” function to one of the open sockets of an “And” block, and add the column “Events.ERROR_STATUS” to it.
Filtering for the same host
To filter for events with the same host, we can simply copy the approach for filtering for time range selection. I.e. we will need a logical “=” comparator block, and we will add the “Events.HOSTNAME” column to one side of it and a String binding block drawing on the selected host of the previous chart to the other side.
Filtering for the (rounded) time stamp
This is now the part where we need to be careful! The intuitive way would have been to simply add a condition similar to this one:
Events.HOSTNAME = Timestamp binding(Chart.Time Stamp)
But this would not give us correct results in our case here. The reason for this is that we are using the Smart Series Round function in the chart above. I.e. the selected time stamp will be a rounded one and it might not exist in all or even not any of the rows in the database table we are querying. Hence, we need to apply the Smart Series Round function with the exact same parameters to the other side of our logical condition as well.
Note: If you use this Smart Series Round function, you need to use it consistently throughout your drill-down. Vice versa, if you do not make use of this function, it will be sufficient to put the condition as suggested in the pseudo code above.
The resulting story definition could look like this:
Note: The order of the conditions does not matter in this case, since we are concatenating the conditions with AND in all of the cases. You only need to be careful if you are mixing AND and OR statements in your overall condition.
Step 4: Defining the columns in your table
As a last step, we will define the columns to display in our table. Having worked with the Analytics Builder a lot by now, you should already be able to guess how to do this.
Yes, you guessed right. You simply do so by defining the Column sockets of your Select statement. The aliases we are defining for the columns will be used as the column headers in the table in the GUI. If you do not use aliases, the GUI will display the name of the corresponding database column.